XLSDATA output gets confused by custom displayformts

edited July 2016 in Devices
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

Comments

  • edited August 2016
    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited August 2016
    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
This discussion has been closed.