(转自晓风残月)
经常需要在数据库与Execl之间互导数据。net时代,ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel,网上已经有很多类似的资源,最典型也是最简单的可能如下:(asp.net环境)
//连接字符串
stringxlsPath=Server.MapPath("~/app_data/somefile.xls");//绝对物理路径
stringconnStr="Provider=Microsoft.Jet.OLEDB.4.0;"+
"ExtendedProperties=Excel8.0;"+
"datasource="+xlsPath;
//查询语句
stringsql="SELECT*FROM[Sheet1$]";
DataSetds=newDataSet();
OleDbDataAdapterda=newOleDbDataAdapter(sql,connStr);
da.Fill(ds);//填充DataSet
//在这里对DataSet中的数据进行操作
//输出,绑定数据
GridView1.DataSource=ds.Tables[0];
GridView1.DataBind();
很简单吧?!
一切就像操作数据库一样,只是需要注意的是:
1。数据提供程序使用Jet,同时需要指定Extended Properties 关键字设置 Excel 特定的属性,不同版本的Excel对应不同的属性值:
用于ExtendedProperties值的有效Excel版本。
对于MicrosoftExcel8.0(97)、9.0(2000)和10.0(2002)工作簿,请使用Excel8.0。
对于MicrosoftExcel5.0和7.0(95)工作簿,请使用Excel5.0。
对于MicrosoftExcel4.0工作簿,请使用Excel4.0。
对于MicrosoftExcel3.0工作簿,请使用Excel3.0。
ref:
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp2。数据源路径使用物理绝对路径(同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 格式如下:
stringconnStr="Provider=Microsoft.Jet.OLEDB.4.0;"+
"ExtendedProperties=\"Excel8.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中,它的例子使用了两个双引号是错的,测试没有通过,原文这样说的:
注意,ExtendedProperties所需的双引号必须还要加双引号。
)
在这种情况下,所有的列名都是以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数据源仅提供相当于表的对象,它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”,而工作表被视为“系统表”)
这里我们将Excel也当作一个“数据库”来对待,然后利用OleDbConnection.GetOleDbSchemaTable 方法
要获取所需的架构信息,该方法获取的架构信息与ANSI SQl-92是兼容的:
注意:对于那些不熟悉OLEDB架构行集的人而言,它们基本上是由ANSISQL-92定义的数据库构造的标准化架构。每个架构行集具有为指定构造提供定义元数据的一组列(称作.NET文档中的“限制列”)。这样,如果请求架构信息(例如,列的架构信息或排序规则的架构信息),则您会明确知道可以得到哪种类型的数据。如果希望了解更多信息,请访问AppendixB:SchemaRowsets。
ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true
以下是读取Excel文件内“表”定义元数据,并显示出来的的程序片断:
//读取Excel数据,填充DataSet
//连接字符串
stringxlsPath=Server.MapPath("~/app_data/somefile.xls");
stringconnStr="Provider=Microsoft.Jet.OLEDB.4.0;"+
"ExtendedProperties=\"Excel8.0;HDR=No;IMEX=1\";"+//指定扩展属性为MicrosoftExcel8.0(97)9.0(2000)10.0(2002),并且第一行作为数据返回,且以文本方式读取
"datasource="+xlsPath;
stringsql_F="SELECT*FROM[{0}]";
OleDbConnectionconn=null;
OleDbDataAdapterda=null;
DataTabletblSchema=null;
IList<string>tblNames=null;
//初始化连接,并打开
conn=newOleDbConnection(connStr);
conn.Open();
//获取数据源的表定义元数据
//tblSchema=conn.GetSchema("Tables");
tblSchema=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{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
//连接字符串
stringxlsPath=Server.MapPath("~/app_data/somefile.xls");
stringconnStr="Provider=Microsoft.Jet.OLEDB.4.0;"+
"ExtendedProperties=\"Excel8.0;HDR=No;IMEX=1\";"+//指定扩展属性为MicrosoftExcel8.0(97)9.0(2000)10.0(2002),并且第一行作为数据返回,且以文本方式读取
"datasource="+xlsPath;
stringsql_F="SELECT*FROM[{0}]";
OleDbConnectionconn=null;
OleDbDataAdapterda=null;
DataTabletblSchema=null;
IList<string>tblNames=null;
//初始化连接,并打开
conn=newOleDbConnection(connStr);
conn.Open();
//获取数据源的表定义元数据
//tblSchema=conn.GetSchema("Tables");
tblSchema=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"TABLE"});
//GridView1.DataSource=tblSchema;
//GridView1.DataBind();
//关闭连接
//conn.Close();
tblNames=newList<string>();
foreach(DataRowrowintblSchema.Rows){
tblNames.Add((string)row["TABLE_NAME"]);//读取表名
}
//初始化适配器
da=newOleDbDataAdapter();
//准备数据,导入DataSet
DataSetds=newDataSet();
foreach(stringtblNameintblNames){
da.SelectCommand=newOleDbCommand(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();
//morecodes
//.
这里我们就不需要对SELEC 语句进行“硬编码”,可以根据需要动态的构造FROM 字句的“表名”。
不仅可以,获取表明,还可以获取每张表内的字段名、字段类型等信息:
tblSchema=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,newobject[]{null,null,null,null});
在ADO.nET 1.x 时候只有OleDb提供了GetOleDbSchemaTable 方法,而SqlClient或者OrcaleClient没有对应的方法,因为对应数据库已经提供了类似功能的存储过程或者系统表供应用程序访问,比如对于Sql Server:
SELECT*
FROMNorthwind.INFORMATION_SCHEMA.COLUMNS
WHERETABLE_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 ADO.NET读取Excel ADO.NET 不支持解析带有密码保护的EXCEL
C#采用ADO.NET读取Excel 0307 数据不完整的问题
NULL 博文链接:https://code.iteye.com/blog/908049
本文实例讲述了C#使用Ado.net读取Excel表的方法。分享给大家供大家参考。具体分析如下:微软NET提供了一个交互的方法,通过使用ADO.NET与Microsoft Office程序。可以使用内置的OLEDB来访问Excel的XLS表格。下面的...
如何在ado.net中对Excel文件进行读取操作
vb.net 读取excel
用ADO.NET访问及操作Excel数据的教程,附实例及源码
经常需要在数据库与Execl之间互导数据。net时代,ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel,网上已经有很多类似的资源,最典型也是最简单的可能如下:(asp.net环境)
初学者Ado(Adodb)读取(访问)Excel数据,有详细的...但这不是VB.net读取(访问)Excel数据的最好方法,最好的方法是应该vb.net平台进行改进后的ado.net方法,该方法我相信我也会详细的提供给初学者,想要的请关注78472220
读取好像有几种方式,通过ADO.net, 通过Microsoft.Interop.Excel支持类库用调用COM读取,还有通过ZIP解压最终读取DOM(这个貌似蛮复杂)这里我用的ADO.NET只介绍这一个。 代码如下:public DataTable ExcelTo...
ADO.NET在开发中的部分使用方法和技巧.txt ADO.NET中的视图和过滤器.doc ASP .NET - ArrayList对象.txt asp.net 2.0中TREEVIEW中动态增加结点.txt Asp.net 实现验证码功能的Web控件.txt asp.net常用的javascript经典...
讲述了ADO记录集输出到Excel的方法。 介绍了CopyFromRecordset,GetRows方法。 讲的比较透彻。
ADO.NET在开发中的部分使用方法和技巧.txt ADO.NET中的视图和过滤器.doc ASP .NET - ArrayList对象.txt asp.net 2.0中TREEVIEW中动态增加结点.txt Asp.net 实现验证码功能的Web控件.txt asp.net常用的javascript经典...
在.NET开发中,不管是web程序还是桌面软件(尤其是涉及数据库操作的MIS系统等),常常需操作Excel,如导出数据到Excel,读取Excel中数据到程序中等。总结起来,其操作不外乎创建、打开、读写、保存(后退出/释放)...
首先讨论了在 ASP.NET 和 ADO.NET 中处理数据的原理,在 ADO.NET 中引入了 Command 对象。然后介绍如何运用 DataReader 获取数据,接着将那些数据绑定到像单选按钮、复选框这样的不同控件上和最重要的 DataGrid 上。...
ADO.NET 2.0 大批量数据操作和多个动态的结果集 ADO.NET 2.0 异步处理 在ASP.NET中使用WINDOWS验证方式连接SQL SERVER数据库 改进ADO.Net数据库访问方式 ASP.NET 2.0 绑定高级技巧 简单实用的DataSet更新数据库的类+...
第7章 数据库与ADO.NET基础 7.1 数据库基础 7.1.1 结构化查询语言 7.1.2 表和视图 7.1.3 存储过程和触发器 7.2 使用SQL Server 2005 管理数据库 7.2.1 初步认识SQL Server 2005 7.2.2 创建数据库 7.2.3 删除数据库 ...