C＃的Excel文件，OLEDB读取HTML IMPORT [英] C# Excel file OLEDB read HTML IMPORT

问题描述

我要自动东西的财务DPT。我已经得到了我想要使用的OleDb读取Excel文件：

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
            }
        }
    }
}

通常情况下，的connectionString 将有一个扩展属性的Excel 8.0，但该文件无法读取的方式，因为它似乎是一个HTML文件重命名为。 XLS。 当我从XLS中的数据复制到一个新的XLS，我的可以阅读新XLS与EP设置为创先争优8.0。

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".

是的，我可以通过创建一个Excel的实例读取该文件，但我宁愿不.. 任何想法如何，我可以读取使用的OleDb的XLS，而不进行手动更改XLS或与范围在Excel中实例化打？

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?

问候，

米歇尔

推荐答案

我问另一个论坛上同样的问题，得到的答案，所以我想我会在这里分享。根据这篇文章： http://ewbi.blogs.com/开发/ 2006/12 /读取％5Fhtml％5Fta.html

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

，你必须使用select语句的网页标题没有$。 SELECT * FROM [HTMLPageTitle]

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

这篇关于C＃的Excel文件，OLEDB读取HTML IMPORT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！