OutofMemoryError:Java heap space when writing large no. of rows to xls file

Question

I need to write a resultset of more than 1,000,000 rows in xls file(Microsoft 97-xp). So I am trying to use JExcel API But I get error OutOfMemoryError: Java heap space . How do I solve this problem besides increasing JVM memory or creating different excel files and then merge them manually. I don't even want to create CSV file. Please help.

Sample Code:

int sheetNumber = 1;
int maxSheetSize=65000;
int start = 0;
int end=maxSheetSize;
int totalsize=1000000;
int completed = 0;
int columnCount=10;
WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));

while (completed < totalsize) {
    WritableSheet sheet = workbook.createSheet("Sheet " + (sheetNumber++), 0);
    for (int r = start; r < end; r++) {
        int i=1;
        for (int c = 0; c < columnCount; c++) {
            Label label = new Label(c, i, "data from resultset");
            sheet.addCell(label);
        }
        i++;
    }
    completed += (end - completed);
    start = end;
    end = (totalsize - completed) < maxSheetSize ? (totalsize - completed) : completed + maxSheetSize;
    end = end < maxSheetSize ? totalsize : end;
}
workbook.write();
workbook.close();

Writing 1M rows in Excel is not supported. See http://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP005199291.aspx — Guillaume Darmont, Nov 15 '13 at 07:30

score 2 · Answer 1 · edited May 23 '17 at 11:51

2

That old versions of the excel format only supports 65,536 rows by 256 columns.

"All existing Java APIs try to build the whole document in RAM at once. Try to write an XML file which conforms to the new xslx file format instead. To get you started, I suggest to build a small file in the desired form in Excel and save it. Then open it and examine the structure and replace the parts you want." Aaron Digulla - API to write huge excel files using java

edited May 23 '17 at 11:51

Community

1
1

answered Nov 15 '13 at 07:29

spydon

9,372
6
33
63

Use @user2310289 solution; re-open the worksheet some times during the process so all of it isn't stored in RAM. – spydon Nov 15 '13 at 10:23

score 0 · Answer 2 · answered Nov 15 '13 at 07:31

0

Up until Excel 2003 the max rows is 65,536.
Even if you use later versions, I would recommend that you re-open the worksheet and append say a max number of rows (Maybe 10000). Close, Open and repeat.

answered Nov 15 '13 at 07:31

Scary Wombat

44,617
6
35
64

OutofMemoryError:Java heap space when writing large no. of rows to xls file

2 Answers2