B4J Question jPOI: Cell.Type "Date"?

KMatle

Expert
Licensed User
I have an Excel file with a date column. When I check and read the cell it returns NUMERIC as the cell type. Unfortunately there is no "date type" as the list shows.

upload_2019-10-17_22-19-10.png


I logged these values:

B4X:
Log(Cell.CellType)
Log(Cell.ValueDate)
Log(Cell.Value)
which created

B4X:
NUMERIC 
40319
Fri May 21 00:00:00 CEST 2010
Question: How can one check a cell to be a date when it returns NUMERIC? Could be an integer, too (assuming you get the value with Cell.ValueNumeric)

PS: I know that Excel stores dates via "ticks" internally
 

DonManfred

Expert
Licensed User
https://poi.apache.org/apidocs/dev/...html#getExcelDate-java.util.Calendar-boolean-

public static java.util.Date getJavaDate(double date,
java.util.TimeZone tz)
Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date. Excel Dates and Times are stored without any timezone information. If you know (through other means) that your file uses a different TimeZone to the system default, you can use this version of the getJavaDate() method to handle it.
Parameters:
date - The Excel date.
tz - The TimeZone to evaluate the date in
Returns:
Java representation of the date, or null if date is not a valid Excel date
https://www.programcreek.com/java-api-examples/?api=org.apache.poi.hssf.usermodel.HSSFDateUtil
 

keirS

Well-Known Member
Licensed User
B4X:
 Sub isCellInternalDateFormatted(Cell As PoiCell)
    Dim JODateFormated As JavaObject
    JODateFormated.InitializeStatic("org.apache.poi.ss.usermodel.DateUtil")
    Return JODateFormated.RunMethod("isCellInternalDateFormatted",Array(Cell))
End Sub
To call:
B4X:
If shtCell.CellType = shtCell.TYPE_NUMERIC Then
        Log(isCellInternalDateFormatted(shtCell))
 End If
Returns true if the cell is formatted as a Date using one of Excels built in date formats.
 
Top