JAVA - Out Of Memory Error while writing Excel Cells in jxl

Question

I am using JXL to write an excel file of 50000 rows and 30 columns. My code looks like this:

for (int j = 0; j < countOfRows; j++) {

myWritableSheet.addCell(new Label(0, j, myResultSet.getString(1), myWritableCellFormat));

myWritableSheet.addCell(new Label(1, j, myResultSet.getString(2), myWritableCellFormat));

.....

.....

}

While writing the cells, the program goes slower and slower

and finally around the row 25000 I am getting the following error:

Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space at jxl.write.biff.WritableSheetImpl.getRowRecord(WritableSheetImpl.java:984) at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:951) at KLL.ConverterMainFrame$exportToXLSBillRightsThread.run(ConverterMainFrame.java:6895)

It's always difficult in Java to handle the memory.

In this case it seems to be the jxl's problem.

Is there a way to write the file, clear the memory and coninue writing cells every 1000 cells?

Would that be a good idea or what else would you propose as a solution?

Answer 1

Is raising the memory available to the VM (with -Xms and -Xmx) not an option?

Answer 2

The JExcel FAQ has a couple of suggestions including Curtis' idea above.

If you don't mind the performance hit, you could use a temporary file instead of doing it all in memory.

WorkbookSettings s = new WorkbookSettings();  
s.setUseTemporaryFileDuringWrite(true);  
WritableWorkbook ws = Workbook.createWorkbook(new File("someFile.xls"),s); 

Answer 3

SORRY COULDN'T PUT IT AS A COMMENT (TOO MANY WORDS)

IT'S A COMMENT TO MARK's ANSWER

I can't find this property in the WorkbookSettings Object.

What version of jxl do you have?

The sets of the object are:

setArrayGrowSize(int sz) void

setCellValidationDisabled(boolean cv) void

setCharacterSet(int cs) void

setDrawingsDisabled(boolean b) void

setEncoding(String enc) void

setExcelDisplayLanguage(String code) void

setExcelRegionalSettings(String code) void

setFormulaAdjust(boolean b) void

setGCDisabled(boolean disabled) void

setlgnoreBlanks(boolean ignoreBlanks) void

setlnitialFileSize(int sz) void

setLocale(Locale 1) void

setMergedCellChecfcing(boolean b) void

setNamesDisabled(boolean b) void

setPropertySets(boolean r) void

setRationalization(boolean r) void

setSappressWarnings(boolean w) void