Thursday 10 January 2013

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

3 comments:

  1. I'd set up separate 'try' blocks for Connection.Open() and Command.ExecuteNonQuery, so you can handle connection errors from command errors without looking for specifics. Also, since you invoke the connection with a 'Using' statement, you don't need to worry about closing it manually.

    ReplyDelete
  2. You can create tables in excel file in C# with Aspose.Cells for .NET Library, you should try it, it offers many more features(code samples) which developers can use in their applications.

    http://www.aspose.com/.net/excel-component.aspx

    ReplyDelete