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 方法
来获取数据源的架构信息。
http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx

refs：
从 .NET 应用程序访问 Microsoft Office 数据
HOW TO：使用 GetOleDbSchemaTable 和 Visual C# .NET 检索架构信息
从数据库中获取架构信息

posted on 2006-08-26 18:54 晓风残月 阅读(3734) 评论(18)  编辑 收藏 网摘 所属分类: ADO.NET

评论

你好，我还是有点不明白，一张.xls文件有多少张sheet，怎么能把多张Sheet中的数据查询出来，在一个GridView中显示出来，你用的是 GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

GridView2.DataSource = ds.Tables[1];
GridView2.DataBind();
.............
是不是要用很多的GridView来显示Excel中的数据，
我现在做的程序是在把Excel中所以Sheet中的数据查询出来并在页面上显示在一个GridView或者DataGrid中，，
我对你的代码可能没有看懂，上边的只是我的想法，我的MSN：lizhuo85126@hotmail.com QQ:295343266,希望你能帮我把问题解决了，
谢谢你了
  回复  引用    

#2楼 [楼主] 2007-08-09 21:02 晓风残月      

@李卓
是的，你的理解基本是对的。

事实上，当你用 ADO/ADO.NET 去连接 Excel 的时候，这时就把 Excel 看作一个 DataBase 了， Excel 一个 sheet 就如同数据库中的一张 table 了

GridView/DataGrid/... 只能同时绑定一个 DataTable/DataView/...

你看，对于一个Excel文件多个sheet还不是要多个工作簿里面？

你要将多个 sheet 绑定一个 DataGrid/GridView 上面，你只有查询出这些sheet到datatable，然后合并这些 datatable 到一个DataTable
中任何绑定到控件上
  回复  引用  查看    

#3楼  2007-08-17 10:56 菜园子 [未注册用户]

本人想通过上传EXCEL文件，再读取数据到Gridview

因为EXCEl里的工作表名假设为未知，所以我采用您上面的代码。
可惜，编译时在IList<string> tblNames = null;那里报错。

The non-generic type 'System.Collections.IList' cannot be used with type arguments


要是我不用你上面的代码的话，如何简简单单的设置获取EXCEl里的工作表名
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [?$]", strCon);
谢谢！
  回复  引用    

#4楼 [楼主] 2007-08-17 11:57 晓风残月      

@菜园子
错误信息很明显了，

你应该导入泛型集合命名控件 using System.Collections.Generic;

或者 System.Collections.Generic.IList<string> tblNames = null;   回复  引用  查看    

#5楼  2007-08-17 14:01 菜园子 [未注册用户]

我的程序出了个奇怪的问题。
不管是以前对Excel的导出操作，还是现在的上传操作。
Excel文件都要处于打开的状态！才能操作。
现在实现上传时，不得不在程序中设置断点，去打开一下上传的文件。
否则抛出“External table is not in the expected format”错误。
不知道您有没有遇到过？
  回复  引用    

#6楼  2007-08-22 18:59 Ankrmi Wong      

如果Excel文件有多个Sheet1,提示[Sheet1$]路径不正确或名字不太长，但是指定Excel表名，就没问题了，是这样的吗？有什么方法可以使这个表名对任何多个Sheet都有效?   回复  引用  查看    

#7楼 [楼主] 2007-08-22 22:30 晓风残月      

@Ankrmi Wong
没看懂你的意思，

对于 [xxx$] 这样的格式是固定的 xxx 表示 工作表 名称或者命名区域名称   回复  引用  查看    

#8楼  2007-10-02 18:01 Ankrmi Wong      

@晓风残月
用[ExcelFileName$]指定表名（取代[Sheet1$]），就不会提示出错信息了，原因是我的ExcelFile包含了若干张Sheet,只有一张Sheet时是不会出现提示错误。
谢谢你的回复！   回复  引用  查看    

#9楼 [楼主] 2007-10-04 19:46 晓风残月      

对于 [xxx$] 这样的格式是固定的，其中 xxx 可表示工作表名称或者命名区域名称，并且追加一个 $（美元符号），同时使用 []（半角方括号）括起来，

而 SheetXX 是 Excel 中对新建表单（工作表）的默认命名格式而已，

假如没有命为 Sheet1 的工作表或者命名区域，引用它自然是错的   回复  引用  查看    

#10楼  2007-10-26 08:43 小志yy [未注册用户]

我老大讓我 在 Excel 讀取到 oracle 中 而切要給我指定在 Excel 中
指定的行.
但是我發現 讀數據時 ﹐如果 前几行都是 空的 它就不讀取了。

1﹐我怎么才能讀 Excel 指定的行﹖

2﹐我想把 Excel 數據讀到 dataset 中取 再 全部 insert 到 oracle 中去﹖   回复  引用    

#11楼 [楼主] 2007-10-26 23:47 晓风残月      

@小志yy
1. 指定名称区域
//..
string sqlSelect = "SELECT * FROM [Sheet1$C5:F20]"; // 表示读取表单 Sheet1 中 C5 到 F20 之间的数据
OleDbDataAdapter da = new OleDbDataAdapter(sqlSelect, connString);
//...


2. ADO 读取 Excel 的时候，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，C 列开始，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开始的数据；以最后表最大范围内的非空单元结束；

因此，只会从第一个非空的行开始加载。

你可以使用 Office Tools 读取，请参见：
Excel 任务
http://msdn2.microsoft.com/zh-cn/library/syyd7czh(vs.80).aspx   回复  引用  查看    

#12楼  2007-11-08 18:07 yanshengwu [未注册用户]

