Out of Memory Error - Java Heap Space while writing to Excel

Question

I have a data of almost 100,000 records and I am trying to write the data to .xlsx file using XSSFWorkbook through Java code. I am able to fetch all the data from database to an ArrayList. By iterating the ArryList, I am writing the data to .xlsx file cell by cell. As it reaches to 8000 rows, java code throws Out of Memory Heap Space Error.

I have read somewhere that SXSSFWorkbook will be lighter when compared to XSSFWorkbook, so I tried using SXSSFWorkbook. But still I am facing the same problem.

So is there anything that I am missing with the Workbooks or with my Java Code??

Initially, when I have 60,000 records data, I had used .xls file. The same java code is able to generate the .xls file with HSSFWorkbook.

Increasing the Java Heap Space is not at all an option as my data will be increased tremendously in future.

Any help will be greatly appreciated.

Small piece of code, the way I am writing the data to Excel.

int rowNum = sheet.getLastRowNum();

Row lastRow = null ;

Cell cell = null;

ReportingHelperVo reportingHelperVo = null; 

for (ReportingVo reportingVo : reportingVos) {

rowNum++;

lastRow = sheet.createRow(rowNum);

reportingHelperVo = reportingVo.reportingHelperVo;

cell = lastRow.createCell(0);

cell.setCellValue(reportingHelperVo.getLocation());

cell.setCellStyle(style);

cell = lastRow.createCell(1);

cell.setCellValue(reportingHelperVo.getCity());

cell.setCellStyle(style);

cell = lastRow.createCell(2);

cell.setCellValue(reportingHelperVo.getCountry());

cell.setCellStyle(style);

}

Answer 1

SXSSFWorkbook is not like light weight,but there is a advantage with this.

If you declare as

SXSSFWorkbook workbook= new SXSSFWorkbook(200);

then for every 200 rows written on the workbook, memory will be flushed to diskspace so there will be no burden in heapspace.