ADO.NET操作Excel的限制__1

1、ADO.NET操作Excel的限制_ ADO.NET操作Excel的限制： 1.不能設(shè)置單元格格式，插入新行會自動(dòng)繼承上一行的格式 . 2.您不能用法 ADO.NET 在單元格中插入公式。 3.Jet OLE DB 供應(yīng)程序無法為 Excel 工作簿中的表供應(yīng)主鍵/索引信息。因此，您不能用法 CommandBuilder 在 Excel 工作簿中自動(dòng)更新和插入記錄。 / 連接字符串 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.數(shù)據(jù)供應(yīng)程序用法Jet,同時(shí)需要指定Extended Properties 關(guān)鍵字設(shè)置 Excel 特定的屬性，不同版本的Excel對應(yīng)不同的屬性值：用于 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.數(shù)據(jù)源路徑用法物理

4、肯定路徑（同Access） 3.如何引用表名？ 若要引用完全用法的工作表的范圍，請指定后面跟有美元符號的工作表名稱。例如： select * from Sheet1$ 若要引用工作表上的特定地址范圍，請指定后面跟有美元符號和該范圍的工作表名稱。例如： select * from Sheet1$A1:B10 若要引用指定的范圍，請用法該范圍的名稱。例如： select * from MyNamedRange ref:l/vbtskcodeexamplereadingexceldataintodataset.asp （在關(guān)系數(shù)據(jù)庫供應(yīng)的各種對象中（表、視圖、存儲過程等），Excel 數(shù)據(jù)源僅供應(yīng)相

5、當(dāng)于表的對象，它由指定工作簿中的工作表和定義的命名區(qū)域組成。命名區(qū)域被視為表,而工作表被視為系統(tǒng)表） 當(dāng)引用工作表名（Sheet1$）時(shí)，數(shù)據(jù)供應(yīng)程序認(rèn)為數(shù)據(jù)表從指定工作表上最左上方的非空單元格開頭。比如，工作表從第 3 行，C 列開頭，第3行，C列之前以及第1、2行全為空，則只會顯示從第3行，C列開頭的數(shù)據(jù)；以最終表最大范圍內(nèi)的非空單元結(jié)束；因此，如需要精確讀取范圍，應(yīng)當(dāng)用法命名區(qū)域 NamedRange,或者指定地址：Sheet1$A1:C10 4.如何引用列名？ 依據(jù)默認(rèn)連接字符串中，數(shù)據(jù)供應(yīng)程序會將有效區(qū)域內(nèi)的第一行作為列名，假如此行某單元格為空則用F1、F2表示，其中序數(shù)，跟單元格的

6、位置全都，從1開頭； 假如盼望第一行作為數(shù)據(jù)顯示，而非列名，可以在連接串的 Extended Properties 屬性指定：HDR=NO 格式如下： string connStr = Provider=Microsoft.Jet.OLEDB.4.0; + Extended Properties=Excel 8.0;HDR=NO; + data source= + xlsPath; 在這種狀況下，全部的列名都是以F開頭，然后跟索引，從F1開頭，F(xiàn)2,F3 留意： Excel 8.0;HDR=NO 需要用法雙引號（這里的反斜扛，是C#中的轉(zhuǎn)義） ref:ms-help:/MS.VSCC.v80/

7、MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 5.為什么有效單元格數(shù)據(jù)不顯示出來？ 消失這種狀況的可能緣由是，默認(rèn)連接中，數(shù)據(jù)供應(yīng)程序依據(jù)前面單元格推斷后續(xù)單元個(gè)的數(shù)據(jù)類型。 可以通過 Extended Properties 中指定 IMEX=1 IMEX=1;通知驅(qū)動(dòng)程序始終將互混數(shù)據(jù)列作為文本讀取 格式如下： 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這個(gè)問題的時(shí)候，有網(wǎng)友說，將每個(gè)單元都加上引號，這當(dāng)然是格方案，但是工作量何其大啊，又不零活，慶幸自己找到治本藥方： more ref: 如何在 Visual Basic 或 VBA 中用法 ADO 來處理 Excel 數(shù)據(jù) 應(yīng)用程序常常需要與Excel進(jìn)行數(shù)據(jù)交互，以上闡述了基于ADO.NET 讀取Excel的基本方法與技巧?，F(xiàn)在要介紹是如何動(dòng)態(tài)的讀取Excel數(shù)據(jù)，這里的動(dòng)態(tài)指的是事先不知道Excel文件的是什么樣的結(jié)構(gòu)，或者無法預(yù)報(bào)，比如一張。xls文件有

