如何阅读超过65535行的Excel 2007电子表格？

Here's what my connection string looks like..

这是我的连接字符串的样子..

m_conn = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=" + (char)34 + "Excel 12.0;HDR=YES" + (char)34, m_fileName));

When I do a select, I only get 65535 rows back but I know the excel spreadsheet has at least 100,000. How Do I read the rest of the rows. Note I have to use OLEDB, no 3rd party tools right now.

当我选择时，我只返回65535行，但我知道excel电子表格至少有100,000行。我如何阅读其余行。注意我必须使用OLEDB，现在没有第三方工具。

Thanks much!

非常感谢！

edit: These are Excel 2007/2010 files I'm working with

编辑：这些是我正在使用的Excel 2007/2010文件

edit2: Would seem I can actually select all the rows in the spreadsheet if I do a "Select * from worksheet" as opposed to "Select * from worksheet WHERE something"

edit2：如果我执行“Select * from worksheet”而不是“Select * from worksheet WHERE something”，我似乎可以实际选择电子表格中的所有行

I guess when you implement a WHERE clause, the resulting rows is capped at 65535

我想当你实现一个WHERE子句时，结果行的上限为65535

3 个解决方案

#1

As per my experience, an Excel worksheet is limited to 65535 lines (Excel up to 2003, I don't know about the 2007 version).

根据我的经验，Excel工作表限制为65535行（Excel到2003年，我不知道2007版本）。

EDIT #1

编辑＃1

My guess is that the OleDB data provider perhaps needs an update to correct this issue issued from the change of this Excel's limit.

我的猜测是，OleDB数据提供程序可能需要更新才能更正因此Excel限制更改而发出的此问题。

#2

If you only want to read an Excel file, I recommend trying the open-source Excel Data Reader.

如果您只想阅读Excel文件，我建议您尝试使用开源Excel Data Reader。

#3

You should open such files and save them with compatibility mode turned off. You will get more than 64k in your queries.

您应该打开这些文件并在兼容模式关闭时保存它们。您的查询中将获得超过64k。

#1