小攻城師的戰場筆記

今天在從 Excel 抓資料要匯進程式裡時出問題了。某欄數值資料在開頭幾筆剛好都是 1，都可以成功轉入，但遇到大於 1 的值時，DataTable 裡的值卻是 DBNull，取用時要做加總就拋出了 Exception 了。

問題發生的原因是，某一欄的資料如果輸入數值 "1"，是可以成功轉型為 double 的，但是輸入大於 1 的數值後，Excel 的儲存格左上角會出現綠色的三角形，提示「此儲存格內的數字其格式為文字或開頭為單引號」，要求使用者更正這種數字。



我參考了 "OleDB incorrectly returning DBNull for an Excel Column"，解決這個問題的方法如下：

1. 修改 Regedit，把 Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel 的 TypeGuessRows 的值改成 0。（原本是 8，代表抓取前 8 列的值來判斷儲存格內的資料型態；改成 0 則表示搜尋整欄的儲存格來判斷資料型態）

2. 在 Connection String 加入 "IMEX=1;"。



以我的程式為例，關於 Excel 讀檔的片段寫起來就長成這個樣子：

string strExcelFile = @"C:\Test.xls";
string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelFile + ";Extended Properties='Excel 8.0;IMEX=1;'";
OleDbDataAdapter oleAp = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strExcelConn);
DataSet ds = new DataSet();
oleAp.Fill(ds, "ExcelData");

接著就可以取用 ds.Tables["ExcelData"] 這個 DataTable 裡的資料來做處理了，此時會發現原本讀進來會被判斷為 DBNull 的資料也能正常轉換進來。