ADO.NET(OleDb)读取Excel表格时的一个BUG

假设我们有如下一个Excel表格:

现在要使用C#程序读取其内容:
using  System;
using  System.Data.OleDb;

namespace  Skyiv.Ben.Test
{
  
sealed class ExcelTest
  
{
    
static void Main()
    
{
      
try
      
{
        
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0;HDR=yes/";Data Source=Std"))
        
{
          conn.Open();
          OleDbCommand comm 
= new OleDbCommand("SELECT [ID],[NAME] FROM [Sheet1$]", conn);
          
using (OleDbDataReader r = comm.ExecuteReader())
          
{
            
while (r.Read())
            
{
               
int id = Convert.ToInt32(r.GetValue(0));
               
string name = Convert.ToString(r.GetValue(1));
               Console.WriteLine(
"{0}:/t{1}", id, name);
            }

          }

        }

      }

      
catch (Exception ex)
      
{
        Console.WriteLine(
"错误: " + ex.Message);
      }

    }

  }

}

该程序的运行结果如下:

看来在读取单元格“B3”时返回了“DBNull”,而不是正确的“1768”。使用我的博客园发表的一篇随笔“数据库小工具(C#)”中给出的 OleDb.exe 查看了一下:

 确实,该 Execl 表格中有 2 个“DBNull”值,再用该工具查看一下其结构:
 
发现其第一列“ID”的数据类型是“double”,第二列“NAME”的数据类型是“string”。经分析,“DBNull”都出现在其数据类型与列的数据类型不相符的单元格中。看来,问题的症结就在这里了。我们知道,Excel 表格并是不真正的数据库,不像真正的数据库一样每个字段(列)都有一个特定的数据类型,而是由ADO.NET通过扫描该表格的开头几行来推测其每一列的数据类型,这样,当某列中有些单元格的数据类型与该列数据类型不一致时,就出问题了,该单元格的值就变成的“DBNull”。
这个问题来源于我的实际工作。在工作中,需要分析一张业务部门提供的 Excel 表格中的数据,该表格有好几千个数据行,其中有些列绝大部分的值是数字型,但其中有一些数字存储为文本格式。而有些列绝大部分的值是字符型,但有少数单元格的值是数字。这样,我的分析程序就不能工作了。我目前的解决方案是将该 Excel 表格另存为文本文件(制表符分隔),然后在 C# 程序中读取该文本文件。
还有一种方法就是在 Excel 表格中选中整列,然后“将存为文本的数字转换为数字”,如下所示:
 
但是,我并没有找到一个简便的方法来“将数字转换为文本”。
不知在 ADO.NET 中有没有办法在不改变原始 Excel 表格的情况下,正确读取其列中有单元格的数据类型不一致的 Excel 表格中的数据?如果谁知道的话,恳请告诉我。谢谢!
不知道要通过什么途径向 Microsoft 报告这个 BUG ?
我认为这个 BUG 的解决方案有两个:
1.  如果某一列被推测为数字型的话,如果在该列中出现字符型的数据,如果该数据是存储为文本的数字,就直接转换为数字返回给调用者好了。如果该数据不能转换为数字,可以返回“DBNull”,或者抛出异常。
    如果某一列被推测为字符型的话,只要该列中的单元格不为空,就转换为字符型返回给调用者。
2. 在 ADO.NET 的 OleDb 连接串中提供一个属性强制指定 Excel 表格中的所有列的数据类型都为“string”,只要单元格的内容不为空,就不返回“DBNull”,而返回“string”。然后由调用者自己使用 Convert.ToXXX() 方法转换到合适的数字类型。
不知大家以为然否?

posted on 2005-10-28 19:41 银河 阅读(3301) 评论(20)   编辑 收藏 所属分类: ADO.NET

评论

昨天同样遇到这个问题
http://community.csdn.net/Expert/TopicView.asp?id=4357468
真他NIANG的邪门

因为我的excel的表是固定的
所以我将有数字的单元格先改成字符串再改回数字,提示将数字以文本方式保存
   回复   引用   查看     

#2楼  2005-10-29 12:46 Bill1 [未注册用户]
楼主把这个问题定义为BUG。我觉得优点不合适。

只能说用户需求MS没有考虑周全。
   回复   引用   查看     

#3楼  [ 楼主] 2005-10-29 13:51 银河
@Bill1
我觉得这个问题应该定位为 BUG, 因为在我的 Execl 表格的例子中, 单元格的内容都不为空, ADO.NET 怎么能返回 DBNull 呢? 特别是 NAME 列(该列的数据类型被 ADO.NET 推测为字符型)中的 B3 单元格, 虽然该单元格的值是数字型的“1768”,而不是字符型,但是任何类型都可以用 ToString() 方法转换为字符型,ADO.NET 应该调用 ToString() 方法将 B3 单元格的内容转换为 "1768" 返回给调用者,绝对不能返回 DBNull 。返回 DBNull 就应该被认为是一个 BUG,而不能说:这是设计,而不是 BUG。 :)
   回复   引用   查看     

