Highlight Highest and Lowest Value in Excel Using Java

Highlight the highest and lowest value in a kind of conditional formatting Microsoft Excel offers. In this article, you will learn how to programmatically achieve this function 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>

Sample Code

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("test.xlsx");

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

        //Apply conditional formatting to range "D1:D9" to highlight the highest value
        ConditionalFormatWrapper format1 = sheet.getCellRange("D1:D9").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.TopBottom);
        format1.getTopBottom().setType(TopBottomType.Top);
        format1.getTopBottom().setRank(1);
        format1.setBackColor(Color.red);

        //Apply conditional formatting to range "D1:D9" to highlight the lowest value
        ConditionalFormatWrapper format2 = sheet.getCellRange("D1:D9").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.TopBottom);
        format2.getTopBottom().setType(TopBottomType.Bottom);
        format2.getTopBottom().setRank(1);
        format2.setBackColor(Color.CYAN);

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