C# Excel file OLEDB read HTML IMPORT
Posted
by Michel van Engelen
on Stack Overflow
See other posts from Stack Overflow
or by Michel van Engelen
Published on 2009-08-06T12:10:38Z
Indexed on
2010/03/30
17:23 UTC
Read the original article
Hit count: 938
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
© Stack Overflow or respective owner