Excel spreadsheets allow storing a huge amount of data in tabular form. In certain cases, we may need to search cells containing specific data values or strings from stored data. Although Excel provides built-in functionality to search through all the spreadsheets, we may need to perform the search operation programmatically in Java applications. In this article, we will learn how to search data in Excel using Java.
The following topics shall be covered in this article:
- Java API to Search Data in Excel
- Find Specific Text in Excel using Java
- Find Text Starting with Specific Letters
- Search Text Ending with Specific Letters
- Find Text containing Specific Letters
- Search with Regular Expression in Excel using Java
- Case-Sensitive Search in Excel using Java
- Search Formula in Excel using Java
Java API to Search Data in Excel
We will be using Aspose.Cells for Java API to perform search operation on XLSX file. It allows performing Excel automation features programmatically without needing a Microsoft Excel application. Please either download the JAR of the API or just add the following pom.xml configuration in a Maven-based Java application.
<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>22.1</version>
</dependency>
Find Specific Text in Excel using Java
We can search any text, number, or date values in an Excel file by following the steps given below:
- Firstly, load an Excel file using the Workbook class.
- Next, accessing the first worksheet in the Excel file.
- Then, get cells of the accessed sheet.
- Next, create an instance of the FindOptions class.
- After that, set the LookAtType as “ENTIRE_CONTENT”.
- Finally, find the text using the Cells.find() method. It takes, search string and FindOptions as arguments.
The following code sample shows how to find a specific text in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a string value | |
findOptions.setLookAtType(LookAtType.ENTIRE_CONTENT); | |
Cell cell = cells.find("A Company", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Find Text Starting with Specific Letters
We can search any text value that starts with specific letters by following the steps mentioned above. However, we just need to set LookAtType as “START_WITH”.
The following code sample shows how to find a specific text that starts with the letter “H” in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a string value | |
findOptions.setLookAtType(LookAtType.START_WITH); | |
Cell cell = cells.find("H", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Search Text Ending with Specific Letters
We can search any text value that ends with specific letters by following the steps mentioned above. However, we just need to set LookAtType as “END_WITH”.
The following code sample shows how to find a specific text that ends with the letters “any” in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a string value | |
findOptions.setLookAtType(LookAtType.ENDS_WITH); | |
Cell cell = cells.find("any", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Find Text Containing Specific Letters
We can search any text value that contains any specific substring by following the steps mentioned above. However, we just need to set LookAtType as “CONTAINS”.
The following code sample shows how to find a text that contains “comp” in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a string value | |
findOptions.setLookAtType(LookAtType.CONTAINS); | |
Cell cell = cells.find("comp", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Search with Regular Expression in Excel using Java
We can also search a cell value using regular expressions by following the steps mentioned above. However, we just need to set the Regex Key to true and the LookAtType as “CONTAINS”.
The following code sample shows how to find text using a regular expression in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Define it as Regex | |
findOptions.setRegexKey(true); | |
findOptions.setLookAtType(LookAtType.ENTIRE_CONTENT); | |
findOptions.setLookInType(LookInType.VALUES); | |
Cell cell = cells.find("[a-z]{1}[\\s]&[\\s][a-z]{1}", null, opt); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Case-Sensitive Search in Excel using Java
We can perform a search operation and find any case-sensitive text string by following the steps mentioned above. However, we just need to set the CaseSensitive property to true.
The following code sample shows how to find a case-sensitive text in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a formula | |
findOptions.setCaseSensitive(true); | |
findOptions.setLookAtType(LookAtType.CONTAINS); | |
Cell cell = cells.find("Ltd", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Search Formula in Excel using Java
We can search a cell containing formula in an Excel file by following the steps given below:
- Firstly, load an Excel file using the Workbook class.
- Next, accessing the first worksheet in the Excel file.
- Then, get cells of the accessed sheet.
- Next, create an instance of the FindOptions class.
- After that, set the LookInType as “FORMULAS”.
- Finally, find the text using the Cells.find() method. It takes, search string and FindOptions as arguments.
The following code sample shows how to find a formula cell in an Excel file using Java.
// Load an Excel file | |
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample.xlsx"); | |
// Access the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Get all the cells in CellCollections | |
Cells cells = worksheet.getCells(); | |
// Initialize FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Find the cell containing a formula | |
findOptions.setLookInType(LookInType.FORMULAS); | |
Cell cell = cells.find("=SUM(C2:C10)", null, findOptions); | |
// Show the cell name and its value | |
System.out.println("Name of the cell containing String: " + cell.getName()); | |
System.out.println("the cell value is: " + cell.getValue()); |
Get a Free License
Please try the API without evaluation limitations by requesting a free temporary license.
Conclusion
In this article, we have learned how to find text or formulas in Excel using Java. Specifically, we have learned how to search any text in Excel that starts, ends, or contains specific letters programmatically. We have also seen how to search using regular expressions in Excel files. Besides, you can learn more about Aspose.Cells for Java API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.