Find and Replace
The document model offers a convenient way to find and replace text and numbers in a workbook, worksheet or a specified range of cells. This article provides information about the options of the find and replace features and demonstrates how they can be used.
Find and FindAll
Both Workbook and Worksheet classes expose methods that search their contents for a specific value. The two classes offer a Find() method that returns the first occurrence of the sought string and a FindAll() method that displays all occurrences of the specified value. Both methods require a single parameter of type FindOptions that determines how the search is performed. The following list outlines the properties of the FindOptions class:
FindWhat: Specifies the sought string.
FindWithin: Determines if the search is conducted for the whole Workbook or for a particular Worksheet. If you call the Find() method of the Workbook class and the FindWithin option is set to Workbook, the search is done for the entire workbook and if the option is Worksheet – the search is performed only for the active worksheet. Note that if you call the Find() method of the Worksheet class, the FindWithin property is ignored and the search is done for the worksheet instance that invokes the search.
FindBy: Indicates whether the search is performed by rows or by columns.
FindIn: Shows if the search includes formulas or only result values.
MatchCase: Determines if the search should match the casing of the sought string.
MatchEntireCellContents: Indicates whether the sought string should match the entire cell content.
StartCell: Marks the cell from which the search begins.
SearchRanges: If the property is set to null, the search is performed in the entire workbook or worksheet, depending on the FindWithin property. If ranges are defined, the search is performed only for these ranges of the active sheet. Note that this property is taken into account only in the Find() and Replace() methods and disregarded in the FindAll() and ReplaceAll() methods.
Example 1 creates a new workbook with two empty worksheets and assigns sample values to the sheets. The FindOptions created below specifies that the search will be conducted for the whole workbook and will start from cell A1 of the first worksheet. The sample snippet illustrates how to use Find() and FindAll() methods.
Example 1: Perform find an find all
Workbook workbook = new Workbook();
Worksheet worksheet1 = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet1.Cells[1, 1].SetValue("SUMMARY");
worksheet1.Cells[1, 2].SetValue("=SUM(5, 6)");
worksheet2.Cells[2, 2].SetValue("=SUM(4, 4)");
worksheet2.Cells[2, 3].SetValue("SUM");
FindOptions options = new FindOptions()
{
StartCell = new WorksheetCellIndex(worksheet1, 0, 0),
FindBy = FindBy.Rows,
FindIn = FindInContentType.Formulas,
FindWhat = "SUM",
FindWithin = FindWithin.Workbook,
};
FindResult findResult = workbook.Find(options);
IEnumerable<FindResult> findResults = workbook.FindAll(options);
Replace and ReplaceAll
As a supplement to the Find() and FindAll() methods, the Workbook and Worksheet classes offer two more methods that allow you to alter the found strings: Replace() and ReplaceAll(). The former method replaces the string of the first occurrence while the latter alters all encountered occurrences. The two methods take one argument of type ReplaceOptions that specifies how the search is performed and also the string that should replace the occurrences. The class derives from FindOptions and defines one more property:
- ReplaceWith: Specifies the string that will replace any found value.
Example 2 creates a workbook from scratch with two empty worksheets and adds some sample values. The ReplaceOptions instance specifies that the replace is performed on the whole workbook and includes formula values. The operation starts from cell A1 of the first worksheet and the search is done by columns.
Example 2: Perform replace and replace all
Workbook workbook = new Workbook();
Worksheet worksheet1 = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet1.Cells[1, 1].SetValue("SUMMARY");
worksheet1.Cells[1, 2].SetValue("=SUM(5, 6)");
worksheet2.Cells[2, 2].SetValue("=SUM(4, 4)");
worksheet2.Cells[2, 3].SetValue("SUM");
ReplaceOptions options = new ReplaceOptions()
{
StartCell = new WorksheetCellIndex(worksheet1, 0, 0),
FindBy = FindBy.Rows,
FindIn = FindInContentType.Formulas,
FindWhat = "SUM",
ReplaceWith = "Test",
FindWithin = FindWithin.Workbook,
};
FindResult findResult = workbook.Find(options);
options.StartCell = findResult.FoundCell;
if (workbook.Replace(options))
{
RadWindow.Alert("Replace was successful!");
}
workbook.ReplaceAll(options);