1. Overview
On an Excel sheet, it always looks elegant when we highlight a table header by changing its background color. This article describes how to change the cell background color using Apache POI.
Also, we recommend reading our Working with Microsoft Excel in Java tutorial to learn some of the basics of working with Excel in Java.
2. Maven Dependency
To get started, we need to add poi-ooxml as a dependency in our pom.xml:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
3. Changing Cell Background Color
3.1. About Cell Background
On an excel sheet, we can change the cell background just by filling it with color or with a pattern. In the following image, cell A1 is filled with a light blue background, whereas cell B1 is filled with patterns. This pattern has a black background and light blue colored spots on top of it:
3.2. Code for Changing Background Color
Apache POI provides three methods for changing the background color. In the CellStyle class, we can use the setFillForegroundColor, setFillPattern, and setFillBackgroundColor methods for this purpose. A list of colors is defined in the IndexedColors class. Similarly, a list of patterns is defined in FillPatternType.
Sometimes, the name setFillBackgroundColor may mislead us. But, that method itself is not sufficient for changing cell background. To change cell background by filling with a solid color, we use the setFillForegroundColor and setFillPattern methods. The first method tells what color to fill, while the second one specifies the solid fill pattern to use.
The following snippet is an example method to change cell background as shown on cell A1:
public void changeCellBackgroundColor(Cell cell) {
CellStyle cellStyle = cell.getCellStyle();
if(cellStyle == null) {
cellStyle = cell.getSheet().getWorkbook().createCellStyle();
}
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle);
}
To change a cell background with a pattern, we need to use two colors: one color to fill the whole background, and one to fill a pattern on top of the first color. Here, we need to use all those three methods.
Method setFillBackgroundColor is used here to specify the background color. We don’t get any effect by using only this method. We need to use setFillForegroundColor to select the second color and setFillPattern to state the pattern type.
The following snippet is an example method to change cell background as shown on cell B1:
public void changeCellBackgroundColorWithPattern(Cell cell) {
CellStyle cellStyle = cell.getCellStyle();
if(cellStyle == null) {
cellStyle = cell.getSheet().getWorkbook().createCellStyle();
}
cellStyle.setFillBackgroundColor(IndexedColors.BLACK.index);
cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
cell.setCellStyle(cellStyle);
}
For more code details, look at the entire Java class and related JUnit test cases.
4. Conclusion
In this quick tutorial, we’ve learned how to change the cell background of a cell in an Excel sheet using Apache POI.
Using only three methods – setFillForegroundColor, setFillPattern, and setFillBackgroundColor from the CellStyle class – we can easily change a cell’s background color and fill pattern.
The examples are available over on GitHub.
