2

I've been appending rows to existing XLSX workbooks using java and Apache POI.
The problem is that I keep running out of memory as the workbooks in question grow, and the -Xmx switch only helps to a point.

The actual reading of large workbooks isn't a problem since I can use the SAX event API for that, and writing large amounts of new data isn't a problem since that's handled by SXSSF, but what I'm in need of is some memory effect way of appending rows to already large workbooks.

Using the SXSSFWorkbook(XSSFWorkbook) constructor has been recommended, but as I see it that still means loading the whole (already huge) workbook into memory.
It's also been recommended that one should load the workbook as a File rather than using an input stream to preserve memory, however then I'm assuming I'd have to write everything out to an entirely new file.

So, is there anyone here who knows of a way, using Apache POI, to append rows to already existing (and huge) workbooks without running out of memory?

coderpc
  • 4,119
  • 6
  • 51
  • 93
Lbm
  • 21
  • 2
  • I don't see a better way than to use the streaming API to read the data and write it using the SXSSF to a new file, but then you will be copying all the data every time, which likely has impact on the runtime of the whole operation... – centic Dec 15 '16 at 05:33
  • Using Office Open XML (`*.xlsx`) we could pick only those XML documents from the ZIP archive which are really needed. Of course that sounds more easy as it will be. See: http://stackoverflow.com/questions/35805878/add-content-to-a-very-large-excel-file-using-apache-poi-run-out-of-alternatives/35826943#35826943 – Axel Richter Dec 15 '16 at 09:21
  • Well, first of all, thanks for the comments. As for the issue itself I didn't come up with any good solution myself but was able to split the files in question into smaller ones to get around the memory problems. – Lbm Dec 27 '16 at 15:00

0 Answers0