Find and Replace in WPF Spreadsheet (SfSpreadsheet)

23 Jul 20266 minutes to read

This section explains the Find and Replace operations in SfSpreadsheet.

Find

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

The common parameters to be passed in Find functions are,

  • The option to specify whether the search can be done within the Workbook(IWorkbook) or Worksheet(IWorksheet).
  • The text to be searched.
  • The option to specify the direction whether the search can be done either row-wise or column-wise using the SearchBy enum.
  • The type to specify whether the search is done in formulas or values, using the ExcelFindType enum.
  • Set to true for a case-sensitive search; otherwise, false.
  • Set to true to match the entire cell content; otherwise, false.

Find All

Searches every occurrence of specific data based on the criteria that you are searching for and returns an IRange list representing the cells in SfSpreadsheet

//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 particular 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 the first occurrence of specific data which matches the conditions and returns the matched IRange from the current range that represents the cell.

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

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

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

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

Find Conditional Formatting

Searches and returns the IRange list which have conditional formatting within 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));          
}

Find Constants

Searches and returns the IRange list which have constants within 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));         
}

Find Formulas

Searches and returns the IRange list which have formulas within 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));          
}

Find Data Validation

Searches and returns the IRange list which have data validation within 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 in the workbook or worksheet based on the given option.

The parameters to be passed in the ReplaceAll function are,

  • The scope of the search: an IWorkbook or IWorksheet.
  • The text to be searched.
  • The text to be replaced.
  • Set to true for a case-sensitive search; otherwise, false.
  • Set to true to match the entire cell content; otherwise, false.
//Replaces the text in the entire workbook
spreadsheet.SearchManager.ReplaceAll(spreadsheet.Workbook, "sample","Sync", false, false);

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

Replace

Searches for the text or numbers that you want to change using the FindNext method, and once a match is found, uses the SetCellValue method to replace it with the specified text or numbers in SfSpreadsheet.

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

NOTE

You can refer to our WPF Spreadsheet Editor feature tour page for its groundbreaking feature representations. You can also explore our WPF Spreadsheet example to know how to render and configure the spreadsheet.