使用 ClosedXML 結合 LINQ 操作
| | 0 | | ![]() |
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