On Mon, Feb 27, 2012 at 12:41 PM, Raffaele Castagno
<[email protected]> wrote:
> I'm generating an excel file using Apache POI 3.8 , and have the need to
> replicate some existing row n° times.
>
> This because I have some complex formula which I use as a template to create
> new lines, replacing cell indexes with regexps.
>
> The problem is that performance are awful, it takes 2h to generate some 4000
> rows.
>
> I've pinpointed the problem to be not in the regexp part, as I initially
> thought, but in the duplication of formula cells.
>
> I actually use this to replicate formula cells:
>
> case Cell.CELL_TYPE_FORMULA:
> newCell.setCellType(oldCell.getCellType());
> newCell.setCellFormula(oldCell.getCellFormula());
> break;
>
> If I copy the formula as text like this:
>
> case Cell.CELL_TYPE_FORMULA:
> newCell.setCellType(Cell.CELL_TYPE_STRING);
> newCell.setCellValue("="+oldCell.getCellFormula());
> break;
>
> it's instead pretty fast, even with my regexp in place.
>
Calling setCellType is extra in both cases. Cell.setCellFormula
automatically changes the cell type to CELL_TYPE_FORMULA and
setCellValue to the appropriate time.
Is POI slow with replacing any formulas or only when setting your
special ones: you mentioned that your formulas are complex and it
might be the case.
You can estimate parsing time by calling the formula parser explicitly:
Ptg[] ptgs = HSSFFormulaParser.parse(formula, workbook);
Are you sure it is the bottleneck?
Yegor
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
