JAVA导出exls时报oom,Poi导出产生OOM解决方案

SXSSFWorkbook这个提供了OOM的解决方案 如上述所说会临时写到临时文件中 /**

* Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy.

*

* This allows to write very large files without running out of memory as only

* a configurable portion of the rows are kept in memory at any one time.

*

* You can provide a template workbook which is used as basis for the written

* data.

*

* See https://poi.apache.org/spreadsheet/how-to.html#sxssf for details.

*

* Please note that there are still things that still may consume a large

* amount of memory based on which features you are using, e.g. merged regions,

* comments, ... are still only stored in memory and thus may require a lot of

* memory if used extensively.

*

* SXSSFWorkbook defaults to using inline strings instead of a shared strings

* table. This is very efficient, since no document content needs to be kept in

* memory, but is also known to produce documents that are incompatible with

* some clients. With shared strings enabled all unique strings in the document

* has to be kept in memory. Depending on your document content this could use

* a lot more resources than with shared strings disabled.

*

* Carefully review your memory budget and compatibility needs before deciding

* whether to enable shared strings or not.

*/

public class SXSSFWorkbook implements Workbook {

/**

* Specifies how many rows can be accessed at most via {@link SXSSFSheet#getRow}.

* When a new node is created via {@link SXSSFSheet#createRow} and the total number

* of unflushed records would exceed the specified value, then the

* row with the lowest index value is flushed and cannot be accessed

* via {@link SXSSFSheet#getRow} anymore.

*/

public static final int DEFAULT_WINDOW_SIZE = 100;

}

默认情况下提供100行的访问【换言之 访问窗口为100】

源码

我们确认一下代码中如何实现临时文件的建立 /**

* Streaming version of XSSFSheet implementing the "BigGridDemo" strategy.

*/

public class SXSSFSheet implements Sheet

{

/*package*/ final XSSFSheet _sh;

private final SXSSFWorkbook _workbook;

private final TreeMap _rows=new TreeMap();

private final SheetDataWriter _writer;

private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;

private final AutoSizeColumnTracker _autoSizeColumnTracker;

private int outlineLevelRow = 0;

private int lastFlushedRowNumber = -1;

private boolean allFlushed = false;

public SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws IOException {

_workbook = workbook;

_sh = xSheet;

_writer = workbook.createSheetDataWriter();

setRandomAccessWindowSize(_workbook.getRandomAccessWindowSize());

_autoSizeColumnTracker = new AutoSizeColumnTracker(this);

}

创建Sheet的时候传入对应参数 【默认100】

首先在创建rows或进行判断 @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;

if(_randomAccessWindowSize>=0&&_rows.size()>_randomAccessWindowSize)

{

try

{

flushRows(_randomAccessWindowSize);

}

catch (IOException ioe)

{

throw new RuntimeException(ioe);

}

}

return newRow;

}

/**

* Specifies how many rows can be accessed at most via getRow().

* The exeeding rows (if any) are flushed to the disk while rows

* with lower index values are flushed first.

*/

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;

}

}

这边_writer负责将对应的数据刷到临时文件中 /**

* Initially copied from BigGridDemo "SpreadsheetWriter".

* Unlike the original code which wrote the entire document,

* this class only writes the "sheetData" document fragment

* so that it was renamed to "SheetDataWriter"

*/

public class SheetDataWriter {

private static final POILogger logger = POILogFactory.getLogger(SheetDataWriter.class);

private final File _fd;

private final Writer _out;

private int _rownum;

private int _numberOfFlushedRows;

private int _lowestIndexOfFlushedRows; // meaningful only of _numberOfFlushedRows>0

private int _numberOfCellsOfLastFlushedRow; // meaningful only of _numberOfFlushedRows>0

private int _numberLastFlushedRow = -1; // meaningful only of _numberOfFlushedRows>0

/**

* Table of strings shared across this workbook.

* If two cells contain the same string, then the cell value is the same index into SharedStringsTable

*/

private SharedStringsTable _sharedStringSource;

public SheetDataWriter() throws IOException {

_fd = createTempFile();

_out = createWriter(_fd);

}

public SheetDataWriter(SharedStringsTable sharedStringsTable) throws IOException{

this();

this._sharedStringSource = sharedStringsTable;

}

/**

* Create a temp file to write sheet data.

* By default, temp files are created in the default temporary-file directory

* with a prefix "poi-sxssf-sheet" and suffix ".xml". Subclasses can override

* it and specify a different temp directory or filename or suffix, e.g. .gz

*

* @return temp file to write sheet data

*/

public File createTempFile() throws IOException {

return TempFile.createTempFile("poi-sxssf-sheet", ".xml");

}

}

很明显此处将会在Temp目录下创建一个临时文件

当数据写完的时候如下 public InputStream getWorksheetXMLInputStream() throws IOException

{

// flush all remaining data and close the temp file writer

flushRows(0);

_writer.close();

return _writer.getWorksheetXMLInputStream();

} public InputStream getWorksheetXMLInputStream() throws IOException {

File fd = getTempFile();

return new FileInputStream(fd);

}

做了一次导出 大约Excel 大小15m 共计4w行数据