Hello,
I’ve found a way to generate our report without getting the exception!
But first, let me explain how we used to generate this report:
Before generating the XLS file, we read the template (hosted in our database), and search for specific cells to retrieve the styles. We have about 400 styles (many of them are similar though), because we have 5 different types of lines, with about 80 columns each.
We use a “DTO” object to store some information about the cells, as well as the Style itself.
In our code, during this initialization phase, we do something like that:
myDto.setStyle1(cells.getCell(START_ROW_INDEX, columnNo).getStyle());
myDto.setStyle2(cells.getCell(START_ROW_INDEX + 1, columnNo).getStyle());
myDto.setStyle3(cells.getCell(START_ROW_INDEX + 2, columnNo).getStyle());
…
Then, we have the “real” Excel file generation.
For each record, we insert a new row in our Excel file, and on each column, we apply the Style on the cell. We do something like that:
cells.insertRows(rowIndex, 1);
for (Data data : someData) { // we iterate on the 80 columns…
Cell cell = cells.getCell(XXX);
cell.setStyle((Style) myDto.getStyle1());
…
}
It seems that when many records are processed, we get the famous exception. Note that we are talking of more than 100,000 cell style application (some reports may also have more than 2 millions of them!).
Now, the solution I found consists in re-generating the Style instead of getting it from the template. In others words, during the initialization I now do that:
myDto.setStyle1(recreateStyle(workbook, cells.getCell(START_ROW_INDEX, columnNo).getStyle()));
myDto.setStyle2(recreateStyle(workbook, cells.getCell(START_ROW_INDEX + 1, columnNo).getStyle()));
myDto.setStyle3(recreateStyle(workbook, cells.getCell(START_ROW_INDEX + 2, columnNo).getStyle()));
…
and the code for recreateStyle method is simply a “transfert” of properties:
private Style recreateStyle(Workbook workbook, Style raw) {
// We create a new Style from the Workbook…
Style style = workbook.createStyle();
// We apply all the properties from the “raw” Style
style.setNumber(raw.getNumber());
style.setName(raw.getName());
style.setColor(raw.getColor());
…
// We do the same thing on the Font
Font font = style.getFont();
Font rawFont = raw.getFont();
font.setBold(rawFont.isBold());
…
style.setFont(font);
return style;
}
No modification has been done in the rest of the code, especially on the report generation part itself.
This modification give two advantages:
- It works, even with reports with lots of data
- It works really faster (I didn’t measure it, but it seems that 2x or 3x faster!)
What I am thinking is that when we get the Style from the template itself (i.e. the old code), we keep some properties in that Style object that may not be handled correctly by Aspose, especially when these objects are used more than 100,000 times.
So using a fresh Style, generated from the code itself, help us to keep a Style with good properties, and also improve the performances!
So I think we will use this approach now…
Anyway, if you need more details about the problem we were facing, do not hesitate to ask.
Thanks again.
)