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

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

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



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

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

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

    Code:
    '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!
     
    joulongleu and keirS like this.
Loading...
  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