This article explains how to create an Microsoft Excel Sheet 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();


You can download the sample source code here.

ExportExcel.zip (10.55 kb)