徹底解決Oledb連接配接Excel資料類型不統一的問題

轉自：http://blog.csdn.net/fupei/article/details/5886058

在使用Microsoft.Jet.OLEDB.4.0連接配接Excel，進行讀取資料，相對使用傳統的COM來讀取資料，效率是很高的。但相對傳統COM操作Excel來說，及存在資料類型轉換的問題。

因為使用OLEDB連接配接Excel讀取資料時，需要确定資料的類型。預設情況使用連接配接字元串：

view source print ?

1.

string

connStr = 

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

+ excelFile + 

";Extended Properties='Excel 8.0;'"

;

 使用上面的連接配接字元串連接配接Excel時，可能會遇到資料類型不一緻的問題。所謂資料類型不一緻，是指同一列裡面資料類型可能出現多種，如浮點數、字元串、日期等；當出現此類情況時，讀取出來的資料就為空，甚至會報錯，如“非法的日期格式”等異常。出現這種問題，我們大家都會想到把資料全部都按字元資料來讀取，但是按什麼資料類型來讀取不是我們能控制的，是OLEDB控制的，至少暫時我還沒有找到能控制輸出資料類型的方法。因為我當初也嘗試使用convert，cast函數對輸出的列進行類型轉換，但oledb連接配接Excel時，使用的SQL不支援這些函數。是以隻能從其他角度來解決該問題。我也在網上搜尋了很多解決方法，最全面的解決方法是：http://www.douban.com/note/18510346/。下面列出了網上出現解決該問題方法的比較：

解決方案	說明	缺點
COM	使用Excel COM接口通路Excel	非托管、不容易釋放資源、效率低下
連接配接字元串添加IMEX＝1	構造的連接配接字元串，如： view source print ? 1. string strConn =  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile +  ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" ; 其中HDR表示是否将Sheet頁的第一行作為字段名，“YES”代表是，“NO”代表不是，當為YES時，将把SHEET頁的第一行作為字段名，資料從第二行開始，而如果是NO時，字段名就是要SHEET的列名，如A,B,C等，資料就從第一行開始取；IMEX是用來告訴驅動程式，使用Excel檔案的模式，其值有0、1、2三種，分别代表導出、導入、混合模式。當我們設定IMEX＝1時将強制混合資料轉換為文本，但僅僅這種設定并不可靠，IMEX＝1隻確定在某列前8行資料至少有一個是文本項的時候才起作用，它隻是把查找前8行資料中資料類型占優選擇的行為作了略微的改變。例如某列前8行資料全為純數字，那麼它仍然以數字類型作為該列的資料類型，随後行裡的含有文本的資料仍然變空。 （摘至：http://www.douban.com/note/18510346/）。	隻根據前8行資料判斷是否使用字元類型
IMEX=1與系統資料庫值TypeGuessRows配合使用	TypeGuessRows 值決定了ISAM 驅動程式從前幾條資料采樣确定資料類型，預設為“8”。可以通過修改“HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel”下的該系統資料庫值來更改采樣行數。但是這種改進還是沒有根本上解決問題，即使我們把IMEX設為“1”， TypeGuessRows設得再大，例如1000，假設資料表有1001行，某列前1000行全為純數字，該列的第1001行又是一個文本，ISAM驅動的這種機制還是讓這列的資料變成空。 （摘至：http://www.douban.com/note/18510346/）。	修改系統資料庫不友善，而且無法事先判讀sheet有多少行，是以還是受行數限制。
将Excel先轉換成csv純文字格式	（1）在讀取Excel的.xls類型的文本資料之前，先将其轉換為.csv格式，在Excel中直接另存為這種格式就可以達到轉換的目的。CSV檔案又稱為逗号分隔的檔案，是一種純文字檔案，它以“,”分隔資料列。 　　 需要指出的是，CSV檔案也可以用Ole DB或ODBC的方式讀取，但是如果采用這些方式讀取其資料又會回到丢失資料的老路上，ISAM機制同樣會發揮作用。 　　 （2）采用普通的讀取文本檔案的方法打開檔案，讀取第一行，用“,”作為分隔符獲得各字段名，在DataTable中建立對應的各字段，字段的類型可以統一建立成“String”。 （3）逐行讀取資料行， 用“,”作為分隔符獲得某行各列的資料并填入DataTable相應的字段中。 簡要代碼： view source print ? 01. String line;  02. String [] split =  null ;  03. DataTable table= new DataTable( "auto" );  04. DataRow row= null ;  05. StreamReader sr= new StreamReader( "c:/auto.csv" ,System.Text.Encoding.Default);  06. //建立與資料源對應的資料列  07. line = sr.ReadLine();  08. split=line.Split( ',' );  09. foreach (String colname  in split){  10. 　　table.Columns.Add(colname,System.Type.GetType( "System.String" ));  11. }  12. //将資料填入資料表  13. int j=0;  14. while ((line=sr.ReadLine())!= null ){  15. 　　j=0;  16. 　　row = table.NewRow();  17. 　　split=line.Split( ',' );  18. 　　 foreach (String colname  in split){  19. 　　    row[j]=colname;  20. 　　    j++; 21. }  22. 　　table.Rows.Add(row); 23. }  24. sr.Close();  25. //顯示資料  26. dataGrid1.DataSource=table.DefaultView;  27. dataGrid1.DataBind(); （摘至：http://www.douban.com/note/18510346/）。	需要事先将excel轉換成csv檔案

這裡提供一個更加友善的辦法，不過前提是第一行必須是作為字段名或者第一行的資料類型就為字元型。這樣一說，大家就明白了。首先修改連接配接字元串為：

view source print ?

1.

string

strConn = 

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

+ excelFile + 

";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"

;

這裡将HDR設為NO，因為我就是将第一行做為資料讀取，而IMEX=1就表示根據前8行判斷列的資料類型，如果有字元型資料，那麼就強制混合資料轉換為文本。這裡就明白為什麼要保證第一行為字元型的原因了。能将列的資料類型強制設為字元型，那麼列中出現什麼類型的資料都不怕了。需要做的工作就是，在擷取完資料後，将字段名重新設定，并删除第一條記錄即可。代碼如下：

view source print ?

01.

DataTable dt = 

new

DataTable();

02.

03.

using

(OleDbCommand cmd = 

new

OleDbCommand()){

04.

cmd.Connection = conn;

05.

cmd.CommandType = CommandType.Text;

06.

cmd.CommandTimeout = 6;

07.

cmd.CommandText = 

string

.Format(

"select * from [{0}$]"

, sheetName);

08.

09.

OleDbDataAdapter adapter = 

new

OleDbDataAdapter(cmd);

10.

adapter.Fill(dt);

11.

}

12.

13.

if

(dt.Rows.Count > 0) {

14.

DataRow dr = dt.Rows[0];

15.

16.

for

(

int

col = 0; col < dt.Columns.Count; col++) {

17.

dt.Columns[col].ColumnName = dr[col].ToString();

18.

}

19.

20.

dt.Rows[0].Delete();

21.

dt.AcceptChanges();

22.

}