C#/VB.NET: Find and Replace Data in Excel

Excel's Find and Replace feature is an indispensable tool for users when editing large Excel spreadsheets. It allows users to search for specific values within a worksheet or cell range and update them with new values quickly and accurately. With this feature, users don't need to perform manual searches, which significantly improves their working efficiency. In this article, we will introduce how to programmatically find and replace data in Excel in C# and VB.NET using Spire.XLS for .NET library.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Find and Replace Data in a Worksheet in Excel in C# and VB.NET

Spire.XLS for .NET offers the Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method which enables you to find the cells containing specific data values in an Excel worksheet. Once the cells are found, you can use the CellRange.Text property to update their values with new values. The detailed steps are as follows:

  • Initialize an instance of the Workbook class.
  • Load an Excel file using the Workbook.LoadFromFile(string fileName) method.
  • Get a specific worksheet of the file using the Workbook.Worksheets[int index] property.
  • Find the cells containing a specific value in the worksheet using the Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method.
  • Iterate through the found cells.
  • Update the value of each cell with another value using the CellRange.Text property.
  • Set a background for the cell so you can easily find the updated cells using the CellRange.Style.Color property.
  • Save the result file to a specific location using the Workbook.SaveToFile(string fileName, ExcelVersion version) method.
  • C#
  • VB.NET
using Spire.Xls;
using System.Drawing;

namespace ReplaceDataInWorksheet
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile(@"Sample.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Find the cells with the specific string value “Total” in the worksheet
            CellRange[] cells = worksheet.FindAllString("Total", true, true);

            //Iterate through the found cells
            foreach (CellRange cell in cells)
            {
                //Replace the value of the cell with another value
                cell.Text = "Sum";
                //Set a background color for the cell
                cell.Style.Color = Color.Yellow;
            }

            //Save the result file to a specific location
            workbook.SaveToFile("ReplaceDataInWorksheet.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

C#/VB.NET: Find and Replace Data in Excel

Find and Replace Data in a Specific Cell Range in Excel in C# and VB.NET

You can find the cells containing a specific value in a cell range using the CellRange.FindAllString(string stringValue, bool formula, bool formulaValue) method. Then you can update the value of each found cell with another value using the CellRange.Text property. The detailed steps are as follows:

  • Initialize an instance of the Workbook class.
  • Load an Excel file using the Workbook.LoadFromFile(string fileName) method.
  • Get a specific worksheet of the file using the Workbook.Worksheets[int index] property.
  • Get a specific cell range of the worksheet using the Worksheet.Range[string rangeName] property.
  • Find the cells with a specific value in the cell range using the CellRange.FindAllString(string stringValue, bool formula, bool formulaValue) method.
  • Iterate through the found cells.
  • Update the value of each found cell to another value using the CellRange.Text property.
  • Set a background for the cell so you can easily find the updated cells using the CellRange.Style.Color property.
  • Save the result file to a specific location using the Workbook.SaveToFile(string fileName, ExcelVersion version) method.
  • C#
  • VB.NET
using Spire.Xls;
using System.Drawing;

namespace ReplaceDataInCellRange
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile(@"Sample.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Get a specific cell range
            CellRange range = worksheet.Range["A1:C9"];            

            //Find the cells with the specific value "Total" in the cell range
            CellRange[] cells = range.FindAllString("Total", true, true);

            //Iterate through the found cells
            foreach (CellRange cell in cells)
            {
                //Replace the value of the cell with another value
                cell.Text = "Sum";
                //Set a background color for the cell
                cell.Style.Color = Color.Yellow;
            }

            //Save the result file to a specific location
            workbook.SaveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

C#/VB.NET: Find and Replace Data in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.