The Will Will Web

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

透過 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 檔案時,設定的連線參數如下:

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 讀取的。

相關連結