为什么一个ADO.NET Excel查询工作而另一个不工作？

I'm working on a SharePoint workflow, and the first step requires me to open an Excel workbook and read two things: a range of categories (from a range named, conveniently enough, Categories) and a category index (in the named range CategoryIndex). Categories is a list of roughly 100 cells, and CategoryIndex is a single cell.

我正在开发一个SharePoint工作流，第一步要求我打开一个Excel工作簿并阅读两件事：一系列类别（从名称范围，方便地，类别）和类别索引（在命名范围CategoryIndex中） ）。类别是大约100个单元格的列表，而CategoryIndex是单个单元格。

I'm using ADO.NET to query the workbook

我正在使用ADO.NET来查询工作簿

string connectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + temporaryFileName + ";" +
    "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();

OleDbCommand categoryIndexCommand = new OleDbCommand();
categoryIndexCommand.Connection = connection;
categoryIndexCommand.CommandText = "Select * From CategoryIndex";

OleDbDataReader indexReader = categoryIndexCommand.ExecuteReader();
if (!indexReader.Read())
    throw new Exception("No category selected.");
object indexValue = indexReader[0];
int categoryIndex;
if (!int.TryParse(indexValue.ToString(), out categoryIndex))
    throw new Exception("Invalid category manager selected");

OleDbCommand selectCommand = new OleDbCommand();
selectCommand.Connection = connection;
selectCommand.CommandText = "SELECT * FROM Categories";
OleDbDataReader reader = selectCommand.ExecuteReader();

if (!reader.HasRows || categoryIndex >= reader.RecordsAffected)
    throw new Exception("Invalid category/category manager selected.");

connection.Close();

Don't judge the code itself too harshly; it's been through a lot. Anyway, the first command never executes correctly. It doesn't throw an exception. It just returns an empty data set. (HasRows is true, and Read() returns false, but there is no data there) The second command works perfectly. These are both named ranges.

不要过于严厉地判断代码本身;经历了很多。无论如何，第一个命令永远不会正确执行。它没有抛出异常。它只返回一个空数据集。 （HasRows为true，Read（）返回false，但没有数据）第二个命令工作正常。这些都是命名范围。

They are populated differently, however. There's a web service call that fills up Categories. Those values are displayed in a dropdown box. The selected index goes into CategoryIndex. After hours of banging my head, I decided to write a couple of lines of code so that the dropdown's value goes into a different cell, then I copy the value using a couple of lines of C# into CategoryIndex, so that the data is set identically. That turned out to be a blind alley, too.

然而，它们的填充方式不同。有一个Web服务调用填满了类别。这些值显示在下拉框中。所选索引进入CategoryIndex。经过几个小时的敲击，我决定编写几行代码，以便下拉列表的值进入另一个单元格，然后我使用几行C＃将值复制到CategoryIndex中，以便数据设置相同。结果证明这也是一条死胡同。

Am I missing something? Why would one query work perfectly and the other fail to return any data?

我错过了什么吗？为什么一个查询完美运行而另一个查询无法返回任何数据？

1 个解决方案

#1

I have found the issue. Excel was apparently unable to parse the value in the cell, so it was returning nothing. What I had to do was adjust the connection string to the following:

我发现了这个问题。 Excel显然无法解析单元格中的值，因此它什么也没有返回。我要做的是将连接字符串调整为以下内容：

string connectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + temporaryFileName + ";" +
    "Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";

It would have been helpful if it would have thrown an exception or given any indication of why it was failing, but that's beside the point now. The option IMEX=1 tells Excel to treat all values as strings only. I'm quite capable of parsing my own integers, thankyouverymuch, Excel, so I didn't need its assistance.

如果它会抛出一个异常或给出任何失败原因的指示，那将会有所帮助，但现在已经不合时宜了。 IMEX = 1选项告诉Excel将所有值都视为字符串。我很有能力解析我自己的整数，而不是Excel，所以我不需要它的帮助。