#4楼  [ 楼主] 2005-10-29 14:01 银河
@nonocast
> 昨天同样遇到这个问题 ... 真他NIANG的邪门
同感!明明在 Excel 表格的单元格内容不为空,ADO.NET 却返回 DBNull !
希望 Microsoft 能够发布补丁解决这个 BUG 。如果不修改 FCL 来修正这个 BUG ,好象没有什么很好的办法来解决这个问题。
   回复   引用   查看     

#5楼  2005-10-30 21:08 路人 [未注册用户]
还是不能定义为BUG,虽然可以ToString() ,但是如果是要转为其他类型呢?
   回复   引用   查看     

#6楼  [ 楼主] 2005-10-30 21:48 银河
@路人
> 还是不能定义为BUG,虽然可以ToString() ,但是如果是要转为其他类型呢?
我认为还是应该定位为 BUG。理由是:
1. 如果某一列被推测为字符型的话,只要该列中的单元格不为空,就可以使用 ToString() 转换为字符型返回给调用者。
2. 如果某一列被推测为其他类型,如数字型或者日期型什么的,只要该单元格的值可以被转换为该列的类型,就转换后返回给调用者。如果不能转换的话,可以返回“DBNull”,或者抛出异常。这样,就不至于因为该单元格明明有合适的值,而调用者却得到一个“DBNull”。要知道,不修正这个 BUG 的话,调用者是没有什么好的方法通过 ADO.NET 来读取 Excel 表格的。
进一步,还可以 在 ADO.NET 的 OleDb 连接串中提供一个属性强制指定 Excel 表格中的所有列的数据类型都为“string”,只要单元格的内容不为空,就不返回“DBNull”,而返回“string”。然后由调用者自己使用 Convert.ToXXX() 方法转换到合适的类型,或者进行其他处理。

   回复   引用   查看     

#7楼  2005-10-31 09:20 嘿呵
http://rexsp.cnblogs.com/archive/2004/09/22/45573.html
这个可能是你需要的东西
   回复   引用   查看     

#8楼  [ 楼主] 2005-10-31 10:35 银河
@嘿呵
非常感谢! 这正是我需要的!

"IMEX=1;" tells the driver to always read "intermixed" data columns as text

我在 ExcelTest.cs 程序中 OleDb 连接串 Extended Properties 中加入 IMEX=1 后,该程序就能够正确读取该 Excel 表格的内容。用 OleDb.exe 程序查看了一下其表结构,发现 ID 列的数据类型由 Double 变成了 String 。
经实验,IMEX=1 使得 Excel 表格中混合列(即该列中的包含有多种类型的数据)被 ADO.NET 认为其数据类型是 String 。如果某列的数据全部都是一种类型,如数字型,那么 ADO.NET 仍然认为该是列的类型是其数据的类型。
这个问题应该是基本上解决了。看来以后写用 ADO.NET 读取 Excel 表格的程序时,一般情况下都应该在 OleDb 连接串中加上 IMEX=1 。
不知道哪里可以查到 OleDb 连接串的完整的属性列表?

   回复   引用   查看     

