使用OleDbDataAdapter从Excel工作表中获取数据的问题 [英] Problem with using OleDbDataAdapter to fetch data from a Excel sheet

问题描述

首先，我想说，我在这里深水，因为我只是对公司里其他人写的代码做了一些修改，使用OleDbDataAdapter来交谈到Excel，不熟悉的有一个错误，我只是不能遵循。

我试图使用一个OleDbDataAdapter来读取一个Excel文件大约450行。

在代码中，这样做是这样的：

  connection = new OleDbConnection（Provider = Microsoft.Jet.OLEDB.4.0;+Data Source ='+ path +';+扩展属性= \Excel 8.0; HDR = Yes; IMEX = 1; \ ）; 
 connection.Open（）; 
 OleDbDataAdapter objAdapter = new OleDbDataAdapter（objCommand.CommandText，connection）; 
 objAdapter.Fill（objDataSet，Excel）; 
 
 foreach（DataColumn dataColumn in objTable.Columns）{
 if（dataColumn.Ordinal> objDataSet.Tables [0] .Columns.Count  -  1）{
 objDataSet.Tables [0] .Columns.Add（）; 
} 
 objDataSet.Tables [0] .Columns [dataColumn.Ordinal] .ColumnName = dataColumn.ColumnName; 
 objImport.Columns.Add（dataColumn.ColumnName）; 
} 
 
 foreach（DataRow dataRow in objDataSet.Tables [0] .Rows）{
 ... 
} 
  

除了一件事，一切似乎都能正常工作。第二列填充的主要是四位数字，如6739,3920等，但是fice行的字母数字值包括8201NO和8205NO。这五个单元格被报告为具有空白内容，而不是其字母数字内容。我已经检查了excel，并且列中的所有单元格都标记为文本。

这是一个xls文件，而不是xlsx。

有没有人有任何线索，为什么这些单元格在DataRow中显示为空白，但数字显示为正确？还有其他列的字母数字内容显示得很好。

解决方案

正在发生的是excel正在尝试分配一个数据类型基于该列中的前几个值到电子表格列。我怀疑，如果您查看该列中的属性，它会说它是一个数字列。

当您开始尝试使用jet查询该电子表格时，问题出现。当它认为它正在处理一个数字列，并发现一个varchar值，它静默地不返回任何东西。甚至没有一个隐秘的错误消息。

作为一个可能的工作，您可以将其中一个字母数字值移动到第一行数据，然后尝试解析。我怀疑你会开始获取字母数字行的值，然后...

查看本文。在这个问题上有更多的细节。它也谈到一个可能的工作是：

但是，根据JET文档，我们
可以覆盖注册表设置通过
连接字符串，如果我们设置
IMEX = 1（作为扩展
属性的一部分），JET将将所有
列类型设置为UNICODE VARCHAR或
ADVARWCHAR，不管
'ImportMixedTypes'key value.hey

First, I want to say that I'm out on deep water here, since I'm just doing some changes to code that is written by someone else in the company, using OleDbDataAdapter to "talk" to Excel and I'm not familiar with that. There is one bug there I just can't follow.

I'm trying to use a OleDbDataAdapter to read in a excel file with around 450 lines.

In the code it's done like this:

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source='" + path + "';" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"");
connection.Open();
OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCommand.CommandText, connection);
objAdapter.Fill(objDataSet, "Excel");

foreach (DataColumn dataColumn in objTable.Columns) {
  if (dataColumn.Ordinal > objDataSet.Tables[0].Columns.Count - 1) {
    objDataSet.Tables[0].Columns.Add();
  }
  objDataSet.Tables[0].Columns[dataColumn.Ordinal].ColumnName = dataColumn.ColumnName;
  objImport.Columns.Add(dataColumn.ColumnName);
}

foreach (DataRow dataRow in objDataSet.Tables[0].Rows) {
   ...
}

Everything seems to be working fine except for one thing. The second column is filled with mostly four digit numbers like 6739, 3920 and so one, but fice rows have alphanumeric values like 8201NO and 8205NO. Those five cells are reported as having blank contents instead of their alphanumeric content. I have checked in excel, and all the cells in this columns are marked as Text.

This is an xls file by the way, and not xlsx.

Do anyone have any clue as why these cells are shown as blank in the DataRow, but the numeric ones are shown fine? There are other columns with alphanumeric content that are shown just fine.

解决方案

What's happening is that excel is trying to assign a data type to the spreadsheet column based on the first several values in that column. I suspect that if you look at the properties in that column it will say it is a numerical column.

The problem comes when you start trying to query that spreadsheet using jet. When it thinks it's dealing with a numerical column and it finds a varchar value it quietly returns nothing. Not even a cryptic error message to go off of.

As a possible work around can you move one of the alpha numeric values to the first row of data and then try parsing. I suspect you will start getting values for the alpha numeric rows then...

Take a look at this article. It goes into more detail on this issue. it also talks about a possible work around which is:

However, as per JET documentation, we can override the registry setting thru the Connection String, if we set IMEX=1( as part of Extended Properties), the JET will set the all column type as UNICODE VARCHAR or ADVARWCHAR irrespective of ‘ImportMixedTypes’ key value.hey

这篇关于使用OleDbDataAdapter从Excel工作表中获取数据的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！