In this tutorial, we will look at how to use the Apache POI to fill the background and foreground colors of cells in Excel. To begin, we will make an Excel file and enter the cell values as well as the colors for each cell. In XSSFCellStyle, Apache POI provides the createcellstyle() method.
XSSFCellStyle style=workbook.createCellStyle();
Pre-requisites:
We required the following software on your computers to work with these.
- Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
- Create a Maven project.
- Add apache poi dependency.
Methods Required
We can style the cells using the creatcellstyle() method. Various styles are available with the style method, including setFillBackgroundColor, setFillForegroundColor, and setFillPattern.
setFillBackgroundColor:
This method allows us to change the background color of the cell; the Apache POI dependency provides us with the Indexed color class, which contains all of the available colors.
style.setFillForegroundColor(IndexedColors."COLOR."getIndex());
setFillForegroundColor:
The setFillForegroundColormethod() is identical/similar to the setBackgroundColor method.
style.setFillForegroundColor(IndexedColors."COLOR".getIndex());
setFillPattern:
The setFillPattern() method provides a variety of design patterns such as Big Spots, Briks, Diamond, Fine Dots, and so on.
style.setFillPattern(FillPatternType.DIAMONDS);
Filling Background Color of Cells in Excel using Java and Apache POI
Here we use STS(Spring Tool Suite) to fill the background color of cells in an excel sheet.
You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.
STS:
Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.
Note:
If you are using MyEclipse then create a maven project.
STS directly imports all the classes from maven if we just add dependencies.
Step#1: Open STS(SpringToolSuite) and create a spring stater project say FillBackgroundColorOfCells in a package com.sheetstips.
Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.
Step#3: Open the pom.xml file and add the following apache poi dependency from the maven repository.
https://mvnrepository.com/
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.12</version> </dependency> <dependency> <groupId>org.apache.poi</ groupId> <artifactId>poi-ooxml</ artifactId> <version>3.12</version> </dependency>
Step#4: Create a new class say FillCellBackground in the src/main/java folder with the same package com.sheetstips.
Step#5: Write the below code in the above FillCellBackground class
Approach:
- Import all the required libraries using the import keyword
- Import apache POI libraries using the import keyword
- Create a class say FillCellBackground
- Inside the Main driver method, Create a new XSSFWorkbook object using the new keyword
- Create a new worksheet in the above workbook object by passing the sheet name as an argument to the createSheet() function(Sheet1 by default)
- Store it in another variable
- Create a new row in the selected sheet using the createRow() function
- Create an object createCellStyle() function to Set the Background color of the cell.
- Fill the background color of the cell using the setFillBackgroundColor() function
- Set the design pattern of the cell using the setFillPattern() function by passing the pattern type as an argument to it.
- Creating cell and adding styles to it.
- Set the foreground color of the cell using the createCellStyle() function.
- Fill the foreground color to the style object by passing some random color as an argument to it.
- Similarly set the background color for the second cell of the above worksheet.
- Create an object for the FileOutputStream by passing the output excel file path as an argument to it to place the output file in that location.
- Write the data which is added in the above steps i.e write the outputFile to the workbook Object using the write() function.
- Close the above-opened output file using the close() function
- Print some random text for acknowledgment.
- The Exit of the Program.
Below is the implementation:
package com.sheetstips;
//Import all the required libraries using the import keyword
import java.io.FileOutputStream;
import java.io.IOException;
//Import apache POI libaries using the import keyword
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;
//Create a class say FillCellBackground
public class FillCellBackground {
// Main driver method
public static void main(String[] args) throws Exception {
// Create a new XSSFWorkbook object using the new keyword
XSSFWorkbook workbookObj = new XSSFWorkbook();
// Create a new worksheet in the above workbook object by passing
// the sheet name as an argument to the createSheet() function(Sheet1 by
// default)
// Store it in another variable
XSSFSheet worksheet = workbookObj.createSheet("Sheet1");
// Create a new row in the selected sheet using the createRow() function
XSSFRow Excelrow = worksheet.createRow(1);
// Create an object createCellStyle() function to Set the Background color of
// the cell
XSSFCellStyle styles = workbookObj.createCellStyle();
// Fill the background color of the cell using the setFillBackgroundColor()
// function
styles.setFillBackgroundColor(IndexedColors.PINK.getIndex());
// Set the design pattern of the cell using the setFillPattern() function by
// passing the pattern type as an argument to it.
styles.setFillPattern(FillPatternType.DIAMONDS);
// Creating cell and adding styles to it
XSSFCell cell = Excelrow.createCell(1);
cell.setCellValue("Welcome");
cell.setCellStyle(styles);
// Set the foreground color of the cell using the createCellStyle() function
styles = workbookObj.createCellStyle();
// Fill the foreground color to the style object by passing some random color as
// an argument to it.
styles.setFillForegroundColor(IndexedColors.GREEN.getIndex());
styles.setFillPattern(FillPatternType.FINE_DOTS);
// Similarly set the background color for the second cell of the above worksheet
cell = Excelrow.createCell(2);
cell.setCellValue("SheetsTips");
cell.setCellStyle(styles);
// Create an object for the FileOutputStream by passing the output excel file
// path as an argument to it to place the output file in that location
FileOutputStream outputfile = new FileOutputStream("FillCellBackground.xlsx");
// Write the data which is added in the above steps
// i.e write the outputFile to the workbook Object using the write() function
workbookObj.write(outputfile);
// Close the above opened outputfile using the close() function
outputfile.close();
// Print some random text for acknowledgment.
System.out.println("Filled Background Color of the cells and Styled Successfully!!");
}
}
Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App
Output:
Filled Background Color of the cells and Styled Successfully!!
