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();

                }

            }

        }

    }

}

Inserting Data into 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 InsertingDataIntoExcel

{

    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 = "INSERT INTO [Students$] ([First Name], [Last Name], [Age]) VALUES ('John', 'Abraham', '34')";

                        Command.ExecuteNonQuery();

                        Console.WriteLine("Data Inserted Successfully");

                    }

                    Console.ReadLine();

                }

                catch (OleDbException ex)

                {

                    Console.WriteLine("Message: " + ex.Message);

                    Console.ReadLine();

                }

                finally

                {

                    Connection.Close();

                }

            }

        }

    }

}

Creating Table 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 TableCreationInExcel

{

    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 = "CREATE TABLE [Students] ([First Name] Char(200), [Last Name] Char(200), [Age] Char(2))";

                        Command.ExecuteNonQuery();

                        Console.WriteLine("Table Created Successfully");

                    }

                    Console.ReadLine();

                }

                catch (OleDbException ex)

                {

                    Console.WriteLine("Message: " + ex.Message);

                    Console.ReadLine();

                }

                finally

                {

                    Connection.Close();

                }

            }

        }

    }

}

Creating Excel file using C#

Step 1: Create a New Project.

Step 2: Right click on the “References” in the “Solution Explorer”.

Step 3: Select “Add Reference”.

Step 4: Select “COM” tab.

Step 5: Select “Microsoft Excel XX.0 Object Library” where XX may be 12 or 14.

Step 6: Write the following code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

// Adding the "Microsoft Excel XX.0 Object Library" dll

using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelCreation

{

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Enter Output File Path: ");

            // The path to create the Excel file along with the total path

            string outputPath = @"" + Console.ReadLine() + "Output.xlsx";

            // Opening Excel Application

            Excel.Application excel = new Excel.Application();

            // Creating Workbook

            Excel._Workbook wb = (Excel._Workbook)(excel.Workbooks.Add(System.Reflection.Missing.Value));

            // Saving Workbook

            wb.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookDefault, null, null, null, null, Excel.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);

            // Closing Excel Application

            excel.Quit();

            Console.WriteLine("Success");

        }

    }

}