Skip to main content

Find, Replace and Highlight Data in Excel in Java

By

Finding and replacing data is one of the most useful features in Microsoft Excel, it allows us to quickly locate the position of a particular data and replace it with another data. This blog demonstrates how to find a specified text, replace it with another text and then highlight the result in Excel file in Java using Free Spire.XLS for Java library.

Before using the below code, please download Free Spire.XLS for Java package then and import Spire.Xls.jar under the lib folder into your project.

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import java.awt.*;

public class ReplaceAndHightlightText {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("test.xlsx");

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

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

        for (CellRange range : ranges)
        {
            //Replace the text with new text
            range.setText("Sum");
            //Set highlight color
            range.getCellStyle().setColor(Color.yellow);
        }

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

The example Excel file before replacing data:


The result Excel file after replacing data:

Labels:

Comments

Post a Comment

Popular posts from this blog

3 Ways to Generate Word Documents from Templates in Java

By
A template is a document with pre-applied formatting like styles, tabs, line spacing and so on. You can quickly generate a batch of documents with the same structure based on the template. In this article, I am going to show you the different ways to generate Word documents from templates programmatically in Java using Free Spire.Doc for Java library. Prerequisite First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file. <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name> ...
Post a Comment
Read more

Add and Delete Digital Signature in Excel with Java

By
A digital signature is a type of electronic signature that helps verify the authenticity of documents. In this blog, I will show you how to sign an Excel file with digital signature and delete the digital signature from the result Excel file programmatically in Java using Spire.XLS for Java API. Add maven dependencies Before coding, you need to add needed dependencies for including Spire.XLS for Java into your Java project. <repositories>             <repository>                 <id>com.e-iceblue</id>                 <name>e-iceblue</name>                 <url>http: //repo.e-iceblue.com/nexus/content/groups/public/</url>    ...
Post a Comment
Read more

Insert and Extract OLE objects in Word in Java

By
You can use OLE (Object Linking and Embedding) to include content from other programs, such as another Word document, an Excel or PowerPoint document to an existing Word document. This article demonstrates how to insert and extract embedded OLE objects in a Word document in Java by using Free Spire.Doc for Java API.   Add dependencies First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file.     <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>     ...
Post a Comment
Read more