EasyExcel 如何在很低的内存下导出百万条数据的 excel

悠悠 2024-03-30 17:12 82阅读 0赞

根据 EasyExcel 的介绍：他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能[Easy Excel][]。我试了下，确实可以在jvm低内存条件下（比如20m等）导出百万级别的数据。其原理是如何呢？在网上稍微查了下，都讲得不太明了，因此自己看了下底层逻辑。

### Excel 2007 的数据结构 ###

Excel 的本质是一个压缩文件，具体可以参考这篇文章[Excel文件的本质：一个包含XML、图片文件的压缩文件夹-压缩文件][Excel_XML_-]。对于 Excel 2007 来说，每个sheet都是一个单独的xml文件。  
假设有以下一个表格：

<table> 
 <thead> 
  <tr> 
   <th>标题</th> 
   <th>阅读数量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>excel本质是压缩文件</td> 
   <td>109</td> 
  </tr> 
  <tr> 
   <td>excel2007</td> 
   <td>209</td> 
  </tr> 
 </tbody> 
</table>

在excel2007的sheet结构如下：

这里会有些奇怪，会发现怎么字符串类型的单元格的值不见了，那是因为字符串类型的单元格做了优化，统一存放到了 sharedStrings.xml 这个文件，c 标签有t这个属性的时候，就是指向了 sharedStrings.xml 的位置。当然也可以不用共享字符串，可以直接像数字一样赋值。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst
        xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="4" uniqueCount="4">
        <si>
            <t>标题</t>
        </si>
        <si>
            <t>阅读数量</t>
        </si>
        <si>
            <t>excel本质是压缩文件</t>
        </si>
        <si>
            <t>excel2007</t>
        </si>
    </sst>

非共享字符串如下：

<row r="1">
        <c r="A1" s="1" t="inlineStr">
            <is>
                <t>字符串标题</t>
            </is>
        </c>
        <c r="B1" s="1" t="inlineStr">
            <is>
                <t>日期标题</t>
            </is>
        </c>
        <c r="C1" s="1" t="inlineStr">
            <is>
                <t>数字标题</t>
            </is>
        </c>
    </row>

基于以上结构，我们可以通过修改压缩包中的 sheet.xml 文件，来增加或者删除行数据，SXSSFWorkbook 正式基于这个原理实现的。

### POI 的 SXSSFWorkbook 实现逻辑 ###

我们都知道，在早期POI导出excel，当数据量比较大，很容易内存溢出。 当我们了解了 excel2007 的数据结构，我们如何进行优化呢？其基本思路就是及时刷盘，将内存中的数据刷到磁盘上，这样就不会对内存造成太多的压力。

> 1.  首先假设，java应用分页查询返回的集合中每一条数据，对应sheet中的一行(row)。
> 2.  对于每个sheet，我们先创建一个xml文件，专门保存行的数据，也就是 sheet.xml 文件中 `<sheetData>...<sheetData/>`标签包裹的内容，每一行对应一个 `<row><row/>`，这个文件我们这里称作 poi-sxssf-sheet.xml。
> 3.  每次从数据库分页查询回来的数据，我们全部追加到 poi-sxssf-sheet.xml。这样内存的消耗就只是每次分页产生的数据，分页越小，内存消耗越小。
> 4.  数据全部查询结束后，我们再将 poi-sxssf-sheet.xml 拼接成为一个完整的 sheet.xml。
> 5.  将 sheet.xml 以及其他必要的文件封装压缩成为一个 .xlsx 文件，使用流输出到目的地。

实现以上逻辑的是 SXSSFWorkbook 以及对应的 SXSSFSheet 。 首先看下 SXSSFSheet：

public class SXSSFSheet implements Sheet{
        
        private final SheetDataWriter _writer;
        private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;
        private final TreeMap<Integer,SXSSFRow> _rows = new TreeMap<>();
        public SXSSFRow createRow(int rownum)
        {
        
            //忽略部分代码
            SXSSFRow newRow = new SXSSFRow(this);
            _rows.put(rownum, newRow);
            allFlushed = false;
            // _randomAccessWindowSize 默认是100
            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
        {
        
            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);
                _rows.remove(firstRowNum);
                lastFlushedRowNumber = rowIndex;
            }
        }
        // 获取输入流，读取xml
        public InputStream getWorksheetXMLInputStream() throws IOException
        {
        
            // flush all remaining data and close the temp file writer
            flushRows(0);
            _writer.close();
            return _writer.getWorksheetXMLInputStream();
        }
    }

以上的逻辑很简单

> 1.  使用 SXSSFSheet 创建 row,并缓存到 TreeMap<Integer,SXSSFRow> 中，这里使用 TreeMap 可以保证行的顺序性。
> 2.  当TreeMap<Integer,SXSSFRow> 的数量超过 \_randomAccessWindowSize （默认100，也可以在创建SXSSFWorkbook的时候配置）的时候，取出TreeMap<Integer,SXSSFRow>的第一条数据，刷到临时xml中。这时候使用的是 SheetDataWriter。

接下来看下 SheetDataWriter 如何实现。

public class SheetDataWriter implements Closeable {
        
        private final File _fd;
        private final Writer _out;
        public SheetDataWriter() throws IOException {
        
            _fd = createTempFile();
            // 本地 debug 文件地址为 /var/folders/46/nm8w6nrx4mnccynkcth49pr00000gn/T/a33d541d-54df-46d7-8787-6dd7575d18ec/poifiles/poi-sxssf-sheet3737426676647744895.xml
            _out = createWriter(_fd);
        }
        public File createTempFile() throws IOException {
        
            // 可以看到，先创建了一个xml格式的临时文件
            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, StandardCharsets.UTF_8));
        }
        
        public void writeRow(int rownum, SXSSFRow row) throws IOException {
        
            if (_numberOfFlushedRows == 0)
                _lowestIndexOfFlushedRows = rownum;
            _numberLastFlushedRow = Math.max(rownum, _numberLastFlushedRow);
            _numberOfCellsOfLastFlushedRow = row.getLastCellNum();
            _numberOfFlushedRows++;
            // 行开始写
            beginRow(rownum, row);
            Iterator<Cell> cells = row.allCellsIterator();
            int columnIndex = 0;
            while (cells.hasNext()) {
        
                // 写单元格
                writeCell(columnIndex++, cells.next());
            }
            // 行结束
            endRow();
        }
        // 本质上是追加拼接xml
        void beginRow(int rownum, SXSSFRow row) throws IOException {
        
            _out.write("<row");
            writeAttribute("r", Integer.toString(rownum + 1));
            if (row.hasCustomHeight()) {
        
                writeAttribute("customHeight", "true");
                writeAttribute("ht", Float.toString(row.getHeightInPoints()));
            }
            if (row.getZeroHeight()) {
        
                writeAttribute("hidden", "true");
            }
            if (row.isFormatted()) {
        
                writeAttribute("s", Integer.toString(row.getRowStyleIndex()));
                writeAttribute("customFormat", "1");
            }
            if (row.getOutlineLevel() != 0) {
        
                writeAttribute("outlineLevel", Integer.toString(row.getOutlineLevel()));
            }
            if(row.getHidden() != null) {
        
                writeAttribute("hidden", row.getHidden() ? "1" : "0");
            }
            if(row.getCollapsed() != null) {
        
                writeAttribute("collapsed", row.getCollapsed() ? "1" : "0");
            }
    
            _out.write(">\n");
            this._rownum = rownum;
        }
        // 省略其他代码
    }

以上逻辑如下：

> 1.  SheetDataWriter 构造函数初始化的时候，创建了一个空白xml文件。
> 2.  创建一个 BufferedWriter 用于将数据传输到 xml 文件。
> 3.  当有新的行数据过来的时候，拼接出正确的 xml 结构，通过 BufferedWriter 输出到 xml 文件。
> 4.  最后适当的时机(比如数据已经加载完毕)调用 close() 方法，BufferedWriter 刷盘并关闭流。

至此，Excel2007 的sheet.xml文件，已经完成了row的部分的逻辑，但是数据还不完整，还需要拼接其他数据成为一个完整的excel文件，这个由 SXSSFWorkbook 类完成。

public class SXSSFWorkbook implements Workbook {
        
        private final XSSFWorkbook _wb;
        private final Map<XSSFSheet,SXSSFSheet> _xFromSxHash = new HashMap<>();
    
        @Override
        public void write(OutputStream stream) throws IOException {
        
            // 首先将所有的缓存内剩余的行，刷到xml文件
            flushSheets();
    
            //Save the template
            File tmplFile = TempFile.createTempFile("poi-sxssf-template", ".xlsx");
            boolean deleted;
            try {
        
                try (FileOutputStream os = new FileOutputStream(tmplFile)) {
        
                    // 这里需要注意，先使用 XSSFWorkbook 输出一个空白的 xlsx 文件。
                    _wb.write(os);
                }
                // 这里才是真正拼接代码的地方
                try (ZipSecureFile zf = new ZipSecureFile(tmplFile);
                     ZipFileZipEntrySource source = new ZipFileZipEntrySource(zf)) {
        
                    // 这里获取xml流
                    injectData(source, stream);
                }
            } finally {
        
                deleted = tmplFile.delete();
            }
            if(!deleted) {
        
                throw new IOException("Could not delete temporary file after processing: " + tmplFile);
            }
        }
    
