Poi large data volume generated Excel

When you use poi to generate an Excel file with a large amount of data, there are several problems:

 

1. Currently, office2003 excle supports a maximum of 65536 lines per sheet. When the data exceeds 65536 lines, an exception is reported.

2. If there are about 17 columns and about rows of data, an OOM error will occur.

Exception in thread "15124183 @ qtp-16902686-1" Java. Lang. outofmemoryerror: Java heap Space
Exception in thread "thread-13" Java. Lang. outofmemoryerror: Java heap Space
At org. Apache. Poi. hssf. usermodel. hssfrow. createcell (hssfrow. Java: 141)
At org. Apache. Poi. hssf. usermodel. hssfrow. createcell (hssfrow. Java: 119)

 

Problem 1 is better solved. Each time data is written, the last row of sheet is obtained, and data is written after the last row. When the maximum number of rows is greater than 65536 or smaller, a new sheet is generated, this problem can be solved.

Code snippet

Int sheetlastsize = sheet. getlastrownum ();
// If the data is an integer multiple of the maximum number of writes (constants. excel_sheet_datasize is the maximum number of rows)
If (sheetlastsize> = constants. excel_sheet_datasize ){
Sheet = Workbook. createsheet ();
Sheetlastsize = sheet. getlastrownum ();
Log.info ("sheetlast size: ========" + sheetlastsize );
MapInfo. Put ("sheet", sheet );
// Process the title Header
Processexceltitle (dataset, workbook, sheet );
}

 

Problem 2 is currently not a good solution. Multiple cell objects are generated in each row. When the data volume is large, Java GC has not had time to recycle the objects. As a result, no cell objects are created in memory. Now we are testing a method (only temporary relief, not the best solution). After each data write, we assign a null value to the row and call system regularly. GC (); there is no good way to test it temporarily.

Still not good, still overflow!

 

 

 

/**
* Input an existing workbook and data source to append data to an Excel worksheet. When each sheet is larger than the specified number of rows, create a new sheet.
*
* @ Param workbook
* Existing work thin
* @ Param Dataset
* Write data set
* @ Param MapInfo
* Prepare the Data Map
*/
@ Suppresswarnings ("unchecked ")
Private void dynamicexcel (hssfworkbook workbook, Map <string, Object> dataset, Map <object, Object> MapInfo ){
Map <string, string> headers = (Map <string, string>) dataset. Get ("1 ");
Iterator <entry <string, string> titleiter = headers. entryset (). iterator ();

// Obtain the data of all rows
List <Map <string, string> ValueList = (list <Map <string, string>) dataset. Get ("2 ");
Wroterecodesize + = ValueList. Size ();
Log.info ("This times size is: ----------------------------------------------------" + ValueList. Size ());
Log.info ("Total wrote recode size is: ------------------------------------------------" + wroterecodesize );
Hssfsheet sheet = (hssfsheet) MapInfo. Get ("sheet ");
For (INT I = 0; I <ValueList. Size (); I ++ ){
Int sheetlastsize = sheet. getlastrownum ();
// If the data is an integer multiple of the maximum number of writes
If (sheetlastsize> = constants. excel_sheet_datasize ){
Sheet = Workbook. createsheet ();
Sheetlastsize = sheet. getlastrownum ();
Log.info ("sheetlast size: ========" + sheetlastsize );
MapInfo. Put ("sheet", sheet );
// Process the title Header
Processexceltitle (dataset, workbook, sheet );
}
// Append a row
Hssfrow ROW = sheet. createrow (sheetlastsize + 1 );
Sheet. setdefacolumcolumnwidth (25 );
// Obtain all values of all records in a row
Int dataindex = 0;
Map <string, string> values = ValueList. Get (I );
Titleiter = headers. entryset (). iterator ();
While (titleiter. hasnext ()){
Map. Entry <string, string> entry = (Map. Entry <string, string>) titleiter. Next ();
String key = entry. getkey ();
Hssfcell cell = row. createcell (dataindex ++ );
// String v = values. Get (key );
Cell. setcellvalue (values. Get (key ));
}

}
}