Highlight Dulicate and Unique Values in Excel using Java

When dealing with Excel files in daily work, we may need to highlight some specific values to draw our attention for further processing. This article will demonstrates how to highlight the duplicate and unique values in a selected range through conditional formatting using Free Spire.XLS for Java.

Import the jar dependency
Method 1: Download the Free Spire.XLS for Java and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly 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>3.9.1</version>
    </dependency>
</dependencies>

Relevant code snippet

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

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

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

        //Load a sample Excel file
        workbook.loadFromFile("order.xlsx");

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

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

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

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

Output