9、多少張sheet,而且每張sheet的結(jié)構(gòu)可能都不一樣等等。 其實(shí)我們可以通過獵取Excel的架構(gòu)信息來動(dòng)態(tài)的構(gòu)造查詢語句。這里的架構(gòu)信息與數(shù)據(jù)庫領(lǐng)域的數(shù)據(jù)庫架構(gòu)信息意義相同（也稱元數(shù)據(jù)），對于整個(gè)數(shù)據(jù)庫，這些元數(shù)據(jù)通常包括數(shù)據(jù)庫或可通過數(shù)據(jù)庫中的數(shù)據(jù)源、表和視圖得到的名目以及所存在的約束等；而對于數(shù)據(jù)庫中的表，架構(gòu)信息包括主鍵、列和自動(dòng)編號字段等。 在上文中提到 在關(guān)系數(shù)據(jù)庫供應(yīng)的各種對象中（表、視圖、存儲過程等），Excel 數(shù)據(jù)源僅供應(yīng)相當(dāng)于表的對象，它由指定工作簿中的工作表和定義的命名區(qū)域組成。命名區(qū)域被視為表,而工作表被視為系統(tǒng)表） 這里我們將Excel也當(dāng)作一個(gè)數(shù)據(jù)庫來對待，然后利

10、用OleDbConnection.GetOleDbSchemaTable 方法 要獵取所需的架構(gòu)信息，該方法獵取的架構(gòu)信息與ANSI SQl-92是兼容的：ref: 以下是讀取Excel文件內(nèi)表定義元數(shù)據(jù)，并顯示出來的的程序片斷： / 讀取Excel數(shù)據(jù)，填充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 ）；/ 獵取數(shù)據(jù)源的表定義元數(shù)據(jù) GetOleDbSchemaTable tblNames = new Liststring（）； foreach （DataRow row in tblSchema.Rows） tblNames.Add（string）rowTABLE_NAME）； / 讀取表名 da = new

12、OleDbDataAdapter（）；/ 初始化適配器 DataSet ds = new DataSet（）； / 預(yù)備數(shù)據(jù)，導(dǎo)入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（）；/ 關(guān)閉連接 throw; if （conn.State = C

13、onnectionState.Open） conn.Close（）；/ 關(guān)閉連接 / 對導(dǎo)入DataSet的每張sheet進(jìn)行處理 （省略） 這里我們就不需要對SELEC 語句進(jìn)行硬編碼,可以依據(jù)需要?jiǎng)討B(tài)的構(gòu)造FROM 字句的表名. 不僅可以，獵取表名，還可以獵取每張表內(nèi)的字段名、字段類型等信息： tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Columns, new object null, null, null, null ）； 在ADO.nET 1.x 時(shí)候只有OleDb供應(yīng)了GetOleDbSchemaTable 方法，而Sq

14、lClient或者OrcaleClient沒有對應(yīng)的方法，由于對應(yīng)數(shù)據(jù)庫已經(jīng)供應(yīng)了類似功能的存儲過程或者系統(tǒng)表供應(yīng)用程序訪問，比如對于Sql Server: SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = NCustomers 而在ADO.NET 2.0中每個(gè)xxxConnenction都實(shí)現(xiàn)了基類System.Data.Common.DbConnection的 GetSchemal 方法 來獵取數(shù)據(jù)源的架構(gòu)信息。 A BugEyes 發(fā)表于 2021-10-13 15:58:00 推舉 1、環(huán)境配置：

15、加入using System.OleDb 2、編寫連接與操作excel文件的通用函數(shù) 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 /執(zhí)行語句 olecommand.ExecuteNonQuery（）； catch （Exception eee） Response.Write（eee.ToString（）； conn.Close（）； finally conn.Close（）；/關(guān)閉數(shù)據(jù)庫 conn.Close（）； 注：1）用法 Excel 工作簿時(shí)，默認(rèn)狀況下，區(qū)域中的第一行是標(biāo)

17、題行（或字段名稱）。假如第一個(gè)區(qū)域不包含標(biāo)題，您可以在連接字符串的擴(kuò)展屬性中指定 HDR=NO.假如您在連接字符串中指定 HDR=NO,Jet OLE DB 供應(yīng)程序?qū)⒆詣?dòng)為您命名字段（F1 表示第一個(gè)字段，F(xiàn)2 表示其次個(gè)字段，依此類推）；2）IMEX=1將全部讀入數(shù)據(jù)看作字符，其他值（0、2）請查閱相關(guān)關(guān)心文檔；3）假如消失找不到可安裝的isam錯(cuò)誤，一般是連接字符串錯(cuò)誤 3、從excel文件讀取數(shù)據(jù) string sql = select * from sheet1$; DoOleSql（sql,test.xls）； 4、更新excel文件中的數(shù)據(jù) string sql = update

18、 sheet1$ set FieldName1=333 where FieldName2=b3; DoOleSql（sql,test.xls）； 5、向excel文件插入數(shù)據(jù) string sql = insert into sheet1$（FieldName1,FieldName2,） values（a,b,）; DoOleSql（sql,test.xls）； 6、刪除excel文件中的數(shù)據(jù)：不提倡用法這種方法 7、對于非標(biāo)準(zhǔn)結(jié)構(gòu)的excel表格，可以指定excel中sheet的范圍 1）讀取數(shù)據(jù)：string sql = select * from sheet1$A3:F20; 2）更新數(shù)據(jù)：string sql = update sheet