Using aPOI and .xlsx spreadsheet.
Date in spreadsheet is 9/1/2022.
When I load this date from my spreadsheet the value is 44805. Line 104.
What am I doing wrong in loading the date from the spreadsheet so that it loads as a date instead of ticks?
In Line 110 the Log always shows 12/31/69 no matter what the cell.NumericCellValue in line 104.
Thank you all in advance for your help.
Date in spreadsheet is 9/1/2022.
When I load this date from my spreadsheet the value is 44805. Line 104.
What am I doing wrong in loading the date from the spreadsheet so that it loads as a date instead of ticks?
In Line 110 the Log always shows 12/31/69 no matter what the cell.NumericCellValue in line 104.
Thank you all in advance for your help.
The data gets loaded from the spreadsheet here::
Sub LoadArrays
Log("LoadArrays sub started")
Private ExitFlag As Boolean = False
Starter.TodoList.Initialize
Categories.Initialize
Statuses.Initialize
Contexts.Initialize
Projects.Initialize
Log("Starter.SpreadsheetDir = "&Starter.SpreadsheetDir)
Log("Starter.FileName = "&Starter.FileName)
xls.Initialize("",File.Combine(Starter.SpreadsheetDir, Starter.FileName))
Private TodoSheet As XLSSheet = xls.getSheetAt(0) 'Selects the first sheet in the spreadsheet.
Private TodoSheetFirstRow As Int = TodoSheet.FirstRowNum 'The row numbering starts at 0. Not 1 like normal spreadsheet cell addresses. If the first row is blank, this will be 1. If the first row is not blank, this will be 0.
Private TodoSheetLastRow As Int = TodoSheet.LastRowNum 'This is the last row which is not blank.
Log("Todo Sheet Row FirstRow= "&TodoSheetFirstRow& "LastRow= "&TodoSheetLastRow)
For RowNumber = TodoSheetFirstRow To TodoSheetLastRow
ExitFlag = False
'Log("...")
Private TempTodo As Todo 'Each row holds the data for a new Todo Item to be added to Starter.TodoList.
TempTodo.Initialize
Dim Row As XSSFRow =TodoSheet.getRow(RowNumber)
'Log("Row #"&RowNumber& "FirstCell="&Row.FirstCellNum&", LastCell="&Row.LastCellNum)
'Log("Row Number = "&RowNumber)
Private ActiveColumns As List 'We only want data from certain columns of the spreadsheet to show in the tables.
ActiveColumns.Initialize
ActiveColumns.AddAll(Array As Int(0,1,2,5,6,9,11,14,18))
For ColumnNumber = 0 To ActiveColumns.Size - 1
Private CellNumber As Int
CellNumber = ActiveColumns.Get(ColumnNumber)
Dim cell As XSSFCell =Row.getCell(CellNumber)
If cell.IsInitialized Then
'Log("Cell # "&CellNumber)
'Log("Cell Initialized = "&cell.IsInitialized)
'Log("Raw:"&cell.RawValue)'Log("Raw:"&cell.RawValue)
'Log("CellValueType= "&cell.CellType)
Select Case CellNumber
Case 0 'ID
If RowNumber = TodoSheetFirstRow Then
HeaderRow(0) = cell.StringCellValue
Else
TempTodo.ID = cell.NumericCellValue
'Log("TempTodo.ID = "&TempTodo.ID)
End If
Case 1 'TodoName
If RowNumber = TodoSheetFirstRow Then
HeaderRow(1) = cell.StringCellValue
Else
TempTodo.Name = cell.StringCellValue
'Log("TempTodo.Name = "&TempTodo.Name)
End If
Case 2 'Status
If RowNumber = TodoSheetFirstRow Then
HeaderRow(2) = cell.StringCellValue
Else
TempTodo.Status = cell.NumericCellValue
'Log("TempTodo.Status = "&TempTodo.Status)
If TempTodo.Status = 0 Or TempTodo.Status > 4 Then
ExitFlag = True
End If
End If
Case 5 'Context
If RowNumber = TodoSheetFirstRow Then
HeaderRow(3) = cell.StringCellValue
Else
TempTodo.Context = cell.NumericCellValue
'Log("TempTodo.Context = "&TempTodo.Context)
End If
Case 6 'Category
If RowNumber = TodoSheetFirstRow Then
HeaderRow(4) = cell.StringCellValue
Else
TempTodo.Category = cell.NumericCellValue
'Log("TempTodo.Category = "&TempTodo.Category)
End If
Case 9 'Priority
If RowNumber = TodoSheetFirstRow Then
HeaderRow(5) = cell.StringCellValue
Else
TempTodo.Priority = cell.NumericCellValue
'Log("TempTodo.Priority = "&TempTodo.Priority)
End If
Case 11 'Project
If RowNumber = TodoSheetFirstRow Then
HeaderRow(6) = cell.StringCellValue
Else
TempTodo.Project = cell.NumericCellValue
'Log("TempTodo.Project = "&TempTodo.Project)
End If
Case 14 'DueDate
If RowNumber = TodoSheetFirstRow Then
HeaderRow(7) = cell.StringCellValue
Else
If cell.RawValue <> Null Then
Log("Raw:"&cell.RawValue)'Log("Raw:"&cell.RawValue)
TempTodo.DueDate = cell.NumericCellValue
Log("RowNumber = "&RowNumber)
Log("Cell Numeric Value = "&cell.NumericCellValue)
Log("TempTodo.DueDate = "&TempTodo.DueDate)
Log("Date check: "&isCellInternalDateFormatted(cell))
DateTime.DateFormat="MM/dd/yy"
Log(DateTime.Date(cell.NumericCellValue)) ***ALWAYS shows 12/31/69 in the log no matter what the cell.NumericCellValue.
End If
End If
Case 18 'Note
If RowNumber = TodoSheetFirstRow Then
HeaderRow(8) = cell.StringCellValue
Else
TempTodo.Note = cell.StringCellValue
'Log("TempTodo.Note = "&TempTodo.Note)
End If
End Select
'Cell Types: Numeric is type 0. 1 is Text. 2 is Formula. 3 is Blank. 4 is Boolean. 5 is error.
'Log(cell)
'Log(row)
End If
If ExitFlag = True Then Exit
Next
If ExitFlag = False Then Starter.TodoList.Add(TempTodo)
Next
Last edited:
