Modify big excel (xslx) file in java

Question

I have been using Apache POI to process data coming from big files. Reading was done via the SAX event API which makes it efficient for large sets of data without consuming much memory.

However there's also a requirement that I need to update an existing template for the final report. This template may have more than 10MB (even 20MB in some cases).

Do you know a way to efficiently update a big template file (xslx)? Currently I am reading the entire contents of the template into memory and modify those contents (using XSSF from POI). My current method works for small files (under 5 MB) but for bigger files it fails with out of memory exception.

Is there a solution for this in Java? (not necessarily using Apache POI) Open source/free solutions are preferred but commercial is also good as long as it has a reasonable price.

Thank you,

Iulian

http://www.oracle.com/technetwork/java/hotspotfaq-138619.html#gc_oom and http://www.oracle.com/technetwork/java/hotspotfaq-138619.html#gc_heap_32bit and http://stackoverflow.com/questions/1473510/general-strategy-to-resolve-java-memory-leak may help — xmojmr, Jun 15 '14 at 05:24

Michael 'Maik' Ardan · Accepted Answer · 2014-06-25T01:13:51.523

1

For large spreadsheet handling, it is advisable to use SXSSF

As far as what I can think of, Streaming classes is a bit slower compared to HSSF and XSSF, but is far more superior when it comes to memory management (Feel free to correct me).

edited Jun 25 '14 at 01:13

answered Jun 24 '14 at 06:47

Michael 'Maik' Ardan

4,213
9
37
60

score 1 · Answer 2 · answered Jul 09 '14 at 11:41

This guy has made a few classes that can read Xlsx files and process them in XML. It returns an array with Strings, these Strings are practically the rows of an Xlsx file.

Link:

You could then use these arrays to load them line by line in a stream, instead of all of them at once.

score 0 · Answer 3 · edited May 06 '15 at 21:36

Most likely the message you are facing is about heap space (java.lang.OutOfMemoryError: Java heap space), which will be triggered when you try to add more data into the heap space area in memory, but the size of this data is larger than the JVM can accommodate in the Java heap space. In many cases you are good to go with just increasing the heap size by specifying (or altering if present) the -Xmx parameter, similar to following:

-Xmx1024m

Modify big excel (xslx) file in java

3 Answers3