Find and Replace in WPF Spreadsheet (SfSpreadsheet)

16 Oct 20237 minutes to read

This section explains about Find and Replace operations in SfSpreadsheet.

Find

Searches for specific data such as particular number or text according to 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 by row wise or column wise using SearchBy enum.
  • The type to specify whether the search can be done either in formulas or values using ExcelFindType enum.
  • For a case sensitive search, pass the parameter as true otherwise you can pass the parameter as false.
  • For matching the entire cell content with the search text, pass the parameter as true otherwise you can pass the parameter as 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 the texts either in the workbook or worksheet based on the given option.

The parameters to be passed in ReplaceAll function is,

  • The option to specify whether the search can be done within the Workbook(IWorkbook) or Worksheet(IWorksheet) in SfSpreadsheet.
  • The text to be searched.
  • The text to be replaced.
  • For a case sensitive search, pass the parameter as true otherwise you can pass the parameter as false.
  • For matching the entire cell content with the search text, pass the parameter as true otherwise you can pass the parameter as 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 FindNext method and once the immediate matched cell has been found, use SetCellValue method to replace it with 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 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.