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.
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...
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):
B4X:
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))