Export selected fields in Virtual Mode + Date and Phone format [SOLVED]

[onivan]

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]

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][+][-]

1. procedure TForm1.WriteCellDataHandler(Sender: TsWorksheet; ARow, ACol: Cardinal;
2.   var AValue: variant; var AStyleCell: PCell);
3. var
4.   field: TField;
5. begin
6.   case ACol of
7.     0: field := FExportDataset.FieldByName('name');
8.     1: field := FExportDataset.FieldByName('address');
9.     2: field := FExportDataset.FieldByName('phone');
10.   end;
11.  
12.   // Header line: we want to show the field names here.
13.   if ARow = 0 then
14.   begin
15.     AValue := field.FieldName;
16.     AStyleCell := FHeaderTemplateCell;
17.     FExportDataset.First;
18.   end
19.   else
20.   // After the header line we write the record data. Note that we are
21.   // responsible for advancing the dataset cursor whenever a row is complete.
22.   begin
23.     AValue := field.Value;
24.     if field.DataType = ftDate then
25.       AStyleCell := FDateTemplateCell
26.     else if field.DataType = ftCurrency then
27.       AStyleCell := FCurrencyTemplateCell;
28.  
29.     if ACol = Sender.VirtualColCount-1 then
30.     begin
31.       // Move to next record after last field has been written
32.       FExportDataset.Next;
33.       // Progress display
34.       if (ARow-1) mod 1000 = 0 then
35.       begin
36.         InfoLabel2.Caption := Format('Writing record %d to spreadsheet...', [ARow-1]);
37.         Application.ProcessMessages;
38.       end;
39.     end;
40.   end;
41. end;

[onivan]

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][+][-]

1.  
2.      with FWorkbook.FormatSettings do
3.         begin
4.           ShortDateFormat:= 'yy/mm/dd';
5.           LongDateFormat:= 'yyyy/mm/dd';
6.           DateSeparator := '-';
7.  
8.         end;
9.     FDateTemplateCell := worksheet.GetCell(0, 2);
10.     worksheet.WriteDateTimeFormat(FDateTemplateCell, nfCustom, 'yyyy/mm/dd');
11.     // does not work either:
12.    //worksheet.WriteDateTimeFormat(FDateTemplateCell, nfLongDate);
13.  
14.     FPhoneTemplateCell := worksheet.GetCell(0, 3);
15.     worksheet.WriteNumberFormat(FPhoneTemplateCell, nfCustom, '000\ 000\-00\-00');
16.  
17.  

and in WriteCellDataHandler:

 

Code: Pascal  [Select][+][-]

1.  
2.     if field.DataType = ftDate then
3.       AStyleCell := FDateTemplateCell;
4.  
5.     if (field.FieldName = 'Mobile_Phone') then
6.         AStyleCell := FPhoneTemplateCell;
7.  

         
   
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]

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][+][-]

1. 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][+][-]

1. procedure TForm1.WriteCellDataHandler(Sender: TsWorksheet; ARow, ACol: Cardinal;
2.   var AValue: variant; var AStyleCell: PCell);
3. var
4.   field: TField;
5. begin
6.   field := FExportDataset.FieldByName('phone');
7.   ...
8.   AValue := field.AsString;
9.   ...
10. end;

[onivan]

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][+][-]

1.     if field.DataType = ftDate then
2.       begin
3.         AValue := FormatDateTime('yyyy-mm-dd',StrToDate(string(AValue)));
4.       end;
5.  
6.  
7.     if (field.FieldName = 'Mobile_Phone')   then
8.       begin
9.          AValue := field.AsString;
10.          if Length(AValue) = 9 then
11.               AValue := '0'+ Copy(AValue,0,2) + ' ' + Copy(AValue,2,3) +
12.                     '-' + Copy(AValue,5,2) + '-' + Copy(AValue,7,2);
13.       end;

wp thank you for your help!