C# Excel file OLEDB read HTML IMPORT
- by Michel van Engelen
Hi,
I have to automate something for the finance dpt. I've got an Excel file which I want to read using OleDb:
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=A_File.xls;Extended Properties=""HTML Import;IMEX=1;""";
using (OleDbConnection connection = new OleDbConnection())
{
using (DbCommand command = connection.CreateCommand())
{
connection.ConnectionString = connectionString;
connection.Open();
DataTable dtSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if( (null == dtSchema) || ( dtSchema.Rows.Count <= 0 ) )
{
//raise exception if needed
}
command.CommandText = "SELECT * FROM [NameOfTheWorksheet$]";
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//do something with the data
}
}
}
}
Normally the connectionstring would have an extended property "Excel 8.0", but the file can't be read that way because it seems to be an html file renamed to .xls.
when I copy the data from the xls to a new xls, I can read the new xls with the E.P. set to "Excel 8.0".
Yes, I can read the file by creating an instance of Excel, but I rather not..
Any idea how I can read the xls using OleDb without making manual changes to the xls or by playing with ranges in a instanciated Excel?
Regards,
Michel
