第188期 / June 5, 2013

研發新視界

分享到臉書!分享到維特!分享到噗浪!分享到Google+!分享到微博!轉寄友人友善列印

Excel 存取工具揣摩

作者/陳冠寧

[發表日期:2013/6/5]

前言

在今日 Microsoft Excel 被廣為業界接受之際,Excel 已幾乎是每個人必會的工具,不論是在校園或是職場,接觸到 Excel 的機率很高,而且 Excel 靠著簡單易用以及高度容錯的能力,讓使用者極易上手,它內建的強大試算以及資料整理的功能,也讓很多使用者樂於使用它,這一點由 Excel 的高市佔率得以印證,用 Excel 來整理與包裝資料已經是司空見慣的事,因此很多的使用者會利用它來處理日常的業務資料或是基本檔等等。

經常會看到使用者不論如何都要用 Excel 檔來放資料,所以總是要求 IT 人員在系統中產出 Excel 檔案下載給使用者驗證,但常遇到一個專案問題-壓力測試時查詢效率不佳、回傳資料量過大消耗過多記憶體容量,印一份報表有時須5~10分鐘甚至卡住當機,這是使用者不能忍受的。

因此開始有很多第三方軟體廠商開始發展不需要 Excel 就可以處理的方法,像是 Aspose 的 Excel Library(Aspose.Cells)、或是在 ComponentSource 中可以找到的許多元件,多數都可以在不需要安裝 Excel 在伺服端的情況下就可以存取 Excel 檔案,不過這些都是要金錢成本消費的,所以有部份開發人員(包含開放原始碼陣營)亦發展出一些免費且開放原始碼的套件,像是 ExcelLibrary(on Google code)、EPPLUS、ClosedXML、LinqToExcel、以及 Java 陣營開發的 Apache POI 專案(Apache POI Project)。.NET 陣營老字號常用版NPOI。

本文在林林總總的許多元件中,就官方版Excel 物件模型、NPOI和EPPLUS 做討論比較相對的優缺點提供編譯人員就各自的條件環境下,選擇出最適合的元件做開發。

.Net內建應用元件, NPOI,EPPLUS比較

開發環境:

開發工具-VS2008
資料庫-Oracle

一、官方版讀取Excel
    1.讀取 excel檔案存取

      這是官方公布最正統的方法,直接將 Excel 物件模型加入專案參考,並使用 .NET內建函示庫來呼叫內含在 Excel 物件庫中的物件,像是 Workbook、Worksheet、Range、Formula、Row、Cell 等等,它的好處是可以精確的控制 Excel 檔案中的各種屬性(儲存格格式、樣式、資料、公式以及條件等等),輸出的檔案也絕對是最正確的 Excel 資料檔,不過它卻有下列缺點:

    • 物件模型複雜不易學習:在 Excel 物件庫中,擁有數百種物件以及數以百計的列舉常數值,每個物件之間又和數個不同的物件有交互關係,在程式的操控上相對不容易。

    • 無法使用資料流方式控制 Excel 檔案:Excel 物件模型是基於 EXCEL.exe(Excel 的執行檔)來存取檔案的內容,物件顯露出來的讀取方法只有實體檔案的方式,而無法使用資料流(如 MemoryStream),這會迫使開發人員必須要另外處理產生的實體檔案,對開發人員來說並不方便。同時實體檔案的作業系統 I/O 功能,也會涉及到權限控制的問題。

    • 以單機為基礎的執行引擎:Excel.exe 本身並不是針對網路環境來設計的,因此它基本上並不支援多人操作與共享的應用程式(簡言之,就是 Web 應用程式),因此經常會在論壇上看到諸如『為什麼 Excel 釋放不掉』的問題,其根本原因就是 Excel 本身的行程特性,在多人產生執行個體以及多行程鎖定的情況下,Excel 行程會被佔住無法釋放,無法放掉的行程愈多,對伺服器的記憶體就愈傷,甚至導致伺服器不穩定。

    2.使用 OLE DB Provider for Jet 來存取

      這是控制 Excel 檔案的另一種作法,經由 Microsoft Jet OLE DB Provider 資料庫引擎來存取,Jet 引擎可以支援多種以檔案為主的資料庫(file-based database),像是 Access、dBase 等等,以 SQL 指令為主的存取能力,在 Excel 上也可以實現,開發人員可以不用特別熟悉 Excel 物件模型,就可以控制 Excel 檔案的內容,不過它也不是一個好的伺服器端 Excel 解決方案,因為:

    • 不支援資料流存取:Jet 引擎和 Excel.exe 一樣,也只能使用實體檔案,無法使用資料流存取,因此也是要由開發人員自行管理使用過的檔案,若疏於管理的話,會讓伺服器上充斥許多的無用檔案。

    • 控制資料的程度有限:Jet 引擎雖然可以存取 Excel 檔案內容,但它畢竟不是 Excel 物件模型本身,對於 Excel 的控制無法做到跟物件模型完全相同的能力,而且也受限於 Jet 所支援的 SQL 指令,對於 Excel 檔案只能使用 SQL 指令來操控,因此像是樣式、條件以及高度依賴物件模型的功能,都沒有辦法被 Jet 控制。


    3.Office 參考加入流程



《圖一》參考加入 step1


《圖二》參考加入 step2


《圖三》參考加入完成


      再來瞭解一下可應用到的類別(示範如下圖) :
      Excel.Application:Excel應用程式
      Excel.Workbook:應用程式裡的活頁簿,預設情況下,不管你開幾個Excel檔案,在工作管理員裡只會出現一個Excel.exe
      Excel.Worksheet:活頁簿裡的工作表
      Excel.Range:工作表裡的儲存格,一格也是Range,多格也是Range,用法Excel.Range[“A1”];
      Excel.Range.Cells: 這是儲存格的最小單位,代表一格的Range,用法Excel.Range.Cells[1,1];



《圖四》Excel 物件註釋


    4.功能操作

      range.NumberFormatLocal = "@"; //設置單元格格式為文字
      range = (Range)worksheet.get_Range("A1", "E1"); //選取Excel多個儲存格區域
      range.Merge(true); //儲存格合併
      worksheet.Cells[1, 1] = "Excel儲存格給值"; //Excel儲存格給值
      range.Font.Size = 15; //設置字體大小
      range.Font.Underline=true; //設置字體是否有底線
      range.Font.Name="黑體"; //設置字體的種類
      //設置字體在儲存格內的對其方式
      range.HorizontalAlignment=XlHAlign.xlHAlignCenter;
      range.ColumnWidth=15; //設置儲存格的寬度
      //設置儲存格的背景顏色
      range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();
      range.Borders.LineStyle=1; //設置儲存格邊框的粗細
      range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,
      System.Drawing.Color.Black.ToArgb()); //給儲存格加邊框
      range.EntireColumn.AutoFit(); //自動調整列寬
      range.HorizontalAlignment= xlCenter; // 文字水平居中
      range.VerticalAlignment= xlCenter //文字垂直居中
      range.WrapText=true; //文字自動換行
      range.Interior.ColorIndex=39; //填充顏色為淡紫色
      range.Font.Color=clBlue; //字體顏色
      xlsApp.DisplayAlerts=false; //保存Excel的時候,不彈出是否保存的視窗直接進行保存
      workbook.SaveCopyAs(temp); //填入完資訊之後另存到路徑及檔案名字

    5.程式碼範例



《圖五》Excel 函式庫引用


《圖六》讀取DB data並寫入Excel另存新檔


