Linq2Excel 的陷阱與處理方法

Linq2Excel 是一個非常方便好用的小工具, 它是一個 Excel 專用的 LINQ Provider, 可以讓我們很快速地讀取 Excel (包括 Excel 2013) 的試算表。我們可以直接在 Visual Studio 中透過 NuGet 取得, 目前的版本是 1.7.1。

如果你對這個套件有興趣, 你可以參考「LINQ - 實作 LinqToExcel」這篇文章, 足以讓你快速入門。

這個套件有幾個值得特別注意的地方。第一個, 這個套件有 X86 和 X64 兩種版本, 如果你使用 X86 版本進行 ASP.NET 的開發, 而你打算把網站發行到裝有 X64 作業系統的伺服器, 那麼發行後會發生錯誤, 就是這個版本引起的。要解決這個問題, 請參考「IIS7 - Running 32-bit and 64-bit ASP.NET versions at the same time on different worker processes」這篇文章所提供的方法。事實上, 如果你想裝的是 64 位元版本, 它的 X64 版本是可以從 VS 的 Nuget 工具中找到並安裝的, 訣竅在於你必須使用 "linqtoexcel_x64" 字樣來尋找, 才找得到。

第二個問題是, 如果你在執行匯入時, 目標檔案正被你或其他人以 Excel 打開的話, 那麼該檔案會被銷住而導致程式無法執行。奇怪的是, 你可能會看到「Microsoft.ACE.OLEDB.12.0 提供者並未登錄於本機電腦上」這種莫名其妙的錯誤訊息。不要被這個錯誤的錯誤訊息誤導, 將那個檔案從 Excel 中關閉即可。

此外, 如果你的 Excel 檔案內含其它標頭, 亦即並非標準標頭加上內容的類型的話, 那麼你可以使用 WorksheetRange 而不是 Worksheet 方法來建立物件。如下列程式所示:

var excel = new ExcelQueryFactory(path);
excel.DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace;
var data = excel.WorksheetRange<tempsheet>("A5", "K600", "工作表1");
// var data = excel.Worksheet<tempsheet>(0);
int rowCount = 0;
foreach (TempSheet temp in data)
{
    Debug.WriteLine("{0}. {1}, {2}, {3}\r\n",
        ++rowCount,
        temp.Field0,
        temp.Field1,
        temp.Field2);
}

如果想要取出任何一個特定的 cell, 可以使用如下的程式:

/// <summary>
/// Get the value of a specified single cell
/// </summary>
/// <param name="excel">The targeted excel object</param>
/// <param name="sheet">Name of the work sheet</param>
/// <param name="location">Location of the targeted cell</param>
private string getSingleCell(ExcelQueryFactory excel, string sheet, string location)
{
    RowNoHeader row = excel.WorksheetRangeNoHeader(location, location, sheet).First();
    Cell cell = row.First<Cell>();
    return cell.Value.ToString();
}

第三個問題比較 tricky, 很難發現, 而且是 data loss 的嚴重問題。

假設你的 Excel 有一整欄都是數字欄位, 但是你在這個欄位裡的某個 cell 裡使用文字輸入 (就是以 '123 而不是 123 的方式輸入; 注意 123 前面那個單引號), 就會發生問題。那麼, 使用者在什麼時候會這麼做呢? 其實這個動作很平常; 例如當我們在輸入廠商統編的時候, 遇到以 0 開頭的統編, 使用者就可能使用這種方式輸入。如此, 若輸入 '01234567 的話, 最前面的 0 就會保留, 而不是消失。

問題是, Linq2Excel 是從試算表的上面往下讀取, 如果這一欄從一開始就是數字, 它就會把這個欄位自動視為數字欄位; 當它突然遇到文字時, 一 cast 下去, 就通通變成了 0。換句話說, 在「統編」這一欄裡, 除非你第一列就使用文字格式, 否則這一欄裡面的所有文字 (即使你看起來它是數字), 會通通讀作 0!

就我個人試到現在, 還沒找到如何從 Linq2Excel 去解決這個問題。唯一的解決方法, 是從 Excel 檔案本身著手。怎麼做呢?

首先, 你必須確定那一欄 (假設是 $E) 確實都是數字。然後, 隨便找個空欄 (假設是 $F), 把原來那一欄填上 "=$E01 + 0" 這個公式, 然後往下填滿。如此, 這一欄就會複製 $E 的所有資料, 而且都是數字格式。然後, 你再把這個新欄以拷目「值」的方式貼到另一個新欄 (假設是 $G), 再把原來那兩欄 ($E 和 $F) 刪除即可。

這麼一來, Linq2Excel 就不會再讀到錯誤的資料了!

順帶補充一下, 若遇到必須在前面補 0 的情況下, 我們其實可以不必把它設定為文字欄位, 而是在「儲存格格式」上面動手腳。方法是把一整欄的儲存格格式選到「數值」、「自訂」, 然後填入八個 0, 這一欄的顯示方式就會自動補 0 了, 而且不會讓它變成文字。

EDIT

2014/10/20 補充: 第三個問題也許可以經由 Linq2Excel 的 AddMapping 功能解決。對這個解法有興趣的朋友, 可以參考「C#: Query Excel and .CSV Files Using LinqToExcel」一文。

2015/3/24 補充: 加上第二個問題的提醒, 並加上使用 WorksheetRange 的範例程式。