Hi,
The short answer is: There is no datetime data type in Excel. A cell in Excel can be a number, a string, a bool, or an error. A datetime is just cell with a number formatted with a date format.
So, in order to enter a datetime you need to also format the cell with the date format you want:
procedure TForm1.Button1Click(Sender: TObject);
var
xls: TXlsFile;
cell: TCellValue;
fmt: TFlxFormat;
begin
xls := TXlsFile.Create(true);
xls.NewFile(1, TExcelFileFormat.v2016);
cell := Now; //no need to create a TCellValue, it is converted automatically.
fmt := xls.GetCellVisibleFormatDef(1, 1);
fmt.Format := 'yyyy-mm-dd hh:ss';
xls.SetCellValue(1, 1, cell, xls.AddFormat(fmt));
xls.Save('c:\temp\01.xlsx');
end;
You could of course use SetCellFromString to have a similar behavior to Excel (and have FlexCel guess that you want a string converted to a date time), but I would not advise using that. First of all, SetCellFromString will have to convert a string to a datetime, which is slower and error prone when you have multiple locales (Is 01/06/2005 june 1 or january 6?). And second, it will use a standard format for the date. By using the code above, you can explicitly say which date/time format you want for the cell. (You can use APIMate to get the format you want).
About the date variants being converted to numbers and not datetimes, I guess this could be improved and convert them to datetimes, but I should have to recheck it. But converting to a number isn't wrong, as there are no real datetimes in Excel. Probably the code is from before TCellValue.DateTime existed. I will see if changing it doesn't have any side effects.
Actually the TCellValue.DateTime is mostly a confusing type: If you enter a DateTime value into a cell and you read the value back, you will get a number. This is because the value gets stored as a number and when you read it back you get the number back, not a datetime.
The only reason we have a TCellValue.DateTime option is because we need to support dates starting in 1900 or in 1904. This is an Excel option which we fully support: 1900 is the standard in Excel for windows, and 1904 used to be the standard in Excel for mac, even if today both default to 1900. But you can change it:
So, if you do
xls.SetCellValue(1, 1, now);
this is converted to
xls.SetCellValue(1, 1, a number_that_represents_now_in_1904_or_1900_date_system);
And then that number is entered into the cell. But at the time of conversion, we can't know if the file where you are entering the date is a 1900 or 1904 file, so we can't correctly convert the number at that time. This is why we have a TDateTime value in TCellValue even if Excel doesn't have TDateTimes. With a TDateTime datatype in TCellValue, now
xls.SetCellValue(1, 1, now);
is converted to
xls.SetCellValue(1, 1, a_TCellValue_with_a_TDateTime) and later, when we are actually setting the value of the cell, we convert that TDateTime into a 1900 or 1904 date depending in the file we are working on.
Ok, the post got long, but the take away is simple: Dates in Excel don't exist. If you want to have a date, you need a number formatted as a date. If you want to read a date, you need to read the number and convert it to a date if the cell is formatted as date. (as shown in the "Reading files" demo).
I will check if it is feasible to convert variants with dates to datetimes (I expect it would be ok), but converting them to numbers as we currently do is actually ok, as along as you use a 1900 date system, which you most likely are.
