Sebastian Brocks-2 wrote:
>
> 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]
>
>
>
I've noticed a similar problem when creating a new evaluator for each
evaluation I need to do. The time consumed for evaluating a formula
increases dramarically, as Sebastian has already mentioned. Reusing the same
evaluator does not end up in such long running evaluations though, but on
the POI-website it is explicitly mentioned that there would be no great
overhead doing this. Well, for me, a 30 seconds gap between reusing a
evaluator and not doing that seems to be sort of overhead to me....
--
View this message in context:
http://www.nabble.com/performance-difference-between-HSSFFormulaEvaluator-evaluateInCell-and-evaluateFormulaCell-tp15618583p18333526.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
