ADO.NET 讀取EXCEL的實現代碼((c#))

// 連接字串

// 連接字串                     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中的資料進行操作                // 輸出，綁定資料         GridView1.DataSource = ds.Tables[0];          GridView1.DataBind();

很簡單吧？！一切就像操作資料庫一樣，只是需要注意的是：
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。如何參考資料表名？
對 Excel 活頁簿中表（或範圍）的有效引用。
若要引用完全使用的工作表的範圍，請指定後面跟有貨幣符號的工作表名稱。例如：

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$] ，Sheet1 就是工作表的名稱
• 工作表上的命名儲存格範圍：[MyNamedRange] （不需要指定工作表，因為整個xls中命名地區只能唯一）
XLS命名方法：選中儲存格範圍》插入》名稱》定義
• 工作表上的未命名儲存格範圍 ：[Sheet1$A1:B10]
（在關聯式資料庫提供的各種對象中（表、視圖、預存程序等），Excel 資料來源僅提供相當於表的對象，它由指定活頁簿中的工作表和定義的命名地區組成。命名地區被視為“表”，而工作表被視為“系統資料表”）

注意：
•必須使用[]（方括弧），否將報：
FROM 子句語法錯誤
•必須跟$（貨幣符號），否則報：
Microsoft Jet 資料庫引擎找不到對象'Sheet2′。請確定對象是否存在，並正確地寫出它的名稱和路徑。
•如果工作表名稱不對，或者不存在，將報：
‘Sheet2$' 不是一個有效名稱。請確認它不包含無效的字元或標點，且名稱不太長。
•在 如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 資料 中提到可以使用
~ 和 ‘(波浪線和單引號）代替[]，使用ADO。NET測試沒有成功，報：
FROM 子句語法錯誤
•當引用工作表明名（[Sheet1$]）時，資料提供者認為資料表從指定工作表上最左上方的非空儲存格開始。比如，工作表從第 3 行，C 列開始，第3行，C列之前以及第1、2行全為空白，則只會顯示從第3行，C列開始的資料；以最後表最大範圍內的非空單元結束；
•因此，如需要精確讀取範圍，應該使用命名地區 [NamedRange]，或者指定地址：[Sheet1$A1:C10]

4。如何引用列名？
•根據預設連接字串中，資料提供者會將有效地區內的第一行作為列名，如果此行某儲存格為空白則用F1、F2表示，其中序數，跟儲存格的位置一致，從1開始；
•如果希望第一行作為資料顯示，而非列名，可以在串連串的 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#中的轉義）

ref：
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm
中 《串連Excel》節（說明：在我自己的MSDN中，它的例子使用了兩個雙引號是錯的，測試沒有通過，原文這樣說的：

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

在這種情況下，所有的列名都是以F開頭，然後跟索引，從F1開始，F2，F3。。。。。。。

5。為什麼有效儲存格資料不顯示出來？
出現這種情況的可能原因是，預設串連中，資料提供者根據前面儲存格推斷後續單元個的資料類型。
可以通過 Extended Properties 中指定 IMEX=1

“IMEX=1;”通知驅動程式始終將“互混”資料列作為文本讀取
ref：同4

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

more ref：
如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 資料
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599

應
用程式經常需要與Excel進行資料互動，以上闡述了基於ADO.NET
讀取Excel的基本方法與技巧。現在要介紹是如何動態讀取Excel資料，這裡的動態指的是事先不知道Excel檔案的是什麼樣的結構，或者無法預
測，比如一張.xls檔案有多少張sheet，而且每張sheet的結構可能都不一樣等等。
其實我們可以通過擷取Excel的“架構資訊”來動態
的構造查詢語句。這裡的“架構資訊”與資料庫領域的“資料庫結構描述資訊”意義相同（也稱“中繼資料”），對於整個資料庫，這些“中繼資料”通常包括資料庫或可通
過資料庫中的資料來源、表和視圖得到的目錄以及所存在的約束等；而對於資料庫中的表，架構資訊包括主鍵、列和自動編號欄位等。
在上文中提到

在關聯式資料庫提供的各種對象中（表、視圖、預存程序等），Excel 資料來源僅提供相當於表的對象，它由指定活頁簿中的工作表和定義的命名地區組成。命名地區被視為“表”，而工作表被視為“系統資料表”）

這裡我們將Excel也當作一個“資料庫”來對待，然後利用OleDbConnection.GetOleDbSchemaTable 方法
要擷取所需的架構資訊，該方法擷取的架構資訊與ANSI SQl-92是相容的：

注
意：對於那些不熟悉 OLE DB 結構描述資料列集的人而言，它們基本上是由 ANSI SQL-92
定義的資料庫構造的標準化架構。每個結構描述資料列集具有為指定構造提供定義中繼資料的一組列（稱作 .NET
文檔中的“限制列”）。這樣，如果請求架構資訊（例如，列的架構資訊或定序的架構資訊），則您會明確知道可以得到哪種類型的資料。如果希望瞭解更多信
息，請訪問 Appendix B:Schema Rowsets。
ref：http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

以下是讀取Excel檔案內“表”定義中繼資料，並顯示出來的的程式片斷：

// 讀取Excel資料，填充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)，並且第一行作為資料返回，且以文本方式讀取                         "data source=" + xlsPath;         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.GetSchema("Tables");         tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });         GridView1.DataSource = tblSchema;         GridView1.DataBind();         // 關閉串連         conn.Close(); GetOleDbSchemaTable 方法的詳細說明可以參考： http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx 接著是一段利用“架構資訊”動態讀取Excel內部定義的表單或者命名地區的程式片斷：         // 讀取Excel資料，填充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)，並且第一行作為資料返回，且以文本方式讀取                         "data source=" + xlsPath;         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.GetSchema("Tables");         tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });         //GridView1.DataSource = tblSchema;         //GridView1.DataBind();         // 關閉串連         //conn.Close();         tblNames = new List<string>();         foreach (DataRow row in tblSchema.Rows) {             tblNames.Add((string)row["TABLE_NAME"]); // 讀取表名         }         // 初始化適配器         da = new OleDbDataAdapter();         // 準備資料，匯入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 {                 // 關閉串連                 if (conn.State == ConnectionState.Open) {                     conn.Close();                 }                 throw;             }         }         // 關閉串連         if (conn.State == ConnectionState.Open) {             conn.Close();         }         // 對匯入DataSet的每張sheet進行處理                 // 這裡僅做顯示         GridView1.DataSource = ds.Tables[0];         GridView1.DataBind();         GridView2.DataSource = ds.Tables[1];         GridView2.DataBind();

這裡我們就不需要對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 方法
來擷取資料來源的架構資訊。
http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx

//---------------------------------------------------------------------------
//IMEX 正確寫法
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +";Extended Properties='Excel 8.0;IMEX=1;'"; //HDR=NO;IMEX=1

using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Data.OleDb; public partial class _Default : System.Web.UI.Page  {     protected void Page_Load(object sender, EventArgs e)     {     }     protected void Button1_Click(object sender, EventArgs e)     {         string strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"D:/last.xls" + ";Extended Properties=Excel 8.0;"; //HDR=no";//IMEX=1         OleDbConnection conn = new OleDbConnection(strconn);         DataSet myset = new DataSet();         try         {             conn.Open();              string mysql = "select * from [Sheet1$] ";//where chs <> ''             OleDbDataAdapter aper = new OleDbDataAdapter(mysql, conn);             myset.Tables.Clear();             aper.Fill(myset, "book");             conn.Close();             GridView1.DataSource = myset.Tables["book"];             GridView1.DataBind();         }         catch (Exception ex)         {             conn.Close();             this.lb_msg.Text = ex.Message;             return;             // return ex.Message;         }     } }