CODEWITHSUNDEEP
out-of-memoryapache-poi
Vasantha
Vasantha

Reputation: 11

Loading 8MB spreadsheet using XSSFWorkbook running into outofmemory

We have a requirement to load and read very large xlsx files, which might include 100s of columns and 1000s of rows. We tried to load a file with size 8MB and the Max heap size on both app and web servers are set to 4GB. The code is running into an out of memory at the following line 

XSSFWorkbook workbook = new XSSFWorkbook(is);

I wonder if a 8MB file occupy the heap size as big as 4GB. If that is the case, what can be done to avoid running into the out of memory issues.

Upvotes: 0

Views: 81

Answers (1)

centic
centic

Reputation: 15880

The 8MB file is compressed with the internal String/XML data being very easy to compress with a high ratio, so the actual data will actually be much more, although exceeding 4GB memory setting with this file still sounds strange..

If you want to be able to read arbitrarily rage files, then there is a streaming interface provided as part of POI via XSSFReader. This allows to read files similar to SAX XML Parsing without loading the full document into memory.

On the writing side you can take a look at SXSSFWorkbook which allows to write out large files via an internal streaming mechanism, see e.g. here for an example.

Upvotes: 1

Related Questions