@晓风残月
有个问题请教兄台，我在Excel某一列的数据设置了格式，即小数位数为n为，我想导入sql数据库的数据也是小数保留n位的，要如何做？因为现在读出来的是原来没有保留Excel设置小数位数的值。   回复  引用    

#13楼 [楼主] 2007-11-11 02:05 晓风残月      

@yanshengwu
不明白你的意思，既然你导入了SQL数据库，那么精度是由数据库的对应的数据类型控制，如 real 或者 float，

假如某个单元格值为 1.234，那么读入 DataTable 的就是 1.234，并且假如改列全部为数字，那么DataTable中该列类型为 System.Double，否则为 System.String

假如两个单元格值为 1 和 1.0001，然后设置小数为3位，Excel均显示为 1.000，这仅仅是一个“格式化显示”，实际上读入 DataTable 的值是单元格实际存储的值，即 1 和 1.0001 （类型为 System.Double），将此值写入数据库，精度是由数据库类型精度控制   回复  引用  查看    

#14楼  2007-11-12 12:48 yanshengwu [未注册用户]

哇，很感到啊，楼主那么晚还回帖。嗯，注意休息。
是第二中情况，就是我想如果1.0001设置了三位小数了之后，我读入DataTable也就是三位，就是1.000。也就是我Excel里面的设置了多少位，我读出来放进DataTable的就是多少位，可以做到不？谢谢！   回复  引用    

#15楼 [楼主] 2007-11-13 01:27 晓风残月      

@yanshengwu
这个需求直接使用 ADO.NET 可能无法获取 Excel 中对单元格精度的设置信息，
直观上理解，Excel 中的“精度”是可以设置到具体的每个单元格的，即同一列的单元格中精度可以不一致，而 ADO.NET 是针对关系数据库模型的，每一列中的数组类型应该一致，因此，若是数字，读入DataTable的时候，将其当作 System.Doulbe 处理

若要获取这个“单元格精度信息“，只有使用 Offices Tools API 了，目前 .NET 中可以很方便访问 Excel 信息，
详细信息，请参见，
Excel 任务
http://msdn2.microsoft.com/zh-cn/library/syyd7czh(vs.80).aspx

当然，甚至可以直接通过 Offices Tools 直接读入 Excel 数据   回复  引用  查看    

#16楼  2008-04-28 16:58 孤独的雪 [未注册用户]

我使用以下代码进行数据导入，但始终都导不成功，是什么原因？麻烦帮我看一下。

Private Sub btnimport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnimport.Click

Dim schemaTable As New DataTable
Dim workAdapter As New OleDb.OleDbDataAdapter
Dim workSet As New DataSet
Dim conn As New OleDb.OleDbConnection
Dim i As Integer
Dim x As Integer
Dim charArray As Char() = {",", " "}
Dim charArray2 As Char() = {"$"}
Dim cmdString As String
Dim cmdString2 As String
Dim cmd As New OleDb.OleDbCommand
Dim tableName As String

'workSet.DataSetName = "excelData"

Try
' 设置连接字符串。
Dim connString As String = _
"Data Source=" & file1.PostedFile.FileName & _
";Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0"

' 打开连接。
conn.ConnectionString = connString
conn.Open()

' 使用数据源表中的架构信息填充 DataTable。
schemaTable = _
conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

' 使用表名称填充数组。
i = schemaTable.Rows.Count - 1
Dim tablesArray(i) As String
For i = 0 To schemaTable.Rows.Count - 1
tablesArray(i) = schemaTable.Rows(i).Item("Table_Name").ToString()
Next

' 清除 DataTable
schemaTable.Clear()

For i = 0 To tablesArray.GetLength(0) - 1
Dim query As String = "SELECT user_id,user_name,user_password,user_level,user_legal FROM " & tablesArray(i)

Dim oleadp As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(query, conn)

'oleadp.Fill(Dtuser, " & tablename(k) & ")
cmd.CommandText = query

oleadp.SelectCommand.Connection = conn
oleadp.SelectCommand = cmd

tableName = _
tablesArray(i).ToString()
oleadp.Fill(workSet, tableName)
'schemaTable.Clear()
Next i

'SQL数据库连接()

Dim sqlcn As SqlClient.SqlConnection = New SqlClient.SqlConnection("workstation id=WANGQIANG;packet size=4096;user id=sa;" & _
"password=120979120979;data source=WANGQIANG;persist security info=False;initial catalog=assessonline")

sqlcn.Open()

Dim k As Integer

For k = 0 To tablesArray.GetLength(0) - 1

Dim sourcerow1 As DataRow

For Each sourcerow1 In workSet.Tables(" & tablesArray(i).tostring() & ").Rows
'sql里数据dataRow1
Dim targetrow1 As DataRow = Dtuser.Tables("User").NewRow()
targetrow1("user_id") = sourcerow1("user_id")
targetrow1("user_name") = sourcerow1("user_name")
targetrow1("user_password") = sourcerow1("user_password")
targetrow1("user_level") = sourcerow1("user_level")
targetrow1("user_legal") = sourcerow1("user_legal")
Dtuser.Tables("User").Rows.Add(targetrow1)

Next sourcerow1

Dim ws As New localhost.Service1
Dim diffmsgbox As New localhost.dtuser
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
diffmsgbox.Merge(Me.Dtuser.GetChanges())
ws.Updateusers(diffmsgbox)
Dtuser.Merge(diffmsgbox)

Next k
Response.Write(" <script> alert('恭喜，数据导入成功！') </script> ")

'olecn.Close()
sqlcn.Close()

Catch ex As Exception
'错误处理
Finally
conn.Close()
End Try

'Return workSet

End Sub  

#1楼  2007-08-09 11:15 李卓 [未注册用户]