|
 |
栏目导栏 |
|
| |
|
|
|
|
|
资料搜索 |
|
| |
|
|
|
|
|
热门文章 |
|
| |
|
|
|
|
|
最新文章 |
|
| |
|
|
|
| |
| |
|
|
|
|
经常需要在数据库与Execl之间互导数据。net时代，ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel，网上已经有很多类似的资源，最典型也是最简单的可能如下：（asp.net环境） swvLinux联盟
swvLinux联盟
// 连接字符串 swvLinux联盟
string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径 swvLinux联盟
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + swvLinux联盟
"Extended Properties=Excel 8.0;" + swvLinux联盟
"data source=" + xlsPath; swvLinux联盟
// 查询语句 swvLinux联盟
string sql = "SELECT * FROM [Sheet1$]"; swvLinux联盟
swvLinux联盟
DataSet ds = new DataSet(); swvLinux联盟
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr); swvLinux联盟
da.Fill(ds); // 填充DataSet swvLinux联盟
swvLinux联盟
// 在这里对DataSet中的数据进行操作 swvLinux联盟
swvLinux联盟
// 输出，绑定数据 swvLinux联盟
GridView1.DataSource = ds.Tables[0]; swvLinux联盟
GridView1.DataBind(); swvLinux联盟
很简单吧？！一切就像操作数据库一样，只是需要注意的是： swvLinux联盟
1。数据提供程序使用Jet，同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值： 用于 Extended Properties 值的有效 Excel 版本。 swvLinux联盟
对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，请使用 Excel 8.0。 swvLinux联盟
swvLinux联盟
对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿，请使用 Excel 5.0。 swvLinux联盟
swvLinux联盟
对于 Microsoft Excel 4.0 工作簿，请使用 Excel 4.0。 swvLinux联盟
swvLinux联盟
对于 Microsoft Excel 3.0 工作簿，请使用 Excel 3.0。 swvLinux联盟
swvLinux联盟
ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp swvLinux联盟
swvLinux联盟
2。数据源路径使用物理绝对路径（同Access） swvLinux联盟
swvLinux联盟
3。如何引用表名？ swvLinux联盟
对 Excel 工作簿中表（或范围）的有效引用。 swvLinux联盟
若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如： swvLinux联盟
swvLinux联盟
select * from [Sheet1$] swvLinux联盟
若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： swvLinux联盟
swvLinux联盟
select * from [Sheet1$A1:B10] swvLinux联盟
若要引用指定的范围，请使用该范围的名称。例如： swvLinux联盟
swvLinux联盟
select * from [MyNamedRange] swvLinux联盟
ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp swvLinux联盟
说明： swvLinux联盟
可以引用Excel 工作簿中的三种对象： swvLinux联盟
• 整张工作表：[Sheet1$] ，Sheet1 就是工作表的名称 swvLinux联盟
• 工作表上的命名单元格区域：[MyNamedRange] （不需要指定工作表，因为整个xls中命名区域只能唯一） swvLinux联盟
XLS命名方法：选中单元格范围》插入》名称》定义 swvLinux联盟
• 工作表上的未命名单元格区域 ：[Sheet1$A1:B10] swvLinux联盟
（在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”） swvLinux联盟
swvLinux联盟
注意： swvLinux联盟
•必须使用[]（方括号），否将报： swvLinux联盟
FROM 子句语法错误 swvLinux联盟
•必须跟$（美元符号），否则报： swvLinux联盟
Microsoft Jet 数据库引擎找不到对象'Sheet2'。请确定对象是否存在，并正确地写出它的名称和路径。 swvLinux联盟
•如果工作表名称不对，或者不存在，将报： swvLinux联盟
'Sheet2$' 不是一个有效名称。请确认它不包含无效的字符或标点，且名称不太长。 swvLinux联盟
•在 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 中提到可以使用 swvLinux联盟
~ 和 '(波浪线和单引号）代替[]，使用ADO。NET测试没有成功，报： swvLinux联盟
FROM 子句语法错误 swvLinux联盟
•当引用工作表明名（[Sheet1$]）时，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，C 列开始，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开始的数据；以最后表最大范围内的非空单元结束； swvLinux联盟
•因此，如需要精确读取范围，应该使用命名区域 [NamedRange]，或者指定地址：[Sheet1$A1:C10] swvLinux联盟
swvLinux联盟
4。如何引用列名？ swvLinux联盟
•根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用F1、F2表示，其中序数，跟单元格的位置一致，从1开始； swvLinux联盟
•如果希望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO swvLinux联盟
默认值为：HDR=NO 格式如下： swvLinux联盟
swvLinux联盟
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + swvLinux联盟
"Extended Properties=""Excel 8.0;HDR=NO"";" + swvLinux联盟
"data source=" + xlsPath; swvLinux联盟
注意： Excel 8.0;HDR=NO 需要使用双引号（这里的反斜扛，是C#中的转义） swvLinux联盟
swvLinux联盟
ref： ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 中 《连接Excel》节（说明：在我自己的MSDN中，它的例子使用了两个双引号是错的，测试没有通过，原文这样说的： swvLinux联盟
swvLinux联盟
注意，Extended Properties 所需的双引号必须还要加双引号。 swvLinux联盟
） swvLinux联盟
swvLinux联盟
在这种情况下，所有的列名都是以F开头，然后跟索引，从F1开始，F2，F3。。。。。。。 swvLinux联盟
swvLinux联盟
5。为什么有效单元格数据不显示出来？ swvLinux联盟
出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。 swvLinux联盟
可以通过 Extended Properties 中指定 IMEX=1 swvLinux联盟
swvLinux联盟
“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取 swvLinux联盟
ref：同4 swvLinux联盟
swvLinux联盟
PS：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到”治本药方“ swvLinux联盟
swvLinux联盟
more ref： swvLinux联盟
如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 swvLinux联盟
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599 swvLinux联盟
swvLinux联盟
注意： swvLinux联盟
在网上有很多同志说是加上HDR或IMEX这两个属性值后会报“找不到可安装的ISAM ”的错误。这时解决方法很简单： swvLinux联盟
就是把连接串写成如下形式： swvLinux联盟
swvLinux联盟
Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=1; IMEX=1;' (注意红色标注的单引号) swvLinux联盟
swvLinux联盟
而不要写成这样的形式： swvLinux联盟
swvLinux联盟
Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0;HDR=1; IMEX=1; swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
应用程序经常需要与Excel进行数据交互，在上一篇文章ADO.NET 如何读取 Excel （上）阐述了基于ADO.NET 读取Excel的基本方法与技巧。今天这里要介绍是如何动态的读取Excel数据，这里的动态指的是事先不知道Excel文件的是什么样的结构，或者无法预测，比如一张.xls文件有多少张sheet，而且每张sheet的结构可能都不一样等等。 swvLinux联盟
其实我们可以通过获取Excel的“架构信息”来动态的构造查询语句。这里的“架构信息”与数据库领域的“数据库架构信息”意义相同（也称“元数据”），对于整个数据库，这些“元数据”通常包括数据库或可通过数据库中的数据源、表和视图得到的目录以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。 swvLinux联盟
在ADO.NET 如何读取 Excel （上）提到 swvLinux联盟
swvLinux联盟
在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”） swvLinux联盟
swvLinux联盟
swvLinux联盟
swvLinux联盟
这里我们将Excel也当作一个“数据库”来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法 swvLinux联盟
要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的： swvLinux联盟
swvLinux联盟
注意：对于那些不熟悉 OLE DB 架构行集的人而言，它们基本上是由 ANSI SQL-92 定义的数据库构造的标准化架构。每个架构行集具有为指定构造提供定义元数据的一组列（称作 .NET 文档中的“限制列”）。这样，如果请求架构信息（例如，列的架构信息或排序规则的架构信息），则您会明确知道可以得到哪种类型的数据。如果希望了解更多信息，请访问 Appendix B: Schema Rowsets。 swvLinux联盟
ref：http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true swvLinux联盟
swvLinux联盟
以下是读取Excel文件内“表”定义元数据，并显示出来的的程序片断： swvLinux联盟
swvLinux联盟
// 读取Excel数据，填充DataSet swvLinux联盟
// 连接字符串 swvLinux联盟
string xlsPath = Server.MapPath("~/app_data/somefile.xls"); swvLinux联盟
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + swvLinux联盟
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 swvLinux联盟
"data source=" + xlsPath; swvLinux联盟
string sql_F = "SELECT * FROM [{0}]"; swvLinux联盟
swvLinux联盟
OleDbConnection conn = null; swvLinux联盟
OleDbDataAdapter da = null; swvLinux联盟
DataTable tblSchema = null; swvLinux联盟
IList<string> tblNames = null; swvLinux联盟
swvLinux联盟
// 初始化连接，并打开 swvLinux联盟
conn = new OleDbConnection(connStr); swvLinux联盟
conn.Open(); swvLinux联盟
swvLinux联盟
// 获取数据源的表定义元数据 swvLinux联盟
//tblSchema = conn.GetSchema("Tables"); swvLinux联盟
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); swvLinux联盟
swvLinux联盟
GridView1.DataSource = tblSchema; swvLinux联盟
GridView1.DataBind(); swvLinux联盟
swvLinux联盟
// 关闭连接 swvLinux联盟
conn.Close(); swvLinux联盟
GetOleDbSchemaTable 方法的详细说明可以参考： swvLinux联盟
http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx swvLinux联盟
swvLinux联盟
接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断： swvLinux联盟
swvLinux联盟
swvLinux联盟
// 读取Excel数据，填充DataSet swvLinux联盟
// 连接字符串 swvLinux联盟
string xlsPath = Server.MapPath("~/app_data/somefile.xls"); swvLinux联盟
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + swvLinux联盟
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 swvLinux联盟
"data source=" + xlsPath; swvLinux联盟
string sql_F = "SELECT * FROM [{0}]"; swvLinux联盟
swvLinux联盟
OleDbConnection conn = null; swvLinux联盟
OleDbDataAdapter da = null; swvLinux联盟
DataTable tblSchema = null; swvLinux联盟
IList<string> tblNames = null; swvLinux联盟
swvLinux联盟
// 初始化连接，并打开 swvLinux联盟
conn = new OleDbConnection(connStr); swvLinux联盟
conn.Open(); swvLinux联盟
swvLinux联盟
// 获取数据源的表定义元数据 swvLinux联盟
//tblSchema = conn.GetSchema("Tables"); swvLinux联盟
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); swvLinux联盟
swvLinux联盟
//GridView1.DataSource = tblSchema; swvLinux联盟
//GridView1.DataBind(); swvLinux联盟
swvLinux联盟
// 关闭连接 swvLinux联盟
//conn.Close(); swvLinux联盟
swvLinux联盟
tblNames = new List<string>(); swvLinux联盟
foreach (DataRow row in tblSchema.Rows) { swvLinux联盟
tblNames.Add((string)row["TABLE_NAME"]); // 读取表名 swvLinux联盟
} swvLinux联盟
swvLinux联盟
// 初始化适配器 swvLinux联盟
da = new OleDbDataAdapter(); swvLinux联盟
// 准备数据，导入DataSet swvLinux联盟
DataSet ds = new DataSet(); swvLinux联盟
swvLinux联盟
foreach (string tblName in tblNames) { swvLinux联盟
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn); swvLinux联盟
try { swvLinux联盟
da.Fill(ds, tblName); swvLinux联盟
} swvLinux联盟
catch { swvLinux联盟
// 关闭连接 swvLinux联盟
if (conn.State == ConnectionState.Open) { swvLinux联盟
conn.Close(); swvLinux联盟
} swvLinux联盟
throw; swvLinux联盟
} swvLinux联盟
} swvLinux联盟
swvLinux联盟
// 关闭连接 swvLinux联盟
if (conn.State == ConnectionState.Open) { swvLinux联盟
conn.Close(); swvLinux联盟
} swvLinux联盟
swvLinux联盟
// 对导入DataSet的每张sheet进行处理 swvLinux联盟
// 这里仅做显示 swvLinux联盟
GridView1.DataSource = ds.Tables[0]; swvLinux联盟
GridView1.DataBind(); swvLinux联盟
swvLinux联盟
GridView2.DataSource = ds.Tables[1]; swvLinux联盟
GridView2.DataBind(); swvLinux联盟
swvLinux联盟
// more codes swvLinux联盟
// . swvLinux联盟
swvLinux联盟
这里我们就不需要对SELEC 语句进行“硬编码”，可以根据需要动态的构造FROM 字句的“表名”。 swvLinux联盟
swvLinux联盟
不仅可以，获取表明，还可以获取每张表内的字段名、字段类型等信息： swvLinux联盟
swvLinux联盟
swvLinux联盟
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null }); swvLinux联盟
swvLinux联盟
在ADO.nET 1.x 时候只有OleDb提供了GetOleDbSchemaTable 方法，而SqlClient或者OrcaleClient没有对应的方法，因为对应数据库已经提供了类似功能的存储过程或者系统表供应用程序访问，比如对于Sql Server： swvLinux联盟
swvLinux联盟
SELECT * swvLinux联盟
FROM Northwind.INFORMATION_SCHEMA.COLUMNS swvLinux联盟
WHERE TABLE_NAME = N'Customers' swvLinux联盟
swvLinux联盟
swvLinux联盟
而在ADO.NET 2.0中每个xxxConnenction都实现了基类System.Data.Common.DbConnection的 GetSchemal 方法 swvLinux联盟
Linux联盟收集整理 ,转贴请标明原始链接,如有任何疑问欢迎来本站Linux论坛讨论 |
|
|
|
|
|
