Java: How to Highlight Values Above or Below Average in Excel

 If you want to find numbers above or below the average, you can look at higher or lower values without calculating the average. Through a Java application, these numbers can be highlighted automatically. In addition to quickly highlighting values above or below the average, you can also see how many values are above or below. Now let's see how this can be implemented in a Java application.

Programming Environment

Method 1:

Introduced manually. Download Free Spire.XLS for Java locally, unzip it, and find the Spire.Xls.jar file in the lib folder. Open the following interface in IDEA, and import the jar file in the local path into the Java program:

If you use Maven, you can easily import the JAR file in your application by adding the following code to your project’s pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Highlight Values Above or Below Average Excel in Java

Below are the steps to highlight values above or below average in Excel using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet from the workbook using Workbook.getWorksheets.get(index) method.
• Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
• Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add an average condition using XlsConditionalFormats.addAverageCondition() method, specify the average type to above and change the background color of the cells that meet the condition to yellow.
• Add another average condition to change the background color of the cells that contain the value below average to light gray.
• Save the workbook to an Excel file using Workbook.saveToFile() method.

Full Code

Java

import com.spire.xls.AverageType;

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightValuesAboveAndBelowAverage {

    public static void main(String[] args) {

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

        //Load an Excel file

   workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Add a conditional format to the worksheet
        XlsConditionalFormats format = sheet.getConditionalFormats().add();

        //Set the range where the conditional format will be applied
        format.addRange(sheet.getRange().get("F2:F14"));

        //Add a condition to highlight the top 2 ranked values
        IConditionalFormat condition1 = format.addAverageCondition(AverageType.Above);
        condition1.setBackColor(Color.yellow);

        //Add a condition to highlight the bottom 2 ranked values
        IConditionalFormat condition2 = format.addAverageCondition(AverageType.Below);
        condition2.setBackColor(Color.lightGray);

        //Get the count of values below average
        sheet.getRange().get("F17").setFormula("=COUNTIF(F2:F14,\"<\"&AVERAGE(F2:F14))");

        //Get the count of values above average
        sheet.getRange().get("F18").setFormula("=COUNTIF(F2:F14,\">\"&AVERAGE(F2:F14))");

        //Save the workbook to an Excel file
        workbook.saveToFile("output/HighlightValues.xlsx", ExcelVersion.Version2016);
    }
}

Effective Shot

Conclusion:

     In this post, you have learned how to merge or unmerge cells in Excel in Java. Not only that, we also have other functions, such as, Highlight Dulicate and Unique Values in Excel in Java，Java: Apply Color to Alternate Rows in Excel Using Conditional Formatting and so on. Apart from that, if you'd like to learn more, you can visit this link to explore more about for Spire.XLS for Java.