Search
- 2 minutes to read
To search for specific data in a range, worksheet or entire document, use the CellRange.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.
Use the CellRange.Value property to replace values in cells that match the search term.
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.
