ADO.NET 如何读取 Excel

原创 2007年09月27日 17:11:00

经常需要在数据库与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;" +
                        
"Extended Properties=Excel 8.0;" + 
                        
"data source=" + xlsPath;
        // 查询语句
        string sql 
= "SELECT * FROM [Sheet1$]";

        DataSet ds 
= new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
        da.Fill(ds);    // 填充DataSet        
        
        // 在这里对DataSet中的数据进行操作        

        // 输出,绑定数据
        GridView1.DataSource 
= ds.Tables[0]; 
        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。 

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开始;
•如果希望第一行作为数据显示,而非列名,可以在连接串的 Extended Properties 属性指定:HDR=NO
默认值为:HDR=NO应该为 HDR=YES,即默认将第一行作为列名,竟然一直没有发现这个错误,汗upadted 2007年8月18日)格式如下:

        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        
"Extended Properties=/"Excel 8.0;HDR=NO/";" + 
                        
"data source=" + 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

related:
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[] nullnullnull"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[] nullnullnull"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[] nullnullnullnull });

在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 检索架构信息
从数据库中获取架构信息
 

相关文章推荐

ADO.NET 如何读取 Excel (上)

转自 http://kb.cnblogs.com/page/42229/ ADO.NET 如何读取 Excel (上) 作者: 晓风残月 来源: 博客园 发布时间: 2008-09-09 19:5...

使用ADO.NET的DbProviderFactory读取或写入Excel中的表单

由于工作需要,需要将数据库中的表填充到Excel中,我本人是相当不喜欢任何类型的手动输入输出,或表格计算的导入导出,但是事情还是要做的。 通过ADO.NET的DbProviderFactory可以轻...

ADO.NET 如何读取 Excel

ADO.NET 如何读取 Excel    原文链接  觉得原作者写得非常详细,操作Excel会经常遇到,故学习收藏了! 经常需要在数据库与Execl之间互导数据。net时代...
  • chenep
  • chenep
  • 2011年12月04日 22:08
  • 232

ADO.NET如何读取 Excel

  • 2010年01月09日 15:14
  • 36KB
  • 下载

ado.net如何读取Excel

  • 2011年09月18日 22:30
  • 32KB
  • 下载

ADO.NET操纵Excel 标点符号 数字和字符混合问题! HDR=Yes;IMEX=1

参数HDR的值: HDR=Yes,这代表第一行是标题,不做为数据使用,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES 参数Excel 8.0 对于Excel 97以...

利用ADO.NET连接Excel数据库,并执行相应的操作

利用ADO.NET连接Excel数据库,并执行相应的操作: 创建表格,读取数据,写入数据,获取工作簿中的所有工作表名称。...
  • zfybs
  • zfybs
  • 2015年03月08日 11:51
  • 268

用ADO.NET实现txt与Excel的互相转换

在园子里看过很多文章,关于设计模式,关于架构等等,我在这里谈谈一些软件的功能,为什么需要这样的功能。 我前段时间写了一个TXT与EXCEL为什么要互相转换的功能,可能有人会问,这样的功能有什么作用?...
  • xbsoar
  • xbsoar
  • 2011年12月04日 21:08
  • 322

Ado.Net操作Excel文件数据常见问题及解决

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

using ado.net to operate excel

  • 2014年02月24日 10:28
  • 95KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ADO.NET 如何读取 Excel
举报原因:
原因补充:

(最多只允许输入30个字)