Setting Alternate Colors for Excel Rows in Java Application

When manipulating Excel documents, we can make the data display clearer by setting alternate colors for adjacent rows in Excel. Beyond that, setting alternate colors can also prevent us from misreading the rows and increase the aesthetics of the Excel table. This article will demonstrate how to to set alternate colors for Excel rows through conditional formatting using Free Spire.XLS for Java.

Installation

Step 1: Download the Free Spire.XLS for Java and unzip it.

Step 2: Add the Spire.Xls.jar file to your project as dependency. In addition, you can also add the jar dependency to maven project by adding the following configurations to the pom.xml.

<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>

Setting alternate colors:

import com.spire.xls.*;

import java.awt.*;

public class ConditionalFormatting {

    public static void main(String[] args) {

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

        //Load a sample document
        workbook.loadFromFile("Input.xlsx");

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

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

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

        //Set the back color of odd rows to yellow using conditional formatting
        ConditionalFormatWrapper format2 = dataRange.getConditionalFormats().addCondition();
        format2.setFirstFormula("=MOD(ROW(),2)=1");
        format2.setFormatType(ConditionalFormatType.Formula);
        format2.setBackColor(Color.orange);

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

Outputs: