處理ReportViewer匯出檔的比武大會上,NPOI與EPPlus都敗下陣來,Open XML SDK雖然勝出,但在應用呼叫上繁瑣難搞,用起來總覺礙手礙腳。在研究Open XML SDK設定工作表保護的過程,發現新大陸 - 另一套Open Source的Excel程式庫,ClosedXML

簡單整理ClosedXML特色如下:

  1. 程式庫很俏皮地命名為ClosedXML,事實它高度依賴Open XML SDK,在引用時,程式必須一併參考DocumentFormat.OpenXml.dll。ClosedXML切入的角度是為Open XML SDK提供容易操作的程式介面,而事實證明它做得很成功,程式介面的確非常簡潔易用。
  2. 以Open XML SDK為基礎,所以只支援xlsx,不支援xls格式。
  3. CodePlex上就文件與範例,內容十分完整,很容易上手。
  4. 支援NuGet安裝,加入時會一併帶入Open XML SDK參照,安裝簡單。
  5. ClosedXML的很多Method在設計上仿照Excel VBA慣例,例如: sheet.Cell("A1").Value = "Boo"、sheet.Range("A1:C5")選取範圍,用起來相當簡單直覺。
  6. 很重要的一點,先前讓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好,看起來很值得一試!

最後不能免俗地,比照NPOIEPPlus,要用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目前覺得仍然是又快又好用

Post a comment


71 - 21 =