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
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
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.
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.