Find or Search Data

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

todo:image_alt_text

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.

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.