Find or Search Data
In Microsoft Excel, users can search for cells that contain specific data. For example, clicking Edit and then Find opens the Search dialog. Users enters a value and clicks OK to search for it. Excel highlights matching fields.
Using the Find dialog to find cells containing a specific value
In this example, the search value is “Oranges”.
Aspose.Cells allows developers to search through the cells in a worksheet to find ones that contain a given value.
Finding Cells that Contain Specific Data
Aspose.Cells provides a class, Workbook, that represents an Excel file. The Workbook class contains WorksheetCollection, a collection that allows access to each of the worksheets in the Excel file. A worksheet is represented by the Worksheet class.
The Worksheet class provides Cells, a collection that represents all the cells in the worksheet.The Cells collection provides several methods for finding cells in a worksheet that contain user-specified data. A few of these methods are discussed below in more detail.
All find methods return the cell references for any cells that contain the specified search value.
Finding Containing a Formula
Developers can find a specified formula in the worksheet by calling the Cells collection’s find method, setting the FindOptions.setLookInType to LookInType.FORMULAS and passing it as a parameter to the find method.
Typically, the find method accepts two or more parameters:
- Object to Search: represents an object that is needed to find in the worksheet.
- The previous Cell: represents the previous cell with the same formula. This parameter can be set to null when searching from the start.
- Find Options: represents the Finding criteria. In the examples below, the following worksheet data is used to practice finding methods:
Sample worksheet data
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(FindingCellsContainingFormula.class) + "data/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Finding the cell containing the specified formula | |
Cells cells = worksheet.getCells(); | |
FindOptions findOptions = new FindOptions(); | |
findOptions.setLookInType(LookInType.FORMULAS); | |
Cell cell = cells.find("=SUM(A5:A10)", null, findOptions); | |
// Printing the name of the cell found after searching worksheet | |
System.out.println("Name of the cell containing formula: " + cell.getName()); |
Searching for Strings
Searching for cells that contain a string value is easy and flexible. There are different ways of searching, for example, search for cells that contain strings that start with a particular character, or set of characters.
Searching for Strings that Start with Specific Characters
To search for the first character in a string, call the Cells collection’s find method, set the FindOptions.setLookAtType to LookAtType.START_WITH and pass it as a parameter to the find method.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(FindingCellsWithStringOrNumber.class) + "data/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Finding the cell containing the specified formula | |
Cells cells = worksheet.getCells(); | |
// Instantiate FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Finding the cell containing a string value that starts with "Or" | |
findOptions.setLookAtType(LookAtType.START_WITH); | |
Cell cell = cells.find("SH", null, findOptions); | |
// Printing the name of the cell found after searching worksheet | |
System.out.println("Name of the cell containing String: " + cell.getName()); |
Searching for Strings that End with Specific Characters
Aspose.Cells can also find strings that end with specific characters. To search for the last characters in a string, call the Cells collection’s find method, set the FindOptions.setLookAtType to LookAtType.END_WITH and pass it as a parameter to the find method.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(FindingCellsEndWithSpecificCharacters.class) + "data/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Finding the cell containing the specified formula | |
Cells cells = worksheet.getCells(); | |
// Instantiate FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Finding the cell containing a string value that ends with "es" | |
findOptions.setLookAtType(LookAtType.END_WITH); | |
Cell cell = cells.find("SH", null, findOptions); | |
// Printing the name of the cell found after searching worksheet | |
System.out.println("Name of the cell containing String: " + cell.getName()); |
Searching with Regular Expressions: the RegEx Feature
A regular expression provides a concise and flexible means of matching (specifying and recognizing) strings of text, such as particular characters, words, or patterns.
For example, the regular expression pattern abc-*xyz matches the strings “abc-123-xyz”, “abc-985-xyz” and “abc-pony-xyz”. * is a wild card so the pattern matches any strings that start with “abc” and end with “-xyz”, regardless of what characters are in the middle.
Aspose.Cells allows you to search with regular expressions.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(FindingwithRegularExpressions.class) + "data/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Finding the cell containing the specified formula | |
Cells cells = worksheet.getCells(); | |
// Instantiate FindOptions | |
FindOptions findOptions = new FindOptions(); | |
// Instantiate FindOptions | |
FindOptions opt = new FindOptions(); | |
// Set the search key of find() method as standard RegEx | |
opt.setRegexKey(true); | |
opt.setLookAtType(LookAtType.ENTIRE_CONTENT); | |
cells.find("abc[\\s]*$", null, opt); |