procedure DatasetToExcel(ADataset: TDataset; AFileName: String);
var
book: TsWorkbook;
sheet: TsWorksheet;
r, c: Integer;
cell: PCell;
begin
book := TsWorkbook.Create;
try
sheet := book.AddWorksheet(ADataset.Name);
// Write field names to first row of worksheet
r := 0;
for c := 0 to ADataset.Fields.Count-1 do
sheet.WriteText(r, c, ADataset.Fields[c].FieldName);
// Write records
inc(r);
ADataset.First;
while not ADataset.EoF do begin
for c := 0 to ADataset.Fields.Count-1 do begin
case ADataset.Fields[c].DataType of
ftText, ftMemo, (... all text types):
cell := sheet.WriteText(r, c, ADataset.Fields[c].DisplayText);
// DisplayText is the text as it appears in the grid. Use .AsString for the raw text)
ftFloat (... all floating point numeric types):
begin
cell := sheet.WriteNumber(r, c, ADataset.Fields[c].AsFloat);
sheet.WriteHorAlignment(cell, caHor); // right-align numbers
sheet.WriteNumberFormat(cell, nfFixed, 3); // display 3 decimals (or whatever you want)
end;
ftInteger (... all integer types):
begin
cell := sheet.WriteNumber(r, c, ADataset.Fields[c].AsInteger);
sheet.WriteHorAlignment(cell, haRight); // right-align numbers
end;
ftDateTime:
begin
cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortDateTime);
sheet.WriteHorAlignment(cell, haCenter); // centered dates (or whatever...)
end;
ftDate:
begin
cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortDate);
sheet.WriteHorAlignment(cell, haCenter); // centered dates (or whatever...)
end;
ftTime:
begin
cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortTime);
sheet.WriteHorAlignment(cell, haCenter); // centered dates (or whatever...)
end;
ftBoolean:
begin
cell := sheet.WriteBoolValue(r, c, ADatasetFields[c].AsBoolean);
sheet.WriteHorAlignment(cell, haCenter); // centered TRUE/FALSE)
end;
ftCurrency:
begin
cell := sheet.WriteCurrency(r, c, ADataset.Fields[c].AsCurrency);
sheet.WriteHorAlignment(cell, haRight); // right-aligned
end;
// here maybe other cases, too
end;
end;
inc(r);
ADataset.Next;
end;
book.WriteToFile(AFileName, sfOOXML, true); // this is for .xlsx. Or use sfExcel8 for .xls.
end;
end;