Find, Highlight and Replace Data in Excel worksheets using Java

Today, I’ll introduce how to find and highlight/replace data in Excel using Java codes. First of all, we need to add a Jar file which is included into a free third-party library called Free Spire.XLS for Java to Intellij IDEA. You can get it from the link, or directly refer to it by using the following Maven configurations.

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

Find and Highlight Data in Excel
import com.spire.xls.*;
import java.awt.*;


public class HighlightData {
    public static void main(String[] args) {
        //Load the sample document
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

        //Find the text string "China"
        CellRange[] ranges = worksheet.findAllString("China", true, true);

        for (CellRange range : ranges)
        {
            //Set the color to highlight the text
            range.getCellStyle().setColor(Color.yellow);
        }

        //Save the document to file
        workbook.saveToFile("output/HighlightData.xlsx", ExcelVersion.Version2010);
    }
}
Output

Find and Replace Data in Excel
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ReplaceData {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

        //Find all the text "Name" in the worksheet
        CellRange[] ranges = worksheet.findAllString("Name", true, true);

        for (CellRange range : ranges)
        {
            //Replace the text with new text
            range.setText("Country");
        }

        //Save the resulting file
        workbook.saveToFile("output/ReplaceData.xlsx", ExcelVersion.Version2013);
    }
}
Output