使用OLEDB匯出EXCEL資料發生部分資料為Null問題

1. 發生現象：
在.NET 使用OLEDB 匯出EXCEL資料時，將資料寫入DB中，發現部分資料欄位為NULL現象。

2. 發生原因：
A. 資料格式非字串所造成。
B. 在 Excel 機碼中 TypeGuessRows 的預設值為 8，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，會先讀取前 8 列的資料，用已判斷之後的資料格式是否固定。
C. 例如：前 8 列的資料都是「字串」格式，那麼在第 8 列之後的資料會自動解析成「字串」格式，但只要遇到有的資料不是「日期」格式時，就會回傳 Null ，也是本障礙產生的原因

3. 改善方法：

說明：

關於這部份是由於Excel規定

前八欄欄位如果是數字，後續欄位皆視為數字

前八欄為文字，後續欄位皆是為文字

前八欄為文字+數字混雜，後續欄位視為文字 ，寫入資料庫。

 

例如：

以你的案例，因你前八欄為文字+數字，所以轉入是沒問題的

若改寫你的案例，改為前八欄數字，第九欄文字就會發生錯誤。

 

主要解法：

1. 只要有數字與文字混合欄位時，此欄位必須加入小逗點

2. 取消前八欄格式檢查，改由每個欄位檢查 (會影響效能，只要有Excel匯入功能都會影響)

3. 程式客製排除

 

我建議作法是3. 程式客製排除。主要是透過加入標題列的方式，讓Excel規則去判讀

使Excel轉入時讓整個EXCEL欄位都視為字串格式轉入。

A. "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='{0}';Extended Properties='Excel 8.0;HDR=No;IMEX=1; //納入標題列

B. For (int i = 1; i < dtData.Rows.Count; i++) //忽略第一筆為標頭

4. 補充資料：
參考路徑：http://blog.yam.com/coochi/article/23234518
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel 位置，在 Excel 機碼中 ImportMixedTypes 的預設值為 Text，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 預設會將欄位的資料格式自動轉成文字(Text)格式！
在 Excel 機碼中 TypeGuessRows 的預設值為 8，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 會先讀取前 8 列的資料，用已判斷之後的資料格式是否固定

一般來說，透過 OleDb 載入 Excel 檔案時，設定的連線參數如下：

string _connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
    "Data Source=C:\\MyExcel.xls;" + 
    "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

其中，最關鍵的部分就是在 Extended Properties 的地方，我逐一介紹：

1. Excel 8.0
   Excel 8.0 是 Office 97 的 Excel 格式，這個格式從 Excel 97 ~ Excel 2003 都相容，如果你在 Excel 中使用「另存新檔」的話，必須要選取這個檔案類型儲存，才能正確用 OleDb 正確開啟，如下圖示：
   
    
2. HDR ( HeaDer Row )
   
   若指定值為 Yes，代表 Excel 檔中的工作表第一列是欄位名稱
   若指定值為 No，代表 Excel 檔中的工作表第一列就是資料了，沒有欄位名稱
    
3. IMEX ( IMport EXport mode )
   IMEX 有三種模式，分別讀寫行為也不同，容後再述：
   0 is Export mode
   1 is Import mode
   2 is Linked mode (full update capabilities)
    

我想最需要說明的就是 IMEX 參數了，因為不同的模式代表著不同的讀寫行為：

• 當 IMEX=0 時為「匯出模式」，這個模式開啟的 Excel 檔案只能用來做「寫入」用途。
• 當 IMEX=1 時為「匯入模式」，這個模式開啟的 Excel 檔案只能用來做「讀取」用途。
• 當 IMEX=2 時為「連結模式」，這個模式開啟的 Excel 檔案可同時支援「讀取」與「寫入」用途。

所以當我們要開發 Excel 檔案匯入功能時，正確的 IMEX 設定應該是 1 才對