|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.
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.
Developers can find a specified formula in the worksheet by calling the Cells collection's find method and setting the FindOption} option in the LookInType class' setLookInType method to FORMULAS.
Typically, the FindFormula method accepts two or more parameters:
- Formula to Search, represents a formula that is needed to find in the worksheet.
- Previous Cell, represents the previous cell with the same formula. This parameter can be set to null when seaching 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 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.
To search for the the first character in a string, call the Cells collection's find method and set the LookAtType class' setLookAtType method's FindOption option to START_WITH.
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 and set the LookAtType class' setLookAtType method's FindOption option to END_WITH.
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.