经常需要在数据库与Execl之间互导数据
net时代
ADO
NET可以使用使用Microsoft
Jet
OleDb访问访问Excel
网上已经有很多类似的资源
最典型也是最简单的可能如下
(环境)
// 连接字符串
string xlsPath = ServerMapPath(~/app_data/somefilexls); // 绝对物理路径
string connStr = PRovider=MicrosoftJetOLEDB; +
Extended Properties=Excel ; +
data source= + xlsPath;
// 查询语句
string sql = SELECT * FROM [Sheet$];
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql connStr);
daFill(ds); // 填充DataSet
// 在这里对DataSet中的数据进行操作
// 输出绑定数据
GridViewDataSource = dsTables[];
GridViewDataBind();
很简单吧?!一切就像操作数据库一样只是需要注意的是
数据提供程序使用Jet同时需要指定Extended Properties 关键字设置 Excel 特定的属性不同版本的Excel对应不同的属性值用于 Extended Properties 值的有效 Excel 版本
对于 Microsoft Excel () () 和 () 工作簿请使用 Excel
对于 Microsoft Excel 和 () 工作簿请使用 Excel
对于 Microsoft Excel 工作簿请使用 Excel
对于 Microsoft Excel 工作簿请使用 Excel
ref*url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodatasetasp
数据源路径使用物理绝对路径(同access)
如何引用表名? 对 Excel 工作簿中表(或范围)的有效引用
若要引用完全使用的工作表的范围请指定后面跟有美元符号的工作表名称例如
select * from [Sheet$]
若要引用工作表上的特定地址范围请指定后面跟有美元符号和该范围的工作表名称例如
select * from [Sheet$A:B]
若要引用指定的范围请使用该范围的名称例如
select * from [MyNamedRange]
ref*url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodatasetasp
说明
可以引用Excel 工作簿中的三种对象
* 整张工作表[Sheet$] Sheet 就是工作表的名称
* 工作表上的命名单元格区域[MyNamedRange] (不需要指定工作表因为整个xls中命名区域只能唯一)
XLS命名方法选中单元格范围》插入》名称》定义
* 工作表上的未命名单元格区域 [Sheet$A:B]
(在关系数据库提供的各种对象中(表视图存储过程等)Excel 数据源仅提供相当于表的对象它由指定工作簿中的工作表和定义的命名区域组成命名区域被视为表而工作表被视为系统表)
注意
*必须使用[](方括号)否将报
FROM 子句语法错误
*必须跟$(美元符号)否则报
Microsoft Jet 数据库引擎找不到对象Sheet请确定对象是否存在并正确地写出它的名称和路径
*如果工作表名称不对或者不存在将报
Sheet$ 不是一个有效名称请确认它不包含无效的字符或标点且名称不太长
*在 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 中提到可以使用
~ 和 (波浪线和单引号)代替[]使用ADONET测试没有成功报
FROM 子句语法错误
*当引用工作表明名([Sheet$])时数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始比如工作表从第 行C 列开始第行C列之前以及第行全为空则只会显示从第行C列开始的数据以最后表最大范围内的非空单元结束
*因此如需要精确读取范围应该使用命名区域 [NamedRange]或者指定地址[Sheet$A:C]
如何引用列名? *根据默认连接字符串中数据提供程序会将有效区域内的第一行作为列名如果此行某单元格为空则用FF表示其中序数跟单元格的位置一致从开始
*如果希望第一行作为数据显示而非列名可以在连接串的 Extended Properties 属性指定HDR=NO
默认值为HDR=NO 格式如下
string connStr = Provider=MicrosoftJetOLEDB; +
Extended Properties=\Excel ;HDR=NO\; +
data source= + xlsPath;
注意 Excel ;HDR=NO 需要使用双引号(这里的反斜扛是C#中的转义)
refmshelp://MSVSCCv/MSMSDNv/MSVisualStudiovchs/WD_ADONET/html/cffb 中 《连接Excel》节(说明在我自己的MSDN中它的例子使用了两个双引号是错的测试没有通过原文这样说的
注意Extended Properties 所需的双引号必须还要加双引号
)
在这种情况下所有的列名都是以F开头然后跟索引从F开始FF
为什么有效单元格数据不显示出来? 出现这种情况的可能原因是默认连接中数据提供程序根据前面单元格推断后续单元个的数据类型
可以通过 Extended Properties 中指定 IMEX=
IMEX=;通知驱动程序始终将互混数据列作为文本读取
ref同
PS在baidu这个问题的时候有网友说将每个单元都加上引号这固然是格方案但是工作量何其大啊又不零活庆幸自己找到治本药方
more ref
如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据
/ShowDetailaspx*id=CECDFECEDBA
应用程序经常需要与Excel进行数据交互以上阐述了基于ADONET 读取Excel的基本方法与技巧现在要介绍是如何动态的读取Excel数据这里的动态指的是事先不知道Excel文件的是什么样的结构或者无法预测比如一张xls文件有多少张sheet而且每张sheet的结构可能都不一样等等
其实我们可以通过获取Excel的架构信息来动态的构造查询语句这里的架构信息与数据库领域的数据库架构信息意义相同(也称元数据)对于整个数据库这些元数据通常包括数据库或可通过数据库中的数据源表和视图得到的目录以及所存在的约束等而对于数据库中的表架构信息包括主键列和自动编号字段等
在上文中提到
在关系数据库提供的各种对象中(表视图存储过程等)Excel 数据源仅提供相当于表的对象它由指定工作簿中的工作表和定义的命名区域组成命名区域被视为表而工作表被视为系统表)
这里我们将Excel也当作一个数据库来对待然后利用OleDbConnectionGetOleDbSchemaTable 方法
要获取所需的架构信息该方法获取的架构信息与ANSI SQl是兼容的
注意对于那些不熟悉 OLE DB 架构行集的人而言它们基本上是由 ANSI SQL 定义的数据库构造的标准化架构每个架构行集具有为指定构造提供定义元数据的一组列(称作 NET 文档中的限制列)这样如果请求架构信息(例如列的架构信息或排序规则的架构信息)则您会明确知道可以得到哪种类型的数据如果希望了解更多信息请访问 Appendix B:Schema Rowsets
refx*mfr=true
以下是读取Excel文件内表定义元数据并显示出来的的程序片断
// 读取Excel数据填充DataSet
// 连接字符串
string xlsPath = ServerMapPath(~/app_data/somefilexls);
string connStr = Provider=MicrosoftJetOLEDB; +
Extended Properties=\Excel ;HDR=No;IMEX=\; + // 指定扩展属性为 Microsoft Excel () () ()并且第一行作为数据返回且以文本方式读取
data source= + xlsPath;
string sql_F = SELECT * FROM [{}];
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
IList<string> tblNames = null;
// 初始化连接并打开
conn = new OleDbConnection(connStr);
connOpen();
// 获取数据源的表定义元数据
//tblSchema = connGetSchema(Tables);
tblSchema = connGetOleDbSchemaTable(OleDbSchemaGuidTables new object[] { null null null TABLE });
GridViewDataSource = tblSchema;
GridViewDataBind();
// 关闭连接
connClose();
GetOleDbSchemaTable 方法的详细说明可以参考
CN/library/systemdataoledboledbconnectiongetoledbschematableaspx
接着是一段利用架构信息动态读取Excel内部定义的表单或者命名区域的程序片断
// 读取Excel数据填充DataSet
// 连接字符串
string xlsPath = ServerMapPath(~/app_data/somefilexls);
string connStr = Provider=MicrosoftJetOLEDB; +
Extended Properties=\Excel ;HDR=No;IMEX=\; + // 指定扩展属性为 Microsoft Excel () () ()并且第一行作为数据返回且以文本方式读取
data source= + xlsPath;
string sql_F = SELECT * FROM [{}];
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
IList<string> tblNames = null;
// 初始化连接并打开
conn = new OleDbConnection(connStr);
connOpen();
// 获取数据源的表定义元数据
//tblSchema = connGetSchema(Tables);
tblSchema = connGetOleDbSchemaTable(OleDbSchemaGuidTables new object[] { null null null TABLE });
//GridViewDataSource = tblSchema;
//GridViewDataBind();
// 关闭连接
//connClose();
tblNames = new List<string>();
foreach (DataRow row in tblSchemaRows) {
tblNamesAdd((string)row[TABLE_NAME]); // 读取表名
}
// 初始化适配器
da = new OleDbDataAdapter();
// 准备数据导入DataSet
DataSet ds = new DataSet();
foreach (string tblName in tblNames) {
daSelectCommand = new OleDbCommand(StringFormat(sql_F tblName) conn);
try {
daFill(ds tblName);
}
catch {
// 关闭连接
if (connState == ConnectionStateOpen) {
connClose();
}
throw;
}
}
// 关闭连接
if (connState == ConnectionStateOpen) {
connClose();
}
// 对导入DataSet的每张sheet进行处理
// 这里仅做显示
GridViewDataSource = dsTables[];
GridViewDataBind();
GridViewDataSource = dsTables[];
GridViewDataBind();
// more codes
//
这里我们就不需要对SELEC 语句进行硬编码可以根据需要动态的构造FROM 字句的表名
不仅可以获取表明还可以获取每张表内的字段名字段类型等信息
tblSchema = connGetOleDbSchemaTable(OleDbSchemaGuidColumns new object[] { null null null null });
在ADOnET x 时候只有OleDb提供了GetOleDbSchemaTable 方法而SqlClient或者OrcaleClient没有对应的方法因为对应数据库已经提供了类似功能的存储过程或者系统表供应用程序访问比如对于Sql Server
SELECT *
FROM NorthwindINFORMATION_SCHEMACOLUMNS
WHERE TABLE_NAME = NCustomers
而在ADONET 中每个xxxConnenction都实现了基类SystemDataCommonDbConnection的 GetSchemal 方法
来获取数据源的架构信息