[Libreoffice-bugs] [Bug 123878] Numeric format inconsistency: Macros vs Writer fields, formula calculation fails

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=123878

jsd.libreoffice at bvac.co.za changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |UNCONFIRMED
     Ever confirmed|1                           |0

--- Comment #2 from jsd.libreoffice at bvac.co.za ---
Thanks for the response.  I just downloaded and installed LO 6.4.2, and the bug
is still present, as described.

I will try to compose a minimal demonstration, but for now let me describe what
I am doing, and what is happening:

Note that in my system regional settings (ZA) the decimal point character is a
comma, 
not a period.  This is central to the problem, I believe.

(1) I have a Writer template with 3 "Set variable" fields...
      Field 1 ("Rate")  value is 1 cent (0.01) actually entered as 0,01
      Field 2 ("Count") value is initially 0
      Field 3 ("Total") value is Count*Rate and so initially displays as 0

    Note that I have to enter the fractional 1 cent value with a comma, not
    a period as the decimal.  If I type the value as 0.01 then formulas
    based on the field do not work (the value displayed for the formula
    field does not change)

(2) The macro opens a spreadsheet and reads a cell value like so:
      tCount = RangeTLCellValue(oSpreadsheet, "TotalCount")
    from a named range spanning one cell. The value is a calculation result
    that is not necessarily an integer.  In the debugger watch window after
    reading it, tCount is displayed as Type Variant/Double, Value 1234.5
    (Note the period in there, not a comma)

(3) The macro then sets tCount into the Writer document "Count" field
    with the equivalent of this line:
       TextFieldMaster(sName).DependentTextFields(iIndex).Content = tCount
    (it actually loops through all the dependent text fields, I can't remember
why)

(4) Finally the macro does this:
       ThisComponent.GetTextFields.Refresh

Now I would expect the Total field in the document to display "12.34" (or
"12,34")
(i.e. the result of calculating 1234.5 * 0.01) but it still shows 0.

If I inspect the document Count field, its value is 1234.5 exactly as it came
from the spreadsheet.

Now if I change the Count field value to 1234,5 (with a decimal comma) then
the Total field changes to the expected calculation result value 12,34

Phew.

To summarise:
  * Writer seems to insist that numeric field values use the decimal character
    defined in the system regional settings, in my case a comma.  If there
    is a period instead of a comma as the decimal, the value is treated as
    non-numeric.  That's arguably justifiable, BUT...

  * A non-integer value extracted from a spreadsheet is rendered using a
    period as the decimal, not a comma, when it is stored in a Writer field.
    Thus Writer treats it as non-numeric is it is used in a formula.

So these two behaviours conflict with each other.

The older behaviour of Open Office (where my macro was initially developed)
was to always use a decimal period in field values, but to _show_ values in
the document using the regional characters.  This is what I would recommend.

Hope that all makes sense, and thanks for reading this far :-)

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190610/3db98bfa/attachment-0001.html>