Find and Replace

• Dec 12, 2022
• 4 minutes to read

The SpreadsheetControl allows you to search for specific data in a document. You can perform a search using the SpreadsheetControl’s user interface, or directly in code using the corresponding Search method overloads.

Search Using the Find and Replace Dialog

You can find data in the current worksheet using the Find and Replace feature of the SpreadsheetControl. To perform a search, on the Home tab, in the Editing group, click the Find & Select button. The button’s drop-down menu will be displayed.

Next, do one of the following.

• Click Find in the Find & Select drop-down menu (or press CTRL + F ) to perform a search in the active worksheet. The Find and Replace dialog (with the Find tab activated) will be invoked.

• Click Replace in the Find & Select drop-down menu (or press CTRL + H ) to search for a text string and optionally replace it with another value. The Find and Replace dialog (with the Replace tab activated) will be invoked.

Search in Code

The SpreadsheetControl also allows you to find text in a range, worksheet or entire document programmatically using the CellRange.Search, Worksheet.Search or IWorkbook.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. As in the case of the user interface, 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.

The example below demonstrates how to perform a search with the specified options in the active worksheet and highlight all matching cells.

View Example

• SearchActions.cs
• SearchActions.vb

workbook.LoadDocument("Documents\\ExpenseReport.xlsx");
workbook.Calculate();
Worksheet worksheet = workbook.Worksheets[0];

// 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;

workbook.LoadDocument("Documents\ExpenseReport.xlsx")
workbook.Calculate()
Dim worksheet As Worksheet = workbook.Worksheets(0)

' 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

The image below shows the result of executing the code. Today’s date is located in the expense report and highlighted in light-green.