When working with a large workbook containing dozens of columns and rows, it may often be necessary to use the "Find" function to quickly locate specific values. This article will demonstrate how to programmatically find all cells with a specific value and highlight them with a background color using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. 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</artifactId>
        <version>14.4.1</version>
    </dependency>
</dependencies>
    

Find and Highlight Data in Excel

The detailed steps are as follows.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Find all cells with matching text using Worksheet.findAllString(java.lang.String stringValue, boolean formula, boolean formulaValue) method.
• Set color to highlight the cells using CellRange.getCellStyle().setColor() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;
import java.awt.*;

public class FindandHighlight {
    public static void main(String[] args) {

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

        //Load the sample document
        workbook.loadFromFile("Test.xlsx");

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

        //Find all cells with the text "Regulator System"
        CellRange[] ranges = worksheet.findAllString("Regulator System", true, true);
        for (CellRange range : ranges)
        {

            //Set color to highlight the cells
            range.getCellStyle().setColor(Color.yellow);
        }

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.