如何使用 ADO.NET 读取 .XLSX (Excel 2007) 文件？我发现“无法找到可安装的 ISAM”-错误

发布于 2024-09-29 06:26:03 字数 660 浏览 11 评论 0原文

我需要在 .net 2.0 中工作。所以我不能使用 OpenXML。

这是我的源代码，我已经安装了 AccessDatabaseEngine.exe。

但仍然遇到异常：

“找不到可安装的 ISAM”。

我还在连接字符串中尝试了“Extended Properties=Excel 8.0”。

static void Main(string[] args)
{
    DataSet dataSet = new DataSet();

    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|Data Directory|\HSC.xlsx;Extended Properties=Excel 12.0;HDR=YES;");           
    OleDbDataAdapter dataAdapter= new OleDbDataAdapter("select * from [Sheet1$]", connection);

    dataAdapter.Fill(dataSet);
}

原文

I need to work in .net 2.0. So I can't use OpenXML.

This is my source code and I have already Installed AccessDatabaseEngine.exe.

But still getting the exception:

"Could not find installable ISAM".

I have also tried "Extended Properties=Excel 8.0" in the connection string.

static void Main(string[] args)
{
    DataSet dataSet = new DataSet();

    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|Data Directory|\HSC.xlsx;Extended Properties=Excel 12.0;HDR=YES;");           
    OleDbDataAdapter dataAdapter= new OleDbDataAdapter("select * from [Sheet1$]", connection);

    dataAdapter.Fill(dataSet);
}

分享到QQ

分享到微博

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

发布评论

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

じ违心 2024-10-06 06:26:03

根据 Carl Prothman 的说法，应该是

 Extended Properties="Excel 12.0 Xml;

- http://www.connectionstrings.com/excel-2007

更详细：

 OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Docs\\Book2.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;'");

注意单引号。

According to Carl Prothman, that should be

 Extended Properties="Excel 12.0 Xml;

-- http://www.connectionstrings.com/excel-2007

In more detail:

 OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Docs\\Book2.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;'");

Note the single quotes.

回复收藏 0 原文

左秋 2024-10-06 06:26:03

我更喜欢使用 Microsoft OpenXML 2.0 SDK 来实现此类功能。它有一个非常漂亮的界面，并且不需要在读取 XLSX 文件的计算机上安装 Office，这是一件好事。

我是用手机写这篇文章的，所以很难提供链接，但谷歌搜索应该很容易找到它。

尝试一下。我想你会喜欢的。

编辑

如果您必须使用.NET 2.0，则可以改用OleDb的JET变体。

这意味着您将执行类似以下操作来连接：

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + 
                 "Data Source='" + filename + "';" + 
                 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";);

然后您可以像上面的示例中那样查询它：

OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from [Sheet1$]", connection);

尝试一下！请注意，Jet 有一些奇怪的逻辑来决定列是否为数字。有关详细信息，请参阅以下 SO 问题：问题使用 OleDbDataAdapter 从 Excel 工作表中获取数据

I prefer to use the Microsoft OpenXML 2.0 SDK for this kind of functionality. It has a really nice interface, and it does not put a demand on having Office installed on the machine reading the XLSX file which is a good thing.

I'm writing this from my mobile, so hard to provide a link, but a Google search should easily find it for you.

Give it a try. I think you will like it.

EDIT

If you have to use .NET 2.0, you can go for using the JET variant of the OleDb instead.

That means you will do something like this to connect:

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + 
                 "Data Source='" + filename + "';" + 
                 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";);

Then you can query it like in your example above:

OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from [Sheet1$]", connection);

Try it! Just note that Jet have some strange logic of deciding if a column is numeric or not. See the following SO questions for details: Problem with using OleDbDataAdapter to fetch data from a Excel sheet

回复收藏 0 原文

陌若浮生 2024-10-06 06:26:03

您应该确保连接字符串如下所示（即使您正在访问 microsoft excel 版本 10 ->

MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='D:\csharp-Excel.xls';Extended Properties='Excel 12.0 Xml;HDR=Yes;'");

You should make sure that the connection string looks like the following ( even if you are accessing microsoft excel version 10 ->

MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='D:\csharp-Excel.xls';Extended Properties='Excel 12.0 Xml;HDR=Yes;'");

回复收藏 0 原文

~没有更多了~