通过OleDbDataAdapter的方法古怪选择Excel文件（C＃） [英] Quirky SELECT from Excel file via OleDbDataAdapter method (C#)

问题描述

我有这种形式的Excel文件：

I have got an Excel file in this form :

Column 1    Column 2    Column 3  
 data1        data2    
 data1        data2  
 data1        data2  
 data1        data2  
 data1        data2       data3  

也就是说，整个第3栏是除了最后一排空。
我通过OleDbDataAdapter的访问Excel文件，返回一个DataTable：这里的代码。

That is, the whole Column 3 is empty except for the last row. I am accessing the Excel file via OleDbDataAdapter, returning a DataTable: here's the code.

query = "SELECT * FROM [" + query + "]";
objDT = new DataTable();
objCmdSQL = this.GetCommand();
objCmdSQL.CommandText = query;
objSQLDad = new OleDbDataAdapter(objCmdSQL);
objSQLDad.Fill(objDT);
return objDT;

的一点是，在这种情况下我的代码返回一个DataTable只是列1和列2 < BR>
我的猜测是，JET引擎试图通过在每一列的第一单元的类型推断列的类型;作为第一个null值，整列被忽略结果
我试图填补零和这个代码实际上返回所有三列。这显然是最可取的解决方案，因为我要处理大量的小文件的结果
反转的选择范围（即A1：C5到C5：A1）。也不起作用。
我在寻找的东西更优雅。结果
我已经发现一对夫妇的帖子讨论类型不匹配（在INT列，反之亦然VARCHAR细胞），但实际上并没有发现相关的任何事情这一个。结果
感谢您阅读！

The point is, in this scenario my code returns a DataTable with just Column 1 and Column 2.
My guess is that JET engine tries to infer column type by the type of the very first cell in every column; being the first value null, the whole column is ignored.
I tried to fill in zeros and this code is actually returning all three columns; this is obviously the least preferable solution because I have to process large numbers of small files.
Inverting the selection range (from, i.e. "A1:C5" to "C5:A1" ) doesn't work either. I'm looking for something more elegant.
I have already found a couple of posts discussing type mismatch (varchar cells in int columns and vice versa) but actually haven't found anything related to this one.
Thanks for reading!

的修改的

再怪异的行为。我有工作的大多是Excel 2003中.xls文件，但由于这个问题已经回答了我以为我可以测试我对2007年的Excel文件.xslx代码。
的连接字符串如下：

Weird behavior again. I have to work on mostly Excel 2003 .xls files, but since this question has been answered I thought I could test my code against Excel 2007 .xslx files. The connection string is the following:

string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _fileName.Trim() + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;""";

我得到的外部表不是预期的格式异常，我估计是标准的例外当有ACE / JET与被打开的文件之间的版本不匹配。

I get the "External table is not in the expected format" exception which I reckon is the standard exception when there is a version mismatch between ACE/JET and the file being opened.

字符串

Provider=Microsoft.ACE.OLEDB.12.0 

意味着我使用最新OLEDB的版本，我参加了一个快速浏览一下周围，这个版本是用来处处有需要连接的.xlsx文件。结果
我还只是一个香草提供商（只是Excel的12.0试过，不IMEX也不HDR），但我得到了同样的异常。结果
我在.NET 2.0.50727 SP2，也许时间升级？

means that I am using the most recent version of OLEDB, I took a quick peek around and this version is used everywhere there is need of connecting to .xlsx files.
I have tried with just a vanilla provider ( just Excel 12.0, without IMEX nor HDR ) but I get the same exception.
I am on .NET 2.0.50727 SP2, maybe time to upgrade?

推荐答案

我重新创建你的情况并按照正确返回3列。也就是说，数据和包含空，直到最后一排，其中有数据的第三个完全填充的前两列。

I recreated your situation and following returned the 3 columns correctly. That is, the first two columns fully populated with data and the third containing null until the last row, which had data.

string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";";
DataTable dt = new DataTable();
OleDbConnection conn = new OleDbConnection(connString);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);

adapter.Fill(dt);

请注意我用了 Access数据库引擎（ACE）提供商，其成功的旧联合引擎技术（JET）供应商，我的结果可能代表了两者之间的行为差​​异。当然，如果你是不是已经在使用它，我建议使用 ACE 提供商，因为我相信微软会了。此外，请注意连接的扩展属性：

Note I used the Access Database Engine(ACE) provider, which succeeded the old Joint Engine Technology(JET) provider, and my results may represent a behavior difference between the two. Of course, if you aren't already using it I suggest using the ACE provider as I believe Microsoft would too. Also, note the connection's Extended Properties:

HDR =是;指示第一
行包含COLUMNNAMES，而不是数据。
HDR =无; ，表示相反

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

IMEX = 1;告诉总是
读混合（数字，日期，
串等）的数据列，文本驱动程序。
请注意，此选项可能会影响
Excel工作表写访问负。

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

让我知道，如果这有助于。

Let me know if this helps.

这篇关于通过OleDbDataAdapter的方法古怪选择Excel文件（C＃）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！