This module uses the DevExpress Spreadsheet API to find and replace text in Excel documents. You can process the predefined sample file or supply your own document. To do the latter, select Upload a File in the file selection drop-down menu.
Select the operation type: replace, remove, or highlight. Enter the search text and specify operation-specific settings. Use Match Case and Match Entire Cell Content checkboxes to fine-tune text matching rules.
Use the Modify Document and Save as ... dropdown button to select the output format, process the document, and download the result.
using DevExpress.Spreadsheet;
using System.Drawing;
Stream FindReplace(Stream inputStream, string searchText, bool matchCase, bool matchEntireCellContents, string replaceText, DocumentFormat outputFormat) {
using var workbook = new Workbook();
// Load document
workbook.LoadDocument(inputStream);
// Search text in an active worksheet
var worksheet = workbook.Worksheets.ActiveWorksheet;
// Specify search options
var searchOptions = new SearchOptions() {
SearchIn = SearchIn.Values,
MatchCase = matchCase,
MatchEntireCellContents = matchEntireCellContents
};
// Find all cells that contain the specified search text
IEnumerable<Cell> searchResult = worksheet.Search(searchText, searchOptions);
// Replace text in found cells
foreach(Cell cell in searchResult)
cell.SetValue(replaceText);
// Create output stream and save document in target format
return CreateOutputStream(workbook, outputFormat);
}
Stream FindRemove(Stream inputStream, string searchText, bool matchCase, bool matchEntireCellContents, DocumentFormat outputFormat) {
using var workbook = new Workbook();
// Load document
workbook.LoadDocument(inputStream);
// Search text in an active worksheet
var worksheet = workbook.Worksheets.ActiveWorksheet;
// Specify search options
var searchOptions = new SearchOptions() {
SearchIn = SearchIn.Values,
MatchCase = matchCase,
MatchEntireCellContents = matchEntireCellContents
};
// Find all cells that contain the specified search text
IEnumerable<Cell> searchResult = worksheet.Search(searchText, searchOptions);
// Clear content in found cells
foreach(Cell cell in searchResult)
cell.Clear();
// Create output stream and save document in target format
return CreateOutputStream(workbook, outputFormat);
}
Stream FindHighlight(Stream inputStream, string searchText, bool matchCase, bool matchEntireCellContents, Color highlightColor, DocumentFormat outputFormat) {
using var workbook = new Workbook();
// Load document
workbook.LoadDocument(inputStream);
// Search text in an active worksheet
var worksheet = workbook.Worksheets.ActiveWorksheet;
// Specify search options
var searchOptions = new SearchOptions() {
SearchIn = SearchIn.Values,
MatchCase = matchCase,
MatchEntireCellContents = matchEntireCellContents
};
// Find all cells that contain the specified search text
IEnumerable<Cell> searchResult = worksheet.Search(searchText, searchOptions);
// Highlight found cells
foreach(Cell cell in searchResult)
cell.Fill.BackgroundColor = highlightColor;
// Create output stream and save document in target format
return CreateOutputStream(workbook, outputFormat);
}
Stream CreateOutputStream(Workbook workbook, DocumentFormat outputFormat) {
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Position = 0;
return outputStream;
}