ADO.NET操作Excel的限制

 ADO.NET操作Excel的限制：

　　1.不能設置單元格格式，插入新行會自動繼承上一行的格式 .

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

　　?

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

　　// 連接字符串

　　string xlsPath = @"d:PathFileName.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

　　需要注意的是：

　　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.如何引用表名？

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

　　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$]）時，數據提供程序認為數據表從指定工作表上最左上方的非空單元格開始。比如，工作表從第 3 行，C 列開始，第3行，C列之前以及第1、2行全為空，則只會顯示從第3行，C列開始的數據；以最後表最大范圍內的非空單元結束；因此，如需要精確讀取范圍，應該使用命名區域 [NamedRange],或者指定地址：[Sheet1$A1:C10]

　　4.如何引用列名？

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

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

　　格式如下：

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

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

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

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

　　5.為什麼有效單元格數據不顯示出來？

　　出現這種情況的可能原因是，默認連接中，數據提供程序根據前面單元格推斷後續單元個的數據類型。

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

　　"IMEX=1;"通知驅動程序始終將"互混"數據列作為文本讀取

　　格式如下：

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

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

　　ref:同4

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

　　more ref:

　　如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 數據

　　應用程序經常需要與Excel進行數據交互，以上闡述了基於ADO.NET 讀取Excel的基本方法與技巧。現在要介紹是如何動態的讀取Excel數據，這裡的動態指的是事先不知道Excel文件的是什麼樣的結構，或者無法預測，比如一張。xls文件有多少張sheet,而且每張sheet的結構可能都不一樣等等。

　　其實我們可以通過獲取Excel的"架構信息"來動態的構造查詢語句。這裡的"架構信息"與數據庫領域的"數據庫架構信息"意義相同（也稱"元數據"），對於整個數據庫，這些"元數據"通常包括數據庫或可通過數據庫中的數據源、表和視圖得到的目錄以及所存在的約束等；而對於數據庫中的表，架構信息包括主鍵、列和自動編號字段等。

　　在上文中提到

　　在關系數據庫提供的各種對象中（表、視圖、存儲過程等），Excel 數據源僅提供相當於表的對象，它由指定工作簿中的工作表和定義的命名區域組成。命名區域被視為"表",而工作表被視為"系統表"）

　　這裡我們將Excel也當作一個"數據庫"來對待，然後利用OleDbConnection.GetOleDbSchemaTable 方法

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

　　以下是讀取Excel文件內"表"定義元數據，並顯示出來的的程序片斷：

　　// 讀取Excel數據，填充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（）； // 打開

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

　　GetOleDbSchemaTable

　　tblNames = new List<string>（）；

　　foreach （DataRow row in tblSchema.Rows） {

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

　　}

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

　　DataSet ds = new DataSet（）； // 准備數據，導入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進行處理 （省略）

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

　　來獲取數據源的架構信息。

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

　　推薦

　　1、環境配置：加入using System.OleDb

　　2、編寫連接與操作excel文件的通用函數

　　protected void DoOleSql（string sql, string database）

　　{

　　OleDbConnection conn = new OleDbConnection（）；

　　conn.ConnectionString = "Provider=Microso