Find and Highlight or Replace Text in Excel in Java

When working with large spreadsheets, it is a common task to need to find a specific value. In this article, I am going to introduce how to find text within a worksheet and highlight their cells with a background color, or replace the text with a new string, by using Free Spire.XLS for Java.

Installing Spire.Xls.jar

If you create a Maven project, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

<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>

Example 1. Find and highlight

import java.awt.*;
import java.util.EnumSet;

public class FindAndHighlight {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\input.xlsx");

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

        //Find the string "CA"
        CellRange[] ranges = worksheet.findAll("Laptop", EnumSet.of(FindType.Text), EnumSet.of(ExcelFindOptions.MatchEntireCellContent));

        for (CellRange range : ranges) {
            //Highlight the cell containing the string
            range.getCellStyle().setColor(Color.yellow);
        }

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

Example 2. Find and Replace

import com.spire.xls.*;

import java.util.EnumSet;

public class FindAndReplace {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\input.xlsx");

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

        //Find the all string "Laptop"
        CellRange[] ranges = worksheet.findAll("Laptop", EnumSet.of(FindType.Text), EnumSet.of(ExcelFindOptions.MatchEntireCellContent));

        for (CellRange range : ranges)
        {
            //Replace the old string with new one
            range.setText("Portable computer");
        }

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