二、NPOI

    NPOI,顧名思義,就是POI的.NET版本。那POI又是什麼呢?POI是一套用Java寫成的函式庫,能夠幫助開發者在沒有安裝微軟Office的情況下讀寫Office 97-2003的檔案。拜 Apache 的 POI 專案之賜,Java 的開發人員可以輕鬆的存取 Excel 檔案,而反觀 .NET 陣營幾乎只能在 Excel 物件模型以及 Jet 資料庫引擎中打轉,所幸後來有一職業級團隊另外開發可直接存取 Excel 的函式庫或是將 Java 中好用的函式庫移植到 .NET 環境來,POI 專案就是一例。

    NPOI 1.x是基於POI 3.x版本開發的,NPOI目前最新發佈的版本是2.0 beta,NPOI 2.0與NPOI 1.x的最大區別在於該版本中已可提供對Office 2007格式的支援,於原先僅支援讀寫Excel檔案格式(xls)實為一大突破,但由於目前不穩定仍需等待眾多user 套用過後,共同來檢討可再加以改進的地方。

    NPOI 專案並沒有使用到 Excel 的任何東西,它直接深入 OLE Compound Document 格式內去存取資料,也可以直接控制到各種儲存格的資訊(顏色,儲存格格式與樣式等),並將它物件導向化,外部開發人員只需要利用 NPOI 提供的屬性就可以控制 Office 格式的檔案資料。

    NPOI 函式庫檔案有七個,分別是:
    ‧NPOI.DLL:NPOI 核心函式庫。
    ‧NPOI.DDF.DLL:NPOI 繪圖區讀寫函式庫。
    ‧NPOI.HPSF.DLL:NPOI 文件摘要資訊讀寫函式庫。
    ‧NPOI.HSSF.DLL:NPOI Excel BIFF 檔案讀寫函式庫。
    ‧NPOI.Util.DLL:NPOI 工具函式庫。
    ‧NPOI.POIFS.DLL:NPOI OLE 格式存取函式庫。
    ‧ICSharpCode.SharpZipLib.DLL:檔案壓縮函式庫。

    一般需要存取 Excel 97-2003 格式(.xls)的檔案時,需要使用 NPOI、NPOI.HSSF、NPOI.POIFS 與 NPOI.Util 函式庫,因此專案中要引用這四個 DLL,若要一併存取文件摘要資訊時,則也要引用 NPOI.HPSF.DLL 檔案,以取得必要的類別宣告。

    NPOI雖然能實現各項Excel操作,但函數介面及呼叫步驟,總讓.NET感覺不直觀不順手。例如: 要寫入文字到新的Cell,必須先CreateRow(),再CreateCell(),而不像在Excel VBA透過.Cells(rowIndex, colIndex)一次到位。

    以下的程式碼範例是使用官方版本1.2.5,NPOI只能存取Office 2003的版本,將下載的DLL,加入至參考並進行功能簡易測試

    1.功能操作

      //引用參考
      using NPOI.HSSF.UserModel;
      using NPOI.HPSF;
      using NPOI.POIFS.FileSystem;
      using NPOI.SS.UserModel;
      //儲存格 格式宣告
      HSSFWorkbook workbook = new HSSFWorkbook();
      HSSFSheet sheet = workbook.CreateSheet("sample");
      HSSFCell cell = null;
      HSSFCellStyle borderStyle = workbook.CreateCellStyle();
      HSSFCellStyle fontStyle = workbook.CreateCellStyle();
      //Style設定
      borderStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
      fontStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
      //字型大小
      font = workbook.CreateFont();
      font.FontHeightInPoints = 14;
      font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
      fontStyle.SetFont(font);
      cell = sheet.CreateRow(1).CreateCell(0);
      cell.CellStyle = fontStyle;
      cell.SetCellValue("字型大小14粗體");
      //合併儲存格
      cell = sheet.CreateRow(2).CreateCell(0);
      cell.SetCellValue("合併儲存格");
      cell.CellStyle = spanStyle;
      sheet.AddMergedRegion(new Region(2, 0, 3, 1));
      //框線設定
      cell = sheet.CreateRow(5).CreateCell(1);
      cell.SetCellValue("邊框");
      cell.CellStyle = borderStyle;
      //插入分頁
      sheet.SetRowBreak(cell.RowIndex);
      P.S. 使用NPOI要注意的地方是如果你要設定每個儲存格不同的儲格格式,需要建立多個HSSFCellStyle並且將它指派給儲存格

    2.程式碼範例


