Highlight Cells Using Conditional Formatting in Excel in Java

Introduction

Conditional Formatting is one of the most powerful features in Excel Spreadsheets. As the name implies, conditional formatting is used to highlight information that meets a specified condition. For example, you can highlight all cells that contain values greater than X but less than Y. Conditional formatting is extremely helpful when we’re looking for key information in a huge worksheet. 
This article provides the following six examples showing you how to highlight cells using conditional formatting in Java using Free Spire.XLS for the Java library.

• Highlight Duplicate Values in Excel
• Highlight Top/Bottom 3 Values in Excel
• Highlight Values Above/Below Average in Excel
• Highlight Values Between Two Numbers in Excel
• Highlight Every Other Row/Column in Excel
• Add Data Bars to a Cell Range in Excel

Install Free Spire.XLS for Java

Free Spire.XLS for Java is a free library for creating and manipulating Excel documents in Java applications. You can download it from the official website of E-ICEBLUE and add the jar file to your program as a dependency. 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>
        <verson>5.1.0</version>
    </dependency>
</dependencies>

Prerequisite Knowledge

Free Spire.XLS for Java offers the XlsConditionalFormats class to deal with the conditional formatting in an Excel document. We should first create an XlsConditionalFormats object. Then, we’re able to specify the cell range to which the conditional formatting will be applied and set a built-in or custom condition for it. Lastly, we can apply font color or/and cell color to the cells that meet the specified condition. The following are the detailed steps.

• Create an object of the Workbook class. 
• Load an Excel document using Workbook.loadFromFile() method.
• Add an XlsConditionalFormats object to the worksheet using Worksheet.getConditionalFormats().add() method.
• Specify the cell range where the conditional format will be applied using XlsConditionalFormats.addRange() method.
• Add a built-in condition using the method like XlsConditionalFormats.addDuplicateValuesCondition(), XlsConditionalFormats.addTopBottomCondition(), etc. Or, you can make a custom condition using XlsConditionalFormats.addContion() method. The custom condition requires you to set one or more formulas as the condition. 
• Apply font color or/and cell color to the cells that meet the condition.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

Example 1. Highlight Duplicate Values in Excel in Java

Before calculating or analyzing data in Excel, it is crucial that we check the worksheet for duplicates and make sure they are not information that has been entered incorrectly. The following example shows you how to identify duplicate values in a dataset using conditional formatting in Java.

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightDuplicates {

    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\\score sheet.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("H4:H15"));

        //Add a duplicate value condition
        IConditionalFormat condition = format.addDuplicateValuesCondition();

        //Highlight the cells that meet the condition with light gray
        condition.setBackColor(Color.LIGHT_GRAY);

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

Example 2. Highlight Top/Bottom 3 Values in Excel in Java

Conditional formatting in Excel can identify top or bottom-ranked values from a dataset. This is helpful in situations where you want to see the top candidates by scores or top deal values in the sales data. The following example shows you how to highlight the top 3 values in a column using conditional formatting. 

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightTop3 {

    public static void main(String[] args) {

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

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Score Sheet.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("H4:H15"));

        //Add a top bottom condition, specifying Top 3
        IConditionalFormat condition = format.addTopBottomCondition(TopBottomType.Top,3);

        //Highlight the cells that meet the condition with red
        condition.setBackColor(Color.red);

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

Example 3. Highlight Values Above/Below Average in Excel in Java

Highlighting values that are above or below average in a student's grade sheet gives us a quick overview of which students are doing better and which ones need to work harder. The following example shows you how to highlight values above average using conditional formatting.

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightValuesAboveAverage {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\score sheet.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("H4:H15"));

        //Add an average condition, specifying the average type to Above
        IConditionalFormat condition = format.addAverageCondition(AverageType.Above);

        //Highlight the cells that meet the condition with yellow
        condition.setBackColor(Color.yellow);

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

Example 4. Highlight Values Between Two Numbers in Excel in Java

Highlighting values between two numbers can help us quickly locate data within a certain data range. The following example shows you how to highlight numbers between 90 and 100 using conditional formatting in Java.

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightBetweenValues {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\score sheet.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("E4:G15"));

        //Add a cell value condition, specifying the comparison operator type to between and specifying the two numbers
        IConditionalFormat condition = format.addCellValueCondition(ComparisonOperatorType.Between,90,100);

        //Apply format to the cells that meet the condition
        condition.setFontColor(Color.YELLOW);
        condition.setBackColor(Color.red);

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

Example 5. Highlight Every Other Row/Column in Excel in Java

Highlighting alternate rows or columns can increase the readability of the data. These zebra lines could be especially helpful when you are printing the data. The following example shows you how to alternate row colors using conditional formation in Java.

import com.spire.xls.ConditionalFormatType;
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 HighlightAlternateRows {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\score sheet.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(4,1,sheet.getLastRow(),sheet.getLastColumn()));

        //Add a condition to highlight even rows with white
        IConditionalFormat condition1 = format.addCondition();
        condition1.setFormatType(ConditionalFormatType.Formula);
        condition1.setFirstFormula("=MOD(ROW(),2)=0");
        condition1.setBackColor(Color.white);

        //Add a condition to highlight odd rows with light gray
        IConditionalFormat condition2 = format.addCondition();
        condition2.setFormatType(ConditionalFormatType.Formula);
        condition2.setFirstFormula("=MOD(ROW(),2)=1");
        condition2.setBackColor(Color.LIGHT_GRAY);

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

 

Example 6. Add Data Bars to a Cell Range in Excel in Java

Data bars in Excel make it very easy to visualize values in a range of cells. A longer bar represents a higher value, while a shorter bar represents a smaller value. The following example shows you how to add data bars to a cell range using conditional formatting. 

import com.spire.xls.ConditionalFormatType;
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 AddDataBars {

    public static void main(String[] args) {

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

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\score sheet.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("F4:F15"));

        //Add a condition and set its format type to DataBar
        IConditionalFormat condition =  format.addCondition();
        condition.setFormatType(ConditionalFormatType.DataBar);

        //Set the fill color of the data bars
        condition.getDataBar().setBarColor(Color.red);

        //Save the result document
        workbook.saveToFile("AddDataBars.xlsx", ExcelVersion.Version2013);
    }
}