Thursday 10 January 2013

Updating Data in Excel using C#


Step 1: Create New Project.
Step 2: Add “System.Data” and “System.Data.OleDb” namespaces.
Step 3: The connection string for accessing Excel file is
“Provider = Microsoft.ACE.OLEDB.12.0; Data Source = [Excel File Path]; Extended Properties = “Excel 12.0 Xml;HDR=YES””
Step 4: Write the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace UpdatingDataInExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel file path
            string excelFilePath = @"F:\Excel File.xlsx";
            // Connection string for accessing excel file
            string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=""Excel 12.0 Xml;HDR=YES""", excelFilePath);

            using (OleDbConnection Connection = new OleDbConnection(connectionString))
            {
                try
                {
                    Connection.Open();
                    using (OleDbCommand Command = new OleDbCommand())
                    {
                        Command.Connection = Connection;
                        Command.CommandText = "UPDATE [Students$] SET [First Name] = 'Sharukh', [Last Name] = 'Khan', [Age] = '40' WHERE [First Name] = 'John'";
                        int i = Command.ExecuteNonQuery();
                        Console.WriteLine("Data Updated Successfully");
                    }
                    Console.ReadLine();
                }
                catch (OleDbException ex)
                {
                    Console.WriteLine("Message: " + ex.Message);
                    Console.ReadLine();
                }
                finally
                {
                    Connection.Close();
                }
            }
        }
    }
}

No comments:

Post a Comment