Export Time Format with AdvGridExcelIO

VCL TMS VCL UI Pack
1

I'm struggling to export time to Excel from an AdvStringGrid. I have dates and floats working, but I keep getting an error in Excel about the time format.. It comes across as a float. If I format the cell once it is in Excel, it is the correct time.

I'm using the technique you outline. So I set Format.Format:= '[$-x-systime]h:mm:ss AM/PM' and no luck. I've tried dropping the [$-x-systime] and still no luck.

Can you tell me what format string I should be using?

3

Hi,
The problem here is in the inverse conversion: Times are stored as strings in TAdvStringGrid, and TAdvGridExcelIO need to convert them back to a string. But it needs to know which formats those times are in. For that, you can use the "DateTimeFormat" event, or the "TimeFormat" and "DateFormat" properties of AdvGridExcelIO. Try with code like this:

procedure TForm1.AdvGridExcelIO1DateTimeFormat(Sender: TAdvStringGrid;
  const GridCol, GridRow, XlsCol, XlsRow: Integer; const Value: WideString;
  var DateFormat, TimeFormat: WideString);
begin
  TimeFormat := 'h:mm:ss AM/PM';
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  AdvStringGrid1.Times[1,1] := TDateTime(0.65);
  TFile.Delete('..\..\result.xls');
  AdvGridExcelIO1.XLSExport('..\..\result.xls');
end;