比NPOI更討喜的Excel元件-EPPlus!
27 |
前陣子發表 【潛盾機】將檔案結構匯成Excel文件,從網友佑翔的留言(特此感謝),認識了一顆被我錯過的l好元件 -- EPPlus!
NPOI源於POI,在很多介面設計上,帶點Java的觀點與風格,雖然能實現各項Excel操作,但函數介面及呼叫步驟,總讓.NET老鳥感覺不順手,就像用筷子吃手扒雞一樣彆扭。例如: 要寫入文字到新的Cell,必須先CreateRow(),再CreateCell(),而不像在Excel VBA透過.Cells(rowIndex, colIndex)一次到位。
LinqToExcel的出現為讀取Excel提供方便的額外選擇,能用熟悉的LINQ語法查詢Excel內容是件暢快的事,只可惜LinqToExcel只限於讀取,要產生Excel,還是得回歸NPOI。
EPPlus是一個起始於2009年底的Open Source專案,目標鎖定在伺服器端產生Office Open XML Excel檔(Excel 2007/2010的xlsx,不包含Excel 2003 xls),提供比NPOI更直覺、更簡便的API介面! 用.Cells[rowIndex, colIndex]就能直接存取欄位,甚至用.Cells[r1, c1, r2, c2]就能取得一段選取範圍,再一口氣改變它們的樣式;而要指定字型顏色時,使用Cells[…].Style.Font.Color.SetColor(Color.Red)就能搞定,不像NPOI需要CreateFont(), CreateCellStyle(), SetFont(), SetCellStyle()一長串操作。這才是.NET客心中理想的好元件呀~~
不過我不禁好奇,為何先前很少聽人提起,幾無知名度。私自揣摩,猜想可能與EPPlus只支援xlsx,無法相容於Excel 2003的xls格式,在需顧及不特定使用群時會有Excel版本門檻的考量。(雖然微軟提供免費的Excel檢視工具,但需要額外安裝仍會有部署面的考量) 另一方面,這個元件採用LGPL授權,代表如果要包含在產品中散佈,產品也必須Open Source(非100%要Open Source,授權限制可參見下方newbie的留言),也會造成一些軟體廠商採用上的疑慮。再者,微軟本身提供Open XML SDK,已涵蓋Excel檔的操作,有些開發者已直接採用SDK,沒想到再花時間評估更便捷元件(呼應了我在前篇文章的感嘆,有小錦囊後真的會讓人錯過其他更犀利的選擇),也可能是原因之一。
依我的看法,隨著時光飛逝,舊版Excel相容的重要性會逐年下降,而應用於網站時,只要不是販售網站程式本體,倒不必擔憂LGPL的限制(可參照先前的討論)。至於與Open XML SDK相比,初看語法,EPPlus確實如網友所說,具有"只見新人笑,不見舊人哭"的魅力! 真的可以跟NPOI說Bye Bye囉,在直覺易用上也已把Open XML SDK比下去。總評之後,EPPlus應是可以安心採用的解決方案。
好東西當然要也要實測體驗一下威力,就同樣用上回的檔案結構匯出Excel案例吧! 這回改用EPPlus來處理寫成Excel的部分。
要在專案中引用EPPlus,最快的方法一樣是透過NuGet: (還不會用NuGet的人,有沒有覺得自己已經輸在起跑點上?)
參考Zeeshan Umar的文章,三兩下就改好程式:
//加入擴充方法: SetQuickStyle,指定前景色/背景色/水平對齊
private static void SetQuickStyle(this ExcelRange range,
Color foreColor,
Color bgColor = default(Color),
ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Left)
{
range.Style.Font.Color.SetColor(foreColor);
if (bgColor != default(Color))
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(bgColor);
}
range.Style.HorizontalAlignment = hAlign;
}
/// <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
using (ExcelPackage p = new ExcelPackage())
{
ExcelWorksheet sheet = p.Workbook.Worksheets.Add("Site Tree");
int colIdx = 1;
foreach (string colName in "Path;File;Description".Split(';'))
{
sheet.Cells[1, colIdx++].Value = colName;
}
//修改標題列Style
sheet.Cells[1, 1, 1, 3].SetQuickStyle(Color.Yellow, Color.Green,
ExcelHorizontalAlignment.Center);
int rowIdx = 2;
foreach (var item in list)
{
//若bypass檢測傳回true,則略過該筆
if (filter != null && filter(item.Path))
continue;
//將Path放在第一欄(稍後隱藏)
sheet.Cells[rowIdx, 1].Value = item.Path;
//存入檔名或目錄名
sheet.Cells[rowIdx, 2].Value =
new String(' ', item.Layer * 4) + item.Name;
if (item.IsFolder)
{
sheet.Cells[rowIdx, 2].SetQuickStyle(Color.Blue);
}
rowIdx++;
}
//第一欄隱藏
sheet.Column(1).Hidden = true;
//自動伸縮欄寬
sheet.Column(2).AutoFit();
sheet.Column(2).Width += 2;
sheet.Column(3).Width = 50;
//寫入檔案
p.SaveAs(new FileInfo(excelPath));
}
一模一樣的結果,跟上回的NPOI寫法相比,是不是清爽順眼很多呢? 在以xlsx為主的網站應用場合,大家就大膽用下去吧!
Comments
# by newbie
LGPL 的 library 用了,你的產品不一定要 open source. 1. 你沒有修改該 library 只是透過 compile time/rutime linking,不用 open source. 2. 修改該 library 的話,修改的那一段要 open source, 以及連接該修改的那一大段妳產品的 source code 都要 open. 3. LGPL 很大宗的應用是 NHibernate/Hibernate,可以這個當例子。敬請黑大參考。
# by Ben Pann
會使用 NPOI 還有一個很大的原因,就是能產生有圖表的 excel 檔。做系統通常會提供報表下載,但是當報表內除了統計表格,還有可能用 ms chart 元件或其他元件產生的統計圖形(長條、圓餅、折線.....)時,要讓使用者能下載具有圖形結果的 excel 檔,就不容易了(微軟居然也沒提供這種元件,是沒想到嗎?)。 不曉得黑大介紹的 EPPlus 是否有支援圖形寫入 excel 相關的方法或函數?
# by Jeffrey
to newbie, 感謝您的專業補充,已加註於內文。 to Ben Pann, EPPlus從2.9.0.1版可支援Charts (Except Bubble-, Radar-, Stock- and Surface charts),REF: http://epplus.codeplex.com/wikipage?title=FAQ
# by jaw
EPPlus的前身是ExcelPackage. 因為GPL被嫌棄到不行 (所以才改成LGPL?) http://excelpackage.codeplex.com/ NPOI最大的遺憾就是不能產生Chart的吧. 只能事先建好已經拉好圖表的template, 再把data塞進去. http://www.zachhunter.com/2010/05/npoi-excel-template/
# by Ben Pann
更正一下,NPOI不是只有事先建好已經拉好圖表的template, 再把data塞進去一種方式而已喔 還可以利用 chart 元件,存成圖形直接寫入 excel 喔,這樣就不用事先建立地一種方法的 template。
# by jaw
Ben大, 請問如何將MS Chart塞到excel, 可以跪求程式碼嗎. NPOI如果可以用這種方式來產生Chart的話那不就無敵了. 謝謝 :)
# by 佑翔
我的名字出現在這裡真是一個殊榮阿!! 感謝黑大您的不嫌棄!!
# by LZ
EPPLUS的一个缺陷是,ExcelStyle.TextRotation的值不能设置为255(只能是0~180),因此不能实现文字的垂直排列,对于中文表格来说不免有些不便。但在NPOI中可以直接设置HSSFCellStyle.Rotation=255来实现文字的垂直排列。 不知在EPPLUS中是否有其它方式解决这一问题。如果有那位知道,请告知:zhiliu001@sina.com,谢谢。
# by LZ
最近也在用EPPLUS,但发现一个问题就是ExcelStyle.TextRotation的值不能设置为255(0~180)。因此不能实现文字的垂直排列,而NPOI中可以将此值设为255,实现垂直排列。不知在EPPLUS中如何实现此方式,如果那位知道,请告知:zhiliu001@sina.com,谢谢!
# by LZ
现在暂时只能用EPPLUS生成excel后,再用Open XML SDK来修改TextRotation的值,来实现文字的垂直排列。太麻烦了,如果能在EEPLUS中解决就好了...
# by sam
查了一下LGPL的授權…分享一下。 http://www.openfoundry.org/news/519 只能用2007以上的版本哦? 好可惜,現在的客戶好像還要向下相容… 2003的功能已有,現在又提出要能用2010,orz, 頗麻煩,好像沒法通吃03-10.
# by kerry
Acey.ExcelX接口简单,功能丰富,大家可以去http://www.aceyoffice.com下载最新版本,支持Excel2003/Excel2007/Excel2010,并支持导出Html和PDF。
# by dxf
Acey.ExcelX真的不錯,具有NOPI和EPPlus的兩者優點:(支持xls,xlsx,pdf,image,html,csv,txt等文件格式,而且語法簡潔),同時又具有兩者都不具備的特性:各種類型的Chart. 而且評估版版是免費的,可以無限期使用(評估版過期之後可以再下載最新版本又可以使用了,貌似優點麻煩,但是免費嘛).如果嫌麻煩可以購買正版羅,而且很便宜.網址(www.aceyoffice.com)
# by 風築殘年
EPPlus 有辦法開啟既有的 Excel 檔案 然後新增一個 Sheet 之後再存檔呢? 想改 EPPlus, 但是如果 EPPlus 做不到這樣的功能.. 那個 NPOI 可以做到嗎? 原本都是透過 OleDB 來建立 Excel 檔案.. 最近遇到 oleDB 在 create table 時沒辦法建立超過 255 個欄位...只好來找找其他 ThirdParty 的元件了...
# by Jeffrey
to 風築殘年, EPPlus開啟現成xlsx,新增Sheet再存檔應屬基本操作,理應可行,但手邊沒有現成範例,在網路找到一個案例: http://www.blueshop.com.tw/board/FUM20050124192253INM/BRD20140425110024LX8.html
# by 風築殘年
那個案例..呃..就是本人的測試! 先前的程式習慣...採用 FileStream 來處理 在測試 EPPlus 的時後,也是採用 FileStream 來操作 結果怎麼存檔都只有第一個 Sheet 會成功.. 之後的任何新增,完全沒辦法寫入。 最後改用了 FileInfo 才正常. 只是到現在還是不了解...為什麼使用 FileStream 沒有辦法更新存檔.
# by 迷惘
Hi,Jeffrey你好, 有個問題想要請教,不知道你方不方便...我最近在工作上有需要用到匯入excel的地方, 爬文後,覺得NPOI正好有符合開發的需求,我的問題是,如果只是引用NPOI,並不對他做任何修改, 關於版權得問題需不需做些什麼聲明...看了NPOI的官網License還是不太明白如何做, 因為是公司產品,所以會牽扯到銷售的問題,所以不知道能不能直接引用...是否可以請你指點...謝謝
# by Jeffrey
to 迷惘,NPOI採用Apache License(https://npoi.codeplex.com/license),可作為商業應用(甚至允許修改),但需要附上相關授權聲明,細節可以參考這篇文章(http://inspire.twgg.org/internet/trends/item/74-comparison-of-five-kinds-of-standard-open-source-license-bsd-apache-gpl-lgpl-mit.html)
# by Henry
Dear jeffrey 請問jeffrey epplus 是否可以用ASP 來呼叫執行,目前公司的內部網站是用ASP 要加上資料匯出至EXCEL ,之前都是用html格式轉存為.xls 但是在每次開啟時都會跳出警告視窗,每次都要按確認才能開啟,看到您的文章介紹,我在想這個元件是否能支援 ASP ,而不是ASP.NET 。謝謝
# by Jeffrey
to Henry, ASP無法直接呼叫EPPlus,但可透過CCW方式,自己寫一個簡單的.NET類別包成COM+物件,供ASP呼叫,再於其內部使用EPPlus完成Excel操作。CCW參考:http://msdn.microsoft.com/zh-tw/library/f07c8z1c(v=vs.110).aspx
# by 陳大胖
請問黑暗大,EPPlus能夠達到單一Cell內可產生H2O,但這個"2"是需下標,這種需求嗎?
# by Jeffrey
to 陳大胖,應該是可行的,請參考 http://epplus.codeplex.com/discussions/263388
# by Stella
您好, 雖然我也試著搜尋找過了,但實在找不出解答。所以想要請問:目前利用EPPlus確定可以操作.xlsx檔,不知道如果是.xlsm檔是否也可以利用EPPlus操作並保留原本存在的巨集?
# by Jeffrey
to Stella, 應該可行。查到CodePlex有網友寫了開xlsm,移除巨集,存成xlsm或xlsx的範例,EPPlus有識別、移除巨集的能力,要保留應該也不會是問題:https://epplus.codeplex.com/discussions/429948
# by player
NPOI可以開現成的檔案, 塞資料進去, 但是裏頭的計算公式卻不會自己算結果, 必須存檔後, 另外用Excel去開, 才會計算公式的內容 至於EPPlus 沒用過, 不予置評
# by 路過
to player : 使用NPOi塞資料進Excel,Excel的公式計算的問題 1. 2007之前會計算沒問題 2. 2010因為其安全性設定故要點「啟用編輯」進入編輯模式才會計算 3. 如果發現12點都不行,請於匯出之前呼叫函式進行計算 sheet.ForceFormulaRecalculation = true;
# by Mark Flayd
I am using www.ZetExcel.com Try it It helped me a lot !