使用ADO从Excel读取数据的空值

I am reading data from an Excel 2007 spreadsheet using ADO. Setting up the connection is easy:

我正在使用ADO阅读Excel 2007电子表格中的数据。建立连接很容易:

Dim ado As ADODB.Connection
Set ado = CreateObject("ADODB.Connection")
ado.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFilename.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
ado.Open

I can call ado.OpenSchema without any trouble on this object. However, when I try to query the data:

我可以叫ado。在这个对象上没有任何问题的OpenSchema。但是，当我试图查询数据时:

Dim rs As ADODB.recordSet
Set rs = ado.Execute("SELECT * FROM [Current Work Load$]")

I simply get a table full of Nulls.

我只需要得到一个满是null的表。

This is mentioned as an issue on the Microsoft Support site - but I have explicitly enabled "Import Mode" (as you can see in the code above - IMEX=1).

这是Microsoft Support站点上的一个问题——但是我已经显式地启用了“导入模式”(正如您在上面的代码中看到的- IMEX=1)。

4 个解决方案

#1

The Execute method does not return any records as it is for action queries. Your might want to try the OpenRecordset method.

Execute方法不返回任何记录，就像操作查询一样。您可能想尝试OpenRecordset方法。

Dim rs As ADODB.recordSet
Set rs = ado.OpenRecordset("SELECT * FROM [Current Work Load$]")

#2

I've found the ADO connection strings here are unbelievably picky. I've gotten reading the spreadsheets to work but with a slightly different connection string:

我发现这里的ADO连接字符串非常复杂。我已经阅读了电子表格，但使用了一个稍微不同的连接字符串:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + @";Extended Properties="Excel 12.0;IMEX=1";

数据来源=" + fileName + @";扩展属性="Excel 12.0;IMEX=1";

(I don't have the XML after the Excel 12.0 declaration).

(在Excel 12.0声明之后，我没有XML)。

#3

SpreadsheetGear for .NET can read Excel workbooks and enables you to access any cells without the kinds of issues / limatations you can run into with ADO.

net的SpreadsheetGear可以阅读Excel工作簿，并且可以访问任何单元，而不会遇到ADO可能遇到的各种问题。

You can see live C# & VB samples here and download the free trial here.

您可以在这里看到c#和VB的实时样本，并在这里下载免费试用。

Disclaimer: I own SpreadsheetGear LLC

免责声明:我拥有SpreadsheetGear LLC公司

#4

As well as using IMEX=1 in the connection string, you need to review a couple of registry keys. For more details, see this answer on SO.

除了在连接字符串中使用IMEX=1之外，还需要检查两个注册表键。有关更多细节，请参见这个答案。

#1