ADO.NET操作Excel的限制__1

1、ADO.NET操作Excel的限制_ ADO.NET操作Excel的限制： 1.不能设置单元格格式，插入新行会自动继承上一行的格式 . 2.您不能用法 ADO.NET 在单元格中插入公式。 3.Jet OLE DB 供应程序无法为 Excel 工作簿中的表供应主键/索引信息。因此，您不能用法 CommandBuilder 在 Excel 工作簿中自动更新和插入记录。 / 连接字符串 string xlsPath = d:PathFileName.XLS; / 肯定物理路径 string connStr = Provider=Microsoft.Jet.OLEDB.4.0; + Extended

2、 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 需要留意的是： 1.数据供应程序用法Jet,同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值：用于 Extended

3、Properties 值的有效 Excel 版本。 对于 Microsoft Excel 8.0 （97）、9.0 （2021） 和 10.0 （2021） 工作簿，请用法 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:l/vbtskcodeexamplereadingexceldataintodataset.asp 2.数据源路径用法物理

4、肯定路径（同Access） 3.如何引用表名？ 若要引用完全用法的工作表的范围，请指定后面跟有美元符号的工作表名称。例如： select * from Sheet1$ 若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： select * from Sheet1$A1:B10 若要引用指定的范围，请用法该范围的名称。例如： select * from MyNamedRange ref:l/vbtskcodeexamplereadingexceldataintodataset.asp （在关系数据库供应的各种对象中（表、视图、存储过程等），Excel 数据源仅供应相

5、当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为表,而工作表被视为系统表） 当引用工作表名（Sheet1$）时，数据供应程序认为数据表从指定工作表上最左上方的非空单元格开头。比如，工作表从第 3 行，C 列开头，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开头的数据；以最终表最大范围内的非空单元结束；因此，如需要精确读取范围，应当用法命名区域 NamedRange,或者指定地址：Sheet1$A1:C10 4.如何引用列名？ 依据默认连接字符串中，数据供应程序会将有效区域内的第一行作为列名，假如此行某单元格为空则用F1、F2表示，其中序数，跟单元格的

6、位置全都，从1开头； 假如盼望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO 格式如下： string connStr = Provider=Microsoft.Jet.OLEDB.4.0; + Extended Properties=Excel 8.0;HDR=NO; + data source= + xlsPath; 在这种状况下，全部的列名都是以F开头，然后跟索引，从F1开头，F2,F3 留意： Excel 8.0;HDR=NO 需要用法双引号（这里的反斜扛，是C#中的转义） ref:ms-help:/MS.VSCC.v80/

7、MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 5.为什么有效单元格数据不显示出来？ 消失这种状况的可能缘由是，默认连接中，数据供应程序依据前面单元格推断后续单元个的数据类型。 可以通过 Extended Properties 中指定 IMEX=1 IMEX=1;通知驱动程序始终将互混数据列作为文本读取 格式如下： string connStr = Provider=Microsoft.Jet.OLEDB.4.0; + Extended Properties=

8、Excel 8.0;HDR=NO;IMEX=1; + data source= + xlsPath; ref:同4 PS:在baidu这个问题的时候，有网友说，将每个单元都加上引号，这当然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到治本药方： more ref: 如何在 Visual Basic 或 VBA 中用法 ADO 来处理 Excel 数据 应用程序常常需要与Excel进行数据交互，以上阐述了基于ADO.NET 读取Excel的基本方法与技巧。现在要介绍是如何动态的读取Excel数据，这里的动态指的是事先不知道Excel文件的是什么样的结构，或者无法预报，比如一张。xls文件有

9、多少张sheet,而且每张sheet的结构可能都不一样等等。 其实我们可以通过猎取Excel的架构信息来动态的构造查询语句。这里的架构信息与数据库领域的数据库架构信息意义相同（也称元数据），对于整个数据库，这些元数据通常包括数据库或可通过数据库中的数据源、表和视图得到的名目以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。 在上文中提到 在关系数据库供应的各种对象中（表、视图、存储过程等），Excel 数据源仅供应相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为表,而工作表被视为系统表） 这里我们将Excel也当作一个数据库来对待，然后利

