Java: Set Background Color and Fill Patterns for Excel Cells

In an Excel worksheet, in order to highlight a specific cell or cell range in an Excel worksheet, you can set the background color or fill patterns for it. This article will demonstrate how to programmatically accomplish the operation using Java codes.

BEFORE CODING

First of all, you need to get the product package of Free Spire.XLS for Java from this link, find Spire.Xls.jar in the lib folder and then add it to your Java program. Or if you use Maven, just type the following codes in the pom.xml file to import the JAR file.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>

USING THE CODE 

Free Spire.XLS for Java supports changing background color for a specified cell range or cell using the CellRange.getStyle().setColor() method, and also supports setting the fill pattern style for a cell range using the CellRange.getStyle().setFillPattern() method. The detailed steps are listed below.

l  Create a Workbook instance.

l  Load an Excel sampel document using Workbook.loadFromFile() method.

l  Get a specified worksheet using Workbook.getWorksheets().get() method.

l  Get a specified cell range using Worksheet.getRange().get() method, and set background color for it using CellRange.getStyle().setColor() method.

l  Get a specified cell using Worksheet.getRange().get() method, and set background color for it using CellRange.getStyle().setColor() method.

l  Get a specified cell range and set the fill pattern style for it using CellRange.getStyle().setFillPattern() method.

l  Save the output document to another file using Workbook.saveToFile() method.

import com.spire.xls.*;
import java.awt.*;

public class CellBackground {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel sample document
        workbook.loadFromFile("C:\\Users\\Tina\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet= workbook.getWorksheets().get(0);

        //Set background color for cell range
        worksheet.getRange().get("A1:F1").getStyle().setColor(Color.green);
        worksheet.getRange().get("A2:A9").getStyle().setColor(Color.yellow);

        //Set background color for cell E9
        worksheet.getRange().get("E3").getStyle().setColor(Color.red);

        //Set fill pattern style for range "C4:D5"
        worksheet.getRange().get("C5:D7").getStyle().setFillPattern(ExcelPatternType.Percent25Gray);

        //Save the document
        workbook.saveToFile("output/CellBackground.xlsx", ExcelVersion.Version2013);
    }
}