經(jīng)常需要在數(shù)據(jù)庫與Execl之間互導數(shù)據(jù)。net時代，ADO.NET可以使用使用Microsoft.Jet.OleDb訪問訪問Excel，網(wǎng)上已經(jīng)有很多類似的資源，最典型也是最簡單的可能如下：(asp.net環(huán)境)

// 連接字符串

string xlsPath = Server.MapPath("~/app_data/somefile.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

// 在這里對DataSet中的數(shù)據(jù)進行操作

// 輸出，綁定數(shù)據(jù)

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

很簡單吧?!一切就像操作數(shù)據(jù)庫一樣，只是需要注意的是：

1。數(shù)據(jù)提供程序使用Jet，同時需要指定Extended Properties 關(guān)鍵字設(shè)置 Excel 特定的屬性，不同版本的Excel對應(yīng)不同的屬性值：用于 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：

2。數(shù)據(jù)源路徑使用物理絕對路徑(同access)

3。如何引用表名?

對 Excel 工作簿中表(或范圍)的有效引用。

若要引用完全使用的工作表的范圍，請指定后面跟有美元符號的工作表名稱。例如：

select * from [Sheet1$]

若要引用工作表上的特定地址范圍，請指定后面跟有美元符號和該范圍的工作表名稱。例如：

select * from [Sheet1$A1:B10]

若要引用指定的范圍，請使用該范圍的名稱。例如：

select * from [MyNamedRange]

ref： 可以引用Excel 工作簿中的三種對象：

* 整張工作表：[Sheet1$] ，Sheet1 就是工作表的名稱

* 工作表上的命名單元格區(qū)域：[MyNamedRange] (不需要指定工作表，因為整個xls中命名區(qū)域只能唯一)

XLS命名方法：選中單元格范圍》插入》名稱》定義

* 工作表上的未命名單元格區(qū)域 ：[Sheet1$A1:B10]

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

注意：

*必須使用[](方括號)，否將報：

FROM 子句語法錯誤

*必須跟$(美元符號)，否則報：

Microsoft Jet 數(shù)據(jù)庫引擎找不到對象'Sheet2'。請確定對象是否存在，并正確地寫出它的名稱和路徑。

*如果工作表名稱不對，或者不存在，將報：

'Sheet2$' 不是一個有效名稱。請確認它不包含無效的字符或標點，且名稱不太長。

*在 如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 數(shù)據(jù) 中提到可以使用

~ 和 '(波浪線和單引號)代替[]，使用ADO。NET測試沒有成功，報：

FROM 子句語法錯誤

*當引用工作表明名([Sheet1$])時，數(shù)據(jù)提供程序認為數(shù)據(jù)表從指定工作表上最左上方的非空單元格開始。比如，工作表從第 3 行，C 列開始，第3行，C列之前以及第1、2行全為空，則只會顯示從第3行，C列開始的數(shù)據(jù);以最后表最大范圍內(nèi)的非空單元結(jié)束;

*因此，如需要精確讀取范圍，應(yīng)該使用命名區(qū)域 [NamedRange]，或者指定地址：[Sheet1$A1:C10]

4。如何引用列名?

*根據(jù)默認連接字符串中，數(shù)據(jù)提供程序會將有效區(qū)域內(nèi)的第一行作為列名，如果此行某單元格為空則用F1、F2表示，其中序數(shù)，跟單元格的位置一致，從1開始;

*如果希望第一行作為數(shù)據(jù)顯示，而非列名，可以在連接串的 Extended Properties 屬性指定：HDR=NO

默認值為：HDR=NO 格式如下：

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

"Extended Properties=\"Excel 8.0;HDR=NO\";" +

"data source=" + xlsPath;

注意： Excel 8.0;HDR=NO 需要使用雙引號(這里的反斜扛，是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é)(說明：在我自己的MSDN中，它的例子使用了兩個雙引號是錯的，測試沒有通過，原文這樣說的：

注意，Extended Properties 所需的雙引號必須還要加雙引號。

)

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

5。為什么有效單元格數(shù)據(jù)不顯示出來?

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

可以通過 Extended Properties 中指定 IMEX=1

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

ref：同4

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

more ref：

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

應(yīng)用程序經(jīng)常需要與Excel進行數(shù)據(jù)交互，以上闡述了基于ADO.NET 讀取Excel的基本方法與技巧?，F(xiàn)在要介紹是如何動態(tài)的讀取Excel數(shù)據(jù)，這里的動態(tài)指的是事先不知道Excel文件的是什么樣的結(jié)構(gòu)，或者無法預(yù)測，比如一張.xls文件有多少張sheet，而且每張sheet的結(jié)構(gòu)可能都不一樣等等。

其實我們可以通過獲取Excel的“架構(gòu)信息”來動態(tài)的構(gòu)造查詢語句。這里的“架構(gòu)信息”與數(shù)據(jù)庫領(lǐng)域的“數(shù)據(jù)庫架構(gòu)信息”意義相同(也稱“元數(shù)據(jù)”)，對于整個數(shù)據(jù)庫，這些“元數(shù)據(jù)”通常包括數(shù)據(jù)庫或可通過數(shù)據(jù)庫中的數(shù)據(jù)源、表和視圖得到的目錄以及所存在的約束等;而對于數(shù)據(jù)庫中的表，架構(gòu)信息包括主鍵、列和自動編號字段等。

在上文中提到

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

這里我們將Excel也當作一個“數(shù)據(jù)庫”來對待，然后利用OleDbConnection.GetOleDbSchemaTable 方法

要獲取所需的架構(gòu)信息，該方法獲取的架構(gòu)信息與ANSI SQl-92是兼容的：

注意：對于那些不熟悉 OLE DB 架構(gòu)行集的人而言，它們基本上是由 ANSI SQL-92 定義的數(shù)據(jù)庫構(gòu)造的標準化架構(gòu)。每個架構(gòu)行集具有為指定構(gòu)造提供定義元數(shù)據(jù)的一組列(稱作 .NET 文檔中的“限制列”)。這樣，如果請求架構(gòu)信息(例如，列的架構(gòu)信息或排序規(guī)則的架構(gòu)信息)，則您會明確知道可以得到哪種類型的數(shù)據(jù)。如果希望了解更多信息，請訪問 Appendix B:Schema Rowsets。

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

// 讀取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\";" + // 指定擴展屬性為 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;

DataTable tblSchema = null;

IList tblNames = null;

// 初始化連接，并打開

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;

GridView1.DataBind();

// 關(guān)閉連接

conn.Close();

GetOleDbSchemaTable 方法的詳細說明可以參考：

接著是一段利用“架構(gòu)信息”動態(tài)讀取Excel內(nèi)部定義的表單或者命名區(qū)域的程序片斷：

// 讀取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\";" + // 指定擴展屬性為 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;

DataTable tblSchema = null;

IList tblNames = null;

// 初始化連接，并打開

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;

//GridView1.DataBind();

// 關(guān)閉連接

//conn.Close();

tblNames = new List();

foreach (DataRow row in tblSchema.Rows) {

tblNames.Add((string)row["TABLE_NAME"]); // 讀取表名

}

// 初始化適配器

da = new OleDbDataAdapter();

// 準備數(shù)據(jù)，導入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();

}

// 對導入DataSet的每張sheet進行處理

// 這里僅做顯示

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

GridView2.DataSource = ds.Tables[1];

GridView2.DataBind();

// more codes

// .

這里我們就不需要對SELEC 語句進行“硬編碼”，可以根據(jù)需要動態(tài)的構(gòu)造FROM 字句的“表名”。

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

tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null });

在ADO.nET 1.x 時候只有OleDb提供了GetOleDbSchemaTable 方法，而SqlClient或者OrcaleClient沒有對應(yīng)的方法，因為對應(yīng)數(shù)據(jù)庫已經(jīng)提供了類似功能的存儲過程或者系統(tǒng)表供應(yīng)用程序訪問，比如對于Sql Server：

SELECT *

FROM Northwind.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'Customers'

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

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

更多信息請查看IT技術(shù)專欄