ADO.NET操作Excel的限制：

1.不能設(shè)置單元格格式，插入新行會(huì)自動(dòng)繼承上一行的格式 .

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

?

3.Jet OLE DB 提供程序無(wú)法為 Excel 工作簿中的表提供主鍵/索引信息。因此，您不能使用 CommandBuilder 在 Excel 工作簿中自動(dòng)更新和插入記錄。

// 連接字符串

string xlsPath = @"d:Path\FileName.XLS"; // 絕對(duì)物理路徑

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" + "data source=" + xlsPath;

// 查詢(xún)語(yǔ)句

string sql = "SELECT * FROM [Sheet1$]";

DataSet ds = new DataSet（）；

OleDbDataAdapter da = new OleDbDataAdapter（sql, connStr）；

da.Fill（ds）； // 填充DataSet

需要注意的是：

1.數(shù)據(jù)提供程序使用Jet,同時(shí)需要指定Extended 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.

ref:http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp

2.數(shù)據(jù)源路徑使用物理絕對(duì)路徑（同Access）

3.如何引用表名？

若要引用完全使用的工作表的范圍，請(qǐng)指定后面跟有美元符號(hào)的工作表名稱(chēng)。例如：

select * from [Sheet1$]

若要引用工作表上的特定地址范圍，請(qǐng)指定后面跟有美元符號(hào)和該范圍的工作表名稱(chēng)。例如：

select * from [Sheet1$A1:B10]

若要引用指定的范圍，請(qǐng)使用該范圍的名稱(chēng)。例如：

select * from [MyNamedRange]

ref:http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp

（在關(guān)系數(shù)據(jù)庫(kù)提供的各種對(duì)象中（表、視圖、存儲(chǔ)過(guò)程等），Excel 數(shù)據(jù)源僅提供相當(dāng)于表的對(duì)象，它由指定工作簿中的工作表和定義的命名區(qū)域組成。命名區(qū)域被視為"表",而工作表被視為"系統(tǒng)表"）

當(dāng)引用工作表名（[Sheet1$]）時(shí)，數(shù)據(jù)提供程序認(rèn)為數(shù)據(jù)表從指定工作表上最左上方的非空單元格開(kāi)始。比如，工作表從第 3 行，C 列開(kāi)始，第3行，C列之前以及第1、2行全為空，則只會(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

格式如下：

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Extended Properties="Excel 8.0;HDR=NO";" + "data source=" + xlsPath;

在這種情況下，所有的列名都是以F開(kāi)頭，然后跟索引，從F1開(kāi)始，F(xiàn)2,F3……

注意： 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

5.為什么有效單元格數(shù)據(jù)不顯示出來(lái)？

出現(xiàn)這種情況的可能原因是，默認(rèn)連接中，數(shù)據(jù)提供程序根據(jù)前面單元格推斷后續(xù)單元個(gè)的數(shù)據(jù)類(lèi)型。

可以通過(guò) Extended Properties 中指定 IMEX=1

"IMEX=1;"通知驅(qū)動(dòng)程序始終將"互混"數(shù)據(jù)列作為文本讀取

格式如下：

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Extended Properties="Excel 8.0;HDR=NO;IMEX=1";" + "data source=" + xlsPath;

ref:同4

PS:在baidu這個(gè)問(wèn)題的時(shí)候，有網(wǎng)友說(shuō)，將每個(gè)單元都加上引號(hào)，這固然是格方案，但是工作量何其大啊，又不零活，慶幸自己找到"治本藥方：

more ref:

如何在 Visual Basic 或 VBA 中使用 ADO 來(lái)處理 Excel 數(shù)據(jù)

應(yīng)用程序經(jī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)，或者無(wú)法預(yù)測(cè)，比如一張。xls文件有多少?gòu)坰heet,而且每張sheet的結(jié)構(gòu)可能都不一樣等等。

其實(shí)我們可以通過(guò)獲取Excel的"架構(gòu)信息"來(lái)動(dòng)態(tài)的構(gòu)造查詢(xún)語(yǔ)句。這里的"架構(gòu)信息"與數(shù)據(jù)庫(kù)領(lǐng)域的"數(shù)據(jù)庫(kù)架構(gòu)信息"意義相同（也稱(chēng)"元數(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)字段等。

在上文中提到

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

要獲取所需的架構(gòu)信息，該方法獲取的架構(gòu)信息與ANSI SQl-92是兼容的：ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

