C#讀取excel檔案資料丟失問題

 最近在處理C#讀取excel檔案時碰到了個BT問題，有部分資料讀取失敗了，翻看了不少資料，終於找到了問題的所在，所以在這裡跟大夥分享下：

簡要問題分析：

用C#讀取excel檔案資料時，出現資料丟失現象。

經查資料，發現產生這種問題的根源與Excel ISAM[3]（Indexed Sequential Access Method，即索引順序存取方法）驅動程式的限制有關，Excel ISAM 驅動程式通過檢查前幾行中實際值確定一個 Excel 列的類型，然後選擇能夠代表其樣本中大部分值的資料類型[4]。也即Excel ISAM尋找某列前幾行（預設情況下是8行），把佔多的類型作為其處理類型。例如如果數字佔多，那麼其它含有字母等文本的資料項目就會置空；相反如果文本居多，純數位資料項目就會被置空。

相關知識：

現具體分析HDR和IMEX所代表的含義。

HDR用來設定是否將Excel表中第一行作為欄位名，“YES”代表是，“NO”代表不是即也為資料內容；

IMEX是用來告訴驅動程式使用Excel檔案的模式，其值有0、1、2三種，分別代表匯出、匯入、混合模式。當我們設定IMEX＝1時將強制混合資料轉換為文本，但僅僅這種設定並不可靠，IMEX＝1隻確保在某列前8行資料至少有一個是文本項的時候才起作用，它只是把尋找前8行資料中資料類型佔優選擇的行為作了略微的改變。例如某列前8行資料全為純數字，那麼它仍然以數字類型作為該列的資料類型，隨後行裡的含有文本的資料仍然變空。

網路上大部分的解決方案：

1、修改連接字串：

如原來的連接字串為： string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + "; Extended Properties=Excel 8.0;";  

將其修改為：string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;IMEX=1;";

2、我還看到的一些改進的措施是IMEX＝1與註冊表值TypeGuessRows配合使用。

TypeGuessRows 值決定了ISAM 驅動程式從前幾條資料採樣確定資料類型，預設為“8”。可以通過修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的該註冊表值來更改採樣行數。但是這種改進還是沒有根本上解決問題，即使我們把IMEX設為“1”， TypeGuessRows設得再大，例如1000，假設資料表有1001行，某列前1000行全為純數字，該列的第1001行又是一個文本，ISAM驅動的這種機制還是讓這列的資料變成空。

故此，以上兩種解決方式都存在著一定的弊端。經過仔細的查閱資料，總結出如下方法：

解決辦法：

從以上的分析中可以得知，當某列資料中含有混合類型時，在.NET中使用Microsoft.Jet.OLEDB.4.0來讀取Excel檔案造成資料丟失是不可避免的，要解決這個問題只能考慮採用其它資料讀取方法。 在.NET中讀取Excel檔案的另外一種方法是回到使用傳統COM組件，這種方法在很多技術文章或論文中都有涉及，本文不作贅述。需要指出的是，使用COM組件來讀取Excel檔案資料的效率較低，在作釋放的時候有可能碰到不可預知的錯誤，特別開發Web應用的程式應該謹慎使用。  （不推薦使用）不過在網上查到了另外一種利用讀取CSV純文字格式解決此問題的方法。 　　（1）在讀取Excel的.xls類型的文本資料之前，先將其轉換為.csv格式，在Excel中直接另存新檔這種格式就可以達到轉換的目的。CSV檔案又稱為逗號分隔的檔案，是一種純文字檔案，它以“,”分隔資料列，本文表1的資料表用CSV格式儲存後用純文字編輯器開啟的表現形式如表3所示。 　　表3 採用CSV格式儲存的表1資料 　　需要指出的是，CSV檔案也可以用Ole DB或ODBC的方式讀取，但是如果採用這些方式讀取其資料又會回到遺失資料的老路上，ISAM機制同樣會發揮作用。 　　（2）採用普通的讀取文字檔的方法開啟檔案，讀取第一行，用“,”作為分隔字元獲得各欄位名，在DataTable中建立對應的各欄位，欄位的類型可以統一建立成“String”。 　　本文原文　　（3）逐行讀取資料行， 用“,”作為分隔字元獲得某行各列的資料並填入DataTable相應的欄位中。 　　實現的簡化代碼如下： 　　String line; 　　String [] split = null; 　　DataTable table=new DataTable("Thylx"); 　　DataRow row=null; 　　StreamReader sr=new StreamReader("c:/Thylx.csv",System.Text.Encoding.Default); 　　//建立與資料來源對應的資料列 　　line = sr.ReadLine(); 　　split=line.Split(','); 　　foreach(String colname in split){ 　　table.Columns.Add(colname,System.Type.GetType("System.String")); } 　　//將資料填入資料表 　　int j=0; 　　while((line=sr.ReadLine())!=null){ 　　 j=0; 　　 row = table.NewRow(); 　　 split=line.Split(','); 　　 foreach(String colname in split){ 　　 row[j]=colname; 　　 j++;} 　　 table.Rows.Add(row);} 　　 sr.Close(); 　　//顯示資料 　　dataGrid1.DataSource=table.DefaultView; 　　dataGrid1.DataBind(); 總結： 在應用程式的設計中，需要訪問Excel資料的情況非常普遍，本文以在.NET中對訪問含有混合類型資料的Excel表格擬採取的方法進行探討。當然，如果不存在混合類型的資料使用Microsoft.Jet.OLEDB為較佳方案，也就是我上面提到的在網路中查到的大部分做法。本文只是針對NET方面的一些研究，對於其他開發語言，可參照類似的做法來解決excel讀取時資料丟失的問題