Excel 傳回的值當作 NULL(使用 OLEDB 將Excel當作DB使用)

今天遇到一個問題

我在 .NET中使用 OLEDB 將Excel工作表讀入成 DataTable

但有一個怪異的現象

就是某一欄在 Excel 中看到的都是日期

而讀進來的卻有些是正確的，而有些是 Null

其原因如下

1. 在Excel看到的日期值，其本身的 DataType 不一定是日期
2. 將 Excel 讀入DataTable時，欄位的DataType只靠前幾列來判斷，所以可能誤判。
   以我的例子來說，其自動判斷資料型別的為 string，而Excel底層若為string則不會傳回Null(DBNull)
   若 Excel該欄位本身為日期，則會回傳Null。

解決方式如下：

將 Conntion String 改為如下：

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

HDR=Yes 表示工作表第一列包含了欄位名稱

IMEX=1 表示要使用匯入模式驅動程式。這會強制轉換成文字混合的資料。

若要更可靠的匯入資料，你可能也要修改registry 中的TypeGuessRows 值

例如，TypeGuessRows=8 表示會使用前8列資料來判斷其資料型別

目前我並沒有更改此機碼值

機碼的位置如下：

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

或

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

詳細資訊請參考：PRB: Excel Values Returned as NULL Using DAO OpenRecordset