XLSDATA output gets confused by custom displayformts

rbuser

Hi,

We have installed out own display formatting function for floating
point values which works fine to screen, printer, pdf etc by setting
gcDisplayFormat in one our units initialization sections.

We have added display format like "utMass', 'utLength" etc to enable
reports to output in english or metric units etc.

When we export to xlsdata we get strange results however:
If Mergecells and all the other advanced XLS options are off and we
export to xlsdata, spreadsheet looks Ok but warns of loss of
formatting data on loading it into Excel 2013.

If some of the advanced xls options ( i have tried merge adjacent
cells, ignore page breaks) are set however the report outputs our
display format string into the cell instead of the converted value ,
eg "utMass" appears in the cells instead of a number.

If we export to xlsXdata, we get this display format string regardless
of the other settings.

RB Enterprize 17.02, delphi 10.1, win10

Thanks,
Chris

nardmoseley

Hi Chris,

The Xlsx and Xls devices export the data value, data type, and display
format as separate cell attributes. For custom display formats, Excel will
not know what do with those. Below is some sample code you can use to
iterate over the Page.DrawCommands[ ] and modify the properties so the
formatted string is exported. For a future RB version we'll consider adding
an XlsSettings.ExportFormattedStrings boolean propery to handle this
situation.


uses
ppTypes,
ppDevice,
ppXlsDevice,
ppDrwCmd;


procedure TForm1.ppReport1FileDeviceCreate(Sender: TObject);
begin

// assign OnPageReceive event-handler, if report exported to Xlsx/Xls
if ppReport1.FileDevice is TppXlsDeviceBase then
ppReport1.FileDevice.OnPageReceive := ehXlsDevice_ReceivePage;

end;

procedure TForm1.ehXlsDevice_ReceivePage(Sender, aPage: TObject);
var
liIndex: Integer;
lPage: TppPage;
lDrawText: TppDrawText;
begin

lPage := TppPage(aPage);

for liIndex := 0 to lPage.DrawCommandCount-1 do
if (lPage.DrawCommands[liIndex] is TppDrawText) then
begin
lDrawText := TppDrawText(lPage.DrawCommands[liIndex]);

// export strings to Xls/Xlsx
lDrawText.DataType := dtString;
lDrawText.Value := '';

end;

end;


Best regards,

-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Thankyou Nard,

That magic sauce with a few extensions works nicely, just added our
own textdrawcommand processor for the floating point fields in the
Onreceivepage event

Regards,
Chris