Using ADO.NET to work with Excel files

This articles gives a quick overview of how we can Create Excel files/Modify existing excel files/Insert data to an excel file using OleDB providers. The code  describes use of Jet oleDB provider , hence it assumes that application is running on x86 platform or atleast built on x86 platform(See build configuration of your project).  I will describe later in this article on How can we make it work for 64 bit platform without modifying build configurations.

First of all we need to create a connection to an Excel sheet. the below string is a sample connection string (i have assumed that D:\\Book1.xls  is the excel file location.)

private const string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Book1.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";

the value HDR=YES describes that excel contains the Header Rows and Insert/Updates will be done on the basis of Header column names. In case header row doesn’t exists , we need to provide HDR=NO in connection string.

Now comes the main part of performing various excel operations.

1-Reading an Existing Excel sheet: Below is a working piece of code , you will need to execute to Read data from excel.

           DataSet dsData = new DataSet();
string command = "Select * from [Sheet1$]";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter adaptor = new OleDbDataAdapter(command, connection);
adaptor.Fill(dsData);
connection.Close();
return dsData;

Note:We need to tell command string , as to which worksheet we want to pull data from ,like Sheet1 in the code above . My excel file contains a worksheet named Sheet1 .

2-Populating Data in an Excel file: Sample code for inserting data in excel file. in Below code i am inserting data in to Sheet1 worksheet. and i have a Header row with column names col1 , col2 , col3.

           string commandTxt = "Insert Into [Sheet1$](col1,col2,col3) Values(2,4,5)";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand(commandTxt ,connection);
command.ExecuteNonQuery();

           connection.Close();

3-Creating a New Sheet in excel: Sample code for Creating a new sheet named Sheet6 and inserting data in to it.

string commandTxt = "CREATE TABLE Sheet6(Id char(255), Name char(255), BirthDate date)";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand(commandTxt ,connection);
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Sheet6 (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')";
command.ExecuteNonQuery();
connection.Close();

4-Creating a Header Row: Based on my experience on working with excel files , i have found that it is always easy and productive to work with excel files when already have a template available. andin such scenarios above piece of code should do the trick. But in real life , not always can we guarranty that we will have a fix template available , which can be used to populate the data.  In such scenarios ,we can first create a template with header row and then populate it with DataSet , as explained above.

As per Microsoft’s documentation , the below command line should work fine,if we want to insert a header row.

string commandTxt = "Insert Into [Sheet2$](F1,F2,F3) Values('NewCol','NewCol2','NewCol3')";

but i wasn’t lucky enough to get this working. (i tried both HDR=YES and HDR=NO , in connection string , still no luck)

Here is what i used to insert Header Row in excel.

Now coming to point of working with xlsx files and on 64 bit platform , Microsoft has release a new driver (ACE 12.0)  for these scenarios. So essentially  by just modifying the connection string , we should be good to work in all scenarios.

that’s it for now, ciao

Updates: Added code for creating a header row in excel.

Technorati Tags: C#,ADO.NET,oledb,Excel,Jet friver,ACE 12.0,oleDbConnection