Java sets different background colors for adjacent rows in Excel

When creating an Excel table, you can fill two adjacent rows of the data table with different background colors to make the data in each row look clearer, avoid misreading the rows, and improve the aesthetics of the Excel table. .. In this article, I will show you how to set an alternative background color for odd and even rows of Excel in a Java program.

** Tools used: ** Free Spire.XLS for Java (Free version)

** How to import JAR file ** ** Method 1: ** Download and unzip the Free Spire.XLS for Java package and import the Spire.Xls.jar package from the lib folder into your Java application.

** Method 2: ** If you are using maven, you need to add the following dependency to your pom.xml file:

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

** Java code example: **

import com.spire.xls.*;

import java.awt.*;

public class ConditionalFormatting {

    public static void main(String[] args) {

        //Create a workbook object
        Workbook workbook = new Workbook();

        //Import an Excel document
        workbook.loadFromFile("test.xlsx");

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

        //Get the data area
        CellRange dataRange = sheet.getAllocatedRange();

        //Use conditional formatting to set the background color of even rows to light gray
        ConditionalFormatWrapper format1 = dataRange.getConditionalFormats().addCondition();
        format1.setFirstFormula("=MOD(ROW(),2)=0");
        format1.setFormatType(ConditionalFormatType.Formula);
        format1.setBackColor(Color.lightGray);

        //Use conditional formatting to set the background color for odd rows to yellow
        ConditionalFormatWrapper format2 = dataRange.getConditionalFormats().addCondition();
        format2.setFirstFormula("=MOD(ROW(),2)=1");
        format2.setFormatType(ConditionalFormatType.Formula);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("AlternateColor.xlsx", ExcelVersion.Version2016);
    }
}

** Output result: **