查询 Excel 2010 时诊断 OLEDB 异常

发布于 2024-08-05 16:06:35 字数 562 浏览 5 评论 0原文

要通过 SQL 查询 Excel 工作表，我曾经使用：

Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""

或

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"""

现在这工作正常，直到我安装了 Office 2010。

现在我得到了

Microsoft.Ace.OLEDB.12.0 提供程序未在此计算机上注册异常。

如何找到正确的连接字符串/提供程序？

原文

To query an excel sheet via SQL, I used to use either:

Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"""

Now this worked fine until I installed Office 2010.

Now I get a

Microsoft.Ace.OLEDB.12.0 provider is not registered on this machine
exception.

How can I find out the correct connection string/provider?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟燃烟灭 2024-08-12 16:06:35

我相信对于 Excel 2010 来说是这样的：

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\\MyDocs\\oledb.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:Engine Type=37"

这似乎在我的视觉工作室中工作，我让 Excel 生成查询字符串，并且其中有额外的条目。

I believe for Excel 2010 it is :

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\\MyDocs\\oledb.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:Engine Type=37"

This appears to work in my visual studio, I got Excel to generate the query string and it had the extra entry in it.

回复收藏 0 原文

[浮城] 2024-08-12 16:06:35

我按照上面的建议下载并安装了 Office 系统驱动程序：数据连接组件 - 以下代码有效：

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Password=\"\";User ID=Admin;Data Source=d:\\Sample.xlsx;Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";

    OleDbConnection connection = new OleDbConnection(connectionString);

    try
    {
        connection.Open();

        OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = command;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();

    }
    catch (Exception)
    {            
        //throw;
    }
    finally
    {
        connection.Close();
    }

I downloaded and installed Office System Driver: Data Connectivity Components as advised above - and the below code worked:

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Password=\"\";User ID=Admin;Data Source=d:\\Sample.xlsx;Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";

    OleDbConnection connection = new OleDbConnection(connectionString);

    try
    {
        connection.Open();

        OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = command;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();

    }
    catch (Exception)
    {            
        //throw;
    }
    finally
    {
        connection.Close();
    }

回复收藏 0 原文