如何优雅的进行大批量数据导出

在Asp.Net程序开发中，经常会遇到大批量数据导出到Excel报表的情况，特别是对于业务型的平台，导出的数据少则几百行，多则几十万行，此时，寻找一个操作优雅且易用（对开发者友好）高效（对使用者友好）的Excel操作插件显的很有必要。如果这一步没有选择好，出现问题后“抓耳挠腮”寻找解决办法的情况十有八九，那可就非常不优雅了，显然这不符合我们本文的主题——“优雅”。

一、方式对比

通过以往的经验，结合米多来发的业务需求，我尝试了目前Asp.Net平台中常用的几种Excel操作方式：

（1）MS Office COM 组件：使用微软官方Microsoft.Office.Interop.Excel组件操作Excel。

（2）NPOI 库： 就是POI的.NET版本。那POI又是什么呢？POI是一套用Java写成的库，能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。

（3）EPPlus 库： EPPlus是一个使用Open Office XML（xlsx）文件格式，能读写Excel 2007/2010文件的开源组件。

经过在实际业务场景下进行测试，不断改造，效果比较，这3种方式的优劣大致可以汇总如下：

1、MS Office COM 组件：

优势：

(1)最原始的Excel操作方式，使用语法类似于VB.Net。

(2)Office中的宏操作代码可以复用到项目中。

(3)支持导入和导出的解析操作。

(4)导出的Excel会自动分析单元格数据的格式。

(5)微软官方Office服务，安全省心有保障。

劣势：

(1)需要在服务器端安装装Office服务，并及时更新以防漏洞(依赖于微软发布的补丁)；

(2)需要设定权限允许.NET访问COM+，如果在导出过程中出问题可能导致服务器宕机。

(3)Excel会把只包含数字的列进行类型转换，本来是文本型的，Excel会将其转成数值型的，比如编号000123会变成123。

(4)导出时，如果字段内容以“-”或“=”开头，Excel会把它识别成公式，会报错。

(5)Excel会根据Excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702E+17格式，日期列变成包含日期和数字的。

2、NPOI 库：

优势：

(1)源自Java，非常出名，应用人群多，完全免费，兼容Excel新旧版本（即xls格式和xlsx格式）。

(2)包含了大部分Excel的特性（单元格样式、数据格式、公式等等）。

(3)专业的技术支持服务(24*7全天候) (非免费)。

(4)同时支持Excel文件的导入和导出。

(5)不需要在服务器上安装微软的Office，可以避免版权问题。

(6)使用起来比Office PIA的API更加方便，更人性化。

(7)不用专人维护，NPOI 团队会不断更新、改善NPOI，节省成本。

劣势：

(1) 因为起源于Java的POI项目，所以接口方面依然透露着Java的风格，对.NET开发者来说不够友好，例如：要在一个单元格中写入数据时，必须先CreateRow()，再CreateCell()才能写入，不能使用类似Cells[rowIndex, colIndex]=value之类的语句一步到位。

(2) 不支持大批量数据的导出，同时导出的数据量过大时，会导致内存溢出的问题。一个sheet最多容纳65536行数据，故数据量大于65536时需要分多个sheet，较为麻烦。

(3) 在实例化了一个WorkBook之后，最好添加一个sheet，虽然在最新版的NPOI.Net中自动添加了，但是遇到迁移到原来版本依然会出现问题，所以根据建议还是需要手动最少添加一个sheet。

(4) 在从单元格取值时需要注意单元格的类型，需要用对应的类型的方法来取单元格中的对应类型的值，如果是不确定的类型，只能是强制转换成为string类型（毕竟string类型是excel中其他类型都可以转换过来的）。

(5) 在获取sheet中的某一行或者某一行的某一个单元格的时候，需要确保已经创建了该行，并且取单元格还要确保创建了单元格，否则会报Null reference not to object 这个经常会看到的异常信息。在外层还要加上try捕获异常。

(6) 合并单元格是sheet的工作，因此还需要获取对应的sheet，然后调用其AddMergedRegion方法合并单元格，在合并单元格的时候，不需要确保该单元格已经存在或创建。

(7) 在为单元格设置样式的过程中，所有和样式相关的类的创建都是通过workBook.Create(Font)..这种方式来执行的，不可以直接new一个类的实例。

(8) 当需要把内存中的Excel表写到硬盘上时，需要调用workBook.write()方法，传入一个文件流进行创建。在这里有可能会出现另外一个问题，就是要创建的文件已经被打开了，这时程序就会出现异常，因此在调试程序的时候一定要记得打开了Excel文件以后要关闭。

(9) 还有就是文件流，在我们把Excel写到硬盘上以后，要显式的调用其close()方法关闭文件流。如果不关闭文件流的话，以后就会出现无法重新创建该文件的错误，并且会提示：某文件正由另一进程使用，因此该进程无法访问此文件。

3、EPPlus 库：

优势：

