使用POI输出超过65536行大Excel（SXSSF技术）

一直以来都用POI做Excel导出，之前导出的Excel格式都是2003版的——XLS，最近客户要求导出超过65536条数据，超出了XLS的上限，所以研究起XSSFWorkbook的用法。

原本以为只是把HSSFWorkbook替换成XSSFWorkbook就能导出数据，无奈总是报告内存溢出。放狗查找资料，最终锁定SXSSFWorkbook类。

首先参考Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)完成HSSFWorkbook转换为XSSFWorkkbook的转换，注意使用CreationHelper类。

再参考SXSSF (Streaming Usermodel API)完成大文件的输出

import junit.framework.Assert;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public static void main(String[] args) throws Throwable {
    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    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);
        }

    }

    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
      Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
    wb.write(out);
    out.close();    // dispose of temporary files backing this workbook on disk
    wb.dispose();
}

写在最后

• SXSSF通过把数据缓存到磁盘临时文件，完成超大Excel的输出。
• 一个Sheet对应一个临时文件
• 通过SXSSFWorkbook构造函数指定多少条写入临时文件，实际测试证明，维持默认100条能够获得较好的性能。
• 最后不要忘记使用SXSSFWorkbook.dispose()方法清除临时文件，该方法返回true，则表示所有的临时文件都已经清除。
  该方法实际最终执行SheetDataWriter.dispose()，此方法内部执行java.io.Writer.close()方法释放资源，同时执行java.io.File.delete()方法删除临时文件。
• 如果碰到临时文件空间的问题，可以尝试启动GZIP，用CPU来换空间了。

GZIP启用方法：

SXSSFWorkbook wb = new SXSSFWorkbook(); 
  wb.setCompressTempFiles(true); // temp files will be gzipped