Ado.Net读取Excel常见问题总结

        经常需要在数据库与Execl之间互导数据。net时代，ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel，网上已经有很多类似的资源，最典型也是最简单的可能如下：（asp.net环境） 
        

1. // 连接字符串 
2. string xlsPath = Server.MapPath("~/app_data/somefile.xls");
3. // 绝对物理路径 
4. string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + xlsPath; 
5. // 查询语句
6. string sql = "SELECT * FROM [Sheet1$]"; 
7.    
8. DataSet ds = new DataSet(); 
9. OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr); 
10. da.Fill(ds); // 填充DataSet 
11.    
12. // 在这里对DataSet中的数据进行操作
13. // 输出，绑定数据 
14. GridView1.DataSource = ds.Tables[0]; 
15. GridView1.DataBind(); 

        很简单吧？！一切就像操作数据库一样，只是需要注意的是： 
        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。

        2. 数据源路径使用物理绝对路径（同Access） 
        3. 如何引用表名：
            对 Excel 工作簿中表（或范围）的有效引用。若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：
            select * from [Sheet1$] 
            若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： 
            select * from [Sheet1$A1:B10] 

            若要引用指定的范围，请使用该范围的名称。例如：
            select * from [MyNamedRange] 
            说明： 
            可以引用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开始； 
            •如果希望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO

                默认值为：HDR=NO 格式如下： 
                    string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0;HDR=NO/";data source=" + xlsPath; 
                    注意： Excel 8.0;HDR=NO 需要使用双引号（这里的反斜扛，是C#中的转义）   
                在这种情况下，所有的列名都是以F开头，然后跟索引，从F1开始，F2，F3。。。。。。。 

        5. 为什么有效单元格数据不显示出来？ 
            出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。可以通过 Extended Properties 中指定 IMEX=1
            “IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取 
   
            PS：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到”治本药方“ 
   
            ref：如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 
   
            注意： 
                在网上有很多同志说是加上HDR或IMEX这两个属性值后会报“找不到可安装的ISAM ”的错误。这时解决方法很简单： 
                就是把连接串写成如下形式：
                    Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=1; IMEX=1;'

                (注意红色标注的单引号)
                而不要写成这样的形式：
                    Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0;HDR=1; IMEX=1; 
        应用程序经常需要与Excel进行数据交互，前面阐述了基于ADO.NET 读取Excel的基本方法与技巧。下面介绍是如何动态的读取Excel数据，这里的动态指的是事先不知道Excel文件的是什么样的结构，或者无法预测，比如一张.xls文件有多少张sheet，而且每张sheet的结构可能都不一样等等。 
        其实我们可以通过获取Excel的“架构信息”来动态的构造查询语句。这里的“架构信息”与数据库领域的“数据库架构信息”意义相同（也称“元数据”），对于整个数据库，这些“元数据”通常包括数据库或可通过数据库中的数据源、表和视图得到的目录以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。 

        前面提到，在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”）  
   
        这里我们将Excel也当作一个“数据库”来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法 
        要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的：
            注意：对于那些不熟悉 OLE DB 架构行集的人而言，它们基本上是由 ANSI SQL-92 定义的数据库构造的标准化架构。每个架构行集具有为指定构造提供定义元数据的一组列（称作 .NET 文档中的“限制列”）。这样，如果请求架构信息（例如，列的架构信息或排序规则的架构信息），则您会明确知道可以得到哪种类型的数据。如果希望了解更多信息，请访问 Appendix B: Schema Rowsets。
   
        以下是读取Excel文件内“表”定义元数据，并显示出来的的程序片断：

 

