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

Discussion in 'B4J Code Snippets' started by keirS, Nov 30, 2018.

  1. keirS

    keirS Well-Known Member Licensed User

    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)
    "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
    Erel and DonManfred like this.
  2. Mashiane

    Mashiane Expert Licensed 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...

    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
        JOWB = wb
    'set evaluator
        JOFormulaEvaluator = JOWB.RunMethodJO("getCreationHelper"Null).RunMethodJO("createFormulaEvaluator"Null)
    "org.apache.poi.ss.formula.ConditionalFormattingEvaluator"Array(JOWB, JOFormulaEvaluator))
    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
    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
    Return ""
    End If
    End Sub
    So instead of seeing this..


    I am now seeing the actual values.

    Thanks a million!
    joulongleu and keirS like this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice