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)