Find and Replace cell value in Excel document using C# and. NET
In today's world, automation of data processing is becoming an integral part of business processes. Microsoft Excel remains one of the most popular spreadsheet tools. However, it is often necessary to automate the tasks of searching and replacing values inside a file, whether it is updating outdated data, mass editing, or preparing reports.
In this article, we will look at how to implement cell search and replacement in an Excel document using C# and .NET, using the powerful and convenient of SautinSoft Excel .NET library. We will look at the advantages of this approach, show you an example of the code, tell you what tasks it is suitable for and in which cases it is recommended to use it.
Automating the search and replace process in Excel significantly improves the efficiency of working with data:
- Massive data updates: for example, correcting erroneous names or values in the entire table in a matter of seconds.
- Preparation of reports: automatic correction of data before export.
- Maintenance of databases and information systems, where Excel acts as an intermediate tool.
- Updating templates and documents: fast data refresh without manual intervention.
Practice shows that such solutions in Excel are found in a wide variety of fields:
- Financial and managerial accounting.
- Analytics and Reporting departments.
- Integration of data from different sources.
- Development of internal business process tools.
Complete code
GitHubusing SautinSoft.Excel;
using System.IO;
namespace Example
{
class Program
{
static void Main(string[] args)
{
// Get your free key here:
// https://sautinsoft.com/start-for-free/
Sample();
}
/// <summary>
/// Find and Replace Text.
/// </summary>
/// <remarks>
/// Details:
/// </remarks>
static void Sample()
{
string inpFile = @"..\..\..\Example.xlsx";
string outFile = @"..\..\..\Result.xlsx";
ExcelDocument excelDocument = ExcelDocument.Load(inpFile);
CellRange range = excelDocument.Worksheets[0].Cells.GetSubrange("A2", "C9");
range.FindText("Random", false, true, out int row, out int col);
if (row > -1 && col > -1) excelDocument.Worksheets[0].Cells[row, col].Value = "Replace";
excelDocument.Save(outFile);
// Important for Linux: Install MS Fonts
// sudo apt install ttf-mscorefonts-installer -y
// Open the result for demonstration purposes.
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(outFile) { UseShellExecute = true });
}
}
}Imports SautinSoft.Excel
Imports System.IO
Namespace Example
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Get your free key here:
' https://sautinsoft.com/start-for-free/
Sample()
End Sub
''' <summary>
''' Find and Replace Text.
''' </summary>
''' <remarks>
''' Details:
''' </remarks>
Private Shared Sub Sample()
Dim inpFile As String = "..\..\..\Example.xlsx"
Dim outFile As String = "..\..\..\Result.xlsx"
Dim excelDocument As ExcelDocument = ExcelDocument.Load(inpFile)
Dim range As CellRange = excelDocument.Worksheets(0).Cells.GetSubrange("A2", "C9")
Dim row As Integer
Dim col As Integer
range.FindText("Random", False, True, row, col)
If row > -1 AndAlso col > -1 Then
excelDocument.Worksheets(0).Cells(row, col).Value = "Replace"
End If
excelDocument.Save(outFile)
' Important for Linux: Install MS Fonts
' sudo apt install ttf-mscorefonts-installer -y
' Open the result for demonstration purposes.
System.Diagnostics.Process.Start(New System.Diagnostics.ProcessStartInfo(outFile) With {.UseShellExecute = True})
End Sub
End Class
End Namespace
If you need a new code example or have a question: email us at support@sautinsoft.com or ask at Online Chat (right-bottom corner of this page) or use the Form below: