On Tue, Jul 8, 2008 at 9:32 AM, Meandron <[EMAIL PROTECTED]> wrote:
>
>
>
> 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....
If you're looking for speed in evaluating spreadsheet formulas in
Java, maybe http://formulacompiler.org/ might be for you. It compiles
the formulas down to JVM byte code.
-parren
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
