事例源码基于POI3.17版本
XLSX
由于xlsx底层使用xml存储,占用内存会比较大,官方也意识到这个问题,在3.8版本之后,提供了SXSSFWorkbook来优化写性能。
官方说明
https://poi.apache.org/components/spreadsheet/how-to.html#sxssf
使用
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的初始化
public SheetDataWriter() throws IOException {
//创建临时文件
_fd = createTempFile();
//拿到文件的BufferedWriter
_out = createWriter(_fd);
<