File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java Heap memory Error while writing large data to Excel

 
Subramanian N V
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
Linux Mac
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you please post some code? Specifically, the code where you loop through the ResultSet and create rows.
 
Anayonkar Shivalkar
Bartender
Posts: 1557
5
Eclipse IDE Java Linux
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vaibhav G Garg
Ranch Hand
Posts: 143
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, you can use threading to write to the excel file. It will help in saving some memory and fasten your process.
 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33713
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amit java
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do increase permgen size. Hopefully,that would solve problem.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic