Find or Search Data

Skip to end of metadata
Go to start of metadata
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.

Contents Summary

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

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 and set the LookAtType class' setLookAtType method's FindOption option to END_WITH.

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.

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.