current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

I asked this same question on another forum and got the answer so I figured I'd share it here. As per this article: http://ewbi.blogs.com/develops/2006/12/reading%5Fhtml%5Fta.html

Instead of using the sheetname, you must use the page title in the select statement without the $. SELECT * FROM [HTMLPageTitle]

share|improve this answer
    
Thank you for your answer, it looks like it will do the trick. I had to finish my program so I implemented it with by instantiating Excel for now. When I get time I will revise the code! –  Michel van Engelen Aug 17 '09 at 7:21
add comment
up vote 1 down vote

I've been searching so many solution, end up I found something really simple and easy - to import XML file to Excel file, I tried to convert XML to HTML first, use -

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=63

then I found I could easily change my output file as .xls, instead of .html

        //create the output stream
        XmlTextWriter myWriter = new XmlTextWriter
("result.html", null);

then the output is perfect Excel file from my XML data file.

hope this will save ur work.

share|improve this answer
add comment
up vote 1 down vote

I have run into the same problem. As previously mentioned, 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".

In this scenario, the file couldn't be saved in the correct format. So we have to convert that file to the correct format. To do this, use MS Office Excel 2007, Click File -> Convert. The file will be converted to the right format automatically.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.