《圖七》寫入Excel另存新檔

三、EPPLUS

    EPPlus是一個起始於2009年底的Open Source專案,目標鎖定在伺服器端產生Office Open XML Excel檔(Excel 2007/2010的xlsx,不包含Excel 2003 xls),API規格更符合.NET風格,主要的限制是無法向下相容到Excel 2003,從2.9.0.1版可支援Charts (Except Bubble-, Radar-, Stock- and Surface charts) 可提供統計圖表功能。

    這套函式庫和目前使用比較多的NPOI有很多相同的地方,但目前中譯說明甚少,如需查閱詳細的使用方法及範例,需到官網或英譯網站比較會有較完整的資料以供使用。

    使用EPPlus撰寫的程式碼範例是用官方版本3.1.2,EPPluse適用Office 2007&2010,注意只支援.net 3.5以上的環境,把下載的DLL加入到參考並進行功能簡易測試。

    1.功能操作

      //引用參考
      using OfficeOpenXml;
      using OfficeOpenXml.Drawing;
      using OfficeOpenXml.Drawing.Chart;
      using OfficeOpenXml.Style;
      //新增sheet1
      var worksheet = package.Workbook.Worksheets.Add(“sheet1");
      //設定儲存格 格式
      worksheet.Cells.Style.Numberformat.Format= "0.00";
      //字型設定
      worksheet.Cells.Style.Font.Name= “正楷”;
      worksheet.Cells.Style.Font.Color
      worksheet.Cells.Style.Font.Size
      //文字對齊
      worksheet.Cells.Style.HorizontalAlignment=ExcelHorizontalAlignment.Center;
      worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;

      //合併儲存格
      worksheet.Cells[int fromRow, fromCol, int toRow,int toCol].Merge = true;
      //框線設定
      worksheet.Cells.Style.Border.Left.Style= ExcelBorderStyle.Thin ;
      worksheet.Cells.Style.Border.Right.Style= ExcelBorderStyle.Thin;
      worksheet.Cells.Style.Border.Top.Style= ExcelBorderStyle.Thin;
      worksheet.Cells.Style.Border.Bottom.Style= ExcelBorderStyle.Thin;
      //儲存格 自動適應大小
      worksheet.Cells.Style.ShrinkToFit= true;

    2.程式碼範例


《圖八》寫入Excel另存新檔


效能分析

一、.NET內建元件的方式(Microsoft.Office.Interop.Excel)讀取Excel

    優點:可以非常靈活的讀取Excel中的資料

    缺點:
    1.如果是Web網站部署在IIS上時,還需要伺服器機子已安裝了Excel,有時候還需要為配置IIS許可權。最重要的一點因為是基於儲存格方式讀取的,所以資料很慢(曾做過試驗,直接讀取千行、200多列的檔,直接讀取耗時15分鐘。即使採用多執行緒分段讀取來提高CPU的利用率也需要8分鐘。)
    2.如執行多次報表印出,可能會在工作管理員中process中殘留多個excel.exe占用大量CPU及memory,導致系統處理的效能隨著操作日益緩慢。

二、NPOI

    優點:讀取Excel速度較快,讀取方式操作靈活性。

    缺點:只支持2003(.xls),2007以上(.xlsx)的無法讀取。Excel 2003 單一試算表資料如果超過65535筆會造成困擾,需要做分頁。

三、EPPLUS

    優點:讀取Excel速度快,匯出的檔案容量小。

    缺點:開啟現有xlsx檔案後,無分是否編輯後儲存,再使用Excel開啟常會出現錯誤訊息(Excel 在通訊錄.xlsx 中找到無法讀取的內容…)。

實際範例評測

兩種元件筆者都分別跑了60000左右的筆數,這次的目的是寫檔案的速度,要扣除掉Query執行時間‧透過 VS2010 效能精靈檢測效能如下:

一、NPOI處理流程檢測分析報告

《圖九》

二、EPPLUS 處理流程檢測分析報告

《圖十》

三、時間效能比較

《圖十一》

四、空間容量比較

《圖十二》

五、小結

  • 在執行速度方面:NPOI小贏一點。

  • 在檔案大小方面:EEPLus大勝。

  • 在撰寫感受方面:目前撰寫程式尚未到深入階段,但現階段我個人認為EEPlus程式碼編寫方式較為流暢直觀,不過隨著NPOI於2013年推出2.0 beta版本可支援Excel2007(.xlsx)讀寫檔案,改善了它最大的缺點,就留待各位評論了。

  • 在資源支援方面:NPOI獲勝,因EEPlUS無論說明或範例簡介目前大多還是原文較多,但NPOI已有中文的完整檔。


問題匯總
  • NPOI 哪個版本才能支援 excel 2007
    --> 1.3beta版

  • NPOI 向下相容性嗎
    --> 不完全是,需自行加入新版dll檔參考後重新編譯測試

  • NPOI 可否藉由 程式中添加 chart 圖檔 或 公司logo
    --> 可

  • Excel 資源被占用 不能給多位User 同一時段編輯
    --> 目前暫且無法解決

  • 在測試程式前,檢查測試機電腦是否有先做好相關設定
    -->可參考 (文章 : The blog of typewriter職人-- [ASP.net] 讀寫Excel的相關設定 ) 關於遇到的問題和處理程序敘述非常詳細。

  • Com元件太慢 當資料很多時 佔用太多資源
    --> 使用 VS2010 效能分析功能,檢查記憶體、處理時間的分配在哪段區域花費最多(如:從資料來源撈取Data、資料轉型或Excel 塞值方式 等等)

  • 使用NPOI要注意的地方是如果你要設定每個儲存格不同的儲格格式,需要建立多個HSSFCellStyle並且將它指派給儲存格

  • Excel如果 cell為空白
    -->使用NPOI讀寫時,會因為讀取為Null值發出error訊息,需先新增Row和Cell設定初始值後才可被讀寫,這個地方不是很人性化的設計。

  • EPPlus 3.1.2 Bug Fixing(開啟使用EPPlus編輯過的Excel2007檔案)
    --> 可參考(http://blog.darkthread.net/post-2012-12-05-epplus-3-1-2-bug.aspx)此篇文章的解法,或者下載新版 dll檔查看是否已有更新。


總結

VS參考中沒有主動提供Excel API不過常用Microsoft.Office.Interop.Excel即可編輯,Microsoft.Office.Interop.Excel 操作Excel功能對於初學者而言已經夠多,使用第三方開發的API 對於我而言則更像是一種新的嘗試,對於能達到目的的方法能知道越多越好,更能從中比較出不同條件的環境下,哪種方法才是最適合的。

再者Microsoft.Office.Interop.Excel是要付費的,還要考慮部署的環境是否有安裝,NPOI和EPPlus API則不需要。每個元件都有自己的優點,要涵蓋所有的需求,應該不容易,選擇自己適用的就好。

Enjoy it!


參考資料

EPPlus 官網 http://epplus.codeplex.com/

NPOI 官網 http://npoi.codeplex.com/

http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx excel 2007

http://office.microsoft.com/zh-tw/excel-help/HP005199291.aspx excel 2003

(程式碼效能剖析)
http://www.syscom.com.tw/ePaper_Content_EPArticledetail.aspx?id=125&EPID=166&j=3

(讀寫Office Excel之前要做的相關設定)
http://www.dotblogs.com.tw/shadow/archive/2011/05/02/24043.aspx

(MSDN for NPOI)
http://msdn.microsoft.com/zh-tw/ee818993.aspx

(Excel利器—NPOI VS EPPLUS)
http://www.dotblogs.com.tw/kkman021/archive/2012/04/21/71692.aspx