# 透過 OleDb 精準讀入 Excel 檔的方法 之前因為有個案子要做 Excel 匯入的功能，需要讓客戶先下載匯入檔案範本，然後讓客戶上傳 Excel 檔 ( \*.xls )，再透過 C# 讀取資料後存入資料庫，我是採用 OleDb 的方式在 Server 端開啟檔案並將資料讀出，不過卻遇到了幾個難解的問題，其中最討厭的問題就是透過 OleDb 載入資料時，它都會自動判斷 Excel 中每個欄位的型別，假設工作表中的第三欄的前 8 列的值是「數字」，而第 9 列的「文字」的話，當讀取到第 3 欄第 9 列的時候，該儲存格的欄位值就會是 Null，可能會引發程式執行錯誤（因為你會預期有資料）。 這又是一個不認真讀書、找資料的典範啦，我當初在寫的時候是有找到一些資料，不過卻沒認真看完，網路上隨便抓一段 Sample Code 就開始寫了（我相信大部分的人都這樣），而當遇到問題的時候就開始直覺的反應 "這怎麼可能" 、 "又是微軟的 Bug" 、 "天阿, OleDb 真難用" 等等(髒話的部分已經刪除)，等在內心抱怨完之後（大約兩秒）就開始發揮創意想解決方案（這也是最好玩的部分），當然沒有無法解決的問題，我還是想到了一個當初自己覺得還蠻不錯的方法。（現在覺得很爛，勿學） 為了確保讀出的資料全部都是「文字」，我自己手動建立了一個 Typed DataSet，並將所有欄位都設定成 string，然後透過 OldeDb 將 Excel 資料讀出後存入 Typed DataTable，但這樣還是會發生資料為 Null 的情形，所以我又修改 Excel 匯入檔案範本加入一段 VBA 程式碼，讓客戶在 Excel 中輸入完文字後按下某個按鈕，強迫將所有欄位格式先轉成文字，反正就是一整個囉唆啦。 不過為了寫出這篇文章，我把之前找的文章仔細的看過一遍了，才知道我之前的 "解決方案" 實在是太蠢了，以下是比較聰明的解法。 一般來說，透過 OleDb 載入 Excel 檔案時，設定的[連線參數](https://www.connectionstrings.com/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 正確開啟，如下圖示： ![Excel 另存新檔所儲存的類型](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/f416d76985419bbceb09-image_3.png)  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 才對，不過就算設定 IMEX=1 還不夠，還有一些需要注意的地方！ 你可以開啟 regedit 程式，選取進系統機碼(Registry)的 **HKEY\_LOCAL\_MACHINE\\SOFTWARE\\Microsoft\\Jet\\3.5\\Engines\\Excel** 位置，這裡有兩個非常重要的設定： ![系統機碼(Registry)的 HKEY\_LOCAL\_MACHINE\\SOFTWARE\\Microsoft\\Jet\\3.5\\Engines\\Excel ](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/db9b1a6074474746b9cf-image_5.png) 在 Excel 機碼中 ImportMixedTypes 的預設值為 Text，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 預設會將欄位的資料格式自動轉成文字(Text)格式！ 在 Excel 機碼中 TypeGuessRows 的預設值為 8，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 會先讀取前 8 列的資料，用已判斷之後的資料格式是否固定。假設若前 8 列的資料都是「日期」格式，那麼在第 8 列之後的資料 Jet Engine 會自動解析成「日期」格式，但只要遇到有的資料不是「日期」格式時，就會回傳 Null 給 Jet Engine，也就是本篇文章一開始的問題。 我們是提供 Excel 檔案範本給客戶沒錯，但是強大的 Excel 實在太好用了，導致客戶要怎麼用你根本管不著，原本設定好的格式經過客戶複製、剪貼後，格式就大亂了，導致匯入程式就很容易出錯，而最可惡的就是在 Excel 中明明就有資料，而匯入到資料庫之後卻有些資料是空的，這..這..這..太難跟客戶解釋了！（這時候不得不搬出[程式設計師最常說的話排行榜](/post/2008/07/29/Careful-using-static-field-in-ASPNET)）。 要解決這個問題也很容易，只要將 Excel 機碼中 TypeGuessRows 的值改成 0 即可，只是若你匯入的資料量蠻大的話可能會影響資料讀取時的效能，因為 Jet Engine 會將所有資料讀完才會判斷出每個欄位應該用什麼資料格式。 最後提醒，若你的 Excel 檔案有在 Office 中做加密保護的話是無法透過 OleDb 讀取的。 **相關連結** - [ConnectionStrings.com - How to connect to Excel](https://www.connectionstrings.com/excel/) - [How To Use ADO with Excel Data from Visual Basic or VBA](http://support.microsoft.com/kb/257819/en-us) - [PRB: Excel Values Returned as NULL Using DAO OpenRecordset](https://web.archive.org/web/20090408123407/http://support.microsoft.com:80/kb/194124) - [External Data - Mixed Data Types](https://web.archive.org/web/20240723075355/http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/) - [Reading and Writing Excel using OLEDB](http://www.codeproject.com/KB/office/excel_using_oledb.aspx) - [PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?]()