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
Top comments (0)