In this post, you will learn how to find and replace text in MS Excel spreadsheets programmatically in Java. In various scenarios, MS Excel spreadsheets consist of a huge amount of data that may spread in multiple sheets. For such cases, the find and replace option could help you minimize the efforts required to update all the occurrences of a particular piece of text. Let’s check out how to automate this option when dealing with a large number of spreadsheets from within the Java applications.
- Java API to Find and Replace Text in Excel Files
- Find and Replace Text in Excel File
- Find and Replace Text using Regular Expression
Java API to Find and Replace Text in Excel Files
Aspose.Cells for Java is a powerful spreadsheet manipulation API that lets you create new and process existing Excel documents. The Excel automation features provided by the API also include finding and replacing the text seamlessly. You can either download the API or install it within your Maven-based applications for free.
<repository>
<id>AsposeJavaAPI</id>
<name>Aspose Java API</name>
<url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
<groupId>com.aspose</groupId>
<artifactId>aspose-cells</artifactId>
<version>20.11</version>
</dependency>
Find and Replace Text in Excel using Java
The following are the steps of how to find and replace text in Excel spreadsheets.
- Load the Excel file using Workbook class.
- Create an instance of ReplaceOptions class.
- Enable case sensitive search using ReplaceOptions.setCaseSensitive(boolean) method.
- Set option for text matching with entire cell’s content using ReplaceOptions.setMatchEntireCellContents(boolean) method.
- Find and replace text using Workbook.replace(String, String, ReplaceOptions) method.
- Save the updated spreadsheet using Workbook.save(String) method.
The following code sample shows how to find and replace a text in an Excel spreadsheet.
Find and Replace Text in Excel using Regular Expression
You can also use regular expressions in order to find and replace text in the spreadsheets. The only difference in code is, you will enable the regex search and provide regex instead of plain text in Workbook.replace method. The following are the steps to perform find and replace operation using regex.
- Load the Excel file using Workbook class.
- Create an instance of ReplaceOptions class.
- Enable regex search using ReplaceOptions.setRegexKey(true) method.
- Find and replace text using Workbook.replace(String, String, ReplaceOptions) method.
- Save the updated spreadsheet using Workbook.save(String) method.
The following code sample shows how to find and replace text in spreadsheets using regex.
Conclusion
Excel automation has become a widely adopted feature in various business domains. In accordance with that, in this post, you have learned one of the basic yet important features of spreadsheet manipulation. The step by step guide and code samples have shown you how to find and replace text in Excel files using Java. In case you want to learn about the advanced features of the API, you can explore the documentation.