LS Price Increase Launch

The Price of all “Learn Spring” course packages will increase by $40 on next Friday:

>> GET ACCESS NOW

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>4.1.1</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.

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.

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.

LS Price Increase Launch

The Price of all “Learn Spring” course packages will increase by $40 on next Friday:

>> GET ACCESS NOW
Generic footer banner
guest
0 Comments
Inline Feedbacks
View all comments