Ado.Net讀取Excel常見(jiàn)問(wèn)題總結(jié)

1、ado.net讀取excel常見(jiàn)問(wèn)題總結(jié) 經(jīng)常需要在數(shù)據(jù)庫(kù)與execl之間互導(dǎo)數(shù)據(jù)。net時(shí)代，ado.net可以使用使用microsoft.jet.oledb訪問(wèn)訪問(wèn)excel，網(wǎng)上已經(jīng)有很多類似的資源，最典型也是最簡(jiǎn)單的可能如下：（環(huán)境） / 連接字符串 string xlspath = server.mappath(/app_data/somefile.xls); / 絕對(duì)物理路徑 string connstr = provider=microsoft.jet.oledb.4.0; + extended properties=excel 8.0; + data source= + xls

2、path; / 查詢語(yǔ)句 string sql = select * from sheet1$; dataset ds = new dataset(); oledbdataadapter da = new oledbdataadapter(sql, connstr); da.fill(ds); / 填充dataset / 在這里對(duì)dataset中的數(shù)據(jù)進(jìn)行操作 / 輸出，綁定數(shù)據(jù) gridview1.datasource = ds.tables0; gridview1.databind(); 很簡(jiǎn)單吧？！一切就像操作數(shù)據(jù)庫(kù)一樣，只是需要注意的是： 1。數(shù)據(jù)提供程序使用jet，同時(shí)需要指定ext

3、ended properties 關(guān)鍵字設(shè)置 excel 特定的屬性，不同版本的excel對(duì)應(yīng)不同的屬性值： 用于 extended properties 值的有效 excel 版本。 對(duì)于 microsoft excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，請(qǐng)使用 excel 8.0。 對(duì)于 microsoft excel 5.0 和 7.0 (95) 工作簿，請(qǐng)使用 excel 5.0。 對(duì)于 microsoft excel 4.0 工作簿，請(qǐng)使用 excel 4.0。 對(duì)于 microsoft excel 3.0 工作簿，請(qǐng)使用 excel 3.0

4、。 ref： . dataintodataset.asp 2。數(shù)據(jù)源路徑使用物理絕對(duì)路徑（同access） 3。如何引用表名？ 對(duì) excel 工作簿中表（或范圍）的有效引用。 若要引用完全使用的工作表的范圍，請(qǐng)指定后面跟有美元符號(hào)的工作表名稱。例如： select * from sheet1$ 若要引用工作表上的特定地址范圍，請(qǐng)指定后面跟有美元符號(hào)和該范圍的工作表名稱。例如： select * from sheet1$a1:b10 若要引用指定的范圍，請(qǐng)使用該范圍的名稱。例如： select * from mynamedrange ref： . dataintodataset.asp 說(shuō)明：

5、 可以引用excel 工作簿中的三種對(duì)象： 整張工作表：sheet1$ ，sheet1 就是工作表的名稱 工作表上的命名單元格區(qū)域：mynamedrange （不需要指定工作表，因?yàn)檎麄€(gè)xls中命名區(qū)域只能唯一） xls命名方法：選中單元格范圍插入名稱定義 工作表上的未命名單元格區(qū)域 ：sheet1$a1:b10 （在關(guān)系數(shù)據(jù)庫(kù)提供的各種對(duì)象中（表、視圖、存儲(chǔ)過(guò)程等），excel 數(shù)據(jù)源僅提供相當(dāng)于表的對(duì)象，它由指定工作簿中的工作表和定義的命名區(qū)域組成。命名區(qū)域被視為“表”，而工作表被視為“系統(tǒng)表”） 注意： 必須使用（方括號(hào)），否將報(bào)： from 子句語(yǔ)法錯(cuò)誤 必須跟$（美元符號(hào)），否則報(bào)：

