Delphi TDateTime to Excel convertion issue

FLX TMS FlexCel for VCL & FMX
1

Hello

I need to dump a dataset into an Excel file. I have an issue with Delphi TDateTime conversion.

Dataset TDatetime 20/07/1974 value will be converted to Excel number 27230 while Dataset TDatetime 01/01/1900 value will be converted to Excel number 2 (should be 1).

How can I fix this ?

procedure RunuReport(ds: Tdataset; header: Boolean = true; fieldlist: TStrings =
    nil);
var
  xls: TXlsFile;
  Row, Col: integer;
  Fmt: TFlxFormat;
  DateXF, DateTimeXF: integer;
begin
  xls := TXlsFile.Create(1, TExcelFileFormat.v2019, true);
  try
    if Header then
    begin
       Row := 2;
       for Col := 1 to ds.FieldCount do
        xls.SetCellValue(1, Col, ds.Fields[col - 1].FieldName);
    end else Row := 1;


    //Now loop over all records and send them to the file.
    ds.First;

    while not ds.Eof do
    begin
      for Col := 1 to ds.FieldCount do
      begin
        case ds.Fields[Col - 1].DataType of

          TFieldType.ftDate,TFieldType.ftDateTime:
          begin

            xls.SetCellValue(Row, Col, TFlxDateTime.ToOADate(ds.Fields[col - 1].AsDateTime,false));
          end;

          else
          begin
            xls.SetCellValue(Row, Col, ds.Fields[col - 1].Value);
          end;
        end;
      end;

      ds.Next;
      Inc(Row);
    end;

    xls.Save('test.xlsx');
  finally
    xls.Free;
  end;
end;
2

Hi,
I am not sure on what is exactly happening here, but I am almost sure it is related to the fact that Excel has an extra day: Feb 29, 1900 which doesn't exist. (see Excel incorrectly assumes that the year 1900 is a leap year - Office | Microsoft Docs and the last note in FlexCel API Developer Guide | FlexCel Studio for VCL and FireMonkey documentation )

Now, TFlxDateTime.ToOADate should handle this correctly (or the most correctly we can, because this is something that can be 100% fixed). So I am not sure on why you are getting a 2.

If you try this code:

  date: TDateTime;
begin

xls := TXlsFile.Create(1, TExcelFileFormat.v2019, true);
try
  date := EncodeDate(1900,1,1);
  xls.SetCellValue(1,1, TFlxDateTime.ToOADate(date, false));
  xls.SetCellValue(2,1, date);
  xls.SetCellValue(3,1, double(date));
  xls.Save('test.xlsx');
finally
  xls.Free;
end;

You should get this file:

image

That is, the TDateTime used by Delphi is indeed 2, and if you cast a TDateTime to a double and try to enter the double, you will enter a 2 (row 3). But if you convert the double to a serial with ToOADate, or even if you pass a TDateTime to SetCellValue (and internally, it will call ToOADate), you should get a 1.

So again, I am not sure on why you are getting a 2, but my guess is that the fields in the db aren't ftDateTime or ftDate, but just numeric fields? If that is the case, then the "else" part of the if would be executed, and it will enter the serial 2, which corresponds to 1/1/1900 in Delphi (and everywhere except Excel). From 0 to 29, all serials in Excel are off by 1, so the serials can be the same after feb 29. But that's what ToOADate fixes. If you look at the code of ToOADate it has this line:

    if (Result < Feb29_1900) and (Result > MinDate) then  // See http://support.microsoft.com/kb/214326
      Result := Result - 1;

Can you debug your code, and double check that the date fields are going through the "ToOADate branch of the if? If they are, we will have to dig deeper, but maybe you can now have some extra information on what is happening.

3

Hi Adrian,

First let me thank you for such a fast answer.

Your guess was right ! DB field type was ftTimestamp. I've amended the code and correct date is displayed now :slight_smile:

Regards