Author Topic: How to import data from Excel file using C#  (Read 23034 times)

Offline admin

  • Administrator
  • Sr. Member
  • *****
  • Posts: 296
    • View Profile
How to import data from Excel file using C#
« 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();
}


Offline admin

  • Administrator
  • Sr. Member
  • *****
  • Posts: 296
    • View Profile
Re: How to import data from Excel file using C#
« Reply #1 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;"

Offline admin

  • Administrator
  • Sr. Member
  • *****
  • Posts: 296
    • View Profile
Re: How to import data from Excel file using C#
« Reply #2 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
 :)

Offline admin

  • Administrator
  • Sr. Member
  • *****
  • Posts: 296
    • View Profile
Re: How to import data from Excel file using C#
« Reply #3 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 " ' ".