B4J Question PoiExcel Value from Formula cell

[rodmcm]

How do I read the value from a formula cell?

row.GetCell(2).ValueNumeric does not work and row.GetCell(2).Value returns the formula.

I've tried changing the cell type from formula to numeric before a read but this also fails

Cant think of any other options

 

[Erel]

POI Library - Formula Refresh Issue Again

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.

www.b4x.com

 

[KMatle]

Had the same issue. Solved by using this snippet to get the displayed (= "what the Excel-user sees") value:

Dim JOCell As JavaObject
    Dim JODataFormatter As JavaObject
    Dim JOConditionalFormattingEvaluator As JavaObject
    Dim JOFormulaEvaluator As JavaObject
    

Cell = Row.GetCell(c) ' get the cell 
 JOCell = Cell
            
JOFormulaEvaluator =JOCell.RunMethodJO("getSheet",Null).RunMethodJO("getWorkbook",Null).RunMethodJO("getCreationHelper",Null).RunMethodJO("createFormulaEvaluator",Null)
                    JOConditionalFormattingEvaluator.InitializeNewInstance("org.apache.poi.ss.formula.ConditionalFormattingEvaluator",Array As Object (JOCell.RunMethodJO("getSheet",Null).RunMethodJO("getWorkbook",Null),JOCell.RunMethodJO("getSheet",Null).RunMethodJO("getWorkbook",Null).RunMethodJO("getCreationHelper",Null).RunMethodJO("createFormulaEvaluator",Null)))
                    CContent=JODataFormatter.InitializeNewInstance("org.apache.poi.ss.usermodel.DataFormatter",Array As Object(locale)).RunMethod("formatCellValue",Array As Object (JOCell,JOFormulaEvaluator,JOConditionalFormattingEvaluator))