Search

• Apr 23, 2019
• 2 minutes to read

To search for specific data in a range, worksheet or entire document, use the Range.Search, Worksheet.Search or Workbook.Search methods, respectively. To set options affecting search in a document, create an instance of the SearchOptions class and pass it as a parameter to the Search method. You can set the following advanced options.

• To specify the direction of the search (whether to perform a search by rows or by columns), use the SearchOptions.SearchBy property.
• To specify what to examine in each cell when searching (cell values only or cell values with formulas), use the SearchOptions.SearchIn property.
• To perform a case-sensitive search, set the SearchOptions.MatchCase property to true.
• To search for an exact match of characters specified by the search term, set the SearchOptions.MatchEntireCellContents property to true.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-part-2-t217615

SearchActions.vb

workbook.Calculate()
Dim worksheet As Worksheet = workbook.Worksheets("ExpenseReport")
workbook.Worksheets.ActiveWorksheet = worksheet

' Specify the search term.
Dim searchString As String = Date.Today.ToString("d")

' Specify search options.
Dim options As New SearchOptions()
options.SearchBy = SearchBy.Columns
options.SearchIn = SearchIn.Values
options.MatchEntireCellContents = True

' Find all cells containing today's date and paint them light-green.
Dim searchResult As IEnumerable(Of Cell) = worksheet.Search(searchString, options)
For Each cell As Cell In searchResult
    cell.Fill.BackgroundColor = Color.LightGreen
Next cell

SearchActions.cs

workbook.Calculate();
Worksheet worksheet = workbook.Worksheets["ExpenseReport"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Specify the search term.
string searchString = DateTime.Today.ToString("d");

// Specify search options.
SearchOptions options = new SearchOptions();
options.SearchBy = SearchBy.Columns;
options.SearchIn = SearchIn.Values;
options.MatchEntireCellContents = true;

// Find all cells containing today's date and paint them light-green.
IEnumerable<Cell> searchResult = worksheet.Search(searchString, options);
foreach (Cell cell in searchResult)
    cell.Fill.BackgroundColor = Color.LightGreen;

The image below shows the result of executing the code (the workbook is opened in Microsoft® Excel®). Today’s date is located in the expense report and highlighted in light-green.