(1) 语法风格更友好，上手简单快捷，操作更人性化，例如用.Cells[rowIndex, colIndex]就能直接存取单元格，甚至用.Cells[r1, c1, r2, c2]就能取得一段选取范围，要指定字型颜色时，使用Cells[…].Style.Font.Color.SetColor(Color.Red)就能搞定，不像NPOI需要CreateFont(), CreateCellStyle(), SetFont(), SetCellStyle()一长串操作。

(2) 开源组件，完全免费，不用担心授权问题。

(3) 包含绝大部分Excel的操作特性（单元格样式，跨行跨列，冻结窗格等），且设置简单。

(4) 支持图表的列印（直线图，折线图，圆形图，散布图，区域图等）。

(5) 相对来说，导出文件体积更小，节省带宽资源。

(6)支持大批量数据的导出操作，单个sheet能支持到20万行数据左右，不用手动切换多个sheet操作。

劣势：

(1) 仅支持xlsx格式的Excel文件（即不兼容Office2003版本的xls格式）。

(2) 知名度较低，目前使用的人相对来说较少，缺少完整的规范性参考资料，需要自己踩坑填坑。

二、组件选择

通过对比了以上3种方式之后，我决定在米多来发项目中采用EPPlus库来操作Excel，原因主要有：

(1) 语法简单，使用方便，贴近C#风格，开发者更容易上手。可以通过简单的属性设置实现基本的报表样式调整。

(2) 虽然不支持2003旧版xls的文件格式，但是作为互联网平台型的项目，这一点其实可以忽略不计。

(3) 一次性单个sheet支持导出的数据量比较大，能支撑到20万行左右，能满足绝大多数业务场景的需求。

(4) 导出文件体积是3种方式中最小的，能节省带宽，提高用户体验。

(5) 单元格属性不会自作主张，改变值的显示方式，进而影响报表数据的准确性。

三、使用介绍

鉴于目前网络上对于EPPlus的完整中文版的使用资料较少，所以结合米多来发报表导出的实战经验对其进行一次上手介绍。

（一） 功效

不用过多解释，必须支持对Excel文档的导入导出，图表（Excel自带的图表基本都可以实现）的列印。

（二） 使用

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必须先创建单元格,然后再给单元格赋值,而Epplus不需要,直接找到单元格进行赋值就可以了.

worksheet.Cells[int row, int col].Value = “”;

或者

worksheet.Cells["A1"].Value = “”;

6)合并单元格（跨行跨列）

worksheet.Cells[int fromRow, fromCol, int toRow,int toCol].Merge = true;

7)获取某一个区域

var rangeData= worksheet.Cells[fromRow, fromCol, toRow, toCol];

8)设置字体

worksheet.Cells.Style.Font.Name= “正楷”;

worksheet.Cells.Style.Font.Color=...;

worksheet.Cells.Style.Font.Size=...;

9)设置边框的属性

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)设置整个sheet的背景色

worksheet.Cells.Style.Fill.PatternType= ExcelFillStyle.Solid;

worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);

12)换行显示

worksheet.Cells.Style.WrapText= true;

13)单元格自动适应大小

worksheet.Cells.Style.ShrinkToFit= true;

14)格式化单元格value值

worksheet.Cells.Style.Numberformat.Format= "0.00";

15)锁定

worksheet.Cells["A1"].Style.Locked= true;

（注:此处锁定某一个单元格的时候,只有在整个sheet被锁定的情况下才可以被锁定,不然加上锁定属性也是不起作用的。）

16)合并单元格

worksheet.Cells[rowIndex1, colIndex1,rowIndex2, colIndex2].Merge = true;

（三） 图表列印

EPPlus另一个出色的地方就是支持图表的列印。功能的实现很简单，难点在于需求比较精细的点上，EPPlus可能不好实现，但是总的来说是比较好的一个列印图表的工具

1)简单介绍一下可以实现的图表类型：直条图、直线图、圆形图、横条图、散布图、区域图的图表。

2)使用：主要分为三步，

第 1 步：将需要显示在图表中的 数据列印到Excel中。

第 2 步：创建所需要的图表类型(折线图为例)。

varchart = (worksheet.Drawings.AddChart("LineChart", eChartType.Line) as ExcelLineChart);

第 3 步：图表添加第一步列印的数据区间就可以了。

chart.Series.Add(Y轴显示的数据源,X轴显示的数据源);

3)图表的功能就这样实现了,简单的实现还是很方便的。
（此处图表的没有写出具体代码，因为觉得代码很简单，只是步骤的问题，上面三步走完，图表即可完成了。）

通过上面几个步骤的摸索和设置，已经可以非常优雅的使用EPPlus导出Excel报表了。在开发难度，导出速度，用户体验上表现都还是很优雅的。

四、其他说明

鉴于目前网络上关于EPPlus相关的完整的规范性文档较少，有疑问的也可以参考它的官方文档：https://archive.codeplex.com/?p=epplus