SQL 查询 Excel 2010 时诊断 OLEDB 异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1461053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Diagnosing an OLEDB exception when Quering Excel 2010
提问by Stefan Steiger
To query an excel sheet via SQL, I used to use either:
要通过 SQL 查询 Excel 表，我曾经使用过：
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""
or
或者
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.
现在这一切正常，直到我安装了 Office 2010。
Now I get a
现在我得到一个
Microsoft.Ace.OLEDB.12.0 provider is not registered on this machine exception.
Microsoft.Ace.OLEDB.12.0 提供程序未在此计算机上注册异常。
How can I find out the correct connection string/provider?
如何找出正确的连接字符串/提供程序？
采纳答案by onedaywhen
Perhaps you uninstalled the Access Database Engine (ACE) components? They are still available for download from MSDN as 2007 Office System Driver: Data Connectivity Components.
也许您卸载了 Access 数据库引擎 (ACE) 组件？它们仍可作为2007 Office System Driver: Data Connectivity Components从 MSDN 下载。
回答by JDunkerley
I believe for Excel 2010 it is :
我相信 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"
This appears to work in my visual studio, I got Excel to generate the query string and it had the extra entry in it.
这似乎在我的视觉工作室中工作，我让 Excel 生成查询字符串，并且其中有额外的条目。
回答by AFD
I downloaded and installed Office System Driver: Data Connectivity Components as advised above - and the below code worked:
我按照上面的建议下载并安装了 Office System Driver: Data Connectivity Components - 下面的代码有效：
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();
}
