ADO.NET 如何读取 Excel （下）

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

 

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

 

这里我们将Excel也当作一个“数据库”来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法
要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的：

 

注意：对于那些不熟悉 OLE DB 架构行集的人而言，它们基本上是由 ANSI SQL- 92  定义的数据库构造的标准化架构。每个架构行集具有为指定构造提供定义元数据的一组列（称作 .NET 文档中的“限制列”）。这样，如果请求架构信息（例如，列的架构信息或排序规则的架构信息），则您会明确知道可以得到哪种类型的数据。如果希望了解更多信息，请访问 Appendix B:Schema Rowsets。

ref：http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

以下是读取Excel文件内“表”定义元数据，并显示出来的的程序片断：

         //  读取Excel数据，填充DataSet
         //  连接字符串            
         string  xlsPath  =  Server.MapPath( " ~/app_data/somefile.xls " );
         string  connStr  =   " Provider=Microsoft.Jet.OLEDB.4.0; "   +
                         " Extended Properties=\ " Excel  8.0 ;HDR = No;IMEX = 1 \ " ; "   +   //  指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                         " data source= "   +  xlsPath;
         string  sql_F  =   " SELECT * FROM [{0}] " ;

        OleDbConnection conn  =   null ;
        OleDbDataAdapter da  =   null ;
        DataTable tblSchema  =   null ;
        IList < string >  tblNames  =   null ;

         //  初始化连接，并打开
        conn  =   new  OleDbConnection(connStr);
        conn.Open();

         //  获取数据源的表定义元数据                        
         // tblSchema = conn.GetSchema("Tables");
        tblSchema  =  conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,  new   object []  { null, null, null, "TABLE" } );

        GridView1.DataSource  =  tblSchema;
        GridView1.DataBind();

         //  关闭连接
        conn.Close();

GetOleDbSchemaTable 方法的详细说明可以参考：
http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断：

         //  读取Excel数据，填充DataSet
         //  连接字符串            
         string  xlsPath  =  Server.MapPath( " ~/app_data/somefile.xls " );
         string  connStr  =   " Provider=Microsoft.Jet.OLEDB.4.0; "   +
                         " Extended Properties=\ " Excel  8.0 ;HDR = No;IMEX = 1 \ " ; "   +   //  指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                         " data source= "   +  xlsPath;
         string  sql_F  =   " SELECT * FROM [{0}] " ;

        OleDbConnection conn  =   null ;
        OleDbDataAdapter da  =   null ;
        DataTable tblSchema  =   null ;
        IList < string >  tblNames  =   null ;

         //  初始化连接，并打开
        conn  =   new  OleDbConnection(connStr);
        conn.Open();

         //  获取数据源的表定义元数据                        
         // tblSchema = conn.GetSchema("Tables");
        tblSchema  =  conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,  new   object []  { null, null, null, "TABLE" } );

         // GridView1.DataSource = tblSchema;
         // GridView1.DataBind();

         //  关闭连接
         // conn.Close();

        tblNames  =   new  List < string > ();
         foreach  (DataRow row  in  tblSchema.Rows)  {
            tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
        }

         //  初始化适配器
        da  =   new  OleDbDataAdapter();
         //  准备数据，导入DataSet
        DataSet ds  =   new  DataSet();

         foreach  ( string  tblName  in  tblNames)  {
            da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
            try {
                da.Fill(ds, tblName);
            }
            catch {
                // 关闭连接
                if (conn.State == ConnectionState.Open) {
                    conn.Close();
                }
                throw;
            }
        }

         //  关闭连接
         if  (conn.State  ==  ConnectionState.Open)  {
            conn.Close();
        }

         //  对导入DataSet的每张sheet进行处理        
         //  这里仅做显示
        GridView1.DataSource  =  ds.Tables[ 0 ];
        GridView1.DataBind();

        GridView2.DataSource  =  ds.Tables[ 1 ];
        GridView2.DataBind();

         //  more codes

这里我们就不需要对SELEC 语句进行“硬编码”，可以根据需要动态的构造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 方法
来获取数据源的架构信息。