Ado.Net讀取Excel常見問題總結

經常需要在數據庫與Execl之間互導數據。net時代，ADO.NET可以使用使用Microsoft.Jet.OleDb訪問訪問Excel，網上已經有很多類似的資源，最典型也是最簡單的可能如下：（ASP.Net環境）

 // 連接字符串            
        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

注意：
在網上有很多同志說是加上HDR或IMEX這兩個屬性值後會報“找不到可安裝的ISAM ”的錯誤。這時解決方法很簡單：
就是把連接串寫成如下形式：

Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended PropertIEs=''Excel 8.0;HDR=1; IMEX=1;'' (注意紅色標注的單引號)

而不要寫成這樣的形式：

Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended PropertIEs=Excel 8.0;HDR=1; IMEX=1;

應用程序經常需要與Excel進行數據交互，在上一篇文章ADO.Net 如何讀取 Excel （上）闡述了基於ADO.Net 讀取Excel的基本方法與技巧。今天這裡要介紹是如何動態的讀取Excel數據，這裡的動態指的是事先不知道Excel文件的是什麼樣的結構，或者無法預測，比如一張.xls文件有多少張sheet，而且每張sheet的結構可能都不一樣等等。
其實我們可以通過獲取Excel的“架構信息”來動態的構造查詢語句。這裡的“架構信息”與數據庫領域的“數據庫架構信息”意義相同（也稱“元數據”），對於整個數據庫，這些“元數據”通常包括數據庫或可通過數據庫中的數據源、表和視圖得到的目錄以及所存在的約束等；而對於數據庫中的表，架構信息包括主鍵、列和自動編號字段等。
在ADO.Net 如何讀取 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();

        // more codes
        // 

這裡我們就不需要對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