Export Data To Excel using ADO.Net

Excel Workbook is just like database with sheets corresponding to tables. See the mapping below.

Database <—————> Excel Workbook

Sheet <—————-> Table

Connection String for Excel 97-2003 Format (.XLS)

For Excel 97-2003 format Microsoft Jet OLEDB Driver 4.0 is used. A sample connection string as follows.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes'"

Connection String for Excel 2007 Format (.XLSX)

For Excel 2007 format the new Microsoft Ace OLEDB Driver 12.0 is used. A sample connection string as follows.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties='Excel 8.0;HDR=Yes'"

Rest everything is same for both versions. One thing to note Microsoft Ace OLEDB Driver 12.0 works for both Excel 2003 and Excel 2007 format.

You can specify whether your Excel file has Headers or not using the HDR property.

When HDR is set to Yes the First Row is considered as the Header of the Excel file.

Establish a Connection

String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

+ "Data Source=Book1.xls;"

+ "Extended Properties='Excel 8.0;HDR=Yes'";

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

Accessing Sheets

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

connExcel.Close();

The dtExcelSchema contains all the Sheets present in your Excel Workbook

You access them in the following way

string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"];

This will give the name of the first sheet. i.e. Sheet1$

Create a new sheet

cmdExcel.CommandText = "CREATE TABLE [tblData]" +

"(ID varchar(10), Name varchar(50));";

connExcel.Open();

cmdExcel.ExecuteNonQuery();

connExcel.Close();

The above code creates a new Sheet in the Excel Workbook with the name tblData with two columns ID and Name.

Insert Record into Sheet

connExcel.Open();

cmdExcel.CommandText = "INSERT INTO [tblData] (ID, Name)" +

" values ('1', 'MAK')";

cmdExcel.ExecuteNonQuery();

connExcel.Close();

Update existing Record into Sheet

connExcel.Open();

cmdExcel.CommandText = "UPDATE [tblData] " +

"SET Name ='John' WHERE ID = '1'";

cmdExcel.ExecuteNonQuery();

connExcel.Close();