1. // 读取Excel数据，填充DataSet 
2. // 连接字符串 
3. string xlsPath = Server.MapPath("~/app_data/somefile.xls"); 
5. // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
6. string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0;HDR=No;IMEX=1/";data source=" + xlsPath;
7. string sql_F = "SELECT * FROM [{0}]";
8.    
9. OleDbConnection conn = null; 
10. OleDbDataAdapter da = null; 
11. DataTable tblSchema = null; 
12. IList<string> tblNames = null; 
13.    
14. // 初始化连接，并打开 
15. conn = new OleDbConnection(connStr); 
16. conn.Open(); 
19. // 获取数据源的表定义元数据 
20. //tblSchema = conn.GetSchema("Tables"); 
21. tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
22.         new object[] { null, null, null, "TABLE" }); 
23.    
24. GridView1.DataSource = tblSchema; 
25. GridView1.DataBind(); 
26.    
27. // 关闭连接 
28. conn.Close(); 

        GetOleDbSchemaTable 方法的详细说明可以参考： 
   
        接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断：

1. // 读取Excel数据，填充DataSet 
2. // 连接字符串 
3. string xlsPath = Server.MapPath("~/app_data/somefile.xls"); 
4. // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
5. string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0;HDR=No;IMEX=1/";data source=" + xlsPath; 
7. string sql_F = "SELECT * FROM [{0}]"; 
8.    
9. OleDbConnection conn = null; 
10. OleDbDataAdapter da = null; 
11. DataTable tblSchema = null; 
12. IList<string> tblNames = null; 
13.    
14. // 初始化连接，并打开 
15. conn = new OleDbConnection(connStr); 
16. conn.Open(); 
17.    
18. // 获取数据源的表定义元数据 
19. //tblSchema = conn.GetSchema("Tables"); 
20. tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); 
21.    
22. //GridView1.DataSource = tblSchema; 
23. //GridView1.DataBind(); 
24.    
25. // 关闭连接 
26. //conn.Close(); 
27.    
28. tblNames = new List<string>(); 
29. foreach (DataRow row in tblSchema.Rows) { 
30.     tblNames.Add((string)row["TABLE_NAME"]); // 读取表名 
31. } 
32.    
33. // 初始化适配器 
34. da = new OleDbDataAdapter(); 
35. // 准备数据，导入DataSet 
36. DataSet ds = new DataSet(); 
37.    
39. foreach (string tblName in tblNames) { 
40.     da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn); 
41.     try { 
42.         da.Fill(ds, tblName); 
43.     } catch { 
44.         // 关闭连接 
45.          if (conn.State == ConnectionState.Open) { 
46.             conn.Close(); 
47.         } 
48.         throw; 
49.     } 
50. } 
51.    
52. // 关闭连接 
53. if (conn.State == ConnectionState.Open) { 
54.     conn.Close(); 
55. } 
56.    
57. // 对导入DataSet的每张sheet进行处理 
58. // 这里仅做显示 
59. GridView1.DataSource = ds.Tables[0]; 
60. GridView1.DataBind(); 
61.    
62. GridView2.DataSource = ds.Tables[1]; 
63. GridView2.DataBind(); 
64.    
65. // more codes 
66. // . 

        这里我们就不需要对SELECT 语句进行“硬编码”，可以根据需要动态的构造FROM 字句的“表名”。不仅可以获取表名，还可以获取每张表内的字段名、字段类型等信息：
        tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null }); 

        在ADO.nET 1.x 时候只有OleDb提供了GetOleDbSchemaTable 方法，而SqlClient或者OrcaleClient没有对应的方法，因为对应数据库已经提供了类似功能的存储过程或者系统表供应用程序访问，比如对于Sql Server：
        SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'
   
        而在ADO.NET 2.0中每个xxxConnenction都实现了基类System.Data.Common.DbConnection的GetSchemal 方法来获取数据源的架构信息。

        

        6. 连接到 Excel

        Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中，Extended Properties 关键字设置 Excel 特定的属性。“HDR=Yes;”指示第一行中包含列名，而不是数据，“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。

        Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyExcel.xls;Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/"
        注意，Extended Properties 所需的双引号必须还要加双引号。