Open In App

How to Fill Background Color of Cells in Excel using Java and Apache POI?

Improve
Improve
Like Article
Like
Save
Share
Report

Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs for the job. In this article, we are going to discuss how to fill the background colors and foreground colors of the cells in Excel using the Apache POI. First, we will create an Excel file and enter the cell values and add the colors for each cell. Apache POI is providing the createcellstyle method in XSSFCellStyle.

XSSFCellStyle style=workbook.createCellStyle();

Pre-Requisite

To work with the Apache POI, we need the following software in our system

  • Check that your system has Java JDK 
  • Install the Eclipse IDE for Java developers from here.
  • Create a Maven project

For creating a maven project Check this How to Create a Selenium Maven Project with Eclipse to Open Chrome Browser?

  • Add the Apache POI and Apache-ooxml dependency into the POM.xml file

 

After installing all the dependencies, we are now ready to write the code, it is styled using the creatcellstyle method. The style method provides many designs such as setFillBackgroundColor, setFillForegroundColor and setFillPattern.

setFillgroundColor:

This method enables us to set the background color of the cell, the Apache POI dependency provides us with the Indexed color class that has all the colors.

style.setFillForegroundColor(IndexedColors.”COLOR.”getIndex());

setFillForegroundColor:

This method is also similar to the setBackgroundColor.

style.setFillForegroundColor(IndexedColors.“COLOR”.getIndex());

setFillPattern:

This method provides various types of design patterns like Big spots, Briks, Diamond, Fine Dots, etc.

style.setFillPattern(FillPatternType.DIAMONDS);

Program for fill background and foreground colors in Excel using Apache POI

Java




package GFG_Maven.GFG_MAven;
import org.testng.annotations.Test;
  
import java.io.FileOutputStream;
import java.io.IOException;
  
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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;
  
public class Geeks {
    
    @Test
    public void geekforgeeks() throws IOException{
          
             XSSFWorkbook workbook=new XSSFWorkbook();
            XSSFSheet sheet=workbook.createSheet("sheet1");
              
            XSSFRow row=sheet.createRow(1);
              
            // Background color
            XSSFCellStyle style=workbook.createCellStyle();
            style.setFillBackgroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
            style.setFillPattern(FillPatternType.DIAMONDS);
              
            XSSFCell cell=row.createCell(1);
            cell.setCellValue("welcome");
            cell.setCellStyle(style);            
              
            // foreground color
            style=workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            style.setFillPattern(FillPatternType.FINE_DOTS);
              
            cell=row.createCell(2);
            cell.setCellValue("Geeks");
            cell.setCellStyle(style);            
              
            FileOutputStream file = new FileOutputStream("C:\\Users\\ADMIN\\Desktop\\style.xlsx");
            workbook.write(file);
            file.close();
            System.out.println("Style Created"); 
                   
    }
}


Code Explanation

  • Create a sheet and row for the created sheet.
  • After that create the style for the workbook

XSSFCellStyle style=workbook.createCellStyle();

  • Then style the background and foreground by setFillBackgroundColor and setFillPattern, then give the cell value and cell style.

      cell.setCellValue(“Geeks”);
     cell.setCellStyle(style);

  • After the styles are done now create the File output stream 
  • Write the cell data into the output stream.

Output

Now run the code in the eclipse, we can get the output as style is created and we can notice that an Excel sheet is created in the specified location with the cell styles.

Output

 

Output

 



Last Updated : 12 May, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads