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

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:

 

[keirS]

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