        protected void flushSheets() throws IOException {
        
            for (SXSSFSheet sheet : _xFromSxHash.values())
            {
        
                sheet.flushRows();
            }
        }
        protected void injectData(ZipEntrySource zipEntrySource, OutputStream out) throws IOException {
        
            ArchiveOutputStream zos = createArchiveOutputStream(out);
            try {
        
                Enumeration<? extends ZipArchiveEntry> en = zipEntrySource.getEntries();
                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);
                        }
                        // 获取列表
                        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);
                            try (InputStream xis = sxSheet.getWorksheetXMLInputStream()) {
        
                                copyStreamAndInjectWorksheet(is, zos, xis);
                            }
                        } else {
        
                            IOUtils.copy(is, zos);
                        }
                    } finally {
        
                        zos.closeArchiveEntry();
                    }
                }
            } finally {
        
                zos.finish();
                zipEntrySource.close();
            }
        }
    
        // 将行数据的 xml 文件内容，拼接到目标 sheet.xml文件中即可。
        private static void copyStreamAndInjectWorksheet(InputStream in, OutputStream out, InputStream worksheetData) throws IOException {
        
            InputStreamReader inReader = new InputStreamReader(in, StandardCharsets.UTF_8);
            OutputStreamWriter outWriter = new OutputStreamWriter(out, StandardCharsets.UTF_8);
            boolean needsStartTag = true;
            int c;
            int pos=0;
            String s="<sheetData";
            int n=s.length();
            //Copy from "in" to "out" up to the string "<sheetData/>" or "</sheetData>" (excluding).
            while(((c=inReader.read())!=-1))
            {
        
                if(c==s.charAt(pos))
                {
        
                    pos++;
                    if(pos==n)
                    {
        
                        if ("<sheetData".equals(s))
                        {
        
                            c = inReader.read();
                            if (c == -1)
                            {
        
                                outWriter.write(s);
                                break;
                            }
                            if (c == '>')
                            {
        
                                // Found <sheetData>
                                outWriter.write(s);
                                outWriter.write(c);
                                s = "</sheetData>";
                                n = s.length();
                                pos = 0;
                                needsStartTag = false;
                                continue;
                            }
                            if (c == '/')
                            {
        
                                // Found <sheetData/
                                c = inReader.read();
                                if (c == -1)
                                {
        
                                    outWriter.write(s);
                                    break;
                                }
                                if (c == '>')
                                {
        
                                    // Found <sheetData/>
                                    break;
                                }
    
                                outWriter.write(s);
                                outWriter.write('/');
                                outWriter.write(c);
                                pos = 0;
                                continue;
                            }
    
                            outWriter.write(s);
                            outWriter.write('/');
                            outWriter.write(c);
                            pos = 0;
                            continue;
                        }
                        else
                        {
        
                            // Found </sheetData>
                            break;
                        }
                    }
                }
                else
                {
        
                    if(pos>0) {
        
                        outWriter.write(s,0,pos);
                    }
                    if(c==s.charAt(0))
                    {
        
                        pos=1;
                    }
                    else
                    {
        
                        outWriter.write(c);
                        pos=0;
                    }
                }
            }
            outWriter.flush();
            if (needsStartTag)
            {
        
                outWriter.write("<sheetData>\n");
                outWriter.flush();
            }
            //Copy the worksheet data to "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();
        }
    }

要理解以上代码，就需要知道 SXSSFWorkbook 与 XSSFWorkbook 的关系：

> 1.  XSSFWorkbook 代表了一个 excel2007 的文件。
> 2.  SXSSFWorkbook 并没有完全重写 XSSFWorkbook，而是包含一个 XSSFWorkbook 实例，除了行数据外，其他配置都保存在这个 XSSFWorkbook 实例上。
> 3.  同理，SXSSFWorkbook 对用的 SXSSFSheet 也是包含一个 XSSFSheet，用于保存 sheet 的相关配置。
> 4.  但是 SXSSFSheet 多了 SheetDataWriter 对象，用于将行数据保存到磁盘上的 xml 文件中，而 XSSFSheet 属性是没有行数据的。
> 5.  当要导出数据的时候，首先使用 XSSFWorkbook 实例就可以创建出一个 xlsx 文件，这个只是一个架子，包含有所需要的所有文件以及相关配置，只是sheet.xml是没有行数据的，最终是依靠 SXSSFWorkbook 将磁盘上对应sheet的 xml 拼接到对应的 sheet.xml中即可。
> 6.  然后通过输出流，将压缩包传送到目的地。

## 总结 ##

从以上我们已经知道，SXSSFWorkbook 本身是支持导出海量数据而不至于内存溢出的， 而使用 EasyExcel 来导出xlsx文件的时候，他的底层实际使用了SXSSFWorkbook，只是做了进一步的封装，因此自然支持海量数据导出。

[Easy Excel]: https://easyexcel.opensource.alibaba.com/
[Excel_XML_-]: https://www.shangyouw.cn/wenjian/arc2264.html