这套 Excel 大批量导入导出解决方案，太牛逼了

最新推荐文章于 2024-07-01 08:49:03 发布

Java-格子衫-凌霄

最新推荐文章于 2024-07-01 08:49:03 发布

阅读量1.8k

点赞数 1

分类专栏：面试文章标签： Java 程序员编程面试互联网

本文链接：https://blog.csdn.net/weixin_45132238/article/details/119754767

版权

本文介绍了如何使用Java的POI库优化处理大数据量的Excel导入导出，包括XLSX的SXSSFWorkbook优化写性能，以及XLS和XLSX的Event Model导入优化，以减少内存消耗和防止FGC问题。

摘要由CSDN通过智能技术生成

概要

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后，再解压出来就可以看到文件结构，对每个文件以及里面内容的意思可以看看Excel 2007（一） - XML存储

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

导出优化

事例源码基于POI3.17版本

XLSX

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

使用

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的初始化