ADO.NET操作Excel的限制

　　ADO.NET操作Excel的限制：

　　1.不能设置单元格格式，插入新行会自动继承上一行的格式 .

　　? 2.您不能使用 ADO.NET 在单元格中插入公式。

　　?

　　3.Jet OLE DB 提供程序无法为 Excel 工作簿中的表提供主键/索引信息。因此，您不能使用 CommandBuilder 在 Excel 工作簿中自动更新和插入记录。

　　// 连接字符串

　　string xlsPath = @"d:\Path\\FileName.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

　　需要注意的是：

　　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.如何引用表名？

　　若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如：

　　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$]）时，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，C 列开始，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开始的数据；以最后表最大范围内的非空单元结束；因此，如需要精确读取范围，应该使用命名区域 [NamedRange],或者指定地址：[Sheet1$A1:C10]

　　4.如何引用列名？

　　根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用F1、F2表示，其中序数，跟单元格的位置一致，从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/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="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文件有多少张sheet,而且每张sheet的结构可能都不一样等等。

　　其实我们可以通过获取Excel的"架构信息"来动态的构造查询语句。这里的"架构信息"与数据库领域的"数据库架构信息"意义相同（也称"元数据"），对于整个数据库，这些"元数据"通常包括数据库或可通过数据库中的数据源、表和视图得到的目录以及所存在的约束等；而对于数据库中的表，架构信息包括主键、列和自动编号字段等。

　　在上文中提到

　　在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为"表",而工作表被视为"系统表"）

　　这里我们将Excel也当作一个"数据库"来对待，然后利用OleDbConnection.GetOleDbSchemaTable 方法

　　要获取所需的架构信息，该方法获取的架构信息与ANSI SQl-92是兼容的：ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

　　以下是读取Excel文件内"表"定义元数据，并显示出来的的程序片断：

　　// 读取Excel数据，填充DataSet

　　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.GetOleDbSchemaTable（OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }）；// 获取数据源的表定义元数据

　　GetOleDbSchemaTable

　　tblNames = new List<string>（）；

　　foreach （DataRow row in tblSchema.Rows） {

　　tblNames.Add（（string）row["TABLE_NAME"]）； // 读取表名

　　}

　　da = new 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 == ConnectionState.Open） {

　　conn.Close（）；// 关闭连接

　　}

　　// 对导入DataSet的每张sheet进行处理 （省略）

　　这里我们就不需要对SELEC 语句进行"硬编码",可以根据需要动态的构造FROM 字句的"表名".

　　不仅可以，获取表名，还可以获取每张表内的字段名、字段类型等信息：

　　tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Columns, new object[] { null, null, null, null }）；

　　在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 方法

　　来获取数据源的架构信息。

　　Asp.net/c#+OleDb操作excel文件 [ BugEyes 发表于 2007-10-13 15:58:00 ]

　　推荐

　　1、环境配置：加入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

　　{//打开连接

　　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 工作簿时，默认情况下，区域中的第一行是标题行（或字段名称）。如果第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 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 [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 [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";

　　3）插入数据：string sql = "insert into [sheet1$A9:F15]（FieldName1,FieldName2,…） values（'a','b',…）";

　　4）删除数据：不提倡

　　注：1）代码根据需要可以自行修改；2）如果出现"操作必须使用一个可更新的查询"错误，可能sql语句中对excel文件中的"字段"引用有错误，或对excel文件不具有"修改"权限；3）如果出现"不能扩充选定范围"错误，可能是对excel文件引用的"范围"有错误。