前言

通常情况下我们要操作Microsoft office文件，Apache POI是一个不错的选择，POI提供了丰富的API给Java程序对Microsoft Office格式档案读和写的功能。

Excel文件的读写

POI提供了HSSF、XSSF以及SXSSF三种方式操作Excel。他们的区别如下：

1. HSSF：是操作Excel97-2003版本，扩展名为.xls，一个sheet最大行数65536，最大列数256。
2. XSSF：是操作Excel2007版本开始，扩展名为.xlsx，一个sheet最大行数1048576，最大列数16384。
3. 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也未见显著上升，当然，对应的数据库连接释放的也非常快。其实，还有很大的优化空间，想进一步了解的童鞋请 点赞，转发，和评论, 下期我们再见。

看完三件事

?如果你觉得这篇内容对你还蛮有帮助，请：点赞，转发，和评论，有你的 参与，才是我创造的动力。

关注我，不定期分享原创知识。