EXPORT DATA TO EXCEL ADO.NET January 12, 2009
Posted by mudassarkhan in ADO.Net, C#.Tags: .nET, ACE, ADO.Net, C# Excel, Excel, Excel 2007, Export To Excel, insert data in Excel, oledb
2 comments
Here I will explain How to insert data in Excel files using ADO.Net
First comes the Excel 97-2003 format (*.XLS)
For this Microsoft OLEDB Jet Driver will be used. Below is the function to insert Data into Excel Sheet,
just like you do in a Database.
Here’s the Mapping
Database <—————> Excel
Sheet <—————> Table
Here’s the function for Excel 97-2003 Format
//Add New Row To Excel 97-2003 File
private Boolean AddExcelRow(String strFilePath)
{
if (!File.Exists(strFilePath)) return false;
String strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;”
+ “Data Source=” + strFilePath + “;”
+ “Extended Properties=’Excel 8.0;HDR=Yes'”;
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
try
{
cmdExcel.Connection = connExcel;
//Check if the Sheet Exists
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema =
connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
DataRow[] dr = dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);
//if not Create the Sheet
if (dr == null || dr.Length == 0)
{
cmdExcel.CommandText = “CREATE TABLE [tblData]” +
“(ID varchar(10), Name varchar(50));”;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
}
connExcel.Open();
//Add New Row to Excel File
cmdExcel.CommandText = “INSERT INTO [tblData] (ID, Name)” +
” values (‘1’, ‘MAK’)”;
cmdExcel.ExecuteNonQuery();
connExcel.Close();
return true;
}
catch
{
return false;
}
finally
{
cmdExcel.Dispose();
connExcel.Dispose();
}
}
To Insert Data in Excel 2007 (*.XLSX) OLEDB Jet will not work. For that you will have
to use the new Microsoft Ace Driver. Rest all will be the same as we did for Excel 97-2003 format.
Here’s the function
//Add New Row To Excel 2007 File
private Boolean AddExcelRow2007(String strFilePath)
{
if (!File.Exists(strFilePath)) return false;
string strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;” +
“Data Source=” + strFilePath + “;Excel 12.0;HDR=YES;”;
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
try
{
cmdExcel.Connection = connExcel;
//Check if the Sheet Exists
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema =
connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
DataRow[] dr =
dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);
//if not Create the Sheet
if (dr == null || dr.Length == 0)
{
cmdExcel.CommandText = “CREATE TABLE [tblData]” +
” (ID varchar(10), Name varchar(50));”; ;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
}
//Add New Row to Excel File
cmdExcel.CommandText = “INSERT INTO [tblData]”
+ “(ID, Name) values (‘1’, ‘MAK’)”;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
return true;
}
catch
{
return false;
}
finally
{
cmdExcel.Dispose();
connExcel.Dispose();
}
}
To call the function on Button click.
protected void Button1_Click(object sender, EventArgs e)
{
//Excel 97-2003
AddExcelRow(“C:\\Book1.xls”);
//Excel 2007
AddExcelRow2007(“C:\\Book1.xlsx”);
}
Please post your comments.
mudassarkhan
Mudassar Ahmed Khan 