Home Devices

Negative values export to Excel

edited November 2014 in Devices
v15.05

Negative currency values are exported as positive values to excel.
Integers and reals work correctly. Reproducible with just a variable
component and standard RB demos. Might be worth checking that v16 does
this or not.

Comments

  • edited November 2014
    Fixed in v16?

  • edited December 2014
    Hi Paul,

    I tested with RB 15.05 and RB 6 and did not encounter any issues.

    I used a TppVariable with DatatType dtCurrency and implemented the OnCalc to
    set the value to -100.00. I have a USA machine, so currency format for
    negative numbers is displayed as ($100.00) in RB preview and in Excel.
    Selecting the cell in Excel the raw cell value is -100 and then the cell has
    a display format set for the currency formatting.

    Let me know what regional settings and currency format settings you are
    using and I can test further.



    Best regards,

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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited December 2014
    On 01/12/2014 20:53, Nard Moseley (Digital Metaphors) wrote:

    I'm using English (United Kingdom) with the currency format settings
    being the Windows default for that locale.
  • edited December 2014
    Nard,

    Further investigation into this reveals that its a formatting issue: if
    I select the cell in Excel the raw data is indeed negative, but it
    displays as "£100.00". If I check its formatting its is NOT "currency"
    but "custom" and takes the form of "£#,##0.00;£#,##0.00". Changing it to
    "currency" corrects the problem.


  • edited December 2014
    Hi Tom,

    For RB 16.0, I'm creating a patch

    For RB 15, a work around is to assign the DBText/Variable DisplayFormat
    property.

    RB translates Delphi display formats to Excel compatible formats and adds
    them to the Workbook as 'custom' formats. The issue is that the custom
    format is incorrect.



    Best regards,

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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.