B4J Question Problem POI with dates from excel

[jopevazquez]

Hello all,

I'm using erel function to get data from an excel file, but I have a problem with dates.

I need them as a DD/MM/YYYY but it is returning MM/DD/YY

I'm sending all cells to this function, then I don't know the colums that are a date.


Is there any way to force locale?

This is Erel function

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

Thanks a lot for your help

 

[walt61]

Hi @jopevazquez , this should help:

Sub GetFormattedCellValue(workbook As XLWorkbookWriter, cell As PoiCell)

    Dim props As Map
    props.Initialize

    Dim val As String = cell.Value.As(String)
    XLgetCellStyleInfo(workbook, props, cell.CellStyle)
    Select Case props.GetDefault("setDataFormat", 0).As(Int) ' See https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 37, 38, 39, 40, 41, 42, 43, 44
            ' Numbers with or without decimals
        Case 11, 48
            ' Float
        Case 14, 15, 16, 17, 169 ' 169: not documented on the aforementioned page, found it experimentally
            ' Date
            Log("Date: " & DateToDDMMYYYYwithSlashes(val.As(Long)))
        Case 18, 19, 20, 21, 45, 46, 47
            ' Time
        Case 22
            ' Datetime
            Log("Date: " & DateToDDMMYYYYwithSlashes(val.As(Long)))
        Case Else
            ' Text
    End Select

    xui.MsgboxAsync("Hello World!", "B4X")

End Sub

'Call as:
'Dim props as Map
'Dim Cell As PoiCell
'XLgetCellStyleInfo(props, Cell.CellStyle)
Public Sub XLgetCellStyleInfo(workBook As XLWorkbookWriter, props As Map, CellStyle As PoiCellStyle)

    ' Code from XLUtils.b4xlib/XLSheetWriter.bas - see https://www.b4x.com/android/forum/threads/xlutils-jpoi-5-read-styles.140554

    Dim jStyle As JavaObject = CellStyle
    For Each methods As List In workBook.InternalAllSimpleMethods
        For Each method As String In methods
            props.Put(method, jStyle.RunMethod("g" & method.SubString(1), Null))
        Next
    Next
    props.Put("setFont", jStyle.RunMethod("getFontIndex", Null))
    For Each method As String In workBook.InternalStyleStrings
        props.Put(method, jStyle.RunMethodJO("g" & method.SubString(1), Null).RunMethod("toString", Null))
    Next

End Sub

Public Sub DateToDDMMYYYYwithSlashes(dateIn As Long) As String

    Dim OrigFormat As String = DateTime.DateFormat 'save orig date format
    DateTime.DateFormat = "dd/MM/yyyy"
    Dim YYYYMMDDout As String = DateTime.Date(dateIn)
    DateTime.DateFormat = OrigFormat 'return to orig date format
    Return YYYYMMDDout

End Sub

 

[emexes]

I need them as a DD/MM/YYYY but it is returning MM/DD/YY

Is there any way to force locale?

Or you could just flip the MM month and DD day around, and add in the century eg YY = 00-23 = 2000-2023 and YY =24-99 = 1924-1999 will cover birthdates for people up to (almost) 100 years old.

 

[emexes]

I need them as a DD/MM/YYYY but it is returning MM/DD/YY

Lol you've just given me some bad déjà vu / PTSD about the leadup to Y2K and a troublemaking customer who was determined to prove that we hadn't prepared for it.

(which we had ? and with bells on too ? it was a programming work of art ?)