Skip to toolbar

Copy formatting & style of cells from one sheet to another.

This blog contains steps to copy Formatting & Style from one Excel sheet to another sheet. Also, shows how to identify the Cell Type.

To implement formatting in the excel sheet while writing data, please visit the below link:

Copy Formatting & Style

Copy Formatting & Style using Java
  • Below are the requirements:
    • inputfile.xlsx file
      • Sheet having list of few Electronic items with below columns
        • S.No | Numeric Data
        • Item Name | String Data
        • Item Code | Numeric Data
        • Quantity | Numeric Data
      • Formatting of Header
        • Background color Tan
        • Text color White
        • All fields are bold
      • Formatting of Item Name column
        • All data is in Italic font.
      • Formatting of the Quantity column
        • Values less than 100 is of Red Color and in Italic font
        • Other values are of Green color

Code Example

  • Steps to copy the data and cell style from one excel sheet to another.
    • Create references for Output File
    • Create references for Input File
      • Navigate throughout all the cells
      • Store cell data in a variable
      • Identify the Cell Type like Numeric, String etc.
      • Identify the Cell Style for each cell
    • Copy data from an input file to Output File
      • Create a new cell style with reference to the input cell
      • Create a new cell type with reference to the input cell
      • Write data, cell style, cell type to the output file
      • Save data to the Output file.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author ashok.kumar
 */
public class CopyFormatting {

	// Creating Output file references.
	static File outputFile=new File("./result/testfile_Result.xlsx");
	static FileOutputStream fos;
	static XSSFWorkbook outputWB;
	static XSSFSheet outputSheet;
	
	public static void main(String[] args) throws IOException {
		
		// Creating Input File References.
		File inputFile=new File("./input/testfile.xlsx");
		FileInputStream fis=new FileInputStream(inputFile);
		XSSFWorkbook inputWB=new XSSFWorkbook(fis);
		
		XSSFSheet inputSheet=inputWB.getSheet("Sheet1");
		
	// Call static method to create Result Excel file to copy the content.
		createNewFile();
		
		// Variable to refer current Row number in the output file.
		int currentRowNum=0;
		
		// Reading the data with formatting from input sheet.
		// Writing the data with formatting to Output Sheet.
		for (Row row : inputSheet)
		{
			Iterator<Cell> cellIterator=row.cellIterator();
			//Reference to current Cell number into Output File.
			int currentCellNum=0;
			
			//Insert new row in the Output File.
			createNewRow(currentRowNum);
			
			// Navigating all the cells in a row.
			while(cellIterator.hasNext())
			{
				Cell currentCell=cellIterator.next();
			
				// Get Data for current cell.
				String cellData=currentCell.toString();
				System.out.println(cellData);
				
		// Get Style for current cell.
             XSSFCellStyle cellStyle=(XSSFCellStyle) currentCell.getCellStyle();
				
		// Get Cell Type for current cell like String, Numeric etc.
			CellType cellType=currentCell.getCellTypeEnum();
				
		// Call static method to write data to Output Sheet.
				writeData(currentRowNum,currentCellNum,cellStyle,cellData,cellType);
				currentCellNum++;
			}
			currentRowNum++;
		}
		
		// Call static method to save all the data in the output file.
		saveFile();
	}
	
	public static void createNewFile() throws FileNotFoundException
	{
		fos=new FileOutputStream(outputFile);
		outputWB=new XSSFWorkbook();
		outputSheet=outputWB.createSheet("Sheet1");
	}
	
	public static void createNewRow(int newRowNum)
	{
		outputSheet.createRow(newRowNum);
	}
	
	@SuppressWarnings("null")
	public static void writeData(int currentRowNum, int currentCellNum, XSSFCellStyle cellStyle, String cellData, CellType cellType)
	{
		// Locating current row in the Output file
		XSSFRow row=outputSheet.getRow(currentRowNum);
		XSSFCell cell = null;
		
		// Verifying the input cell type and write data in the same type
		if(cellType==cellType.STRING)
		{
			cell=row.createCell(currentCellNum,CellType.STRING);
			cell.setCellValue(cellData);
		}
		else if(cellType==cellType.NUMERIC)
		{
			cell=row.createCell(currentCellNum, CellType.NUMERIC);
			cell.setCellValue(cellData);
			System.out.println(cellData+" Numeric");
		}
		else
		{
			System.out.println("Cell Type not found...");
		}
		
		// Creating New Cell Style for Output File.
		XSSFCellStyle newStyle=outputWB.createCellStyle();
		
		// copy existing style for individual cell into Output File.
		newStyle.cloneStyleFrom(cellStyle);
		cell.setCellStyle(newStyle);
		
	}
	
	public static void saveFile() throws IOException
	{
		outputWB.write(fos);
		System.out.println("File saved successfully...");
	}
}

Java Programs:

TestNG:

Computer Basics:

Java Basics:

Java File Handling:

OOPs Concept:

Java Question And Answer:

Selenium Tutorials:

JIRA:

Agile:


2973total visits,4visits today

Leave a Reply

Your email address will not be published. Required fields are marked *

Site Statistics

  • Users online: 0 
  • Visitors today : 2
  • Page views today : 2
  • Total visitors : 48,855
  • Total page view: 67,273

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.