Highlight Values Below or Above Average in Excel Using Java

#java #excel #api

This article demonstrates how to highlight the values below average or above average, and how to calculate the number of these values respectively using Free Spire.XLS for Java.

Installation (2 Method)
1# Download the free library and unzip it, then add the Spire.Xls.jar file to your project as dependency.
2# 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>3.9.1</version>
    </dependency>
</dependencies>

Sample Code

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:B9"));
        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:B9"));
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(Color.yellow);

        //Get the count of values below average
        sheet.getCellRange("D12").setFormula("=COUNTIF(B2:B9,\"<\"&AVERAGE(B2:B9))");

        //Get the count of values above average
        sheet.getCellRange("D13").setFormula("=COUNTIF(B2:B9,\">\"&AVERAGE(B2:B9))");

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