Posted 17 December 2020, 6:07 pm EST
Hi,
The problem seems to some from the invalid format of the date values in the cells. The cells have the value “'122019” which is not a valid date value string that Spread COM can parse.
Spread COM requires the date values to either follow the format for the cell, or conform to the Spread COM hardcoded date format of “MMDDYYYY” for the cell value.
If I edit the XLSX and change the cells to have a valid date value “'12202019” (I assume this is the date that you intend but if that is not correct, you can correct this value to specify the correct date) in the format “MMDDYYYY” then it works to import those values correctly to the cells.
Also, the reason that the XLS loads successfully with the invalid date values in the cell is that the cells are imported as EDIT cells instead of DATE cells. In that case, the value is not parsed as a date and instead treated as a string, and the “'” character is removed from the beginning of each string.
I am not sure why the XLS loads the cells as EDIT cells instead of DATE cells; there might be some extra logic to check whether the value can be parsed as a date and to change the cell type to EDIT if it cannot, but the XLSX import does not have such logic and always tried to import the cells with date cell format as DATE cell type.
Thanks,
Pragati