jxls file generation hangs for ever when rows > 10,000

Question

I have a excel report that gets generated. Most of the time, it works fine when rows are up to about 2,000.

This morning, I tried to generate a report that has 11,000 rows and could not figure out why it would just hang since it was following the same unchanged code - see below. I still can't figure it out besides the fact that XLSTransformer could be really slow?

List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>();
    Map<String, Object> sheetData = null;
    for(EntityForecastWrapper wrapper : wrappers) {
        sheetName = Integer.toString(wrapper.getEntityId());
        sheetNames.add(sheetName);
        sheetData = new HashMap<String, Object>();
        sheetData.put("wrapper", wrapper);
        maps.add(sheetData);
    }
    Map<String, Object> beansMap = new HashMap<String, Object>();
    Workbook workbook = null;
    XLSTransformer transformer = new XLSTransformer();
    try {
        Logger.getLogger(getClass()).error("generating excel");
        InputStream is = getClass().getResourceAsStream(getReportTemplate().getLabel());
        workbook = transformer.transformMultipleSheetsList(is, maps, sheetNames, "map", beansMap, 0);
        Logger.getLogger(getClass()).error("generated excel");
        /*FileOutputStream fos = new FileOutputStream(new File(fileName));
        workbook.write(fos);
        fos.flush();
        fos.close();*/
    } catch(Exception e) {
        Logger.getLogger(getClass()).error("Error writing excel data: ", e);
    }

The code basically never comes back from line:

workbook = transformer.transformMultipleSheetsList(is, maps, sheetNames, "map", beansMap, 0);

It is very fast when the row count is not huge (eg 500 to 1000 rows). The row content never changes, same # of columns...

Any ideas?

Thanks!

score 5 · Answer 1 · answered Oct 03 '12 at 06:43

Jxls tries to build the whole document in RAM at once. I had a quick flip over the code and saw, that jxls uses the POI HSSFWorkbook internally - no streams to help the processing of huge amount of data.

A solution that might work is to process the data in smaller parts (in my case ~1000 Entries) and save the output of jxls temporary on the file system. After all data is processed, merge the files using poi. Not a very pretty solution...

score 1 · Answer 2 · edited Oct 30 '19 at 14:00

1

In our case this happens when the JXLS template uses dynamic columns or other tags. When we have a simple template just with ${name} property access then it runs fast. We have switched to direct POI API for more complicated reports.

Update: also processing of XLSX templates consumes much more memory and time then XLS templates.

edited Oct 30 '19 at 14:00

hariprasad

555
11
20

answered Jan 22 '13 at 15:47

xmedeko

7,336
6
55
85

score 0 · Answer 3 · answered Oct 30 '19 at 13:40

We did many tests with Jxls XLS Transformer and some comparison with Jett XLS Transformer.

Jxls transformer (ver. 2.6.0):

Very quick in simple generation to XLS (about 1.5-2 times faster than Jett), when everything is prepared (no any formatting or formulas, only simple text) e.g. 12000 rows takes 2 sec.
Very very slow (performance leak) generation to XLSX. About 1000 times (sic!) slower, than generating to XLS, e.g. 12000 rows takes 30 min.
Template has limited capabilities, something not working well or there is no good explanation, how to use it

Jett transformer (0.11.0 beta):

Quick in simple generation to XLS (about 1.5-2 times slower than Jxls), when everything is prepared (no any formatting or formulas, only simple text) e.g. 12000 rows takes 4 sec.
Slower generation to XLSX. About 10 times slower, than generating to XLS, e.g. 12000 rows takes 40 sec.
Template has simpler syntax, and more capabilities (not tested)

jxls file generation hangs for ever when rows > 10,000

3 Answers3