File Handling | Implement formatting in Excel using Java.

While writing data through the code we can also implement formatting in Excel sheets.

Applying to format is a very simple process. Here we are taking an example that we have to write a few data into the excel sheet. And then apply format on Heading Row as setting font color WHITE, BOLD and Cell color RED.

To get the understanding that how to create Excel File and writing data in it via JAVA File Handling visit below post:

Formatting in Excel

In this example, we are assuming that we have to create an excel sheet using Java and write pre-defined data into it using the two-dimensional array.

  • Create an Excel sheet at a specified location.
  • Pre-defined data set has 5 Rows and 2 Columns, which needs to be written in the excel sheet.
  • Writing data from inputArray[][] to the new excel sheet.
  • After that, we have Apply Formatting using below steps.
  • Then write all the data to the excel sheet using write() of the XSSFWorkbook Class.

Steps to Apply Formatting

  1. Create object of XSSFCellStyle class using createCellStyle() of XSSFWorkbook object.
  2. Create an object of XSSFColor to get Color. e.g. RED
  3. Add above color to the ‘style’ object using setFillForegroundColor().
  4. Add a pattern to the ‘style’ object for the cell to be filled using setFillPattern().
  5. To apply Font color or Bold need to create an object of Font class using createFont() of XSSFWorkbook object.
  6. Set Font Type Bold using setBold(true).
  7. Set Font Color using setColor(). e.g. WHITE
  8. Add above Font Color to the ‘style’ object using setFont() method.
  9. Embed ‘style’ object to the specific cell using setCellStyle() method.

Code Example

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
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 WriteExcel {

	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		
		// Create an Excel sheet at a specified location.
		File filePath=new File("result//temp.xlsx");
		FileOutputStream fos=new FileOutputStream(filePath);
		XSSFWorkbook workbook =new XSSFWorkbook(); 	
		System.out.println("workbook located");
		XSSFSheet sheet=workbook.createSheet("ResultSheet");
		
		// Pre-defined data set has 5 Rows and 2 Columns, which needs to be written in the excel sheet.
		String[][] inputArray= {{"S.No.","Item Name"},				 
                                         {"001","Television"},								 
                                          {"002","Projector"},
				             {"003","Laptop"},
					     {"004","Printer"}};
		
		System.out.println("Sheet created successfully..");
		for(int i=0;i<5;i++)
		{
		// Writing data from inputArray[][] to the new excel sheet.
			System.out.println("Row "+i+" created...");
			XSSFRow currentRow= sheet.createRow(i);
			currentRow.createCell(0).setCellValue(inputArray[i][0]);
			currentRow.createCell(1).setCellValue(inputArray[i][1]);
			
			if(i==0)
			{
		     System.out.println("Applying colors for Heading.");
				// Step #1: Create object of XSSFCellStyle class using createCellStyle() of XSSFWorkbook object. 
				XSSFCellStyle style=workbook.createCellStyle();
				
				// Step #2: Create object of XSSFColor to get Color.
				XSSFColor color=new XSSFColor(Color.RED);
				
				// Step #3: Add above color to the 'style' object using setFillForegroundColor(). 
				style.setFillForegroundColor(color);
				
				// Step #4: Add a pattern to the 'style' object for the cell to be filled using setFillPattern().
				 
                          style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
				
				// Step #5: To apply Font color or Bold need to create an object of Font class using createFont() of XSSFWorkbook object.
				Font font=workbook.createFont();
				
				// Step #6: Set Font Type Bold.
				font.setBold(true);
				
				// Step #7: Set Font Color using setColor().
				font.setColor(IndexedColors.WHITE.getIndex());
				
				// Step #8: Add above Font Color to the 'style' object using setFont() method.
				style.setFont(font);
				
				// Step #9: Embed 'style' object to the specific cell using setCellStyle() method.
				currentRow.getCell(0).setCellStyle(style);
				currentRow.getCell(1).setCellStyle(style);
			}
		}
		
		workbook.write(fos);
		//wb.close();
	}
}

Result Analysis

Output of the program while Formatting in Excel.

Java File Handling:

Computer Basics:

Java Basics:

TestNG:

OOPs Concept:

Java Question And Answer:

Java Programs:

Selenium Tutorials:

JIRA:

Agile:


293total visits,5visits today

1 thought on “File Handling | Implement formatting in Excel using Java.”

Leave a Reply

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