使用 ClosedXML 結合 LINQ 操作

ClosedXML 是我用程式操作 Excel 時的奧林匹克指定程式庫(參考： 令人驚豔的Excel程式庫 - ClosedXML )，在 .xls 格式退休後，在 .NET 中要讀寫操作 Excel 檔案，非它莫屬，尤其結合 LINQ 用來更是得心應手。最近學到一點小眉角，筆記備忘。

假設我有個 Excel 檔案如下：

第二列起為樂團資料，第一欄為團名，第二欄位為備註，第三欄起為團員清單。想解析取出團名及團員名字陣列。 若用傳統做法，最直覺的寫法是跑 rowIdx、colIdx 迴圈，檢查 .Cell(roxIdx, colIdx).IsEmpty() 偵測資料邊界，像是這樣：

(程式碼一併展示用 new StackTrace().GetFrame(n).GetMethod().Name 取得當下執行方法的小技巧)

static void WriteMethodName()
{
    Console.WriteLine("\n******** {0} ********", 
                      new StackTrace().GetFrame(1).GetMethod().Name);
}

static void Test0()
{
    WriteMethodName();
    using (var wb = new XLWorkbook("D:\\Test.xlsx"))
    {
        var sht = wb.Worksheet(1);
        var rowIdx = 2;
        while (!sht.Cell(rowIdx, 1).IsEmpty())
        {
            var colIdx = 3;
            var grpName = sht.Cell(rowIdx, 1).GetString();
            var members = new List<string>();
            while (!sht.Cell(rowIdx, colIdx).IsEmpty())
            {
                members.Add(sht.Cell(rowIdx, colIdx).GetString());
                colIdx++;
            }
            Console.WriteLine($"Group[{grpName}]: {string.Join(",", members)}");
            rowIdx++;
        }
    }
}

執行結果：

******** Test0 ********
Group[五月天]: 阿信,怪獸,瑪莎,石頭,冠佑
Group[S.H.E.]: Selina,Hebe,Ella
Group[無印良品]: 光良,品冠

這種寫法簡單易懂，但平日寫慣 LINQ，總想再簡潔一點，於是我改寫成 LINQ 版：

static void Test1()
{
    WriteMethodName();
    using (var wb = new XLWorkbook("D:\\Test.xlsx"))
    {
        var sht = wb.Worksheet(1);
        var rows = sht.Rows().Skip(1).TakeWhile(o => !o.Cell(1).IsEmpty());
        foreach (var row in rows)
        {
            var cells = row.Cells();
            var grpName = cells.First().GetString();
            var members = cells.Skip(2).TakeWhile(o => !o.IsEmpty()).Select(o => o.GetString()).ToArray();
            Console.WriteLine($"Group[{grpName}]: {string.Join(",", members)}");
        }
    }
}

用 Rows().Skip(1).TakeWhile(o => !o.Cell(1).IsEmpty()) 取得有填資料的資料列集合，再逐一取得資料列的 Cells()，由 .First() 取得團名，Skip(2) 跳掉團名跟註記由第三欄位開始取團員，一樣用 TakeWhile() 向後抓姓名直到遇上空白。但結果有異，發現 S.H.E. 的 Selina 脫團失蹤了...

******** Test1 ********
Group[五月天]: 阿信,怪獸,瑪莎,石頭,冠佑
Group[S.H.E.]: Hebe,Ella
Group[無印良品]: 光良,品冠

經過一番實驗分析，發現 Row() 與 Cells() 與我想像不同，傳回的並非工作表所有列或欄的集合，是自動剔除空欄位的結果。以下程式碼可印出 Rows() 及各 Row.Cells() 進行觀察：

static void Test2()
{
    WriteMethodName();
    using (var wb = new XLWorkbook("D:\\Test.xlsx"))
    {
        var sht = wb.Worksheet(1);
        var rows = sht.Rows();
        Console.WriteLine($"Rows.Count={rows.Count()}");
        var idx = 1;
        foreach (var row in rows)
        {
            var cells = row.Cells();
            Console.Write($"Row[{idx}] ({cells.Count()} Cells): ");
            Console.WriteLine(
                string.Join(",", cells.Select(o => o.GetString())));
            idx++;
        }
    }
}

由結果可知，S.H.E. 列只傳回四個儲存格，備註欄因未填被直接略過，Skip(2) 時 Selina 被跳過；無印良品的備註欄看似無內容，其實有個空白字元，傳回四格與 Skip(2) 前題吻合。第五列雖無資料但也被納入，且 .Cells() 傳回五欄。

******** Test2 ********
Rows.Count=5
Row[1] (3 Cells): Group,Remark,Members
Row[2] (7 Cells): 五月天,天團,阿信,怪獸,瑪莎,石頭,冠佑
Row[3] (4 Cells): S.H.E.,Selina,Hebe,Ella
Row[4] (4 Cells): 無印良品, ,光良,品冠
Row[5] (5 Cells): ,,,,

我沒有找到 .Rows() 與 .Cells() 用法的完整說明，但 ClosedXML 是個開源專案，它早已備妥全世界最完整的說明文件 - Source Code!

從 XLRow.cs 查到 .Cells() 有個 bool usedCellsOnly 參數，預設為 true 只包含有用到的儲存格，設為 false 時則取 FirstCellUsed().Address.ColumnNumber 到 LastCellUsed().Address.ColumnNumber 間的儲存格。ClosedXML 內部有判斷儲存格使用與否的邏輯，但有點小複雜，甚至受字型樣式影響，填入資料再刪除結果也可能不同。故建議把這段當成黑箱，關鍵應用靠自己用 IsEmpty() 判斷比較準。以下是一個字型形響使用與否的案例，重設 A1:G4 範圍字型後，該範圍儲存格全被判斷為「已使用」，不管有無填入文字，而第五列的儲存格數則是 5：

至於 .Rows() 多傳回一列無資料列的問題，可改用 .RowUsed() 取代 .Rows() 解決。

原本的 Test1() 只需將 var cells = row.Cells(); 改為 var cells = row.Cells(false); ，而 .Rows().Skip(1).TakeWhile(o => !o.Cell(1).IsEmpty()) 則再簡化為 .RowsUsed().Skip(1) 取代，即可完美達到要求：

static void Test3()
{
    WriteMethodName();
    using (var wb = new XLWorkbook("D:\\Test.xlsx"))
    {
        var sht = wb.Worksheet(1);
        var rows = sht.RowsUsed().Skip(1);
        foreach (var row in rows)
        {
            var cells = row.Cells(false);
            var grpName = cells.First().GetString();
            var members = cells.Skip(2)
                .TakeWhile(o => !o.IsEmpty())
                .Select(o => o.GetString()).ToArray();
            Console.WriteLine($"Group[{grpName}]: {string.Join(",", members)}");
        }
    }
}

程式簡潔且結果正確：

******** Test3 ********
Group[五月天]: 阿信,怪獸,瑪莎,石頭,冠佑
Group[S.H.E.]: Selina,Hebe,Ella
Group[無印良品]: 光良,品冠

以上就是我研究 ClosedXML 結合 LINQ 操作的一點小心得，提供大家參考。

Tips of how to use .RowsUsed()、.Cells() of ClosedXML in LINQ way.