修改POI源码，优化百万级大数据量Excel导出

最新推荐文章于 2024-06-20 13:34:21 发布

Shadow_Light

最新推荐文章于 2024-06-20 13:34:21 发布

阅读量1.8k

点赞数 4

分类专栏： Java 文章标签： excel java poi

本文链接：https://blog.csdn.net/Shadow_Light/article/details/110387794

版权

Java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、问题背景

最近在做项目大数据量Excel导出时，有前端反馈响应速度很慢，基本表现为前端页面触发请求之后，过了很长时间才会在浏览器页面弹出下载文件的窗口，而且响应时间会随着数据量的递增成正比。

注：目前项目中涉及Excel导出相关的基本都是使用阿里的EasyExcel，而可以了解到的是，目前主流开源的excel导出的第三方工具其底层均是基于POI这一开源框架上所做的一些API功能集成，简化操作，可以让开发人员不需要关注底层POI的操作，但是大数据量下还是无法满足需求，100w数据量级别导出浏览器需要10s才能弹出下载框。

二、定位问题

List<Info> data = getExcelData();
EasyExcel.write(response.getOutputStream(), Info.class).registerWriteHandler(setStyle()).sheet(sheetName).doWrite(data);

// EasyExcel写excel调用链
ExcelWriterSheetBuilder.doWrite
          -> ExcelWriter.finish
          -> ExcelBuilderImpl.finish
          -> WriteContextImpl.finish
          // 使用POI框架API触发真正的写数据
          -> writeWorkbookHolder.getWorkbook().write

从EasyExcel调用API方式来进行解析，就会发现，触发excel生成的必要条件是，需要导出的数据必须要提前先生成，之后才能进行后续的操作，因此会消耗大量的时间，造成用户体验上来讲我明明触发了请求，但是需要等很久才能看到浏览器弹出下载，会让用户以为可能没有触发，从而导致重复去进行请求下载。

那此时肯定会想到，这个时候要是能使用多线程的方式去进行读取数据后马上去调用触发excel写入，这样一来前端不就能立马能接收到文件流了吗？于是带着这个想法去做了多线程的方式去实现，不使用EasyExcel，自己去调用POI的接口去生成excel，结果发现生成的数据不全，或者是空文件，因此说明问题出在POI底层，它在写入的时候可能做了一些其他的事情。

三、POI源码分析

大数据量导出的时候，Excel有限制，对于每个sheet页，07版本之后最大行数支持1048576，因此在大于100W时，需要分多个sheet进行写数据，并且推荐大数据量下官方推荐使用SXSSFWorkbook去进行写数据，生成的文件其实是由一些xml文件所组成的，读者可以自行将xlsx文件，重命名为zip，解压后会发现这个xlsx的style.xml、sheet1.xml实际数据等文件。

下面就开始进行源码追踪定位问题：

public void write(OutputStream stream) throws IOException {
        // 1、将内存中的数据刷写磁盘
        flushSheets();

        // 2、创建临时xlsx文件
        File tmplFile = TempFile.createTempFile("poi-sxssf-template", ".xlsx");
        boolean deleted;
        try {
            try (FileOutputStream os = new FileOutputStream(tmplFile)) {
                // 3、根据创建的sheet，生成对应个数的空sheet，以及生成表头、单元格等格式
                _wb.write(os);
            }

            //Substitute the template entries with the generated sheet data files
            try (ZipSecureFile zf = new ZipSecureFile(tmplFile);
                 ZipFileZipEntrySource source = new ZipFileZipEntrySource(zf)) {
                // 4、处理数据
                injectData(source, stream);
            }
        } finally {
            // 5、删除临时文件
            deleted = tmplFile.delete();
        }
        if(!deleted) {
            throw new IOException("Could not delete temporary file after processing: " + tmplFile);
        }
    }

执行步骤：

提醒所有的sheet将内存中数据写入磁盘中，当全部写入后更新对应的 allFlushed = true
在指定路径下【System.getProperty("java.io.tmpdir") + "/poifiles"】创建poi-sxssf-template随机数.xlsx临时文件
根据用户自定义的表头、单元格、几个sheet等信息封装为xml并写入到这个临时文件中
将xlsx解压获取其文件流，然后将excel数据写入
将生成的临时文件进行删除

接着看主要的数据写入逻辑：

protected void injectData(ZipEntrySource zipEntrySource, OutputStream out) throws IOException {
        ZipArchiveOutputStream zos = new ZipArchiveOutputStream(out);
        try {
            Enumeration<? extends ZipArchiveEntry> en = zipEntrySource.getEntries();
            // 1、遍历xlsx文件流
            while (en.hasMoreElements()) {
                ZipArchiveEntry ze = en.nextElement();
                ZipArchiveEntry zeOut = new ZipArchiveEntry(ze.getName());
                zeOut.setSize(ze.getSize());
                zeOut.setTime(ze.getTime());
                zos.putArchiveEntry(zeOut);
                try (final InputStream is = zipEntrySource.getInputStream(ze)) {
                    if (is instanceof ZipArchiveThresholdInputStream) {
                        // #59743 - disable Threshold handling for SXSSF copy
                        // as users tend to put too much repetitive data in when using SXSSF :)
                        ((ZipArchiveThresholdInputStream)is).setGuardState(false);
                    }
                    // 2、根据名称获取xsheet对象
                    XSSFSheet xSheet = getSheetFromZipEntryName(ze.getName());
                    // See bug 56557, we should not inject data into the special ChartSheets
                    if (xSheet != null && !(xSheet instanceof XSSFChartSheet)) {
                        SXSSFSheet sxSheet = getSXSSFSheet(xSheet);
                        // 3、获取xsheet文件流
                        try (InputStream xis = sxSheet.getWorksheetXMLInputStream()) {
                            copyStreamAndInjectWorksheet(is, zos, xis);
                        }
                    } else {
                        // 4、流拷贝
                        IOUtils.copy(is, zos);
                    }
                } finally {
                    zos.closeArchiveEntry();
                }
            }
        } finally {
            zos.finish();
            zipEntrySource.close();
        }
    }

执行步骤：

遍历xml文件
如果xml文件的名称与sheet名称一致，则需要获取实际的数据存放位置文件，进行数据的封装和写入到xml中
如果xml文件不属于sheet，则直接将文件流进行拷贝输出

从这部分逻辑可以看出，它为什么要创建这个poi-sxssf-template临时文件，因为它有xlsx共有的xml文件，所以需要这个临时文件去输出到用户指定的流里，实际区别与每个xlsx文件的主要是sheetN.xml的数据文件。

public class SXSSFSheet implements Sheet
{
    /*package*/ final XSSFSheet _sh;
    private final SXSSFWorkbook _workbook;
    private final TreeMap<Integer,SXSSFRow> _rows = new TreeMap<>();
    private final SheetDataWriter _writer;
    private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;
    private final AutoSizeColumnTracker _autoSizeColumnTracker;
    private int outlineLevelRow;
    private int lastFlushedRowNumber = -1;
    private boolean allFlushed;

    public SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws IOException {
        _workbook = workbook;
        _sh = xSheet;
        _writer = workbook.createSheetDataWriter();
        setRandomAccessWindowSize(_workbook.getRandomAccessWindowSize());
        _autoSizeColumnTracker = new AutoSizeColumnTracker(this);
    }
...
    public InputStream getWorksheetXMLInputStream() throws IOException {
        // flush all remaining data and close the temp file writer
        flushRows(0);
        _writer.close();
        return _writer.getWorksheetXMLInputStream();
    }
}

public class SheetDataWriter implements Closeable {
...
    private final File _fd;
    private final Writer _out;
...
    public SheetDataWriter() throws IOException {
        _fd = createTempFile();
        _out = createWriter(_fd);
    }

    public SheetDataWriter(SharedStringsTable sharedStringsTable) throws IOException {
        this();
        this._sharedStringSource = sharedStringsTable;
    }

    public File createTempFile() throws IOException {
        return TempFile.createTempFile("poi-sxssf-sheet", ".xml");
    }

    public Writer createWriter(File fd) throws IOException {
        FileOutputStream fos = new FileOutputStream(fd);
        OutputStream decorated;
        try {
            decorated = decorateOutputStream(fos);
        } catch (final IOException e) {
            fos.close();
            throw e;
        }
        return new BufferedWriter(
                new OutputStreamWriter(decorated, "UTF-8"));
    }
...
}

通过上述代码可以了解到，这个文件是在用户进行创建每个sheet时，会产生一个对应的文件输出流，之后会根据用户配置(sxssfsheet.flushRows(N))进行刷写磁盘。最后在injectData时，调用sheet的flushrows方法将内存中的数据刷到磁盘，然后关闭sheet的写入流，再进行数据的封装生成xml文件输出。至此可以最终下结论为，大数据量导出excel时，你一旦调用POI的write方法时，你就默认了要此时数据已经完全写到内存里了。

四、优化方案

导出记录时，我们可以提前知晓数据量的大小，即可以得出需要生成多少个sheet页，并且每个sheet数据互不影响，因此可以使用多线程进行不同sheet页数据的获取和写入，当前端请求时，我们可以先把xlsx共有的xml文件流先进行输出，这样一来前端页面浏览器会马上进行响应，后续达到一个sheet文件可以进行数据的边写边输出，基本响应耗时在于查询数据总量所耗费的时间上，之后所需的时间取决于服务器的配置以及对应的网速。

但是要想实现这个需求，就必须对POI源码进行改造，原先的write方法并不适合，因为上边分析过，获取sheet的磁盘文件时，会默认关闭掉这个文件流。

