令人驚豔的Excel程式庫 - ClosedXML
19 |
處理ReportViewer匯出檔的比武大會上,NPOI與EPPlus都敗下陣來,Open XML SDK雖然勝出,但在應用呼叫上繁瑣難搞,用起來總覺礙手礙腳。在研究Open XML SDK設定工作表保護的過程,發現新大陸 - 另一套Open Source的Excel程式庫,ClosedXML。
簡單整理ClosedXML特色如下:
- 程式庫很俏皮地命名為ClosedXML,事實它高度依賴Open XML SDK,在引用時,程式必須一併參考DocumentFormat.OpenXml.dll。ClosedXML切入的角度是為Open XML SDK提供容易操作的程式介面,而事實證明它做得很成功,程式介面的確非常簡潔易用。
- 以Open XML SDK為基礎,所以只支援xlsx,不支援xls格式。
- CodePlex上就文件與範例,內容十分完整,很容易上手。
- 支援NuGet安裝,加入時會一併帶入Open XML SDK參照,安裝簡單。
- ClosedXML的很多Method在設計上仿照Excel VBA慣例,例如: sheet.Cell("A1").Value = "Boo"、sheet.Range("A1:C5")選取範圍,用起來相當簡單直覺。
- 很重要的一點,先前讓EPPLUS及NPOI灰頭土臉的ReportViewer匯出檔測試,ClosedXML輕易過關,產出結果與Excel相容,程式碼又比Open XML SDK簡短易理解,大勝! (以下範例順便展示了保護工作表功能,一行搞定。)
//ClosedXML
var wb = new XLWorkbook(src);
var ws = wb.Worksheets.First();
ws.Cells("A1").Value = "已修改";
ws.Protect("LetMeEdit");
wb.SaveAs(@"d:\temp\closedXml.xlsx");
初步評估,ClosedXML支援不少Excel VBA風格的簡潔API,在ReportViewer匯出檔案相容性測試又比NPOI及EPPlus好,看起來很值得一試!
最後不能免俗地,比照NPOI、EPPlus,要用ClosedXML試做網站檔案結構轉Excel的範例:
/// <summary>
/// 將目錄下的目錄檔案結構匯出成Excel工作表
/// </summary>
/// <param name="dirPath">要匯出的目錄路徑</param>
/// <param name="excelPath">匯出Excel路徑</param>
/// <param name="filter">過濾函數,傳入Path進行判斷,傳回true時表排除</param>
/// <returns></returns>
public static void WebTreeToExcel(
string dirPath, string excelPath,
Func<string, bool> filter = null)
{
//將目錄結構整理成清單
List<WebItem> list = new List<WebItem>();
explore(list, dirPath, 0);
//建立Excel
XLWorkbook workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Site Tree");
int colIdx = 1;
foreach (string colName in "Path;File;Description".Split(';'))
{
sheet.Cell(1, colIdx++).Value = colName;
}
//修改標題列Style
var header = sheet.Range("A1:C1");
header.Style.Fill.BackgroundColor = XLColor.Green;
header.Style.Font.FontColor = XLColor.Yellow;
header.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
int rowIdx = 2;
foreach (var item in list)
{
//若bypass檢測傳回true,則略過該筆
if (filter != null && filter(item.Path))
continue;
//將Path放在第一欄(稍後隱藏)
sheet.Cell(rowIdx, 1).Value = item.Path;
//存入檔名或目錄名
sheet.Cell(rowIdx, 2).Value =
new String(' ', item.Layer * 4) + item.Name;
if (item.IsFolder)
{
sheet.Cell(rowIdx, 2).Style.Font.FontColor = XLColor.Blue;
}
rowIdx++;
}
//第一欄隱藏
sheet.Column(1).Hide();
//自動伸縮欄寬
sheet.Column(2).AdjustToContents();
sheet.Column(2).Width += 2;
sheet.Column(3).Width = 50;
//寫入檔案
workbook.SaveAs(excelPath);
}
程式簡潔度與EPPlus相近,結果也正確無誤,ClosedXML勝出之處在於產出檔與Excel相容度較高,是一套值得推薦的Excel程式庫。
Comments
# by 阿信
暗黑大,你好 我在使用ClosedXML時遇到一個問題 https://drive.google.com/file/d/0B-BFYSVGqDkBUWk3dF9McXdEaFk/view 連結是我公司的一個Excel檔案,原本裡面有許多工作表,已經被我全部刪除然後新增了一個工作表,然後這個Excel如果經過 ClosedXML 的處理後,第一次開啟是正常的,但是如果存檔後再開啟就會出現 Excel 在 XXX中找到無法讀取的內容這個錯誤訊息 我的程式碼只有用↓ 也是會這樣,想請問這是Excel檔案的問題還是ClosedXML的問題? XLWorkbook workbook = new LWorkbook(@"D:\test.xlsx"); workbook.Save();
# by Jeffrey
to 阿信,我做了XLWorkbook workbook = new XLWorkbook(@"D:\test.xlsx"); workbook.Save();測試,未能重現你說的問題。我用的版本:ClosedXML版本0.76.0,Excel 2010。懷疑會不會跟Excel環境有關,建議換台機器測試對照一下。
# by 阿信
感謝大大的回覆及測試 我的環境也跟大大一樣,是只有使用連結的Excel檔案才會有這個狀況發生耶 我說明一下我的操作流程 步驟一 使用ClosedXML處理該Excel檔案(連結內的Excel,而且只有用.Save();....|||Orz) 步驟二 開啟處理完後Excel並編輯裡面的資料,然後存檔 步驟三 再次開啟該Excel檔案 就會出現那些訊息了 @@! 會發現這樣的問題,主要是因為我們公司有個固定格式的Excel,裡面有同事在裡面編輯好的函數跟巨集,主要都是先Copy那份Excel再依據工作表名稱把資料加進去 連結內的Excel就是我把那份Excel檔案的工作表跟巨集都刪除後的,可是它"看起來"明明就很乾淨了,可是就是會出現那個問題...|||Orz
# by Jeffrey
to 阿信,懂了,ClosedXML存檔後要再用Excel編輯存檔一次,再次開啟才會出錯,已成功重現錯誤,我再研究看看有什麼奧妙之處。
# by Jeffrey
to 阿信,找到問題根源,XL/style.xml中timelineStyles節點附近的XML格式有誤,在ClosedXML討論區找到相關文章(https://closedxml.codeplex.com/discussions/403797),似乎都發生在「Excel 2013建立xlsx->ClosedXML處理->Excel 2010開啟」的情境,我測試如果ClosedXML Save()後改用Excel2013編輯存檔再開啟,並不會出錯。看起來是Bug,但不確定這要算在Excel 2010還是ClosedXML頭上? :P 以上資訊供你參考。
# by 阿信
好的 感謝大大~@@! 原本我還在猜是不是因為 ClosedXML 處理後的 Excel檔 的 XML 都會變成 <x: 開頭... 可是想想就算是變成這樣也不應該影響到什麼啊... 感謝大神的解說。
# by 貓大王
您好: 想請問,如果我要取出(B1:B10)的最大值。 該怎麼做呢? ws.Columns(2).Max() 這個寫法對嗎? Max裡面要放selector As System.Func(Of T, Double)) selector As System.Func(Of T, Double)) 這個要怎麼做? 感謝回應。
# by Jeffrey
to 貓大王,我的話會這樣寫:sht.Range("B1:B10").Cells().Max(o => o.Value)
# by 貓大王
To Jeffrey 感謝您的回應。 我用的是VB,目前是run每個cell再找出最大值。 關於問題,我這樣寫,結果執行錯誤,說物件必需是Double型別 Dim myMax = ws.Range("B1:B10").Cells().Max(Function(o) Return o.Value End Function) 再請問,用Graphic畫文字時,可以寫直行的字嗎? 我目前是把每個字中間加入斷行文字。 是否還有其他的方行可以寫直行字? 感謝您的回應。
# by Jeffrey
to 貓大王, B1到B10的內容與儲存格格式都是數字嗎?或是你能提供Excel跟程式範例檔重現問題。 至於垂直排列文字,之前遇到時我也都是自行加入換行符號解決,沒想到再研究其他解法 XD
# by 貓大王
To Jeffery 這個連結你有辦法下載得到嗎? 我是放在Dropbox的。 https://dl.dropboxusercontent.com/u/47565344/ExcelTest.rar
# by 貓大王
剛又再試了一次,原來全部格子的數值都要是數字才行。 因為裡面有空格。 所以才會出錯。 感謝Jeffery的回答。 感恩。
# by 佑翔
Hello 自從發現closedxml後 我們也甩了epplus改追隨他了 使用三年來沒任何問題 萬歲! but 最近有某些特定情況 我們又找回epplus來支援 關鍵原因在於「記憶體使用量」 情況是這樣的 我們要憑空生成一份 30欄位 x 10萬筆資料的 excel 在過程中 closedxml 用了約1G的記憶體 epplus 卻不到 400M 考量到目前火燒屁股 我們就先換了epplus來接手這個案 未來再找時間詳究 只是提一下有這情況 未來如果遇到才不會手足無措 我們這如果有進一步消息再來通報囉!
# by Jeffrey
to 佑翔, 謝謝你的寶貴經驗分享,已筆記。(10萬筆資料好驚人,但查了一下,Excel的上限是1,048,576 列乘以 16,384 欄 https://goo.gl/AtBP1I ,真是了不起)
# by 白皮
佑翔說的沒錯,ClosedXML可能不適合大量資料,存檔時間也是另外一個問題,我測試產生500頁A4(大約2萬多Rows)光存檔就花了200秒以上 同樣用OpenXML的SpreadSheetLight就正常多了,有機會可以試試 而EPPlus目前覺得仍然是又快又好用
# by RogerWigaa
I am using ZetExcel's Library. Try.
# by 紅中
EPPlus 好像 5版之後就要收費了?https://epplussoftware.com/
# by Latishaaaaaa
ClosedXml 疑似官方文件改地方放了,所以原網址進不去... 新網址在這:https://docs.closedxml.io/ 感謝黑暗大的教學,受益良多(❁´◡`❁)
# by Jeffrey
to Latishaaaaaa,感謝通報,已更新。