6、 microsoft jet 數(shù)據(jù)庫(kù)引擎找不到對(duì)象sheet2。請(qǐng)確定對(duì)象是否存在，并正確地寫(xiě)出它的名稱和路徑。 如果工作表名稱不對(duì)，或者不存在，將報(bào)： sheet2$ 不是一個(gè)有效名稱。請(qǐng)確認(rèn)它不包含無(wú)效的字符或標(biāo)點(diǎn)，且名稱不太長(zhǎng)。 在 如何在 visual basic 或 vba 中使用 ado 來(lái)處理 excel 數(shù)據(jù) 中提到可以使用 和 (波浪線和單引號(hào)）代替，使用ado。net測(cè)試沒(méi)有成功，報(bào)： from 子句語(yǔ)法錯(cuò)誤 當(dāng)引用工作表明名（sheet1$）時(shí)，數(shù)據(jù)提供程序認(rèn)為數(shù)據(jù)表從指定工作表上最左上方的非空單元格開(kāi)始。比如，工作表從第 3 行，c 列開(kāi)始，第3行，c列之前以及第1、2

7、行全為空，則只會(huì)顯示從第3行，c列開(kāi)始的數(shù)據(jù)；以最后表最大范圍內(nèi)的非空單元結(jié)束； 因此，如需要精確讀取范圍，應(yīng)該使用命名區(qū)域 namedrange，或者指定地址：sheet1$a1:c10 4。如何引用列名？ 根據(jù)默認(rèn)連接字符串中，數(shù)據(jù)提供程序會(huì)將有效區(qū)域內(nèi)的第一行作為列名，如果此行某單元格為空則用f1、f2表示，其中序數(shù)，跟單元格的位置一致，從1開(kāi)始； 如果希望第一行作為數(shù)據(jù)顯示，而非列名，可以在連接串的 extended properties 屬性指定：hdr=no 默認(rèn)值為：hdr=no 格式如下： string connstr = provider=microsoft.jet.oled

8、b.4.0; + extended properties=excel 8.0;hdr=no; + data source= + xlspath; 注意： excel 8.0;hdr=no 需要使用雙引號(hào)（這里的反斜扛，是c#中的轉(zhuǎn)義） ref： ms-help:/ms.vscc.v80/ms.msdn.v80/ms.visualstudio.v80.chs/wd_adonet/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 中 連接excel節(jié)（說(shuō)明：在我自己的msdn中，它的例子使用了兩個(gè)雙引號(hào)是錯(cuò)的，測(cè)試沒(méi)有通過(guò)，原文這樣說(shuō)的： 注意，exten

9、ded properties 所需的雙引號(hào)必須還要加雙引號(hào)。 ） 在這種情況下，所有的列名都是以f開(kāi)頭，然后跟索引，從f1開(kāi)始，f2，f3。 5。為什么有效單元格數(shù)據(jù)不顯示出來(lái)？ 出現(xiàn)這種情況的可能原因是，默認(rèn)連接中，數(shù)據(jù)提供程序根據(jù)前面單元格推斷后續(xù)單元個(gè)的數(shù)據(jù)類型。 可以通過(guò) extended properties 中指定 imex=1 “imex=1;”通知驅(qū)動(dòng)程序始終將“互混”數(shù)據(jù)列作為文本讀取 ref：同4 ps：在baidu這個(gè)問(wèn)題的時(shí)候，有網(wǎng)友說(shuō)，將每個(gè)單元都加上引號(hào)，這固然是格方案，但是工作量何其大啊，又不零活，慶幸自己找到”治本藥方“ more ref： 如何在 visual

10、 basic 或 vba 中使用 ado 來(lái)處理 excel 數(shù)據(jù) http:/dotnet.aspx.cc/showdetail . 9-8ce0-d69b894a0599 注意： 在網(wǎng)上有很多同志說(shuō)是加上hdr或imex這兩個(gè)屬性值后會(huì)報(bào)“找不到可安裝的isam ”的錯(cuò)誤。這時(shí)解決方法很簡(jiǎn)單： 就是把連接串寫(xiě)成如下形式： provider=microsoft.jet.oledb.4.0;data source=0;extended properties=excel 8.0;hdr=1; imex=1; (注意紅色標(biāo)注的單引號(hào)) 而不要寫(xiě)成這樣的形式： provider=microsoft.

11、jet.oledb.4.0;data source=0;extended properties=excel 8.0;hdr=1; imex=1; 應(yīng)用程序經(jīng)常需要與excel進(jìn)行數(shù)據(jù)交互，在上一篇文章ado.net 如何讀取 excel （上）闡述了基于ado.net 讀取excel的基本方法與技巧。今天這里要介紹是如何動(dòng)態(tài)的讀取excel數(shù)據(jù)，這里的動(dòng)態(tài)指的是事先不知道excel文件的是什么樣的結(jié)構(gòu)，或者無(wú)法預(yù)測(cè)，比如一張.xls文件有多少?gòu)坰heet，而且每張sheet的結(jié)構(gòu)可能都不一樣等等。 其實(shí)我們可以通過(guò)獲取excel的“架構(gòu)信息”來(lái)動(dòng)態(tài)的構(gòu)造查詢語(yǔ)句。這里的“架構(gòu)信息”與數(shù)據(jù)庫(kù)領(lǐng)域

