aspose file tools*
The moose likes Other Open Source Projects and the fly likes Out of Memory Error - Java Heap Space while writing to Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Out of Memory Error - Java Heap Space while writing to Excel" Watch "Out of Memory Error - Java Heap Space while writing to Excel" New topic
Author

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

Sanjay Krishna Bitra
Greenhorn

Joined: Jan 10, 2013
Posts: 6

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);

}


-Sanjay
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12781
    
    5
If you can't write to an .xlsx file, why not create a simple CSV - (comma separated values) file? Excel should be able to understand a CSV file.

Bill
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2374
    
  28

I have never used excel with large number of records. For large records, CSV is the defacto standard. It's much easier to stream it

In your case, however, have you tried to find out how much memory POI is taking versus how much memory the List that holds the records in memory are taking? Even if you were streaming data to CSV, you will have to make sure that you don;t load all your records in memory. You'll have to read them in batches.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41823
    
  62
As great as POI is, it is notorious for requiring large amounts of memory. With that many records, an approach not based on POI is likely to be a better solution, as Jayesh said.


Ping & DNS - my free Android networking tools app
Sanjay Krishna Bitra
Greenhorn

Joined: Jan 10, 2013
Posts: 6

Jayesh A Lalwani wrote:I have never used excel with large number of records. For large records, CSV is the defacto standard. It's much easier to stream it

In your case, however, have you tried to find out how much memory POI is taking versus how much memory the List that holds the records in memory are taking? Even if you were streaming data to CSV, you will have to make sure that you don;t load all your records in memory. You'll have to read them in batches.


No. I haven't tried to find the memory that POI and List are taking. Can you please guide me how to find that? I am trying to fetch the data form database in chunks and writing to CSV file. Again I am fetching another chunk of data and writing to CSV. I have tried this with POI, but it still throws Out Of Memory. If I try with CSV file, will it also throw OOM at this point? Thanks for your suggestion.
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12781
    
    5
If I try with CSV file, will it also throw OOM at this point?


If you manage memory correctly, no. The output file will use quite a small buffer and the rest of the memory will be consumed by database related objects which will be related to the number of records retrieved at one time.

Bill
Sanjay Krishna Bitra
Greenhorn

Joined: Jan 10, 2013
Posts: 6

William Brogden wrote:
If I try with CSV file, will it also throw OOM at this point?


If you manage memory correctly, no. The output file will use quite a small buffer and the rest of the memory will be consumed by database related objects which will be related to the number of records retrieved at one time.

Bill


Thanks Bill. Now I am trying to write into CSV file and then import it to .xlsm file using Macros. I make sure that I am fetching the data as small chunks from database and write/append to CSV file.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2374
    
  28

If you want to profile memory usage, I would use JConsole and/or memory profile it in JVisualVM while it is executing.

JConsole gives you a nice graph oh how much of the memory is being used. If you do some trial and error (like comment out the code that calls POI) you can figure out how much memory differrent parts of your system you are using

JVisualVM allows you to take a snapshot of the heap and inspect it. This will tell you exactly what objects are taking up memory. This will tell you where exactly the problem is but requires a little bit of learning curve.

Sometimes, OOMs can be caused by things outside your code. For example, we were getting OOM in one environment, and it was working fine in another. I used heap dumps to figure out the problem was someone had set hibernate logging to DEBUG, and hibernate in it's infinite wisdom logs the entire record when it reads and writes from the database. One of our tables had a huge BLOB, and if it's a Blob, Hibernate Base 64 encodes it and logs it Turning the logging back to INFO "fixed" the OOM.

The point is heap dump inspection will tell you where exactly the problem is. I would advise you to learn how to do it if you are going to be regularly playing with lots of data.
Sanjay Krishna Bitra
Greenhorn

Joined: Jan 10, 2013
Posts: 6

Jayesh A Lalwani wrote:If you want to profile memory usage, I would use JConsole and/or memory profile it in JVisualVM while it is executing.

JConsole gives you a nice graph oh how much of the memory is being used. If you do some trial and error (like comment out the code that calls POI) you can figure out how much memory differrent parts of your system you are using

JVisualVM allows you to take a snapshot of the heap and inspect it. This will tell you exactly what objects are taking up memory. This will tell you where exactly the problem is but requires a little bit of learning curve.

Sometimes, OOMs can be caused by things outside your code. For example, we were getting OOM in one environment, and it was working fine in another. I used heap dumps to figure out the problem was someone had set hibernate logging to DEBUG, and hibernate in it's infinite wisdom logs the entire record when it reads and writes from the database. One of our tables had a huge BLOB, and if it's a Blob, Hibernate Base 64 encodes it and logs it Turning the logging back to INFO "fixed" the OOM.

The point is heap dump inspection will tell you where exactly the problem is. I would advise you to learn how to do it if you are going to be regularly playing with lots of data.


Thanks Javesh. But I dont have any idea of how to use JvisualM. and can you tell me how to do heap dump inspection? Thanks for your suggestion..!!!
Meer Nasirudeen
Greenhorn

Joined: Sep 30, 2007
Posts: 4
Hello everyone... I too faced the same issue of OOM while parsing xlsx file...after two days of struggle, I finally found out the below code that was really perfect;

This code is based on sjxlsx. It reads the xlsx and stores in a HSSF sheet.





 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Out of Memory Error - Java Heap Space while writing to Excel