Java: Set Background Color and Pattern for Excel Cells

When working with an Excel document that contains a lot of data, setting color or pattern for selected cells can make it very easy for users to locate specific types of information. In Microsoft Excel, you can achieve this function by simply clicking the "Fill Color" button on the formatting toolbar. In this article, you will learn how to programmatically set background color and pattern style for a specified cell or cell range in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml 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</artifactId>
        <version>14.11.0</version>
    </dependency>
</dependencies>
    

Set Background Color and Pattern for Excel Cells

The detailed steps are as follows.

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Get a specified cell range using Worksheet.getRange().get() method.
• Set background color for the specified cell range using CellRange.getStyle().setColor() method.
• Set fill pattern style for the specified cell range using CellRange.getStyle().setFillPattern() method.
• Save the result 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 object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

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

        //Set background color for range "A1:E1" and "A2:A10"
        worksheet.getRange().get("A1:E1").getStyle().setColor(Color.green);
        worksheet.getRange().get("A2:A10").getStyle().setColor(Color.yellow);

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

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

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.