Forums Register Login

Java Heap memory Error while writing large data to Excel

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Can you please post some code? Specifically, the code where you loop through the ResultSet and create rows.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Also, you can use threading to write to the excel file. It will help in saving some memory and fasten your process.
2
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
Do increase permgen size. Hopefully,that would solve problem.
Enjoy the full beauty of the english language. Embedded in this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 4579 times.
Similar Threads
Write Huge Excel file (.Xlsx) POI and Java - java.lang.OutOfMemoryError: Java heap space
APACHE POI
Page Count in PDF document
How to read excel file of 2MB and having around 200 sheets
startNodeManager.sh and startWeblogic.sh doesn't start to execute
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 14:57:53.