B4J Code Snippet jPOI Get A Cells Text Property (Display Value)

The code is the equivalent of the VBA .Text property of a cell. It's the value displayed to the user including formatting such as thousands separators,decimal places and currency. It requires the latest version the jPOI library that wraps Apache POI 4.00.

Sub PoiGetFormattedCellValue(ExcelCell As PoiCell) As String
    Dim JOCell As JavaObject = ExcelCell
    Dim JODataFormatter As JavaObject
    Dim JOConditionalFormattingEvaluator As JavaObject
    Dim JOFormulaEvaluator As JavaObject =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)))
    Return JODataFormatter.InitializeNewInstance("org.apache.poi.ss.usermodel.DataFormatter",Null).RunMethod("formatCellValue",Array As Object (JOCell,JOFormulaEvaluator,JOConditionalFormattingEvaluator))
   
End Sub

 

[Mashiane]

Thanks a lot @keirS for this, @OliverA referred me to it as I just posted a question about it.

Can I add something? if you don't mind (based on your solution), as my excel files contain a lot of formulars to process, I have re-written the code like this..

In my Process_Globals, I have defined these...

Private JOFormulaEvaluator As JavaObject
    Private JOWB As JavaObject
    Private JOConditionalFormattingEvaluator As JavaObject
    Private JODataFormatter As JavaObject

Then when I open my workbook, I call this code..

Dim wb As PoiWorkbook
    wb.InitializeExisting(dir,fil,pwd)
    JOWB = wb
    'set evaluator
    JOFormulaEvaluator = JOWB.RunMethodJO("getCreationHelper", Null).RunMethodJO("createFormulaEvaluator", Null)
    JOConditionalFormattingEvaluator.InitializeNewInstance("org.apache.poi.ss.formula.ConditionalFormattingEvaluator", Array(JOWB, JOFormulaEvaluator))
    JODataFormatter.InitializeNewInstance("org.apache.poi.ss.usermodel.DataFormatter",Null)

And then finally re-wrote my script as...

'return the cell value
Sub PoiGetCell(cell As PoiCell, bFormulasAsValues As Boolean) As String
    If cell.IsInitialized = True Then
        Select Case cell.CellType
            Case cell.TYPE_NUMERIC
                Return cell.ValueNumeric
            Case cell.TYPE_STRING
                Return cell.ValueString
            Case cell.TYPE_FORMULA
                If bFormulasAsValues = False Then
                    Return cell.ValueFormula
                Else
                    Dim JOCell As JavaObject = cell
                    Dim cv As String = JODataFormatter.RunMethod("formatCellValue",Array As Object (JOCell,JOFormulaEvaluator,JOConditionalFormattingEvaluator))
                       Return cv
                End If
            Case cell.TYPE_BLANK
                Return ""
            Case cell.TYPE_BOOLEAN
                Return cell.ValueBoolean
            Case cell.TYPE_ERROR
                Return ""
            Case Else
                Return cell.Value
        End Select
    Else
        Return ""
    End If
End Sub

So instead of seeing this..

I am now seeing the actual values.

Thanks a million!