在Asp.Net程序開發(fā)中熬荆，經(jīng)常會(huì)遇到大批量數(shù)據(jù)導(dǎo)出到Excel報(bào)表的情況钞螟，特別是對于業(yè)務(wù)型的平臺兔甘，導(dǎo)出的數(shù)據(jù)少則幾百行，多則幾十萬行鳞滨，此時(shí)洞焙，尋找一個(gè)操作優(yōu)雅且易用（對開發(fā)者友好）高效（對使用者友好）的Excel操作插件顯的很有必要。如果這一步?jīng)]有選擇好拯啦，出現(xiàn)問題后“抓耳撓腮”尋找解決辦法的情況十有八九澡匪，那可就非常不優(yōu)雅了，顯然這不符合我們本文的主題——“優(yōu)雅”褒链。
一唁情、方式對比
通過以往的經(jīng)驗(yàn)，結(jié)合米多來發(fā)的業(yè)務(wù)需求碱蒙，我嘗試了目前Asp.Net平臺中常用的幾種Excel操作方式：
（1）MS Office COM 組件：使用微軟官方Microsoft.Office.Interop.Excel組件操作Excel荠瘪。
（2）NPOI 庫： 就是POI的.NET版本夯巷。那POI又是什么呢赛惩？POI是一套用Java寫成的庫，能夠幫助開發(fā)者在沒有安裝微軟Office的情況下讀寫Office 97-2003的文件趁餐，支持的文件格式包括xls, doc, ppt等喷兼。
（3）EPPlus 庫： EPPlus是一個(gè)使用Open Office XML（xlsx）文件格式，能讀寫Excel 2007/2010文件的開源組件后雷。
經(jīng)過在實(shí)際業(yè)務(wù)場景下進(jìn)行測試季惯，不斷改造吠各，效果比較，這3種方式的優(yōu)劣大致可以匯總?cè)缦拢?/p>
1勉抓、MS Office COM 組件：
優(yōu)勢：
(1)最原始的Excel操作方式贾漏，使用語法類似于VB.Net。
(2)Office中的宏操作代碼可以復(fù)用到項(xiàng)目中藕筋。
(3)支持導(dǎo)入和導(dǎo)出的解析操作纵散。
(4)導(dǎo)出的Excel會(huì)自動(dòng)分析單元格數(shù)據(jù)的格式。
(5)微軟官方Office服務(wù)隐圾，安全省心有保障伍掀。
劣勢：
(1)需要在服務(wù)器端安裝裝Office服務(wù)，并及時(shí)更新以防漏洞(依賴于微軟發(fā)布的補(bǔ)丁)暇藏；
(2)需要設(shè)定權(quán)限允許.NET訪問COM+蜜笤，如果在導(dǎo)出過程中出問題可能導(dǎo)致服務(wù)器宕機(jī)。
(3)Excel會(huì)把只包含數(shù)字的列進(jìn)行類型轉(zhuǎn)換盐碱，本來是文本型的把兔，Excel會(huì)將其轉(zhuǎn)成數(shù)值型的，比如編號000123會(huì)變成123瓮顽。
(4)導(dǎo)出時(shí)垛贤，如果字段內(nèi)容以“-”或“=”開頭，Excel會(huì)把它識別成公式趣倾，會(huì)報(bào)錯(cuò)聘惦。
(5)Excel會(huì)根據(jù)Excel文件前8行分析數(shù)據(jù)類型，如果正好你前8行某一列只是數(shù)字儒恋，那它會(huì)認(rèn)為該列為數(shù)值型善绎，自動(dòng)將該列轉(zhuǎn)變成類似1.42702E+17格式，日期列變成包含日期和數(shù)字的诫尽。
2禀酱、NPOI 庫：
優(yōu)勢：
(1)源自Java，非常出名牧嫉，應(yīng)用人群多剂跟，完全免費(fèi)，兼容Excel新舊版本（即xls格式和xlsx格式）酣藻。
(2)包含了大部分Excel的特性（單元格樣式曹洽、數(shù)據(jù)格式、公式等等）辽剧。
(3)專業(yè)的技術(shù)支持服務(wù)(24*7全天候) (非免費(fèi))送淆。
(4)同時(shí)支持Excel文件的導(dǎo)入和導(dǎo)出。
(5)不需要在服務(wù)器上安裝微軟的Office怕轿，可以避免版權(quán)問題偷崩。
(6)使用起來比Office PIA的API更加方便辟拷，更人性化。
(7)不用專人維護(hù)阐斜，NPOI 團(tuán)隊(duì)會(huì)不斷更新衫冻、改善NPOI，節(jié)省成本谒出。
劣勢：
(1) 因?yàn)槠鹪从贘ava的POI項(xiàng)目羽杰，所以接口方面依然透露著Java的風(fēng)格，對.NET開發(fā)者來說不夠友好到推，例如：要在一個(gè)單元格中寫入數(shù)據(jù)時(shí)考赛，必須先CreateRow()，再CreateCell()才能寫入莉测，不能使用類似Cells[rowIndex, colIndex]=value之類的語句一步到位颜骤。
(2) 不支持大批量數(shù)據(jù)的導(dǎo)出，同時(shí)導(dǎo)出的數(shù)據(jù)量過大時(shí)捣卤，會(huì)導(dǎo)致內(nèi)存溢出的問題。一個(gè)sheet最多容納65536行數(shù)據(jù)董朝，故數(shù)據(jù)量大于65536時(shí)需要分多個(gè)sheet鸠项，較為麻煩。
(3) 在實(shí)例化了一個(gè)WorkBook之后祟绊，最好添加一個(gè)sheet牧抽，雖然在最新版的NPOI.Net中自動(dòng)添加了遥赚，但是遇到遷移到原來版本依然會(huì)出現(xiàn)問題凫佛，所以根據(jù)建議還是需要手動(dòng)最少添加一個(gè)sheet晨炕。
(4) 在從單元格取值時(shí)需要注意單元格的類型厚满，需要用對應(yīng)的類型的方法來取單元格中的對應(yīng)類型的值遵馆，如果是不確定的類型货邓，只能是強(qiáng)制轉(zhuǎn)換成為string類型（畢竟string類型是excel中其他類型都可以轉(zhuǎn)換過來的）换况。
(5) 在獲取sheet中的某一行或者某一行的某一個(gè)單元格的時(shí)候盗蟆，需要確保已經(jīng)創(chuàng)建了該行戈二，并且取單元格還要確保創(chuàng)建了單元格觉吭，否則會(huì)報(bào)Null reference not to object 這個(gè)經(jīng)常會(huì)看到的異常信息仆邓。在外層還要加上try捕獲異常徙硅。
(6) 合并單元格是sheet的工作嗓蘑，因此還需要獲取對應(yīng)的sheet扳埂，然后調(diào)用其AddMergedRegion方法合并單元格，在合并單元格的時(shí)候岩调，不需要確保該單元格已經(jīng)存在或創(chuàng)建号枕。
(7) 在為單元格設(shè)置樣式的過程中抛姑，所有和樣式相關(guān)的類的創(chuàng)建都是通過workBook.Create(Font)..這種方式來執(zhí)行的定硝，不可以直接new一個(gè)類的實(shí)例抄肖。
(8) 當(dāng)需要把內(nèi)存中的Excel表寫到硬盤上時(shí)桌硫，需要調(diào)用workBook.write()方法尊浪，傳入一個(gè)文件流進(jìn)行創(chuàng)建工育。在這里有可能會(huì)出現(xiàn)另外一個(gè)問題旭贬，就是要?jiǎng)?chuàng)建的文件已經(jīng)被打開了怔接，這時(shí)程序就會(huì)出現(xiàn)異常，因此在調(diào)試程序的時(shí)候一定要記得打開了Excel文件以后要關(guān)閉稀轨。
(9) 還有就是文件流扼脐，在我們把Excel寫到硬盤上以后，要顯式的調(diào)用其close()方法關(guān)閉文件流奋刽。如果不關(guān)閉文件流的話瓦侮，以后就會(huì)出現(xiàn)無法重新創(chuàng)建該文件的錯(cuò)誤，并且會(huì)提示：某文件正由另一進(jìn)程使用佣谐，因此該進(jìn)程無法訪問此文件肚吏。
3、EPPlus 庫：
優(yōu)勢：
(1) 語法風(fēng)格更友好狭魂，上手簡單快捷罚攀，操作更人性化，例如用.Cells[rowIndex, colIndex]就能直接存取單元格雌澄，甚至用.Cells[r1, c1, r2, c2]就能取得一段選取范圍斋泄，要指定字型顏色時(shí)，使用Cells[…].Style.Font.Color.SetColor(Color.Red)就能搞定镐牺，不像NPOI需要CreateFont(), CreateCellStyle(), SetFont(), SetCellStyle()一長串操作炫掐。
(2) 開源組件，完全免費(fèi)睬涧，不用擔(dān)心授權(quán)問題卒废。
(3) 包含絕大部分Excel的操作特性（單元格樣式，跨行跨列宙地，凍結(jié)窗格等）摔认，且設(shè)置簡單。
(4) 支持圖表的列诱唷（直線圖参袱，折線圖，圓形圖，散布圖抹蚀，區(qū)域圖等）剿牺。
(5) 相對來說，導(dǎo)出文件體積更小环壤，節(jié)省帶寬資源晒来。
(6)支持大批量數(shù)據(jù)的導(dǎo)出操作，單個(gè)sheet能支持到20萬行數(shù)據(jù)左右郑现，不用手動(dòng)切換多個(gè)sheet操作湃崩。
劣勢：
(1) 僅支持xlsx格式的Excel文件（即不兼容Office2003版本的xls格式）。
(2) 知名度較低接箫，目前使用的人相對來說較少攒读，缺少完整的規(guī)范性參考資料，需要自己踩坑填坑辛友。
二薄扁、組件選擇
通過對比了以上3種方式之后，我決定在米多來發(fā)項(xiàng)目中采用EPPlus庫來操作Excel废累，原因主要有：
(1) 語法簡單邓梅，使用方便，貼近C#風(fēng)格邑滨，開發(fā)者更容易上手日缨。可以通過簡單的屬性設(shè)置實(shí)現(xiàn)基本的報(bào)表樣式調(diào)整驼修。
(2) 雖然不支持2003舊版xls的文件格式殿遂，但是作為互聯(lián)網(wǎng)平臺型的項(xiàng)目，這一點(diǎn)其實(shí)可以忽略不計(jì)乙各。
(3) 一次性單個(gè)sheet支持導(dǎo)出的數(shù)據(jù)量比較大墨礁，能支撐到20萬行左右，能滿足絕大多數(shù)業(yè)務(wù)場景的需求耳峦。
(4) 導(dǎo)出文件體積是3種方式中最小的恩静，能節(jié)省帶寬叹洲，提高用戶體驗(yàn)鞠值。
(5) 單元格屬性不會(huì)自作主張趟大，改變值的顯示方式炭懊，進(jìn)而影響報(bào)表數(shù)據(jù)的準(zhǔn)確性。
三熄攘、使用介紹
鑒于目前網(wǎng)絡(luò)上對于EPPlus的完整中文版的使用資料較少撕瞧，所以結(jié)合米多來發(fā)報(bào)表導(dǎo)出的實(shí)戰(zhàn)經(jīng)驗(yàn)對其進(jìn)行一次上手介紹尤蛮。
（一） 功效
不用過多解釋县匠，必須支持對Excel文檔的導(dǎo)入導(dǎo)出风科，圖表（Excel自帶的圖表基本都可以實(shí)現(xiàn)）的列印撒轮。
（二） 使用
1)下載并添加dll文件至工程中
2)在程序中添加引用
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
3)所有的操作語句需要放置在下面的using中
using (ExcelPackage package = new ExcelPackage())
{
......
}
4)添加新的sheet
varworksheet = package.Workbook.Worksheets.Add(“sheet1");
5)單元格賦值,NPOI必須先創(chuàng)建單元格,然后再給單元格賦值,而Epplus不需要,直接找到單元格進(jìn)行賦值就可以了.
worksheet.Cells[int row, int col].Value = “”;
或者
worksheet.Cells["A1"].Value = “”;
6)合并單元格（跨行跨列）
worksheet.Cells[int fromRow, fromCol, int toRow,int toCol].Merge = true;
7)獲取某一個(gè)區(qū)域
var rangeData= worksheet.Cells[fromRow, fromCol, toRow, toCol];
8)設(shè)置字體
worksheet.Cells.Style.Font.Name= “正楷”;
worksheet.Cells.Style.Font.Color=...;
worksheet.Cells.Style.Font.Size=...;
9)設(shè)置邊框的屬性
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;
10)對齊方式
worksheet.Cells.Style.HorizontalAlignment=ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
11)設(shè)置整個(gè)sheet的背景色
worksheet.Cells.Style.Fill.PatternType= ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
12)換行顯示
worksheet.Cells.Style.WrapText= true;
13)單元格自動(dòng)適應(yīng)大小
worksheet.Cells.Style.ShrinkToFit= true;
14)格式化單元格value值
worksheet.Cells.Style.Numberformat.Format= "0.00";
15)鎖定
worksheet.Cells["A1"].Style.Locked= true;
（注:此處鎖定某一個(gè)單元格的時(shí)候,只有在整個(gè)sheet被鎖定的情況下才可以被鎖定,不然加上鎖定屬性也是不起作用的贼穆。）
16)合并單元格
worksheet.Cells[rowIndex1, colIndex1,rowIndex2, colIndex2].Merge = true;
（三） 圖表列印
EPPlus另一個(gè)出色的地方就是支持圖表的列印题山。功能的實(shí)現(xiàn)很簡單，難點(diǎn)在于需求比較精細(xì)的點(diǎn)上故痊，EPPlus可能不好實(shí)現(xiàn)顶瞳，但是總的來說是比較好的一個(gè)列印圖表的工具
1)簡單介紹一下可以實(shí)現(xiàn)的圖表類型：直條圖、直線圖愕秫、圓形圖慨菱、橫條圖、散布圖豫领、區(qū)域圖的圖表抡柿。
2)使用：主要分為三步舔琅，
第 1 步：將需要顯示在圖表中的 數(shù)據(jù)列印到Excel中等恐。
第 2 步：創(chuàng)建所需要的圖表類型(折線圖為例)。
varchart = (worksheet.Drawings.AddChart("LineChart", eChartType.Line) as ExcelLineChart);
第 3 步：圖表添加第一步列印的數(shù)據(jù)區(qū)間就可以了备蚓。
chart.Series.Add(Y軸顯示的數(shù)據(jù)源,X軸顯示的數(shù)據(jù)源);
3)圖表的功能就這樣實(shí)現(xiàn)了,簡單的實(shí)現(xiàn)還是很方便的课蔬。
（此處圖表的沒有寫出具體代碼，因?yàn)橛X得代碼很簡單郊尝，只是步驟的問題二跋，上面三步走完，圖表即可完成了流昏。）
通過上面幾個(gè)步驟的摸索和設(shè)置扎即，已經(jīng)可以非常優(yōu)雅的使用EPPlus導(dǎo)出Excel報(bào)表了。在開發(fā)難度况凉，導(dǎo)出速度谚鄙，用戶體驗(yàn)上表現(xiàn)都還是很優(yōu)雅的。
四刁绒、其他說明
鑒于目前網(wǎng)絡(luò)上關(guān)于EPPlus相關(guān)的完整的規(guī)范性文檔較少闷营，有疑問的也可以參考它的官方文檔：https://archive.codeplex.com/?p=epplus
