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