How to Replace Data in Excel with C#

It is difficult to find data in one cell of an Excel file which has a large amount of data. Therefore, MS Excel offers a Find function to get wanted data information quickly. Also, after finding, we can replace the original information to another if necessary.

In this post, I will introduce a method to find and replace data in Excel. If the data is in file, replace it directly with other words. If not, a dialog box will pop up and show error message. The following image shows the original data in file.

Note: This method is based on component Spire.XLS. Therefore, I need to add its DLL file as reference in my project.

Steps:

  1. Get the file which we want to find and replace data.

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@"D:\work\My Documents\VIP.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

  2. Replace found data with another information. But if this data doesn’t exist in this file, give an error message.

            try

            {

                CellRange range = worksheet.FindString("Australia", false, false);

                worksheet.Replace(range.Value, "United Kingdom");

            }

            catch (System.Exception)

            {

     MessageBox.Show("Microsoft Excel cannot find the data you're searching for", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }

  3. Save and launch new file.

            workbook.SaveToFile("Replace.xlsx", ExcelVersion.Version2010);

            System.Diagnostics.Process.Start("Replace.xlsx");

Complete Coding:

using System;

using System.Windows.Forms;

using Spire.Xls;

namespace replace

{

    class Replace

    {

        static void Main(string[] args)

        {

            //Load File

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@"D:\work\My Documents\VIP.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            //Replace Australia with United Kingdom);

            try

            {

                //Find String to Replace

                CellRange range = worksheet.FindString("Australia", false, false);

                //Replace This String

                worksheet.Replace(range.Value, "United Kingdom");

            }

            catch (System.Exception)

            {

                //If Cannot Find Specified String, Give This Message

                MessageBox.Show("Microsoft Excel cannot find the data you're searching for", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }

            //Save and Launch File

            workbook.SaveToFile("Replace.xlsx", ExcelVersion.Version2010);

            System.Diagnostics.Process.Start("Replace.xlsx");

        }

    }

}

After running, we can get the following result:

If we change the string "Australia" in the sentence: CellRange range = worksheet.FindString("Australia", false, false); to "xyz" which doesn't exist in original file, we can get the error message as following:

 ___________________________________________________________________________________________

Click here to learn more about Spire.XLS

Click here to download Spire.XLS

Advertisements

3 thoughts on “How to Replace Data in Excel with C#

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s