How to Automate Find and Replace in Excel Using C# with XlsIO Library

TL;DR: Manually editing Excel files is slow and error-prone. Using Syncfusion^® XlsIO in C#, you can automate find and replace operations across multiple files with precision and speed.

Manually updating Excel files is manageable if you only have one. But when dealing with dozens or even hundreds of spreadsheets, manual edits become a time-consuming, error-prone chore.

Whether you’re:

• Replacing outdated product codes in multiple reports,

• Updating client names across archived financial sheets, or

• Correcting formulas in templates,

…automation is the key to saving time and ensuring accuracy.

With the Syncfusion^®.NET Excel Library ** **** (XlsIO)**, you can automate find and replace operations in Excel using C#, turning hours of work into seconds of execution.

What is Syncfusion^® .NET Excel Library?

The Syncfusion^® .NET Excel Library, also known as Essential XlsIO, is a robust tool that facilitates the smooth creation, reading, and editing of Excel documents using C#. It supports:

• Excel formulas and cell formatting.

• Charts, Pivot Charts, Sparklines.

• Tables and pivot tables.

• Import/export data from/to Excel from various data sources.

• Conditional formatting, data validations, and much more.

In this article, you will learn how to execute precise find and replace operations, ensuring accuracy and optimal performance.

Why automate find and replace?

Automation removes the repetitive, error-prone steps from Excel editing:

• Speed: Process hundreds of files in seconds.

• Accuracy: Exact matches, case sensitivity, and whole-cell control.

• Scalability: Works on large datasets without slowing down.

• Flexibility: Target specific sheets, columns, or even comments.

Steps to automate find and replace in Excel using C

1. Create a .NET Core Console application in Visual Studio.

2. Install the latest Syncfusion.XlsIO.NET.Core NuGet package in your app.

3. Load your Excel file using XlsIO.

4. Run find and replace with specific matching rules.

5. Save and close the document without manual intervention.

How to find data in Excel using C

The XlsIO library supports two primary search methods:

• FindFirst(): Locate the first match ( fastest ).

• FindAll(): Retrieve every match ( ideal for batch updates ).

// Returns the cell range only for the first occurrence of the cell value
var firstRange = worksheet.FindFirst("Gill", ExcelFindType.Text);

// Returns all the cell ranges that match the cell value
var allRanges = worksheet.FindAll("Gill", ExcelFindType.Text);

Supported search types

Choose exactly what you want to search:

Match options

The Excel library allows you to find the cell value, either by matching the text casing or the entire cell content.

// Case-sensitive search
worksheet.FindAll("Pen Set", ExcelFindType.Text, ExcelFindOptions.MatchCase);

// Match entire cell content
worksheet.FindAll("5", ExcelFindType.Text, ExcelFindOptions.MatchEntireCellContent);

Find scopes

For maximum speed, limit your search to the smallest scope needed:

// Search within a worksheet
worksheet.FindAll("5", ExcelFindType.Text);

// Search across the workbook
workbook.FindAll("5", ExcelFindType.Text);

The following code snippet shows how to automate finding and highlighting cell values from an Excel document using C#.

using Syncfusion.XlsIO;
using System.IO;

namespace Find
{
    class Program
    {
        public static void Main(string[] args)
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;
                FileStream fileStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
                IWorkbook workbook = application.Workbooks.Open(fileStream);
                IWorksheet worksheet = workbook.Worksheets[0];

                // Searches for the given string within the text of the worksheet
                IRange[] textCells = worksheet.FindAll("Gill", ExcelFindType.Text);

                // Searches for the given string within the text of the worksheet
                IRange[] numberCells = worksheet.FindAll(700, ExcelFindType.Number);

                // Searches for the given string in formulas
                IRange[] formulaCells = worksheet.FindAll("=SUM(F10:F11)", ExcelFindType.Formula);

                // Searches for the given string in the calculated value, number, and text
                IRange[] valueCells = worksheet.FindAll("41", ExcelFindType.Values);

                // Searches for the given string within the text of the worksheet, and case
                IRange[] textMatchingCaseCells = worksheet.FindAll("Pen Set", ExcelFindType.Text, ExcelFindOptions.MatchCase);

                // Searches for the given string within the text of the worksheet and the entire cell content matching the search text
                IRange[] textMatchingEntireContentCells = worksheet.FindAll("5", ExcelFindType.Text, ExcelFindOptions.MatchEntireCellContent);

                foreach (IRange cell in textCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 255, 0, 0); // Highlight found text cells in red
                }

                foreach (IRange cell in numberCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 0, 255, 0); // Highlight found number cells in green
                }

                foreach (IRange cell in formulaCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 0, 0, 255); // Highlight found formula cells in blue
                }

                foreach (IRange cell in valueCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 255, 165, 0); // Highlight found value cells in orange
                }

                foreach (IRange cell in textMatchingCaseCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 128, 0, 128); // Highlight found case-matching text cells in purple
                }

                foreach (IRange cell in textMatchingEntireContentCells)
                {
                    cell.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(255, 0, 128, 128); // Highlight found entire content matching text cells in teal
                }

                // Saving the workbook as a stream
                FileStream stream = new FileStream(Path.GetFullPath(@"Output/Find.xlsx"), FileMode.Create, FileAccess.ReadWrite);
                workbook.SaveAs(stream);
                stream.Dispose();
            }
        }
    }
}

The following image illustrates Excel documents with Find and Highlight features.

Excel document with Find and Highlight features

How to replace values in Excel using C

You can replace values in a worksheet or across the entire workbook:

// Replace in a single worksheet
worksheet.Replace("4.99", "4.90");

// Replace across workbook
workbook.Replace("4.99", "4.90");

Replace options

The XlsIO library provides customization options for replacing cell values in the worksheet.

Match options

Like Find operations, you can control how replacements are matched using ExcelFindOptions.

// Replace only if the case matches exactly (e.g., "Pen Set" but not "pen set")
worksheet.Replace("Pen Set", "Pen", ExcelFindOptions.MatchCase);

// Replace only if the entire cell content is "Pen Set"
worksheet.Replace("Pen Set", "Pen", ExcelFindOptions.MatchEntireCellContent);

Replace with other data types

You’re not limited to replacing text with text. You can also replace placeholders with other data types like DateTime:

// Replaces the given string with the DateTime value
worksheet.Replace("DateValue", DateTime.Now);

Replace with an array

You can replace a single match with multiple values using an array. This is helpful when populating a range of cells:

// Replaces the given string vertically with the Array. The third parameter (true) indicates vertical insertion. Use false for horizontal.
worksheet.Replace("Central", new string[] { "Central", "East" }, true);

Note: The array will be inserted vertically or horizontally, depending on the selected replace options.

The following code snippet demonstrates how to automate replacing cell values in Excel documents using C#.

using Syncfusion.XlsIO;
using System.IO;

namespace Replace
{
    class Program
    {
        public static void Main(string[] args)
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;
                FileStream fileStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
                IWorkbook workbook = application.Workbooks.Open(fileStream);
                IWorksheet worksheet = workbook.Worksheets[0];

                // Replaces the given string with another string
                worksheet.Replace("4.99", "4.90");

                // Replaces the given string with another string on a match case
                worksheet.Replace("Wilson", "William", ExcelFindOptions.MatchCase);

                // Replaces the given string with another string matching the entire cell content to the search word
                worksheet.Replace("Pen Set", "Pen", ExcelFindOptions.MatchEntireCellContent);

                // Replaces the given string with a DateTime value
                worksheet.Replace("DateValue", DateTime.Now);

                // Replaces the given string with an Array vertically
                worksheet.Replace("Central", new string[] { "Central", "East" }, true);

                // Saving the workbook as a stream
                FileStream stream = new FileStream(Path.GetFullPath("Output/Replace.xlsx"), FileMode.Create, FileAccess.ReadWrite);
                workbook.Version = ExcelVersion.Xlsx;
                workbook.SaveAs(stream);
                stream.Dispose();
            }
        }
    }
}

The following images show the input and output Excel documents before and after replacing the cell text.

Excel document before Replace

Excel document after Replace

Automating multiple file updates

To save time, you can easily process the find and replace operations on a folder containing multiple Excel files in a loop using C#. Please utilize the method below to perform the find and replace process.

public void FindAndReplace(string folderPath)
{
    foreach (var filePath in Directory.GetFiles(folderPath, "*.xlsx"))
    {
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Xlsx;
            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = application.Workbooks.Open(fileStream);

            // Perform the find and replace process here.

            // Saving the workbook in the same file path
            fileStream.Close();
            File.Delete(filePath);
            FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            workbook.Version = ExcelVersion.Xlsx;
            workbook.SaveAs(stream);
            stream.Dispose();
        }
    }
}

This way, entire directories of Excel files are updated without ever opening Excel manually.

Performance tips for large-scale automation

• Use FindFirst() when you only need one match per sheet.

• Limit to specific worksheets and specific search type when possible.

• Combine multiple replacements in a single pass before saving.

References

You can download the samples of exporting data to Excel in C# here.

FAQs

Q1: Does Syncfusion^® XlsIO support older Excel formats like .xls?

Yes. XlsIO supports both .xls ( Excel 97–2003 ) and .xlsx formats.

Q2: Can I search and replace across multiple worksheets?

Yes. You can use IWorkbook.FindAll() and IWorkbook.Replace () to perform operations across all worksheets in a workbook.

Q3: Can I restrict find and replace to specific ranges or columns in Excel?

Yes. You can define a specific range using IRange and apply FindAll () or Replace () only within that range for precise control.

Q4: Is it possible to undo changes after replacement?

Not directly. XlsIO does not support undo operations. It’s recommended that the original file be backed up before performing a replacement.

Q5: Can I use Syncfusion^® XlsIO with Excel files stored in cloud platforms like OneDrive or SharePoint?

Yes, but you must first download the file to a local or accessible stream. XlsIO works with file streams, so integration with cloud APIs is possible.

Conclusion

As you can see, Syncfusion^® Excel Library ( XlsIO ) provides various options to find and replace Excel documents in C#. They can be used effectively to generate Excel reports with high performance and to process large amounts of data.

Take a moment to peruse the documentation, where you’ll find other options and features, all with accompanying code samples. Using the library, you can also export or write Excel data to PDF, images, data tables, HTML, CSV, TSV, collections of objects, ODS, and JSON. For more insights, please refer to our online demos.

If you are new to our .NET Excel library, it is highly recommended that you follow our Getting Started guide.

Existing customers can download the new version of Essential Studio^® on the license and downloads page. If you are not a Syncfusion^® customer, try our 30-day free trial to check our incredible features.

If you have questions, comment below. You can also contact us through our support forum, support portal, or feedback portal. We are always eager to help you!

Related Blogs

• How to Export Data from SQL Server to Excel in C#

• How to Add Comments to Excel Documents Using C#

• Easy Steps to Export HTML Tables to an Excel Worksheet in C#

• Export Data from Collection to Excel and Group It in C#

This article was originally published at Syncfusion.com.