Export selected fields in Virtual Mode + Date and Phone format
[SOLVED]
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
onivan · 2017-07-08 15:55

Hello!

I'm making a kind of address book. My database is SQLite and I need to
export some data from it to ODS.

FPSpreadsheet is an excellent library, I managed to export to ods
using FPSExport where I could select fields from a dataset to export.
It is OK but I did not find a way how to format cells while using it.
Now I want to do the same using ExportUsingVirtualMode from a
db_import_export example.

Could somebody give me a hint how could I select fields to export
because it exports all the fields.

For instance I have a dataset with following fields: id, name,
address, phone, gender, mar_state, photo, notes and need to export it
to ods spreadsheet with only following columns: name, address, phone.

----------------------------------------------------------------------
wp · 2017-07-08 16:56

You only use the fields that you need to. Try this
WriteCellDataHandler in the db_export_import sample project: field
"name" will go into column 0, "address" into column 1, and "phone"
into column 2, the worksheet's VirtualColCount must be set to 3
because there are 3 columns to be written. Note that this code is not
tested, but I think it should explain the principle:
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
TForm1.WriteCellDataHandler(Sender: TsWorksheet; ARow, ACol:
Cardinal;  var AValue: variant; var AStyleCell: PCell);var  field:
TField;begin  case ACol of    0: field :=
FExportDataset.FieldByName('name');    1: field :=
FExportDataset.FieldByName('address');    2: field :=
FExportDataset.FieldByName('phone');  end;   // Header line: we want
to show the field names here.  if ARow = 0 then  begin    AValue :=
field.FieldName;    AStyleCell := FHeaderTemplateCell;   
FExportDataset.First;  end  else  // After the header line we write
the record data. Note that we are  // responsible for advancing the
dataset cursor whenever a row is complete.  begin    AValue :=
field.Value;    if field.DataType = ftDate then      AStyleCell :=
FDateTemplateCell    else if field.DataType = ftCurrency then     
AStyleCell := FCurrencyTemplateCell;     if ACol =
Sender.VirtualColCount-1 then    begin      // Move to next record
after last field has been written      FExportDataset.Next;      //
Progress display      if (ARow-1) mod 1000 = 0 then      begin       
InfoLabel2.Caption := Format('Writing record %d to spreadsheet...',
[ARow-1]);        Application.ProcessMessages;      end;    end; 
end;end;

----------------------------------------------------------------------
onivan · 2017-07-08 19:54

Wow!
It worked like a charm! Thanks a lot!

Could you also help with a data formatting?

My columns now are:  name, address, Date_of_Birth, Mobile_Phone.
Date_of_Birth stored in a SQLite  as DATE: HHHH-YY-DD,
Mobile_Phone as INTEGER.

To display them properly in a TDBGrid these fields have following
settings in a  Dataset of type TZQuery (I use ZeosDB):
Date_of_Birth has type of TDateField with property DispayFormat :=
'HHHH-YY-DD',
Mobile_Phone has type of TLongIntField with property DisplayFormat := 
'### ##0-00-00'

Date_of_Birth column needs to be in a ODS' cell format  Date HHHH-YY-
DD to display as 1997-07-19
Mobile_Phone in a format in ODS' cell format presentation  Number 000"
"000"-"00"-"00  to display as 093 954-70-33

In ExportUsingVirtualMode I have:   
    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       with
FWorkbook.FormatSettings do        begin          ShortDateFormat:=
'yy/mm/dd';          LongDateFormat:= 'yyyy/mm/dd';         
DateSeparator := '-';         end;    FDateTemplateCell :=
worksheet.GetCell(0, 2);   
worksheet.WriteDateTimeFormat(FDateTemplateCell, nfCustom,
'yyyy/mm/dd');    // does not work either: 
 //worksheet.WriteDateTimeFormat(FDateTemplateCell, nfLongDate);    
FPhoneTemplateCell := worksheet.GetCell(0, 3);   
worksheet.WriteNumberFormat(FPhoneTemplateCell, nfCustom, '000\
000\-00\-00');  
and in WriteCellDataHandler:

 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       if
field.DataType = ftDate then      AStyleCell := FDateTemplateCell;   
if (field.FieldName = 'Mobile_Phone') then        AStyleCell :=
FPhoneTemplateCell;          
   
I tried different things from the wiki and examples but neither had
given me a desired result in a resulting ODS file.

Here what I get in resulting ODS:

Date_of_Birth  has format of  DD/MM/YY HH:MM and displays as 19/07/97
00:00
Mobile_Phone has format of Number Standard and displays as 939547033

Where am I wrong?

----------------------------------------------------------------------
wp · 2017-07-08 21:16

You seem to have some localized date format which I do not understand:
Does HHHH-YY-DD mean the same as YYYY-MM-DD in English? (i.e. 4-digit-
year, 2-digit month, 2-digit day)? And you want to display the
date/time in this format, with a "minus" as separator?

In this case, I would write the date format string as "yyyy-mm-dd".
Alternatively you can use "yyyy/mm/dd", but then you must define the
DateSeparator in the workbook's FormatSettings as '-' (because the '/'
is replaced by the DateSeparator); the other FormatSettings you
mentioned are not needed. So:
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
worksheet.WriteDateTimeFormat(FDateTemplateCell, nfCustom, 'yyyy-mm-
dd');This should return today's date as "2017-07-08".

The phone number is more difficult because I never though that
somebody would ever want to format numbers this way. But of course,
this is a valid request. My tests showed me that a format string like
"000 000" prints the number twice, separated by a space. This is
because "000" is interpreted to display the number with at least 3
digits. An alternative interpretation of assigning each digit of the
number to a 0 of the format mask is not in the format parser.

What you could do is to write the phone number as a string. Calling
field.AsString will get you the database value of the phone number as
a correctly formatted string, and this will be written directly to the
worksheet as a text cell without any special formatting needed.
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.WriteCellDataHandler(Sender: TsWorksheet; ARow, ACol:
Cardinal;  var AValue: variant; var AStyleCell: PCell);var  field:
TField;begin  field := FExportDataset.FieldByName('phone');  ... 
AValue := field.AsString;  ...end;

----------------------------------------------------------------------
onivan · 2017-07-08 21:54

It is my mistake. It should be YYYY-MM-DD.

For the date and phone number I've stopped on the following solution
for now:
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     if
field.DataType = ftDate then      begin        AValue :=
FormatDateTime('yyyy-mm-dd',StrToDate(string(AValue)));      end;    
if (field.FieldName = 'Mobile_Phone')   then      begin         AValue
:= field.AsString;         if Length(AValue) = 9 then             
AValue := '0'+ Copy(AValue,0,2) + ' ' + Copy(AValue,2,3) +           
        '-' + Copy(AValue,5,2) + '-' + Copy(AValue,7,2);      end;wp
thank you for your help!