12、的“數(shù)據(jù)庫(kù)架構(gòu)信息”意義相同（也稱“元數(shù)據(jù)”），對(duì)于整個(gè)數(shù)據(jù)庫(kù)，這些“元數(shù)據(jù)”通常包括數(shù)據(jù)庫(kù)或可通過(guò)數(shù)據(jù)庫(kù)中的數(shù)據(jù)源、表和視圖得到的目錄以及所存在的約束等；而對(duì)于數(shù)據(jù)庫(kù)中的表，架構(gòu)信息包括主鍵、列和自動(dòng)編號(hào)字段等。 在ado.net 如何讀取 excel （上）提到 在關(guān)系數(shù)據(jù)庫(kù)提供的各種對(duì)象中（表、視圖、存儲(chǔ)過(guò)程等），excel 數(shù)據(jù)源僅提供相當(dāng)于表的對(duì)象，它由指定工作簿中的工作表和定義的命名區(qū)域組成。命名區(qū)域被視為“表”，而工作表被視為“系統(tǒng)表”） 這里我們將excel也當(dāng)作一個(gè)“數(shù)據(jù)庫(kù)”來(lái)對(duì)待，然后利用oledbconnection.getoledbschematable 方法 要獲取所

13、需的架構(gòu)信息，該方法獲取的架構(gòu)信息與ansi sql-92是兼容的： 注意：對(duì)于那些不熟悉 ole db 架構(gòu)行集的人而言，它們基本上是由 ansi sql-92 定義的數(shù)據(jù)庫(kù)構(gòu)造的標(biāo)準(zhǔn)化架構(gòu)。每個(gè)架構(gòu)行集具有為指定構(gòu)造提供定義元數(shù)據(jù)的一組列（稱作 .net 文檔中的“限制列”）。這樣，如果請(qǐng)求架構(gòu)信息（例如，列的架構(gòu)信息或排序規(guī)則的架構(gòu)信息），則您會(huì)明確知道可以得到哪種類型的數(shù)據(jù)。如果希望了解更多信息，請(qǐng)?jiān)L問(wèn) appendix b: schema rowsets。 ref： . anet2.mspx?mfr=true 以下是讀取excel文件內(nèi)“表”定義元數(shù)據(jù)，并顯示出來(lái)的的程序片斷： /

14、讀取excel數(shù)據(jù)，填充dataset / 連接字符串 string xlspath = server.mappath(/app_data/somefile.xls); string connstr = provider=microsoft.jet.oledb.4.0; + extended properties=excel 8.0;hdr=no;imex=1; + / 指定擴(kuò)展屬性為 microsoft excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作為數(shù)據(jù)返回，且以文本方式讀取 data source= + xlspath; string sql_f

15、 = select * from 0; oledbconnection conn = null; oledbdataadapter da = null; datatable tblschema = null; ilist tblnames = null; / 初始化連接，并打開(kāi) conn = new oledbconnection(connstr); conn.open(); / 獲取數(shù)據(jù)源的表定義元數(shù)據(jù) /tblschema = conn.getschema(tables); tblschema = conn.getoledbschematable(oledbschemaguid.table

16、s, new object null, null, null, table ); gridview1.datasource = tblschema; gridview1.databind(); / 關(guān)閉連接 conn.close(); getoledbschematable 方法的詳細(xì)說(shuō)明可以參考： . edbschematable.aspx 接著是一段利用“架構(gòu)信息”動(dòng)態(tài)讀取excel內(nèi)部定義的表單或者命名區(qū)域的程序片斷： / 讀取excel數(shù)據(jù)，填充dataset / 連接字符串 string xlspath = server.mappath(/app_data/somefile.xls)

17、; string connstr = provider=microsoft.jet.oledb.4.0; + extended properties=excel 8.0;hdr=no;imex=1; + / 指定擴(kuò)展屬性為 microsoft excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作為數(shù)據(jù)返回，且以文本方式讀取 data source= + xlspath; string sql_f = select * from 0; oledbconnection conn = null; oledbdataadapter da = null; datata

18、ble tblschema = null; ilist tblnames = null; / 初始化連接，并打開(kāi) conn = new oledbconnection(connstr); conn.open(); / 獲取數(shù)據(jù)源的表定義元數(shù)據(jù) /tblschema = conn.getschema(tables); tblschema = conn.getoledbschematable(oledbschemaguid.tables, new object null, null, null, table ); /gridview1.datasource = tblschema; /gridvi

19、ew1.databind(); / 關(guān)閉連接 /conn.close(); tblnames = new list(); foreach (datarow row in tblschema.rows) tblnames.add(string)rowtable_name); / 讀取表名 / 初始化適配器 da = new oledbdataadapter(); / 準(zhǔn)備數(shù)據(jù)，導(dǎo)入dataset dataset ds = new dataset(); foreach (string tblname in tblnames) da.selectcommand = new oledbcommand(string.format(sql_f, tblname), conn); try da.fill(ds, tblname); catch / 關(guān)閉連接 if (conn.state = connectionstate.open) conn.close(); throw; / 關(guān)閉連接 if (conn.state = connectionstate.open) conn.close();