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.



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

Expert
Licensed User
Longtime User
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...

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

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

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

formulars.png


I am now seeing the actual values.

Thanks a million!
 
Top