B4J Code Snippet Tip - Forcing Excel Columns To Be Stored As Text

Discussion in 'B4J Code Snippets' started by Erel, Jul 8, 2019.

  1. Erel

    Erel Administrator Staff Member Licensed User

    Use case: reading an Excel workbook where the column data is not consistent. Some of the cells are correctly stored as dates and some as text. Handling the various types is easy in B4J assuming that you can access the raw text value.

    In Excel:

    1. Select the column.
    2. Data - Text to Columns
    3. Choose the default values in the first two screens. It doesn't really matter.
    4. Change the format to Text:

    DonManfred and Johan Hormaza like this.
  2. keirS

    keirS Well-Known Member Licensed User

    You can do this in JPOI. This sub gets the text value of the cell (what is displayed to the user) rather than the underlying cell value.

    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
    DonManfred likes 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