AsDateTime shows raw number in Excel

[billegge]

I have upgraded to v5 and now all my date cells are just numbers when I open it in excel.

My program mainly exports data from a query to excel, so a whole column except for the header should be a date format.

Below is test code showing the date problem, but I want to know what the most effcient way is to format the date column to dates (and not mess with the header).

Code: Select all

procedure TForm12.Button1Click(Sender: TObject);
const
  FirstRow = 0;
  AsIntegerCol = 0;
  AsDateTimeCol = 1;
  AsFloatCol = 2;
  AsBooleanCol = 3;
  AsStringCol = 4;
var
  Sheet: TXLSWorksheet;
  XLS: TXLSReadWriteII5;
begin
  XLS:= TXLSReadWriteII5.Create(nil);
  try
    Sheet:= XLS.Sheets[0];

    XLS.CmdFormat.BeginEdit(nil);
    XLS.CmdFormat.Font.Name:= 'Arial';
    XLS.CmdFormat.Font.Size:= 8;
    XLS.CmdFormat.Fill.BackgroundColor.IndexColor:= xcPaleSky;
    XLS.CmdFormat.AddAsDefault('ReportHeader');

    XLS.CmdFormat.BeginEdit(nil);
    XLS.CmdFormat.Font.Name:= 'Arial';
    XLS.CmdFormat.Font.Size:= 8;
    XLS.CmdFormat.AddAsDefault('ReportBody');

    // Add Columns
    XLS.DefaultFormat:= XLS.CmdFormat.Defaults.Find('ReportHeader');
    Sheet.AsString[AsIntegerCol, FirstRow]:= 'AsInteger';
    Sheet.AsString[AsFloatCol, FirstRow]:= 'AsFloat';
    Sheet.AsString[AsDateTimeCol, FirstRow]:= 'AsDateTime';
    Sheet.AsString[AsBooleanCol, FirstRow]:= 'AsBoolean';
    Sheet.AsString[AsStringCol, FirstRow]:= 'AsString';

    // Add Rows
    XLS.DefaultFormat:= XLS.CmdFormat.Defaults.Find('ReportBody');
    Sheet.AsInteger[AsIntegerCol, 1]:= 123;
    Sheet.AsDateTime[AsDateTimeCol, 1]:= Trunc(Now());
    Sheet.AsFloat[AsFloatCol, 1]:= 123.123;
    Sheet.AsBoolean[AsBooleanCol, 1]:= True;
    Sheet.AsString[AsStringCol, 1]:= 'Just Text';

    Sheet.AutoWidthCols(0, 4);
    XLS.Filename:= 'C:\Temp\TestFile.xlsx';
    XLS.Write;
  finally
    XLS.Free;
  end;
end;

[larsa]

Hello

Set the number format of the column to the date format you want.
Example:

Code: Select all

  XLS.CmdFormat.BeginEdit(Nil);
  XLS.CmdFormat.Number.Format := 'YY/MM/dd';
  XLS.CmdFormat.AddAsDefault('ReportBody');