POI Export Data Memory overflow problem

The previous version of POI does not support big data processing, and if too much data is often reported to oom errors, sometimes resizing the JVM is not a good result. 3.8 version of the POI new out Sxssfworkbook, can support the operation of large data volume, but only sxssfworkbook support. xlsx format, not supported in. xls format.

3.8 version of the POI export operations to Excel, generally only use Hssfworkbook and Sxssfworkbook,Hssfworkbook to handle less data volume, Sxssfworkbook is used to handle the export of large data volumes and large amounts of data. The use of Hssfworkbook and the previous version of the use of the same method, there is no way to state the use of Sxssfworkbook examples are as follows:

Import Junit.framework.Assert;

Import Org.apache.poi.ss.usermodel.Cell;  

Import Org.apache.poi.ss.usermodel.Row;  

Import Org.apache.poi.ss.usermodel.Sheet;  

Import Org.apache.poi.ss.usermodel.Workbook;  

Import org.apache.poi.ss.util.CellReference;  

Import Org.apache.poi.xssf.streaming.SXSSFWorkbook;  

public static void Main (string[] args) throws Throwable {  

Workbook wb = new Sxssfworkbook (100); Keep rows in memory, exceeding rows would be flushed to disk  

Sheet sh = wb.createsheet ();  

Row row = Sh.createrow (rownum);  

for (int cellnum = 0; cellnum < cellnum++) {  

Cell cell = Row.createcell (Cellnum);  

String address = new Cellreference (cell). formatasstring ();  

Cell.setcellvalue (address); }  

FileOutputStream out = new FileOutputStream ("/temp/sxssf.xlsx");  

Wb.write (out);  

}

The above is reproduced

It is worth noting that Sxssfworkbook can only write unreadable. But often we need to export the data to an Excel template so that we can better define the format and VBA code in advance.

Here you will need to use another constructor for Sxssfworkbook:

Sxssfworkbook (Xssfworkbook Workbook) Construct a workbook from a template.

Use Xssfworkbook to read the template, then use the Sxssfworkbook to set the style and write the data, using the reference API for details.

Http://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html

POI Export Data Memory overflow problem