POI导出报表总结

监控报表基于POI导出EXCEL总结

POI操作EXCEL对象
HSSF：操作Excel 97(.xls)格式
XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL内存占用高于HSSF
SXSSF:从POI3.8 beta3开始支持，基于XSSF，低内存占用。
1. 实现原理
基于EXCEL配置模板进行报表导出，实现导出EXCEL字段灵活配置及格式的丰富化。

模板格式
根据程序设定的模板，先解析模板，将参数（以$P开头）单元格、静态数据（无需变更）单元格及数据列（以$F开头）单元格分别存放到对应的集合对象。再根据准备好的EXCEL数据,生成一个EXCEL对象，根据参数单元格集合、静态数据单元格集合及数据列单元格集合进行数据填充，最后将EXCEL转换成流的形式响应到浏览器,提供给用户下载。
2. 监控报表导出EXCEL历程
 使用POI的HSSF对象，生成Excel 97(.xls)格式，数据库查询不进行分页、生成的EXCEL不经过压缩直接导出。
线上问题：负载服务器转发请求到应用服务器阻塞。
 数据库分页查询、并在服务端对生成的EXCEL进行压缩后导出
线上问题：不能满足生产需求、配置framework-agent.jar后，LIST集合超过10万条异常，不能导出。
 改用XSSF操作EXCEL，增加最大条数，并发量控制
线上问题：内存溢出
 使用SXSSF对象操作EXCEL,对程序进行优化、对文件流极端情况下是否关闭进行检查,
线上问题：暂时无
3. 优化总结
 通过数据库查询导出报表时，数据库要进行分页查询，分多次生成报表（并非生成多个excel文件或者sheet页，而是追加的形式生成一个sheet页）。
如果未分页，一次查出上万条数据，甚至更高，会长时间占用数据库连接，导致系统并发量下降。另外、由于一次加载数据过多（数据库查询的数据通常会放入到java的一个集合），会长时间占用虚拟机的堆内存，直到生成EXCEL结束，GC才有可能对此集合对象进行回收，这样并发量大时，很容易造成堆内存溢出。在数据库分页查询过程中，每页建议在5000左右，实践证明，生成EXCEL过程，时间主要花费在了数据库查询上，写EXCEL是很快的，所以如果每页条数太小，要查询多次数据库，数据库查询是比较耗时的，这样整体生成EXCEL的时间要长。
 如果系统存在大数据量报表导出，则考虑使用POI的SXSSF进行EXCEL操作。
HSSF生成的Excel 97(.xls)格式本身就有每个sheet页不能超过65536条的限制。
XSSF生成Excel 2007 OOXML (.xlsx)格式，条数增加了，但是导出过程中，内存占用率却高于HSSF.
SXSSF是自3.8-beta3版本后，基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中，这样内存中只保存了少量的EXCEL行进行操作。
 导出数据量大，服务器端要对导出的EXCEL进行压缩后，再转成二进制流，响应给浏览器
EXCEL的压缩率特别高，能达到80%，12M的文件压缩后才2M左右。 如果未经过压缩、不仅会占用用户带宽，且会导致负载服务器（apache）和应用服务器之间，长时间占用连接(二进制流转发)，导致负载服务器请求阻塞，不能提供服务。
 如果应用使用进思的framework-agent.jar，尽量不要使用HSSF对象操作EXCEL
HSSF操作EXCEL对象时，内部会使用一个List集合，存放EXCEL的所有单元格对象，这时就要注意所操作的EXCEL行数*列数，不能超过framework-agent.jar所设置的数量。
 一定要注意文件流的关闭
打开一个流文件，不能只想着在正常情况下正常关闭，一定要注意在各种异常情况下，把流文件给关闭，否则很容易造成内存泄露。在关闭流文件时，建议在调用close方法后，再将该文件置为null。因为在使用中发现，在内部对文件流关闭后，再在外围判断该文件流对象是否为空，不为空调用close方法，仍然会被执行。所以为保万无一失，在调用close方法后，在将其设置为Null。
 防止前台（页面）连续触发导出EXCEL
导出大数据量的EXCEL，在后台是比较耗时的，也比较耗IO资源，这时应避免用户在等待的过程中，再次触发导出EXCEL操作，所以在触发导出EXCEL后，应将导出按钮置为不可用状态。（一旦触发导出EXCEL，TOMCAT导出EXCEL线程不会因为用户关掉页面或者转向其他页面而停止，会直至整个过程执行完）
 导出并发量限制及最大条数限制
导出EXCEL过程,IO操作及循环写入EXCEL比较耗操作系统资源，所以要限制每个实例导出EXCEL的并发数，监控报表并发量限制为15，当超过15时，会给用户提示，引导其重新发起导出EXCEL，这样可能会负载到其他实例，进行导出。
最大条数限制，是根据业务需求，做出合理的设置。导出的EXCEL，如果数据量过大，导出过程中耗时长，占用大量的系统资源，且导出来后，庞大的数据量，打开都比较困难，更不用说查看的意义了。所以要根据用户提供的导出参数，要首先查出数据条数，如果条数过大，直接给用户提示，避免在极端情况或者不正常情况，浪费系统资源。

监控报表通过以上优化、现在导出报表基本稳定、可以支持最大15万条数据导出，不再出现内存溢出、阻塞响应等情况。

评论

2 楼 jveqi 2015-02-07  

1 楼 liuhu2967 2014-01-14  

大哥，我想问你个问题，防止前台（页面）连续触发导出EXCEL ，关于这个，我也打算这么做，但怎么才知道导出已经完成了，再让按钮恢复为可用状态，不然的话，我如果需要继续导出，就得刷新页面。