Recent

Author Topic: Export DBGrid to Excel  (Read 2318 times)

Vodnik

  • Full Member
  • ***
  • Posts: 109
Export DBGrid to Excel
« on: September 29, 2018, 11:14:53 pm »
Well, I searched for this and finally have come to FPSpreadsheet.
Short code using FPSExport easily do the work exporting DataSet to Excel.
Problem is that to make DBGrid more presentable for the users, I have used DBQuery OnGetText events to apply formatting to the digits, to replace some codes with text, etc.
This looks nice in DBGrid, but this is not exported to Excel. Excel just gets original DataSet.
Well, I guess FPSExport should have similar procedures to modify field content when exporting,
but maybe there is a more clever approach?

lucamar

  • Hero Member
  • *****
  • Posts: 2253
Re: Export DBGrid to Excel
« Reply #1 on: September 30, 2018, 01:20:18 am »
Since you have to use FPSpreadsheet and you're already ... "interfering", let's say, with the display, why not use all of FPSpreadSheet and discard the DBGrid? Then you could export a "normal" spreadsheet with all its formatting, formulae, etc. After all, the more important events* are in the DBQuery.

Just my 2 cents.

* ETA: Regarding the database, I mean.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.4/2.0.6  - FPC 3.0.4 on:
(K|L)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

wp

  • Hero Member
  • *****
  • Posts: 6620
Re: Export DBGrid to Excel
« Reply #2 on: September 30, 2018, 10:36:10 am »
First of all, don't export/import (*) from the DBGrid - it is very probable that it does not display all records -, use the underlying dataset instead. Write the field types accordingly to the worksheet cell types. Then apply special formatting that you need. See http://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell_formatting for the available commands. Something like this (untested):
Code: Pascal  [Select]
  1. procedure DatasetToExcel(ADataset: TDataset; AFileName: String);
  2. var
  3.   book: TsWorkbook;
  4.   sheet: TsWorksheet;
  5.   r, c: Integer;
  6.   cell: PCell;
  7. begin
  8.   book := TsWorkbook.Create;
  9.   try
  10.     sheet := book.AddWorksheet(ADataset.Name);
  11.     // Write field names to first row of worksheet
  12.     r := 0;
  13.     for c := 0 to ADataset.Fields.Count-1 do
  14.       sheet.WriteText(r, c, ADataset.Fields[c].FieldName);
  15.     // Write records
  16.     inc(r);
  17.     ADataset.First;
  18.     while not ADataset.EoF do begin
  19.       for c := 0 to ADataset.Fields.Count-1 do begin
  20.         case ADataset.Fields[c].DataType of
  21.           ftText, ftMemo, (... all text types):
  22.             cell := sheet.WriteText(r, c, ADataset.Fields[c].DisplayText);
  23.             // DisplayText is the text as it appears in the grid. Use .AsString for the raw text)
  24.           ftFloat (... all floating point numeric types):
  25.             begin
  26.               cell := sheet.WriteNumber(r, c, ADataset.Fields[c].AsFloat);
  27.               sheet.WriteHorAlignment(cell, caHor);  // right-align numbers
  28.               sheet.WriteNumberFormat(cell, nfFixed, 3);  // display 3 decimals (or whatever you want)
  29.            end;
  30.           ftInteger (... all integer types):
  31.             begin
  32.               cell := sheet.WriteNumber(r, c, ADataset.Fields[c].AsInteger);
  33.               sheet.WriteHorAlignment(cell, haRight);  // right-align numbers
  34.             end;
  35.           ftDateTime:
  36.             begin
  37.               cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortDateTime);
  38.               sheet.WriteHorAlignment(cell, haCenter);  // centered dates (or whatever...)
  39.             end;
  40.           ftDate:
  41.             begin
  42.               cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortDate);
  43.               sheet.WriteHorAlignment(cell, haCenter);  // centered dates (or whatever...)
  44.             end;
  45.           ftTime:
  46.             begin
  47.               cell := sheet.WriteDateTime(r, c, ADatasetFields[c].AsDateTime, nfShortTime);
  48.               sheet.WriteHorAlignment(cell, haCenter);  // centered dates (or whatever...)
  49.             end;
  50.           ftBoolean:
  51.             begin
  52.               cell := sheet.WriteBoolValue(r, c, ADatasetFields[c].AsBoolean);
  53.               sheet.WriteHorAlignment(cell, haCenter);  // centered TRUE/FALSE)
  54.             end;
  55.           ftCurrency:
  56.             begin
  57.               cell := sheet.WriteCurrency(r, c, ADataset.Fields[c].AsCurrency);
  58.               sheet.WriteHorAlignment(cell, haRight);  // right-aligned
  59.            end;
  60.         // here maybe other cases, too
  61.         end;
  62.       end;
  63.       inc(r);
  64.       ADataset.Next;
  65.     end;
  66.     book.WriteToFile(AFileName, sfOOXML, true);  // this is for .xlsx. Or use sfExcel8 for .xls.
  67.   end;
  68. end;

(*) These are ambiguous words: an "export" as seen from the dataset is an "impor"t as seen from the workbook and vice versa.
« Last Edit: September 30, 2018, 01:07:00 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Vodnik

  • Full Member
  • ***
  • Posts: 109
Re: Export DBGrid to Excel
« Reply #3 on: September 30, 2018, 11:56:00 pm »
Thank you for ideas, lucamar and wp!