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.


Comments

Be the first to post a comment

Post a comment