The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

透過 OleDb 精準讀入 Excel 檔的方法

📅 2008/08/05 14:11 📁 .NET Framework, C#, Office, Tips, VBA

之前因為有個案子要做 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 檔案時，設定的連線參數如下：

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 另存新檔所儲存的類型 
  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

在 Excel 機碼中 ImportMixedTypes 的預設值為 Text，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 預設會將欄位的資料格式自動轉成文字(Text)格式！

在 Excel 機碼中 TypeGuessRows 的預設值為 8，代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話，Jet Engine 會先讀取前 8 列的資料，用已判斷之後的資料格式是否固定。假設若前 8 列的資料都是「日期」格式，那麼在第 8 列之後的資料 Jet Engine 會自動解析成「日期」格式，但只要遇到有的資料不是「日期」格式時，就會回傳 Null 給 Jet Engine，也就是本篇文章一開始的問題。

我們是提供 Excel 檔案範本給客戶沒錯，但是強大的 Excel 實在太好用了，導致客戶要怎麼用你根本管不著，原本設定好的格式經過客戶複製、剪貼後，格式就大亂了，導致匯入程式就很容易出錯，而最可惡的就是在 Excel 中明明就有資料，而匯入到資料庫之後卻有些資料是空的，這..這..這..太難跟客戶解釋了！（這時候不得不搬出程式設計師最常說的話排行榜）。

要解決這個問題也很容易，只要將 Excel 機碼中 TypeGuessRows 的值改成 0 即可，只是若你匯入的資料量蠻大的話可能會影響資料讀取時的效能，因為 Jet Engine 會將所有資料讀完才會判斷出每個欄位應該用什麼資料格式。

最後提醒，若你的 Excel 檔案有在 Office 中做加密保護的話是無法透過 OleDb 讀取的。

相關連結

留言評論