Hi there,
I've been using an svn build of 3.0.alpha2, from November 27, 2006 for
the last 1 1/2 years to evaluate some formulas, using this code:
for ( int i = tStartCellRow.getRowNum(); i <=
tEndCellRow.getRowNum(); i++)
{
HSSFRow tRow = tSheet.getRow(i);
tEvaluator.setCurrentRow(tRow);
for (int j = tStartCellRef.getCol(); j <= tEndCellRef.getCol();
j++)
{
HSSFCell tCell = tRow.getCell((short)j);
if (tCell != null && tCell.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA)
{
HSSFFormulaEvaluator.CellValue tCellValue =
tEvaluator.evaluate(tCell);
for ( int i = tStartCellRow.getRowNum(); i <=
tEndCellRow.getRowNum(); i++)
{
HSSFRow tRow = tSheet.getRow(i);
tEvaluator.setCurrentRow(tRow);
for (int j = tStartCellRef.getCol(); j <= tEndCellRef.getCol();
j++)
{
HSSFCell tCell = tRow.getCell((short)j);
if (tCell != null && tCell.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA)
{
HSSFFormulaEvaluator.CellValue tCellValue =
tEvaluator.evaluate(tCell);
tCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
tCell.setCellValue(tCellValue.getNumberValue());
cCat.debug(i+" "+j);
}
}
}
tWorkbook.write(tFos);
This worked perfectly fine, and pretty fast, taking less than a second
for about 1500 cells, but the forumula are lost in that case.
Just now I updated to 3.0.2 final release, to use the new
evaluateFormulaCell method.
The old code above runs just as fast as before, which is good.
But, when I replace the evaluate call and the new value setting above
with just one evaluateFormulaCell call, it becomes agonizingly slow
after a few cells have been evaluated.
Take a look at this log:
19:32:08,718 DEBUG [RecalcXLSProcessor] 38 2
19:32:08,734 DEBUG [RecalcXLSProcessor] 38 3
19:32:08,734 DEBUG [RecalcXLSProcessor] 38 4
19:32:08,734 DEBUG [RecalcXLSProcessor] 38 5
19:32:08,750 DEBUG [RecalcXLSProcessor] 38 6
19:32:08,765 DEBUG [RecalcXLSProcessor] 38 7
19:32:08,781 DEBUG [RecalcXLSProcessor] 38 8
19:32:08,812 DEBUG [RecalcXLSProcessor] 38 9
19:32:08,859 DEBUG [RecalcXLSProcessor] 38 10
19:32:08,921 DEBUG [RecalcXLSProcessor] 38 11
19:32:09,015 DEBUG [RecalcXLSProcessor] 38 12
19:32:09,218 DEBUG [RecalcXLSProcessor] 38 13
19:32:09,609 DEBUG [RecalcXLSProcessor] 38 14
19:32:10,375 DEBUG [RecalcXLSProcessor] 38 15
19:32:11,906 DEBUG [RecalcXLSProcessor] 38 16
19:32:15,031 DEBUG [RecalcXLSProcessor] 38 17
19:32:21,171 DEBUG [RecalcXLSProcessor] 38 18
19:32:33,421 DEBUG [RecalcXLSProcessor] 38 19
19:32:57,937 DEBUG [RecalcXLSProcessor] 38 20
19:33:47,109 DEBUG [RecalcXLSProcessor] 38 21
The first few cells are evaluated very fast, but then the time taken to
evaluate each formula increases dramatically, even though the formulas
are basically the same, just referencing other columns.
Anyone have any ideas?
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
