Find and Highlight or Replace Text in Excel in Java
Tags: java, find, replace, highlight
Published: 2021-01-12 08:59:04

---
title: Find and Highlight or Replace Text in Excel in Java
published: true
description: This article shows you how to find and highlight text, and how to find and replace text inside a worksheet in java.
tags: java,find,replace,highlight
//cover_image: https://direct_url_to_image.jpg
---
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](https://www.e-iceblue.com/Download/xls-for-java-free.html) and add it as a dependency in your application.
```java
<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 
```java
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);
    }
}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/6xyx1vb8wjb71w8nul38.png)
##Example 2. Find and Replace 
```java
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);
    }
}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/awyi44zkm3exgr8llvuj.png)