wood burning stoves*
The moose likes Performance and the fly likes Java Heap memory Error while writing large data to Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Java Heap memory Error while writing large data to Excel" Watch "Java Heap memory Error while writing large data to Excel" New topic
Author

Java Heap memory Error while writing large data to Excel

Subramanian N V
Greenhorn

Joined: Mar 05, 2008
Posts: 5
Hello all,

Good Day!

i have to write more than 100000 rows in a excel sheet (file size more than 20 MB) via java.

when i use XSSF, i am getting below Error.

java.lang.OutOfMemoryError: Java heap space
at org.apache.xmlbeans.impl.store.Saver$TextSaver.resize(Saver.java:1592)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.preEmit(Saver.java:1223)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.emit(Saver.java:1144)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.emitElement(Saver.java:926)
at org.apache.xmlbeans.impl.store.Saver.processElement(Saver.java:456)
at org.apache.xmlbeans.impl.store.Saver.process(Saver.java:307)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.saveToString(Saver.java:1727)
at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546)
at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436)
at org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1455)
at org.apache.xmlbeans.impl.values.XmlObjectBase.toString(XmlObjectBase.java:1440)
at org.apache.poi.xssf.model.SharedStringsTable.addEntry(SharedStringsTable.java:162)
at org.apache.poi.xssf.usermodel.XSSFCell.setCe llValue(XSSFCell.java:304)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:275)
at com.brocade.swportal.reports.util.ResultSetToExcel.writeCell(ResultSetToExcel.java:186)
at com.brocade.swportal.reports.util.ResultSetToExcel.writeCell(ResultSetToExcel.java:167)
at com.brocade.swportal.reports.util.ResultSetToExcel.generate(ResultSetToExcel.java:111)
at com.brocade.swportal.reports.util.ResultSetToExcel.generate(ResultSetToExcel.java:164)

when i use HSSF , i am getting the below Error.
java.lang.OutOfMemoryError: Java heap space


I have tried increasing the java heap size , by giving upto -Xms1500m -Xmx2048m

none of them helps.

can some one help in providing a solution for this?

thanks
Subbu
Ninad Naik
Greenhorn

Joined: Jun 11, 2012
Posts: 4

Can you please post some code? Specifically, the code where you loop through the ResultSet and create rows.
Anayonkar Shivalkar
Bartender

Joined: Dec 08, 2010
Posts: 1505
    
    5

Hi Subramanian,

Further to suggestion made by Ninad, you can pass -XX:+HeapDumpOnOutOfMemoryError and -XX:HeapDumpPath arguments to JVM so that you'll get a heap dump whenever JVM crashes with OOME.

This heap can further be analysed via profiler tool.


Regards,
Anayonkar Shivalkar (SCJP, SCWCD, OCMJD, OCEEJBD)
Vaibhav G Garg
Ranch Hand

Joined: Sep 23, 2011
Posts: 140
Also, you can use threading to write to the excel file. It will help in saving some memory and fasten your process.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41509
    
  53
You mean multithreading? That is a bad idea for accessing files, nor will it save memory. I would question the idea of creating such a large Excel file; such files are for human use, and no person will want to look at so much data. If the idea is data storage, then there are better alternatives.


Ping & DNS - my free Android networking tools app
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30299
    
150

While there are sone reporting reasons to download that much data, see if you can use a CSV format instead of Excel. It will be faster to create, use less memory and create a smaller file. Theuser. Can save the downloaded file into a full fledged Excel file if he/she needs pivot tables or some other feature.

Also, make sure you don't have any users with very old versionsof E xcel. Before Excel 2007 came out, the limit was aro und 65k rows in an Excel file.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Amit java
Greenhorn

Joined: Jul 30, 2014
Posts: 2
Ulf Dittmer wrote:You mean multithreading? That is a bad idea for accessing files, nor will it save memory. I would question the idea of creating such a large Excel file; such files are for human use, and no person will want to look at so much data. If the idea is data storage, then there are better alternatives.


Use batch to write data, else writing such huge will create OOM issues.
Rajit kumar
Greenhorn

Joined: Mar 04, 2012
Posts: 11
Do increase permgen size. Hopefully,that would solve problem.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java Heap memory Error while writing large data to Excel