Export DBGrid to Excel [Solved]
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2018-09-29 21:14

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 · 2018-09-29 23:20

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.

----------------------------------------------------------------------
wp · 2018-09-30 08:36

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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;
(*) These are ambiguous words: an "export" as seen from the dataset is
an "impor"t as seen from the workbook and vice versa.

----------------------------------------------------------------------
Vodnik · 2018-09-30 21:56

Thank you for ideas, lucamar and wp!

----------------------------------------------------------------------
Vodnik · 2020-01-21 13:26

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?

----------------------------------------------------------------------
wp · 2020-01-21 14:24

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.

Untested:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
ExportDBGridToExcel(AGrid: TDBGrid; AFileName: String);var  ... //
like in old example  ADataSet: TDataset;begin  ADataSet :=
AGrid.DataSource.Dataset;  ....  while not ADataSet.EoF do begin   
for c := 0 to AGrid.Columns.Count-1 do begin      if not
AGrid.Columns[i].Visible then   // skip hidden columns       
continue;      field := AGrid.Columns[c].Field;      case
field.DataType of        ftText, ftMemo:            cell :=
sheet.WriteText(r, c, ADataset.Fields[c].DisplayText);       ....   
end;

----------------------------------------------------------------------
Vodnik · 2020-01-23 19:35

Thanks a lot, wp, I have successfully implemented your idea.