因此第一步：首先对这部分进行改造，在SXSSFSheet中新增2个方法，一个是获取数据文件输入流，一个是数据文件写入流的关闭，（之前获取流时，会先进行流的关闭，因此我们改造时，需要进行手动关闭）。
```
SXSSFSheet ：

public InputStream getWorksheetXMLInputStreamNotClose() throws IOException {
   return _writer.getWorksheetXMLInputStream();
}

public void closeWriter() throws IOException{
        _writer.close();
}
```

第二步：改造数据文件拷贝，经过我们这种方式进行改动时，我们需要进行判断什么时候一个sheet数据已经写完了，可以进行下一个sheet数据文件流的拷贝，所以在根据临时文件去组装xml时，需要去进行不断的阻塞输出，这里我使用了POI自带的一个标志位allFlushed来作为判断一个sheet页全部写入内存的标志

SXSSFWorkbook：
 
protected void multiplyInjectData(ZipEntrySource zipEntrySource, OutputStream out) throws IOException {
    ...
    XSSFSheet xSheet = getSheetFromZipEntryName(ze.getName());
    if (xSheet != null && !(xSheet instanceof XSSFChartSheet)) {
        SXSSFSheet sxSheet = getSXSSFSheet(xSheet);
        InputStream xis = null;
        try {
            // 获取输入流
            xis = sxSheet.getWorksheetXMLInputStreamNotClose();
            copyMultiplyStreamAndInjectWorksheet(is, zos, xis, sxSheet);
        } finally {
            // 手动关闭流
            sxSheet.closeWriter();
            if (xis != null) {
                xis.close();
            }
        }
    } else {
        IOUtils.copy(is, zos);
    }
    ...           
}

    private static void copyMultiplyStreamAndInjectWorksheet(InputStream in, OutputStream out, InputStream worksheetData, SXSSFSheet sxSheet) throws IOException {
        ...
        outWriter.flush();
        if (needsStartTag)
        {
            outWriter.write("<sheetData>\n");
            outWriter.flush();
        }
        // 循环组装数据输出，根据poi自带的标志位进行判断
        while (!sxSheet.areAllRowsFlushed()){
            IOUtils.copy(worksheetData,out);
        }
        IOUtils.copy(worksheetData,out);
        outWriter.write("</sheetData>");
        outWriter.flush();
        //Copy the rest of "in" to "out".
        while(((c=inReader.read())!=-1)) {
            outWriter.write(c);
        }
        outWriter.flush();
    }

但是到这里还有个问题存在，原先POI默认你调用完wirte方法，就认为数据已经全部写到内存，只要把数据都写完，之后用户需要手动调用删除临时文件的一个api，但是采用多线程异步写sheet下，我是不知道什么时候会写完数据的，所以还需要加一个标志位来标志excel文件已经全部输出完了，提醒用户可以调用删除临时文件了

@Override
public void multiplyWrite(OutputStream stream) throws IOException{
    //Save the template
    File tmplFile = TempFile.createTempFile("poi-sxssf-multiply-template", ".xlsx");
    boolean deleted;
    try {
        try (FileOutputStream os = new FileOutputStream(tmplFile)) {
            _wb.write(os);
        }

        //Substitute the template entries with the generated sheet data files
        try (ZipSecureFile zf = new ZipSecureFile(tmplFile);
             ZipFileZipEntrySource source = new ZipFileZipEntrySource(zf)) {
            multiplyInjectData(source, stream);
        }
    } finally {
        deleted = tmplFile.delete();
        multiplyFinished();
    }
    if(!deleted) {
        throw new IOException("Could not delete temporary file after processing: " + tmplFile);
    }
}

public void multiplyFinished() {
    this.isFinished = true;
}

至此，POI端的改造已经完成，接下来需要自己根据改造后的POI源码进行客户端的程序改造。这里便不再进行叙述如何修改，直接看demo例子即可。

五、客户端改造以及POI源码编译jar包

excel-demo

六、与EasyExcel进行对比

100w数据分2个sheet页写入

	耗时（ms）	页面相应速度
EasyExcel	25445	等待10s左右才有下载弹窗
自己修改后的POI	12491	浏览器立即弹窗

Shadow_Light

关注

4
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
修改POI源码，优化百万级大数据量Excel导出

一、问题背景最近在做项目大数据量Excel导出时，有前端反馈响应速度很慢，基本表现为前端页面触发请求之后，过了很长时间才会在浏览器页面弹出下载文件的窗口，而且响应时间会随着数据量的递增成正比。注：目前项目中涉及Excel导出相关的基本都是使用阿里的EasyExcel，而可以了解到的是，目前主流开源的excel导出的第三方工具其底层均是基于POI这一开源框架上所做的一些API功能集成，简化操作，可以让开发人员不需要关注底层POI的操作。二、定位问题 List&l...
复制链接

扫一扫

专栏目录