How to Find and Replace Data in Excel with C#, VB.NET

Excel Find and Replace function can be effectively used to find a certain value in the text and replace it to another. Especially, when in a long excel document, it really takes much time to correctly find what you want and change it. The value you want to find maybe a data, a formula, a name or other text. Whatever it is,Spire.XLS, an excel component, can help you quickly realize this task in a few minutes. 

Excel Find and Replace with C#,VB.NET.

Using Spire.XLS,  you not only can replace data on the condition that you can find them directly with a few seconds, but also allows you to quickly look for what you need to replace in a long excel worksheet. Now, follow the blow steps to finish this task with C#, VB.NET.




Procedure
1.Create a workbook and load an excel file from the system.

2.Find and replace. I find "Asia" and replace it to "South American" in the column 20 and 21.

3.Save and launch the file.

Full Code:

C# Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
namespace find_and_replace
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a workbook
            Workbook workbook = new Workbook();
           workbook.LoadFromFile(@"C:\e-iceblue\employee.xls ", ExcelVersion.Version97to2003);
            Worksheet worksheet = workbook.Worksheets[0];

            //find and replace
            CellRange[] ranges = worksheet.FindAllString("Asia", false, false);
            foreach (CellRange range in ranges)
            {
                range.Text = "South American";
            }

            //save the workbook
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start("sample.xls");
        }
    }
}

VB.NET Code:

Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Spire.Xls
Namespace find_and_replace
         Class Program
                 Private Shared Sub Main(args As String())
                        'create a workbook                        
                         Dim workbook As New Workbook()
  workbook.LoadFromFile("C:\e-iceblue\employee.xls ", ExcelVersion.Version97to2003
                          Dim worksheet As Worksheet = workbook.Worksheets(0)
                        'find and replace                         
                    Dim ranges As CellRange() = worksheet.FindAllString("Asia", False, False)
                          For Each range As CellRange In ranges
                                   range.Text = "South American"
                          Next
                       'save the workbook                     
                        workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
                          System.Diagnostics.Process.Start("sample.xls")
                 End Sub
         End Class
End Namespace

Preview

original file

find and replace

Find and Replace is just a small function of Spire.XLS. Besides, Spire.XLS  have scores of other functions including the basic format of data, formula, worksheet edition, background color, image, security and so on.  Spire.XLS also can be converted to many other popular file formats such as PDF, HTML, XML, Image, CSV etc. Click to find more features about Spire.XLS.

Source