Simple Excel report leads to out-of-memory error

Support
#1

Hi,

I’ve been working on an Excel report which should contain some static content, not only the generated data but also some introduction, headers etc.

I’ve tried to create something and ended up running into an OOM. Some testing brought it down to just having a few named ranges and empty bands in the report itself. Still, the OOM appears.

Previously I had some data also but that didn’t make a difference.

This report: TestReport.zip, can be uploaded into any Cuba 7.2.15 application that also includes the reporting addon. When doing so and running the report, the OOM appears (at least in all of my tests).

As a side note, the Excel file has been worked on using LibreOffice.

By the way, I find creating Excel report using the reporting addon very cumbersome. Why all information is removed that is not mapped by a named range that also needs to match a band in the report is beyond my understanding. This really makes creating reports very hard and something that cannot be used by end users at all. Especially if it leads to an OOM this easy. I really doubt whether anyone uses this in an Enterprise environment which would require some make-up / styling, introduction texts etc.

Anyway - I hope you can point me to a solution here because this is a definite must have.

Regards,
-b

#3

Hi,

Any news on this? It’s still a problem as I cannot get the Excel reports to work nicely.

I’ve created an Excel that avoids the OOM but when I put a header on tob of a table (defined by a named range matching the band in the report), the data is generated starting from row 1, instead of below the header region.

It’s rather frustrating to be honest. Hope anyone can help.

Regards,
-b

#4

Hi,

Why did you define this unlimited named region?

Screenshot 2021-11-12 at 12.23.07
Screenshot 2021-11-12 at 12.23.07867×645 112 KB

It’s always better to limit named regions both horizontally and vertically.

If you change the region borders to something like $Blad1.$A$1:$B$9 everything should work:

Screenshot 2021-11-12 at 12.24.40
Screenshot 2021-11-12 at 12.24.40864×609 87.1 KB

#5

Hi @gorbunkov,

Thanks for your reply on this, much appreciated. The reason why I made this unlimited named range is that we want to have a template prepared that end users can alter freely without the burden of defining all kinds of named ranges.

After we limited the range and found out that order of bands in the report matter a great deal in generating a correct report, we have managed to get a workable solution.

On the other hand, it still worries me that an end user can easily crash the system (OOM) by putting a unlimited named range in an Excel file, upload it and click ‘run report’. Even unintended by the end user, this will cause problems to us.

This is something of a bug in my opinion - do you agree?

Regards,
-b

#6

I don’t think it’s a bug. That’s how report generator works under the hood - it processes and copies all cells from the named region into result document.

#7

Hi @gorbunkov,

Thanks again for coming back on this.

I do not agree that it isn’t a bug however. If an end user is able to let the system crash on an OOM that easily it should be prevented. So a check in the code on an unlimited range / region would be a good thing I guess.

I hope we can prevent the OOM by implementing a pre-check ourselves but I really think it should be handled by the report generation.

Regards,
-b

#8

HI @gorbunkov,

I’ve managed to avoid the memory issue by overriding the XlsxFormatter and adding a check on the copyCells method:

    @Override
    protected List<Cell> copyCells(BandData band, Range templateRange, List<Row> resultSheetRows, Row firstRow, Worksheet resultSheet) {
        // Check range to avoid memory issues
        if (templateRange.getLastRow() - templateRange.getFirstRow() < 1000 &&
                templateRange.getLastColumn() - templateRange.getFirstColumn() < 100)
            return super.copyCells(band, templateRange, resultSheetRows, firstRow, resultSheet);

        log.warn("Template contains defined ranges that exceed the limits of 1000 rows x 100 columns for band {}", band.getName());
        return new ArrayList<>();
    }

Seems to work well.

Regards,
-b