Excel大批量数据的导入和导出，如何做优化？

Excel大批量数据的导入和导出，如何做优化？

概要
Java对Excel的操作一般都是用POI，但是数据量大的话可能会导致频繁的FGC或OOM，这篇文章跟大家说下如果避免踩POI的坑，以及分别对于xls和xlsx文件怎么优化大批量数据的导入和导出。

一次线上问题
这是一次线上的问题，因为一个大数据量的Excel导出功能，而导致服务器频繁FGC，具体如图所示

可以看出POI的对象以及相关的XML对象占用了绝大部分的内存消耗，频繁FGC说明这些对象一直存活，没有被回收。

原因是由于导出的数据比较大量，大概有10w行 * 50列，由于后台直接用XSSFWorkbook导出，在导出结束前内存有大量的Row，Cell，Style等，以及基于XLSX底层存储的XML对象没有被释放。

Excel的存储格式
下面的优化内容涉及Excel的底层存储格式，所以要先跟大家讲一下。

XLS
03版的XLS采用的是一种名为BIFF8(Binary-Interchange-File-Format)，基于OLE2规范的二进制文件格式。大概就是一种结构很复杂的二进制文件，具体细节我也不是很清楚，大家也没必要去了解它，已经被淘汰了。想了解的话可以看看Excel XLS文件格式

XLSX
07版的XLSX则是采用OOXML(Office Open Xml)的格式存储数据。简单来说就是一堆xml文件用zip打包之后文件。这个对于大家来说就熟悉了，把xlsx文件后缀名改为zip后，再解压出来就可以看到文件结构

打开sheet1.xml，可以看到是描述第一个sheet的内容

导出优化
事例源码基于POI3.17版本

XLSX
由于xlsx底层使用xml存储，占用内存会比较大，官方也意识到这个问题，在3.8版本之后，提供了SXSSFWorkbook来优化写性能。

官方说明

https://poi.apache.org/components/spreadsheet/how-to.html#sxssf

使用
SXSSFWorkbook使用起来特别的简单，只需要改一行代码就OK了。

原来你的代码可能是长这样的

Workbook workbook = new XSSFWorkbook(inputStream);
那么你只需要改成这样子，就可以用上SXSSFWorkbook了

Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(inputStream));
其原理是可以定义一个window size（默认100），生成Excel期间只在内存维持window size那么多的行数Row，超时window size时会把之前行Row写到一个临时文件并且remove释放掉，这样就可以达到释放内存的效果。

SXSSFSheet在创建Row时会判断并刷盘、释放超过window size的Row。

@Override
public SXSSFRow createRow(int rownum)
{
int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
if (rownum < 0 || rownum > maxrow) {
throw new IllegalArgumentException(“Invalid row number (” + rownum
+ “) outside allowable range (0…” + maxrow + “)”);
}

    // attempt to overwrite a row that is already flushed to disk
    if(rownum <= _writer.getLastFlushedRow() ) {
        throw new IllegalArgumentException(
                "Attempting to write a row["+rownum+"] " +
                "in the range [0," + _writer.getLastFlushedRow() + "] that is already written to disk.");
    }

    // attempt to overwrite a existing row in the input template
    if(_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum() ) {
        throw new IllegalArgumentException(
                "Attempting to write a row["+rownum+"] " +
                        "in the range [0," + _sh.getLastRowNum() + "] that is already written to disk.");
    }

    SXSSFRow newRow=new SXSSFRow(this);
    _rows.put(rownum,newRow);
    allFlushed = false;
    //如果大于窗口的size，就会flush
    if(_randomAccessWindowSize>=0&&_rows.size()>_randomAccessWindowSize)
    {
        try
        {
           flushRows(_randomAccessWindowSize);
        }
        catch (IOException ioe)
        {
            throw new RuntimeException(ioe);
        }
    }
    return newRow;
}

public void flushRows(int remaining) throws IOException
{
    //flush每一个row
    while(_rows.size() > remaining) {
        flushOneRow();
    }
    if (remaining == 0) {
        allFlushed = true;
    }
}

private void flushOneRow() throws IOException
{
    Integer firstRowNum = _rows.firstKey();
    if (firstRowNum!=null) {
        int rowIndex = firstRowNum.intValue();
        SXSSFRow row = _rows.get(firstRowNum);
        // Update the best fit column widths for auto-sizing just before the rows are flushed
        _autoSizeColumnTracker.updateColumnWidths(row);
        //写盘
        _writer.writeRow(rowIndex, row);
        //然后把row remove掉，这里的_rows是一个TreeMap结构
        _rows.remove(firstRowNum);
        lastFlushedRowNumber = rowIndex;
    }
}

我们再看看刷盘的具体操作

SXSSFSheet在创建的时候，都会创建一个SheetDataWriter，刷盘动作正是由这个类完成的

看下SheetDataWriter的初始化

public SheetDataWriter() throws IOException {
//创建临时文件
_fd = createTempFile();
//拿到文件的BufferedWriter
_out = createWriter(_fd);
}
//在本地创建了一个临时文件前缀为poi-sxssf-sheet，后缀为.xml
public File createTempFile() t