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.
- Find and Replace Data in a Worksheet in Excel
- Find and Replace Data in a Specific Cell Range in Excel
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();
}
}
}
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();
}
}
}
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.
