How to Use Conditional Formatting to Highlight Cells in Java

When we process data in Excel, we may need to highlight specific information, such as the top 5 values, the values below average, or the duplicate values, etc. In this case, we can use conditional formatting to easily filter out these special values and highlight the cells or rows where they are.

The following sections will show you how to programmatically apply conditional formatting in Excel using Free Spire.XLS for Java through there examples.

Source document

Add Spire.Xls.jar as dependency

Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Xls.jar file from the “lib” folder. Import the jar file in your project as a dependency.

Method 2:  If you are creating a Maven project, you can easily add the jar dependency 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>

Highlight values below or above average

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightBelowAboveAverage {

    public static void main(String[] args) {

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

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

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

        //Use conditional formatting to highlight the values below average in the range "B2:B9"
        XlsConditionalFormats format1 = sheet.getConditionalFormats().add();
        format1.addRange(sheet.getCellRange("B2:B13"));
        IConditionalFormat cf1 = format1.addAverageCondition(AverageType.Below);
        cf1.setBackColor(Color.red);

        //Use conditional formatting to highlight the values above average in the range "B2:B9"
        XlsConditionalFormats format2 = sheet.getConditionalFormats().add();
        format2.addRange(sheet.getCellRange("B2:B13"));
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(Color.yellow);

        //Save the file 
        workbook.saveToFile("BolowOrAboveAverage.xlsx", ExcelVersion.Version2016);
    }
}

Hightlight top 3 or bottom 3 values

import com.spire.xls.*;

import java.awt.*;

public class HighlightTopBottom {

    public static void main(String[] args) {

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

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

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

        //Apply conditional formatting to range "B2:B13" to highlight the top 3 values
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.TopBottom);
        format1.getTopBottom().setType(TopBottomType.Top);
        format1.getTopBottom().setRank(3);
        format1.setBackColor(Color.red);

        //Apply conditional formatting to range "B2:B13" to highlight the bottom 3 values
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.TopBottom);
        format2.getTopBottom().setType(TopBottomType.Bottom);
        format2.getTopBottom().setRank(3);
        format2.setBackColor(Color.yellow);

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

Highlight duplicate or unique values

import com.spire.xls.*;

import java.awt.*;

public class HighlightDuplicated {
    public static void main(String[] args) {

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

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

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

        //Use conditional formatting to highlight duplicated values in the range "A2:A11" with red
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.DuplicateValues);
        format1.setBackColor(Color.red);

        //Use conditional formatting to highlight unique values in the range "A2:A11" with yellow
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.UniqueValues);
        format2.setBackColor(Color.yellow);

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