#9楼  2005-10-31 15:32 空间/IV
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

以上内容摘自 http://www.connectionstrings.com/
   回复   引用   查看     

#10楼  2005-11-02 14:22 九杆 [未注册用户]
首先声明不懂C#!
使用Vb读写 Excel 表时按字符串类型,从未出现过BUG
是否C#中也可参照处理???

   回复   引用   查看     

#11楼  [ 楼主] 2005-11-02 15:51 银河
@九杆
我也不懂 VisualBasic :)
我想你说的“使用Vb读写 Excel 表时按字符串类型”应该相当于:
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
也就是说,IMEX=1 使得 Excel 表格中混合列(即该列中的包含有多种类型的数据)被 ADO.NET 认为其数据类型是 String 。这样就没有我所说的 BUG 出现了。
实际上,我认为最好“IMEX=1”应该是默认设置,也就是说,如果你不指定“IMEX=0”的话,就应该认为“IMEX=1”。
这个方案(IMEX=1),类似于我提出的解决方案之一:
-----------------------------------------------------------------------
在 ADO.NET 的 OleDb 连接串中提供一个属性强制指定 Excel 表格中的所有列的数据类型都为“string”,只要单元格的内容不为空,就不返回“DBNull”,而返回“string”。
-----------------------------------------------------------------------
但是还有一个微小的差别:IMEX=1 应该是需要扫描整个 Excel 表格才能决定某一列的数据类型(默认情况下 ADO.NET 只扫描 Excel 表格的头几行来决定某一列的数据类型),如果当一个 Excel 表格中有几万行时,可能会很慢(这仅是我的推测)。
如果采用我的方案,根本就不需要扫描 Excel 表格,因为在这种情况下,所有列的数据类型都是 string 。
   回复   引用   查看     

#12楼  2005-11-13 15:48 子沉 [未注册用户]
把它转转类型吧.这不算BUG
   回复   引用   查看     

#13楼  2006-05-25 17:54 John Lee [未注册用户]
但是却出现了以下问题:

如果某个单元格的数据为较长的数值的话,那么该单元格
返回的值将为科学技术法表示的字符串。
例如: 该单元格的数据为:20060501,
则会返回这样的数值:2.00605+E7

而我们可能只是想获得其中的全部数值。

如何解决???

   回复   引用   查看     

#14楼  2006-05-25 18:19 John Lee [未注册用户]
返回值写错了,应该是 2.00605e+007
   回复   引用   查看     

#15楼  2006-06-21 15:36 Spark Chen
@银河

"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + strFileName + ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"" "

为什么我加上IMEX=1;后,混合列还是会返回DBNULL ?

   回复   引用   查看     

#16楼  2006-09-08 17:28 4vsky [未注册用户]
问题应该不是这样的,加了照样没用
   回复   引用   查看     

#17楼  2007-03-20 01:26 蓝海 [未注册用户]
Check out the
[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.

在注册表里要改一下! 
 
#1楼  2005-10-29 08:05 nonocast
这个方法对规则的表可用,对不规则的表就不方便了,我给你一个一劳永逸的方法,
下面的代码非常灵活,可以对任意单元格的值进行读取或者修改

//创建一个excel应用程序实例
Excel.Application app = new Excel.Application();
app.Visible = true;
app.UserControl = true;
Excel.Workbooks workbooks =app.Workbooks;
//打开指定的表
Excel._Workbook workbook = workbooks.Add("c://b.xls");
Excel.Sheets sheets = workbook.Worksheets;
Excel._Worksheet worksheet = (Excel._Worksheet) sheets.get_Item(1);

//提取A1单元格的值,并天赋给strTemp
Excel.Range range1 = worksheet.get_Range("A1", Type.Missing);
string strTemp;
strTemp = range1.Value2.ToString();

//对A1单元格进行赋值
const int nCells = 2345;
range1.Value2 = nCells;

//保存当前文件
workbook.SaveCopyAs("c://1.xls");

//释放相关内存
app.Quit ();
worksheet = null;
sheets = null;
workbook = null;
workbooks = null;
app = null;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值