Programming => Databases => Topic started by: Vodnik on September 29, 2018, 11:14:53 pm

Title: Export DBGrid to Excel [Solved]
Post by: Vodnik 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?
Title: Re: Export DBGrid to Excel
Post by: lucamar 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.
Title: Re: Export DBGrid to Excel
Post by: wp 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 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.
Title: Re: Export DBGrid to Excel
Post by: Vodnik on September 30, 2018, 11:56:00 pm
Thank you for ideas, lucamar and wp!
Title: Re: Export DBGrid to Excel - new question
Post by: Vodnik on January 21, 2020, 02:26:46 pm
Well, I have used wp's example (export from Dataset to Excel) in my application, and customer was happy.
But he discovered that it is possible to hide some columns in DBGrid, move them to another position and now wants to obtain similar table in the exported file.
Is it possible to adapt this example for that case?
Title: Re: Export DBGrid to Excel
Post by: wp on January 21, 2020, 03:24:41 pm
My example of reply #2 does not use a DBGrid at all. Therefore it does not "know" whether grid columns are hidden or interchanged.

Of course you can make the code consider hidden or interchanged columns. Instead of iterating over the Fields of a TDataSet you could iterate over the columns of the DBGrid and extract the field from there -- this will pick the fields in the order they are visible in the grid, and of course you can skip hidden columns by checking the Visible property of each Column.


Code: Pascal  [Select]
  1. procedure ExportDBGridToExcel(AGrid: TDBGrid; AFileName: String);
  2. var
  3.   ... // like in old example
  4.   ADataSet: TDataset;
  5. begin
  6.   ADataSet := AGrid.DataSource.Dataset;
  7.   ....
  8.   while not ADataSet.EoF do begin
  9.     for c := 0 to AGrid.Columns.Count-1 do begin
  10.       if not AGrid.Columns[i].Visible then   // skip hidden columns
  11.         continue;
  12.       field := AGrid.Columns[c].Field;
  13.       case field.DataType of
  14.         ftText, ftMemo:
  15.             cell := sheet.WriteText(r, c, ADataset.Fields[c].DisplayText);
  16.        ....
  17.     end;

Title: Re: Export DBGrid to Excel
Post by: Vodnik on January 23, 2020, 08:35:50 pm
Thanks a lot, wp, I have successfully implemented your idea.