Why does one ADO.NET Excel query work and another not?

Question

I am working on a SharePoint workflow, and in the first step I need to open an Excel workbook and read two things: a range of categories (from a range that is called, quite conveniently, Categories ) and a category index (in the named Range CategoryIndex ). Categories is a list of approximately 100 cells, and CategoryIndex is one cell.

I am using ADO.NET to request a book

 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(); 

Do not judge the code itself too harshly; it was a lot. In any case, the first command is never executed correctly. This is no exception. It just returns an empty data set. ( HasRows is true , and Read() returns false , but there is no data there). The second team works just fine. These are both named ranges.

However, they are populated differently. There is a web service call that populates Categories . These values ​​are displayed in the drop-down list. The selected index goes to CategoryIndex . After several hours of headers, I decided to write a couple of lines of code so that the drop-down value goes to another cell, then I copy the value using a couple of C # lines in CategoryIndex , so the data is identical. This also turned out to be a dead end.

Am I missing something? Why does one query work fine and the other cannot return any data?

Answer 1

I found a problem. Excel, apparently, could not parse the value in the cell, so it did not return anything. I needed to configure the connection string to the following:

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

It would be helpful if it raised an exception or gave any indication as to why it is failing, but this is not the case now. The IMEX=1 option tells Excel to treat all values ​​only as strings. I am quite capable of parsing my own integers, thank you Excel, so I don't need his help.