黑马程序员之ADO.NET学习笔记:ADO.NET如何读取Excel

--------------------------------------------------- 2345王牌技术员联盟、2345王牌技术员联盟、期待与您交流！---------------------------------------------------------

经常需要在数据库与execl之间互导数据。net时代，ado.net可以使用使用microsoft.jet.oledb访问访问excel，网上已经有很多类似的资源，最典型也是最简单的可能如下：（asp.net环境）
       // 连接字符串            
        string xlspath =server.mappath("~/app_data/somefile.xls"); // 绝对物理路径
        string connstr = "provider=microsoft.jet.oledb.4.0;"+ "extendedproperties=excel 8.0;" +  "datasource=" + xlspath;
        // 查询语句
        string sql = "select * from[sheet1$]";

        dataset ds = newdataset();
        oledbdataadapter da = newoledbdataadapter(sql, connstr);
        da.fill(ds);    // 填充dataset        
        // 在这里对dataset中的数据进行操作        

        // 输出，绑定数据
        gridview1.datasource =ds.tables[0]; 
        gridview1.databind();

        //附带段可以获取excel表明的代码

        mycnn.Open();
        DataSet myds=new DataSet();
        DataTable mytable=new DataTable();
        //所有表的名称
        mytable=mycnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"TABLE"});
        myds.Tables.Add(mytable);

很简单吧？！一切就像操作数据库一样，只是需要注意的是：
1. 数据提供程序使用jet，同时需要指定extended properties 关键字设置 excel 特定的属性，不同版本的excel对应不同的属性值：用于 extended properties 值的有效 excel 版本。 
    对于 microsoft excel 8.0 (97)、9.0(2000) 和 10.0 (2002) 工作簿，请使用excel 8.0。 

    对于 microsoft excel 5.0 和 7.0 (95) 工作簿，请使用 excel 5.0。 

    对于 microsoft excel 4.0 工作簿，请使用 excel 4.0。 

    对于 microsoft excel 3.0 工作簿，请使用 excel 3.0。 

ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/chs/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp

2. 数据源路径使用物理绝对路径（同access）

3. 如何引用表名？
    对 excel 工作簿中表（或范围）的有效引用。 
    若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如： 

    select * from [sheet1$]
    若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： 

    select * from [sheet1$a1:b10]
    若要引用指定的范围，请使用该范围的名称。例如： 

    select * from [mynamedrange]
    ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/chs/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp
    说明：
    可以引用excel 工作簿中的三种对象：
    • 整张工作表：[sheet1$]  ，sheet1 就是工作表的名称
    • 工作表上的命名单元格区域：[mynamedrange] （不需要指定工作表，因为整个xls中命名区域只能唯一）
    xls命名方法：选中单元格范围》插入》名称》定义
    • 工作表上的未命名单元格区域 ：[sheet1$a1:b10]
  （在关系数据库提供的各种对象中（表、视图、存储过程等），excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，    而   工作表被视为“系统表”）

   注意：
          •必须使用[]（方括号），否将报：
          from 子句语法错误
          •必须跟$（美元符号），否则报：
          microsoft jet 数据库引擎找不到对象’sheet2’。请确定对象是否存在，并正确地写出它的名称和路径。
          •如果工作表名称不对，或者不存在，将报：
          ’sheet2$’ 不是一个有效名称。请确认它不包含无效的字符或标点，且名称不太长。
           •在 如何在 visual basic 或vba 中使用 ado 来处理 excel 数据   中提到可以使用
           ~  和 ’(波浪线和单引号）代替[]，使用ado。net测试没有成功，报：
           from 子句语法错误
          •当引用工作表明名（[sheet1$]）时，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，c 列开始，第3行，c列之前以及第1、2行全为空，则只会显示从第3行，c列开始的数据;以最后表最大范围内的非空单元结束;
          •因此，如需要精确读取范围，应该使用命名区域 [namedrange]，或者指定地址：[sheet1$a1:c10]

4.  如何引用列名？
          •根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用f1、f2表示，其中序数，跟单元格的位置一致，从1开始;
          •如果希望第一行作为数据显示，而非列名，可以在连接串的 extendedproperties 属性指定：hdr=no
          默认值为：hdr=no 格式如下：

        string connstr ="provider=microsoft.jet.oledb.4.0;" +
                        "extendedproperties=\"excel 8.0;hdr=no\";" + 
                        "datasource=" + xlspath;
注意： excel 8.0;hdr=no  需要使用双引号（这里的反斜扛，是C#中的转义）

ref：ms-help://ms.vscc.v80/ms.msdn.v80/ms.visualstudio.v80.chs/wd_adonet/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm中 《连接excel》节（说明：在我自己的msdn中，它的例子使用了两个双引号是错的，测试没有通过，原文这样说的：

注意，extended properties 所需的双引号必须还要加双引号。
）

在这种情况下，所有的列名都是以f开头，然后跟索引，从f1开始，f2，f3。。。。。。。

5。为什么有效单元格数据不显示出来？
出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。
可以通过 extended properties 中指定imex=1 

“imex=1;”通知驱动程序始终将“互混”数据列作为文本读取 ref：同4

ps：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到”治本药方“
more ref： 

如何在 visual basic 或 vba 中使用 ado 来处理 excel 数据
http://dotnet.aspx.cc/showdetail.aspx?id=c673e2cd-3f1e-4919-8ce0-d69b894a0599
  

应用程序经常需要与excel进行数据交互，以上阐述了基于ado.net 读取excel的基本方法与技巧。现在要介绍是如何动态的读取excel数据，这里的动态指的是事先不知道excel文件的是什么样的结构，或者无法预测，比如一张.xls文件有多少张sheet，而且每张sheet的结构可能都不一样等等。
其实我们可以通过获取excel的“架构信息”来动态的构造查询语句。这里的“架构信息”与数据库领域的“数据库架构信息”意义相同（也称“元数据”），对于整个数据库，这些“元数据”通常包括数据库或可通过数据库中的数据源、表和视图得到的目录以及所存在的约束等;而对于数据库中的表，架构信息包括主键、列和自动编号字段等。
在上文中提到

在关系数据库提供的各种对象中（表、视图、存储过程等），excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”）

--------------------------------------------------- 2345王牌技术员联盟、2345王牌技术员联盟、期待与您交流！---------------------------------------------------------