B4J Question POI Library - Formula Refresh Issue Again

[paragkini]

Hi, have one more problem now...
When the I change the values from B4J program, and use above methods (with B4J app still running) the formula cell shows old formula value.

Any idea why?

Refer my old thread here.

 

[Erel]

What has changed? I see that it did previously work for you.

 

[paragkini]

Yes it did.

But now when I update the numbers, in same B4J app i run "setforcerecalculation..." and try to read the new value in formula cell, but it shows the old one.
I also tried running same method then saving the file and reopening, but my numeric value is updated but formula cell value is not.

I tried apache site POI documentation and tried somemethods (evaluateAllFormulaCells, evaluateFormulaCell) but gives errors...

 

[Erel]

setForceFormulaRecalculation will not help if you are trying to read the updated value in the B4J code. It only forces Excel to recalculate the formulas when the workbook is opened.

To evaluate a cell (not tested):

Dim jo As JavaObject = Workbook1
Dim evaluator As JavaObject = jo.RunMethodJO("getCreationHelper", Null).RunMethodJO("createFormulaEvaluator", Null)
'now use evaluator whenever you want to evaluate a cell:
Log(evaluator.RunMethodJO("evaluate", Array(Cell1)).RunMethod("formatAsString", Null))

 

[paragkini]

Works perfectly fine... (Tough took some time to achieve what I wanted to do, but above code was the solution... )

Thanks.
I may be exaggerating, but you and other Java experts here are GODs for a Java-disabled person like me...