10、用OleDbConnection.GetOleDbSchemaTable 方法 要猎取所需的架构信息，该方法猎取的架构信息与ANSI SQl-92是兼容的：ref: 以下是读取Excel文件内表定义元数据，并显示出来的的程序片断： / 读取Excel数据，填充DataSet string sql_F = SELECT * FROM 0; OleDbConnection conn = null; OleDbDataAdapter da = null; DataTable tblSchema = null; IListstring tblNames = null; conn = new OleDb

11、Connection（connStr）；/ 初始化连接 conn.Open（）； / 打开 tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables, new object null, null, null, TABLE ）；/ 猎取数据源的表定义元数据 GetOleDbSchemaTable tblNames = new Liststring（）； foreach （DataRow row in tblSchema.Rows） tblNames.Add（string）rowTABLE_NAME）； / 读取表名 da = new

12、OleDbDataAdapter（）；/ 初始化适配器 DataSet ds = new DataSet（）； / 预备数据，导入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 = C

13、onnectionState.Open） conn.Close（）；/ 关闭连接 / 对导入DataSet的每张sheet进行处理 （省略） 这里我们就不需要对SELEC 语句进行硬编码,可以依据需要动态的构造FROM 字句的表名. 不仅可以，猎取表名，还可以猎取每张表内的字段名、字段类型等信息： tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Columns, new object null, null, null, null ）； 在ADO.nET 1.x 时候只有OleDb供应了GetOleDbSchemaTable 方法，而Sq

14、lClient或者OrcaleClient没有对应的方法，由于对应数据库已经供应了类似功能的存储过程或者系统表供应用程序访问，比如对于Sql Server: SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = NCustomers 而在ADO.NET 2.0中每个xxxConnenction都实现了基类System.Data.Common.DbConnection的 GetSchemal 方法 来猎取数据源的架构信息。 A BugEyes 发表于 2021-10-13 15:58:00 推举 1、环境配置：

15、加入using System.OleDb 2、编写连接与操作excel文件的通用函数 protected void DoOleSql（string sql, string database） OleDbConnection conn = new OleDbConnection（）； conn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath（） + database + ; Extended Properties=Excel 8.0;HDR=no;IMEX=0; try /打开连接

16、 conn.Open（）； catch （Exception e） Response.Write（e.ToString（）； OleDbCommand olecommand = new OleDbCommand（sql, conn）； try /执行语句 olecommand.ExecuteNonQuery（）； catch （Exception eee） Response.Write（eee.ToString（）； conn.Close（）； finally conn.Close（）；/关闭数据库 conn.Close（）； 注：1）用法 Excel 工作簿时，默认状况下，区域中的第一行是标

17、题行（或字段名称）。假如第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 HDR=NO.假如您在连接字符串中指定 HDR=NO,Jet OLE DB 供应程序将自动为您命名字段（F1 表示第一个字段，F2 表示其次个字段，依此类推）；2）IMEX=1将全部读入数据看作字符，其他值（0、2）请查阅相关关心文档；3）假如消失找不到可安装的isam错误，一般是连接字符串错误 3、从excel文件读取数据 string sql = select * from sheet1$; DoOleSql（sql,test.xls）； 4、更新excel文件中的数据 string sql = update

18、 sheet1$ set FieldName1=333 where FieldName2=b3; DoOleSql（sql,test.xls）； 5、向excel文件插入数据 string sql = insert into sheet1$（FieldName1,FieldName2,） values（a,b,）; DoOleSql（sql,test.xls）； 6、删除excel文件中的数据：不提倡用法这种方法 7、对于非标准结构的excel表格，可以指定excel中sheet的范围 1）读取数据：string sql = select * from sheet1$A3:F20; 2）更新数据：string sql = update sheet