Thursday, April 24, 2008

c# read Excel 2007 .xlsx files

In order to read .xlsx files you need the drivers: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

If you don't, you may get the error: "Could not find installable ISAM."

dir = new DirectoryInfo(System.Environment.CurrentDirectory);
xlsFiles = dir.GetFiles("*.xls", SearchOption.AllDirectories);

//loop thru all the text files
foreach (FileInfo xlsFile in xlsFiles)
{
DataSet dataSet1 = new DataSet();
OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
OleDbDataAdapter dataAdapter1;

if (System.IO.Path.GetExtension(xlsFile.Name) == ".xlsx")
{

connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 12.0;'";
}
else
{
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 8.0;'";
}

dataAdapter1 = new OleDbDataAdapter("select * from [Sheet1$]", connection);

dataAdapter1.Fill(dataSet1);
DataTable dt = dataSet1.Tables[0];

}