Search code examples
javajxl

JExcel (jxl): Writing large record set to excel Worksheet fails (OutOfMemoryError: Java heap space)

I'm trying to write a result set (100,000 rows, 45 cols) from a web application to excel using JExcel 2.6.9.

The process fails with:

Caused by:
java.lang.OutOfMemoryError - Java heap space
at jxl.write.biff.WritableSheetImpl.getRowRecord(WritableSheetImpl.java:1179)

I've tried the following:

        WorkbookSettings ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        ws.setUseTemporaryFileDuringWrite(true);

        WritableWorkbook workbook = Workbook.createWorkbook(new File("tempExcel.xls"),ws);

so Basically in a while loop, the ResultSet from database is iterated and then

s.addCell(new Label(colIndex, rowIndex, value, format)); is done for each row.

I'm not quit sure how to get pass this issue. There is memory constraints in the env where this application is running from so to simply allocate more RAM for JVM is not an option unless there is absolutely no other way. I would appreciate any feedback on this. Thanks

Solution

  • You can't put X pounds of anything into a Y sized bag if X > Y.

    That's 4.5 million values. If lots of them are Strings, with an average size of 1K bytes each, you'd have ~4.5GB of data.

    If you've got the whole thing in memory at once you're in trouble. The solution is to chunk it into smaller pieces, commit them, and then create the next chunk.

    I'd wonder if any web client needed that much data at once. Another solution would be better filtering to limit the size of the ResultSet.