Replace Data in Excel in C#, VB.NET

  • Demo
  • C# source
  • VB.Net source

The sample demonstrates how to replace data in Excel workbook via Spire.XLS.

using Spire.Xls;
using System;

namespace Replace
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a workbook
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\country.xls",ExcelVersion.Version97to2003);
            Worksheet worksheet = workbook.Worksheets[0];
           
            //replace the "South America" to "China";
            try
            {
                //find the word will be replaced
                CellRange range = worksheet.FindString("South America", false, false);
                
                //use the word "China" repalce the word just founded
                worksheet.Replace(range.Value, "China");
            }
            catch (System.Exception)
            {

                //if couldn't find the word,give a message
                Console.WriteLine("The text doesn't exist the word you want to replace");

            }

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

Imports Spire.Xls
Imports System

Namespace Replace
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			'create a workbook
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\country.xls",ExcelVersion.Version97to2003)
			Dim worksheet As Worksheet = workbook.Worksheets(0)

			'replace the "South America" to "China";
			Try
				'find the word will be replaced
				Dim range As CellRange = worksheet.FindString("South America", False, False)

				'use the word "China" repalce the word just founded
				worksheet.Replace(range.Value, "China")
			Catch e1 As System.Exception

				'if couldn't find the word,give a message
				Console.WriteLine("The text doesn't exist the word you want to replace")

			End Try

			'save the workbook
			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace