使用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
Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s