Skip to toolbar

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:


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

Leave a Reply

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

Site Statistics

  • Users online: 0 
  • Visitors today : 7
  • Page views today : 20
  • Total visitors : 49,394
  • Total page view: 68,004

   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.