• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Java Heap memory Error while writing large data to Excel

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 4
Mac Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you please post some code? Specifically, the code where you loop through the ResultSet and create rows.
 
Bartender
Posts: 1558
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 143
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 43081
77
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 42073
932
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do increase permgen size. Hopefully,that would solve problem.
 
I've never won anything before. Not even a tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic