Apache POI公式细胞复制非常慢

I'm generating an excel file using Apache POI 3.8 , and have the need to replicate some existing row n° times.

我正在使用Apache POI 3.8生成一个excel文件,并且需要复制一些现有的行n°次。

This because I have some complex formula which I use as a template to create new lines, replacing cell indexes with regexps.

这是因为我有一些复杂的公式,我用它作为模板来创建新行,用regexp替换单元格索引。

The problem is that performance are awful, it takes 2h to generate some 4000 rows.

问题是性能很糟糕,生成大约4000行需要2小时。

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.

相反,它很快,即使我的正则表达式已经到位。

Anyway, this is an imperfect solution, because the formula has english keywords (ie IF()), when I need to write in italian format.

无论如何,这是一个不完美的解决方案,因为当我需要用意大利语格式编写时,公式有英文关键字(即IF())。

More, cells with formula inserted like that need to be forcefully re-evaluated in excel with something like "replace all -> '=' with '='".

更多的是,插入公式的单元格需要在excel中强制重新评估,例如“将所有 - >'='与'='”替换。

The problem seems to rely in the setCellFormula(), because of the HSSFFormulaParser.parse().

由于HSSFFormulaParser.parse(),问题似乎依赖于setCellFormula()。

What's strange, is that parsing time seems to grow exponentially:

奇怪的是,解析时间似乎呈指数级增长:

100 rows ->  6785ms
200 rows -> 23933ms
300 rows -> 51388ms
400 rows -> 88586ms

What it seems, is that each time I copy a formula, the POI library re-evaluates or re-parses or re-something all preceding rows.

看起来,每次复制公式时,POI库都会重新评估或重新解析或重新解释所有前面的行。

Do anyone know how can solve this problem? Thanks in advance.

有谁知道如何解决这个问题?提前致谢。

1 个解决方案

#1

Oh my...I think I found it...

哦,我...我想我发现了......

Original was:

// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null) {
    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
} else {
    newRow = worksheet.createRow(destinationRowNum);
}

I've commented everything leaving only

我评论了一切只留下

newRow = worksheet.createRow(destinationRowNum);

And now I'm down to 60sec to process all rows!

而现在我已经下降到60秒来处理所有行!

Probably, there's some dirt in my template which was causing POI to shift everything at each iteration.

可能在我的模板中有一些污垢导致POI在每次迭代时移动所有东西。

#1