以下是讀取Excel文件內(nèi)"表"定義元數(shù)據(jù)，并顯示出來(lái)的的程序片斷：

// 讀取Excel數(shù)據(jù)，填充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（）； // 打開(kāi)

tblSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }）；// 獲取數(shù)據(jù)源的表定義元數(shù)據(jù)

GetOleDbSchemaTable

tblNames = new List<string>（）；

foreach （DataRow row in tblSchema.Rows） {

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

}

da = new OleDbDataAdapter（）；// 初始化適配器

DataSet ds = new DataSet（）； // 準(zhǔn)備數(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 == ConnectionState.Open） {

conn.Close（）；// 關(guān)閉連接

}

// 對(duì)導(dǎo)入DataSet的每張sheet進(jìn)行處理 （省略）

這里我們就不需要對(duì)SELEC 語(yǔ)句進(jìn)行"硬編碼",可以根據(jù)需要?jiǎng)討B(tài)的構(gòu)造FROM 字句的"表名".

不僅可以，獲取表名，還可以獲取每張表內(nèi)的字段名、字段類(lèi)型等信息：

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

在ADO.nET 1.x 時(shí)候只有OleDb提供了GetOleDbSchemaTable 方法，而SqlClient或者OrcaleClient沒(méi)有對(duì)應(yīng)的方法，因?yàn)閷?duì)應(yīng)數(shù)據(jù)庫(kù)已經(jīng)提供了類(lèi)似功能的存儲(chǔ)過(guò)程或者系統(tǒng)表供應(yīng)用程序訪問(wèn)，比如對(duì)于Sql Server:

SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'Customers'

而在ADO.NET 2.0中每個(gè)xxxConnenction都實(shí)現(xiàn)了基類(lèi)System.Data.Common.DbConnection的 GetSchemal 方法

來(lái)獲取數(shù)據(jù)源的架構(gòu)信息。

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

推薦

1、環(huán)境配置：加入using System.OleDb

2、編寫(xiě)連接與操作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

{//打開(kāi)連接

conn.Open（）；

}

catch （Exception e）

{

Response.Write（e.ToString（））；

}

OleDbCommand olecommand = new OleDbCommand（sql, conn）；

try

{//執(zhí)行語(yǔ)句

olecommand.ExecuteNonQuery（）；

}

catch （Exception eee）

{

Response.Write（eee.ToString（））；

conn.Close（）；

}

finally

{

conn.Close（）；//關(guān)閉數(shù)據(jù)庫(kù)

}

conn.Close（）；

}

注：1）使用 Excel 工作簿時(shí)，默認(rèn)情況下，區(qū)域中的第一行是標(biāo)題行（或字段名稱(chēng)）。如果第一個(gè)區(qū)域不包含標(biāo)題，您可以在連接字符串的擴(kuò)展屬性中指定 HDR=NO.如果您在連接字符串中指定 HDR=NO,Jet OLE DB 提供程序?qū)⒆詣?dòng)為您命名字段（F1 表示第一個(gè)字段，F(xiàn)2 表示第二個(gè)字段，依此類(lèi)推）；2）IMEX=1將所有讀入數(shù)據(jù)看作字符，其他值（0、2）請(qǐng)查閱相關(guān)幫助文檔；3）如果出現(xiàn)"找不到可安裝的isam"錯(cuò)誤，一般是連接字符串錯(cuò)誤

3、從excel文件讀取數(shù)據(jù)

string sql = "select * from [sheet1$]";

DoOleSql（sql,"test.xls"）；

4、更新excel文件中的數(shù)據(jù)

string sql = "update [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、對(duì)于非標(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 [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";

3）插入數(shù)據(jù)：string sql = "insert into [sheet1$A9:F15]（FieldName1,FieldName2,…） values（'a','b',…）";

4）刪除數(shù)據(jù)：不提倡

注：1）代碼根據(jù)需要可以自行修改；2）如果出現(xiàn)"操作必須使用一個(gè)可更新的查詢(xún)"錯(cuò)誤，可能sql語(yǔ)句中對(duì)excel文件中的"字段"引用有錯(cuò)誤，或?qū)xcel文件不具有"修改"權(quán)限；3）如果出現(xiàn)"不能擴(kuò)充選定范圍"錯(cuò)誤，可能是對(duì)excel文件引用的"范圍"有錯(cuò)誤。

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