Developers Heaven Forum

Desktop Programming => .NET Programming => ADO.NET => Topic started by: admin on August 24, 2008, 12:36:17 PM

Title: How to import data from Excel file using C#
Post by: admin on August 24, 2008, 12:36:17 PM

private void btnImportExcelToGrid_Click(object sender,
System.EventArgs e)
{
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Book2.xls;" +
"Extended Properties=Excel 8.0;";

DataSet ds = new DataSet();
//You must use the $ after the object
//you reference in the spreadsheet
OleDbDataAdapter da = new OleDbDataAdapter
("SELECT * FROM [Sheet1$]", strConn);

//da.TableMappings.Add("Table", "ExcelTest");

da.Fill(ds);
DataGrid2.DataSource = ds.Tables[0].DefaultView;
DataGrid2.DataBind();
}

Title: Re: How to import data from Excel file using C#
Post by: admin on August 25, 2008, 10:19:37 AM

For Excel 2007 you can use the following connection string:
"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\Book2.xls;" +
"Extended Properties=Excel 12.0;"

Title: Re: How to import data from Excel file using C#
Post by: admin on August 31, 2008, 02:45:44 PM

by the way you needn't to have Excel instauled on the server machine only you can download the driver from:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&DisplayLang=en
 :)

Title: Re: How to import data from Excel file using C#
Post by: admin on January 20, 2009, 04:17:26 PM

Important note: if you are selecting data for a specific date you can use the following:
"SELECT * FROM [Sheet1$] where datecol=#3/15/2009#"
don't try to use the single quotes " ' ".