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

April 30, 2018 Leave a comment

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:

File Handling | Implement formatting in Excel using Java.

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:
Post Views: 368

1979total visits,1visits today

Leave a Reply

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