Search Data in Excel using Java

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

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.

Find Specific Text in Excel using Java

Find Specific Text in Excel using Java.

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.

Find Text Starting with Specific Letters.

Find Text Starting with Specific Letters.

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.

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.

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.

Search with Regular Expression in Excel using Java

Search with Regular Expression in Excel using Java.

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.

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.

Search Formula in Excel using Java.

Search Formula in Excel using Java.

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.

See Also