根据 EasyExcel 的介绍：他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能Easy Excel。我试了下，确实可以在jvm低内存条件下（比如20m等）导出百万级别的数据。其原理是如何呢？在网上稍微查了下，都讲得不太明了，因此自己看了下底层逻辑。
Excel 2007 的数据结构
Excel 的本质是一个压缩文件，具体可以参考这篇文章Excel文件的本质：一个包含XML、图片文件的压缩文件夹-压缩文件。对于 Excel 2007 来说，每个sheet都是一个单独的xml文件。
假设有以下一个表格：
| 标题 | 阅读数量 |
|---|---|
| excel本质是压缩文件 | 109 |
| excel2007 | 209 |
在excel2007的sheet结构如下：
<worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData">
<sheetPr/>
<dimension ref="A1:B3"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="F9" sqref="F9"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultColWidth="9.23076923076923" defaultRowHeight="16.8" outlineLevelRow="2" outlineLevelCol="1"/>
<cols>
<col min="1" max="1" width="22.5384615384615" customWidth="1"/>
</cols>
<sheetData>
<row r="1" spans="1:2">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
</row>
<row r="2" spans="1:2">
<c r="A2" t="s">
<v>2</v>
</c>
<c r="B2">
<v>109</v>
</c>
</row>
<row r="3" spans="1:2">
<c r="A3" t="s">
<v>3</v>
</c>
<c r="B3">
<v>209</v>
</c>
</row>
</sheetData>
<pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
<headerFooter/>
</worksheet>
这里会有些奇怪，会发现怎么字符串类型的单元格的值不见了，那是因为字符串类型的单元格做了优化，统一存放到了 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 的数据结构，我们如何进行优化呢？其基本思路就是及时刷盘，将内存中的数据刷到磁盘上，这样就不会对内存造成太多的压力。
- 首先假设，java应用分页查询返回的集合中每一条数据，对应sheet中的一行(row)。
- 对于每个sheet，我们先创建一个xml文件，专门保存行的数据，也就是 sheet.xml 文件中
<sheetData>...<sheetData/>标签包裹的内容，每一行对应一个<row><row/>，这个文件我们这里称作 poi-sxssf-sheet.xml。- 每次从数据库分页查询回来的数据，我们全部追加到 poi-sxssf-sheet.xml。这样内存的消耗就只是每次分页产生的数据，分页越小，内存消耗越小。
- 数据全部查询结束后，我们再将 poi-sxssf-sheet.xml 拼接成为一个完整的 sheet.xml。
- 将 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();
}
}
以上的逻辑很简单
- 使用 SXSSFSheet 创建 row,并缓存到 TreeMap<Integer,SXSSFRow> 中，这里使用 TreeMap 可以保证行的顺序性。
- 当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;
}
// 省略其他代码
}
以上逻辑如下：
- SheetDataWriter 构造函数初始化的时候，创建了一个空白xml文件。
- 创建一个 BufferedWriter 用于将数据传输到 xml 文件。
- 当有新的行数据过来的时候，拼接出正确的 xml 结构，通过 BufferedWriter 输出到 xml 文件。
- 最后适当的时机(比如数据已经加载完毕)调用 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 的关系：
- XSSFWorkbook 代表了一个 excel2007 的文件。
- SXSSFWorkbook 并没有完全重写 XSSFWorkbook，而是包含一个 XSSFWorkbook 实例，除了行数据外，其他配置都保存在这个 XSSFWorkbook 实例上。
- 同理，SXSSFWorkbook 对用的 SXSSFSheet 也是包含一个 XSSFSheet，用于保存 sheet 的相关配置。
- 但是 SXSSFSheet 多了 SheetDataWriter 对象，用于将行数据保存到磁盘上的 xml 文件中，而 XSSFSheet 属性是没有行数据的。
- 当要导出数据的时候，首先使用 XSSFWorkbook 实例就可以创建出一个 xlsx 文件，这个只是一个架子，包含有所需要的所有文件以及相关配置，只是sheet.xml是没有行数据的，最终是依靠 SXSSFWorkbook 将磁盘上对应sheet的 xml 拼接到对应的 sheet.xml中即可。
- 然后通过输出流，将压缩包传送到目的地。
总结
从以上我们已经知道，SXSSFWorkbook 本身是支持导出海量数据而不至于内存溢出的， 而使用 EasyExcel 来导出xlsx文件的时候，他的底层实际使用了SXSSFWorkbook，只是做了进一步的封装，因此自然支持海量数据导出。
扫一扫
5505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
