0

i have a trouble in my project. i want to export my data in dbgrid to excel with ole.

when i export field that contain time (format shorttime hh:mm) to excel, it showing in excel as long date format (MM/dd/yyyy h:mm:ss AM/PM).

what i want is it still showing shorttime format (hh:mm) in excel

i have set a number format to hh:mm in my coding but it still showing in long date format.

ADOQuery1.First;
x:=1;
while not ADOQuery1.Eof do
begin
  for i:=0 to DBGrid1.FieldCount-1 do
    begin
      XlSheet.cells[2+x,i+1].value:=DBGrid1.Fields[i].Text;
      XlSheet.cells[2+x,i+1].Borders.Weight:=xlThin;
      XlSheet.cells[2+x,i+1].Font.Size:=10;
    end;
    ADOQuery1.Next;
    Inc(x);
  end;
XlSheet.Columns[8].NumberFormat:='hh:mm;@';

i'm using database access and data type for that field is Date/Time and ShortTime format. Sorry for my bad english. Thank you. :D

Ken White
  • 123,280
  • 14
  • 225
  • 444
Billi Nata Wijaya
  • 1
  • 1
  • check that date column is actually #8 and not something else. Actually that is rather fragile - little restructuring of DBgrid and it will not work anymore. /// try removing ";@" from the format. – Arioch 'The Jun 09 '13 at 07:58

1 Answers1

0

Do not use a String to assign a date or numeric Value

Change 

XlSheet.cells[2+x,i+1].value:=DBGrid1.Fields[i].Text;

to

XlSheet.cells[2+x,i+1].value:=DBGrid1.Fields[i].Value;
bummi
  • 27,123
  • 14
  • 62
  • 101
  • as a sidenote using a VariantArray would be much faster [example implementaion by Ken White](http://stackoverflow.com/a/16642049/1699210) – bummi Jun 09 '13 at 10:35