How can I free memory held by an XSSFWorkbook?

Question

I have written some Java code that takes information from three spreadsheets and generates a large spreadsheet ( 10 columns, 26,950 rows ) on a machine with 4GB memory. It was crashing as it was generating the spreadsheet (out of memory), but by rewriting the code to read the large spreadsheet in chunks of 100 lines at a time, I've managed to get to the point where it is writing the output file as it hits the out of memory error.

So I'm thinking if I can free the memory held by the three XSSF workbooks, it might give me enough memory to complete the writing of the output file- but there doesn't appear to be a close() instruction.

I've tried setting the workbooks to null, but I'm not convinced that freed any memory.

Does anyone know the 'proper' way to close an XSSF workbook and reclaim the memory it takes up?

I'm running Java 1.8.0_25.

Answer 1

Try using SXSSFWorkbook instead of XSSFWorkbook. It is designed to flush rows to disk instead of holding them in memory.

Answer 2

You most likely hold on to some of the read worksheets data, otherwise it should get garbage collected.

You can

• use jvisualvm (included in your JDK) to find your memory leak,
• try Runtime.getRuntime().gc() for a explicit GC after releasing all references to the worksheet,
• take a look at the streaming usermode API to generate the new file with hopefully less memory requirements.