Find and Replace in Windows Forms Spreadsheet

23 Jul 20266 minutes to read

This section explains Find and Replace operations in the Spreadsheet control.

Find

Searches for specific data, such as a particular number or text, according to the specified options and returns an IRange representing the cell, or null if no cell is found. The various Find options are:

The common parameters passed to Find functions are:

  • The option that specifies whether the search is performed within the IWorkbook or IWorksheet.
  • The text to search for.
  • The SearchBy enum that specifies whether the search proceeds by row or by column.
  • The ExcelFindType enum that specifies whether the search is performed in text, numbers, or formulas.
  • For a case-sensitive search, pass true; otherwise pass false.
  • To match the entire cell content with the search text, pass true; otherwise pass false.

Find All

Searches every occurrence of specific data that matches the criteria and returns an IRange list representing the matched cells in the Spreadsheet.

//Search the entire workbook
var list = spreadsheet.SearchManager.FindAll(spreadsheet.Workbook, "sample", SearchBy.ByRows, ExcelFindType.Text, false, true);

// To select the matched cell content ranges,

foreach (var cell in list)
{
  spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

//Search the specified worksheet
var list = spreadsheet.SearchManager.FindAll(spreadsheet.Workbook.Worksheets[0], "sample", SearchBy.ByRows, ExcelFindType.Text, false, true);

// To select the matched cell content ranges,

foreach (var cell in list)
{
  spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

Find Next

Searches for the first occurrence of specific data that matches the conditions and returns the matched IRange from the current range.

//Search the text in the entire workbook by column
var cell = spreadsheet.SearchManager.FindNext(spreadsheet.Workbook, "sample", SearchBy.ByColumns, ExcelFindType.Text, false, true);

// To move the current cell to the matched cell,
spreadsheet.ActiveGrid.CurrentCell.MoveCurrentCell(cell.Row,cell.Column);

//Search the text in the specified worksheet by row
var cell = spreadsheet.SearchManager.FindNext(spreadsheet.Workbook.Worksheets[0], "sum", SearchBy.ByRows, ExcelFindType.Text, false, false);

// To move the current cell to the matched cell,
spreadsheet.ActiveGrid.CurrentCell.MoveCurrentCell(cell.Row,cell.Column);

Finding conditional formatting

Searches and returns the IRange list of cells that have conditional formatting in the specified worksheet.

//Searches the conditional formatting within the worksheet,
var list = spreadsheet.SearchManager.FindConditionalFormatting(spreadsheet.Workbook.Worksheets[0]);

// To select the matched cell content ranges,

foreach (var cell in list)
{
  spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

Finding constants

Searches and returns the IRange list of cells that contain constants in the specified worksheet.

//Searches the constants within the worksheet,
var list = spreadsheet.SearchManager.FindConstants(spreadsheet.Workbook.Worksheets[0]);

// To select the matched cell content ranges,

foreach (var cell in list)
{
   spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

Finding formulas

Searches and returns the IRange list of cells that contain formulas in the specified worksheet.

//Searches the formulas within the worksheet,
var list = spreadsheet.SearchManager.FindFormulas(spreadsheet.Workbook.Worksheets[0]);

// To select the matched cell content ranges,

foreach (var cell in list)
{
   spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

Finding data validation

Searches and returns the IRange list of cells that have data validation in the specified worksheet.

//Searches the data validation within the worksheet,
var list = spreadsheet.SearchManager.FindDataValidation(spreadsheet.Workbook.Worksheets[0]);

// To select the matched cell content ranges,

foreach (var cell in list)
{
   spreadsheet.ActiveGrid.SelectionController.AddSelection(GridRangeInfo.Cell(cell.Row, cell.Column));
}

Replace All

Searches and replaces all occurrences of the text in the workbook or in a specified worksheet.

The parameters passed to the ReplaceAll method are:

  • The option that specifies whether the search is performed within the IWorkbook or IWorksheet in the Spreadsheet.
  • The text to search for.
  • The replacement text.
  • For a case-sensitive search, pass true; otherwise pass false.
  • To match the entire cell content with the search text, pass true; otherwise pass false.
//Replaces the text in the entire workbook
spreadsheet.SearchManager.ReplaceAll(spreadsheet.Workbook, "sample","Sync", false, false);

//Replaces the text in the specified worksheet
spreadsheet.SearchManager.ReplaceAll(spreadsheet.Workbook.Worksheets[0], "sample", "sync", false, true);

Replace

Searches for the text or number to change using FindNext; once a match is found, use SetCellValue to replace it with the specified text or number in the Spreadsheet.

//Searches the given text and replaces it with the specified text
var cell = spreadsheet.SearchManager.FindNext(spreadsheet.Workbook, "sample", SearchBy.ByColumns, ExcelFindType.Text, false, true);
spreadsheet.ActiveGrid.SetCellValue(cell, "sync");