TDateTime values in cells

how I am supposed to put a Date or DateTime value into a cell?

procedure TForm1.Button1Click(Sender: TObject);
  xls: TXlsFile;
  cell: TCellValue;
  xls := TXlsFile.Create(true);
  xls.NewFile(1, TExcelFileFormat.v2016);
  cell := TCellValue.Create(Now);
  xls.SetCellValue(1, 1, cell);
  xls.Save('c:\temp\01.xlsx', TFileFormats.Automatic);

Here the cell variable has a ValueType of DateTime, but the resulting Excel file only displays a decimal number.
Additional question: I am trying to populate the sheet from a database table, and the incoming values are variants. Here

  value := Now;
  cell := TCellValue.Create(value);

cell.ValueType is Number in debugger (due to _UCellValue.pas line 428) which seems even more wrong ;-)

Thanks for your advice


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);
  xls: TXlsFile;
  cell: TCellValue;
  fmt: TFlxFormat;
  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));

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.

Hi Adrian,

thank you for the fast answer. I understand that I have to supply a TFlxFormat index in order to display values in Excel correctly.

What Format strings would I use to display either dates, times, or date+time values so that the format visible in Excel would not be languge dependent? With "yyy-mm-dd", I end up with Excel telling me the cell format is custom defined, which is not what I want. In our .net implementation for OpenXml, we use the number formats 14, 21 and 22 respectively. Is there any way to use the built-in formats?

Thanks again

Well: 14, 21 and 22 are internal "magic" ids which actually come from xls, you shouldn't use that directly.  
I imagine that you are referring to the formats marked with an "*" in the format dialog:

Which as explained at the bottom of the dialog, change depending in your machine locale settings.

If that is the case, of course the simplest solution is to use APIMate. Save a file in Excel with a cell formatted with the format you want, and open it in APIMate. It will tell you the format string needed for that.

Well, I just run APIMate for a file with the 3 default (*) formats in the English (United States) locale, and I got the formats:

 fmt := xls.GetCellVisibleFormatDef(1, 1);
  fmt.Format := 'mm/dd/YYYY';
  xls.SetCellFormat(1, 1, xls.AddFormat(fmt));

  fmt := xls.GetCellVisibleFormatDef(2, 1);
  fmt.Format := '[$-F800]dddd\,\ mmmm\ dd\,\ yyyy';
  xls.SetCellFormat(2, 1, xls.AddFormat(fmt));

  fmt := xls.GetCellVisibleFormatDef(3, 1);
  fmt.Format := '[$-F400]h:mm:ss\ AM/PM';
  xls.SetCellFormat(3, 1, xls.AddFormat(fmt));

[$-F800] means "use machine  short date format" and what goes after it doesn't matter, it is just a backup in case for any reason the app reading the file can't understand the code.
[$-F400] means "use machine short time format".

There is sadly no code for "long date format", but if you use "mm/dd/YYYY" both FlexCel and Excel will recognize it as a "machine locale format" and it will be entered as internal format 14, which is what you used directly.

Isn't "mm/dd/YYYY" just another magic value? ;-)

Seriousely, if I use that on my machine (german locale), Excel displays today as "04.11.2016" (November 4th) in the cell (the edit pane is right with April 11th).

On the other side, APIMate instructs me to use  'dd/mm/YYYY'.

I really don't want to introduce a language dependency here. The application creating the xlsx file must be running on both US and German installations. I would regard code like "if OS.Languange = German then ... else ..." as bad, not only in this case.

(I'm currently using Windows 7, Delphi 10, Excel 2010.)

Thanks for your comments

Well, indeed "mm/dd/YYYY" is a "magic value", but it is a documented magic value, instead of "14" which is actually an accident more than a conscious decision (when they realized that this format behaved differently then they added the * and the explanation to the format dialog). Most of those magic numbers disappeared when moving from xls to xlsx (like XF=15 which used to be the "normal" format) and it makes sense that they disappeared: they weren't documented as a feature. Format 14 did indeed survive the transition, but formats 21 and 22 did not (now they use the [F-800] nomenclature and if you open an xlsx file created with Excel 2016 you'll see that those formats are custom formats like 167/168)

"mm/dd/YYYY" is what you would write in the format dialog (in English Excel), in the custom formatting to get the culture variant format. But of course, in german Excel you would have to write "dd/mm/YYYY" to get it, and that is what APIMate reports in a german machine. We behave similar to what Excel does here, but we don't have a german version of FlexCel.dll (which would also make the functions like =SUMME or similar instead of using english names)

Bunt not all is lost: there is no need to check if the machine is german or not. Just do:
  fmt.Format := TFlxNumberFormat.RegionalDateString;

And it will create the variable format under the hood. If the machine is german, TFlxNumberFormat.RegionalDateString will be 'dd/mm/YYYY" and if it is english it will be "mm/dd/YYYY" and internally they will be converted to format 14 today, and whatever they use in newer Excel versions if they change it.

I will change APIMate so it  reports TFlxNumberFormat.RegionalDateString instead of "mm/dd/YYYY" or "dd/mm/YYYY" when the format is locale-variable.

That did the trick. I'm using RegionalDateString and RegionalDateTimeString now. It would be nice if you could introduce a RegionalTimeString sometime.

Thank you