Two Laptop Bag*
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

Java Heap memory Error while writing large data to Excel

Subramanian N V

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.values.XmlObjectBase.xmlText(
at org.apache.xmlbeans.impl.values.XmlObjectBase.toString(
at org.apache.poi.xssf.model.SharedStringsTable.addEntry(
at org.apache.poi.xssf.usermodel.XSSFCell.setCe llValue(
at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(
at com.brocade.swportal.reports.util.ResultSetToExcel.writeCell(
at com.brocade.swportal.reports.util.ResultSetToExcel.writeCell(
at com.brocade.swportal.reports.util.ResultSetToExcel.generate(
at com.brocade.swportal.reports.util.ResultSetToExcel.generate(

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?

Ninad Naik

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

Joined: Dec 08, 2010
Posts: 1502

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.

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

Joined: Mar 22, 2005
Posts: 41101
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

Joined: May 26, 2003
Posts: 30116

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
I agree. Here's the link:
subject: Java Heap memory Error while writing large data to Excel
Similar Threads
Page Count in PDF document and doesn't start to execute
Write Huge Excel file (.Xlsx) POI and Java - java.lang.OutOfMemoryError: Java heap space
How to read excel file of 2MB and having around 200 sheets