性能优化:百万行Excel数据导出（十万条数据导出excel要多久）
前言
通常情况下我们要操作Microsoft office文件，Apache POI是一个不错的选择，POI提供了丰富的API给Java程序对Microsoft Office格式档案读和写的功能。
Excel文件的读写
POI提供了HSSF、XSSF以及SXSSF三种方式操作Excel。他们的区别如下：
- HSSF：是操作Excel97-2003版本，扩展名为.xls，一个sheet最大行数65536，最大列数256。
- XSSF：是操作Excel2007版本开始，扩展名为.xlsx，一个sheet最大行数1048576，最大列数16384。
- SXSSF：是在XSSF基础上，POI3.8版本开始提供的一种支持低内存占用的操作方式，扩展名为.xlsx。
需求背景
在实际应用中，用户导出的一个sheet的数据量超过130万，超出了.xlsx文件关于行数的限制。Developer基于XSSF去读取，一次性把百万条数据读入内存，每次导出需要时间20分钟左右，服务器CPU和内存会飙的很高，高并发下同时占用数据库连接长期不释放，结果OOM，直接导致系统down机。
解决方案
使用SXSSF操作Excel，并且在对超出限制范围的数据进行分片。SXSSF是XSSF API的兼容流式扩展，在必须生成非常大的电子表格、并且对空间有限时使用。 SXSSF通过限制对滑动窗口内数据的访问实现低内存占用，相反而XSSF允许访问文档中的所有行，不在窗口中的数据将变得不可访问，因为它们已经被写入磁盘。 由于需求场景中只是为了生成文档提供给用户下载，无须即时访问Excel。
SXSSF流式API使用方法
官方参考文档 https://poi.apache.org/components/spreadsheet/how-to.html#sxssf
可以通过SXSSFWorkbook(int windowSize)在工作簿创建时指定窗口大小，也可以通过SXSSFSheet.setRandomAccessWindowSize(int windowSize)在每个工作表中设置。 当通过createRow()创建新行并且未刷新记录的总数超过指定的窗口大小时，将刷新具有最低索引值的行数据，并且不能再通过getRow()访问该行。 默认窗口大小为100，由SXSSFWorkbook.DEFAULT_WINDOW_SIZE定义。 windowSize为-1表示无限制访问。在这种情况下，所有未通过调用flushRows()刷新的记录都可随机访问。 务必在操作完成时调用调用dispose方法来清理的临时文件生成的文件，否则不断生成的临时文件一样会撑爆服务器的硬盘空间。 请注意，根据使用的功能不同，仍然可能会消耗大量内存，例如： 合并区域、超链接、注释等仍然只存储在内存中，因此如果广泛使用可能仍需要大量内存。
SXSSF demo
当行计数达到101时，rownum = 0的行被刷新到磁盘并从内存中删除，当rownum达到102时，则刷新rownum = 1的行。
import java.io.FileOutputStream;
public class SxssfDemo {
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(100); // 在内存中只存入100行
Sheet sh = wb.createSheet();
for (int rownum = 0; rownum < 1000; rownum++) {
Row row = sh.createRow(rownum);
for (int cellnum = 0; cellnum < 10; cellnum++) {
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// 切记释放生成的临时文件
wb.dispose();
}
}总结
本地测试，生成100万行数据可以在10秒内完成，内存占用未见显著上升，同时CPU也未见显著上升，当然，对应的数据库连接释放的也非常快。其实，还有很大的优化空间，想进一步了解的童鞋请 点赞，转发，和评论, 下期我们再见。
看完三件事
如果你觉得这篇内容对你还蛮有帮助，请：点赞，转发，和评论，有你的 参与，才是我创造的动力。
关注我，不定期分享原创知识。