Bug Hunting Session
Bug 123878 - Numeric format inconsistency: Macros vs Writer fields, formula calculation fails
Summary: Numeric format inconsistency: Macros vs Writer fields, formula calculation fails
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Extensions (show other bugs)
Version:
(earliest affected)
6.2.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-UNOAPI
  Show dependency treegraph
 
Reported: 2019-03-05 15:50 UTC by jsd.libreoffice
Modified: 2019-09-19 11:52 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description jsd.libreoffice 2019-03-05 15:50:40 UTC
[Sorry if Extensions component is wrong, wasn't sure what to pick]

Running LO 6.2.0.3 on MacOS 10.13.6 in South Africa, where system regional setting uses comma rather than period for decimal separator.

Writer treats strings with period, eg "1.5" as non-numeric and evaluates that as 0.  With a comma instead, eg "1,5" the value is one-and-a half. OK, fair enough.

Now I have a macro that extracts a value from a spreadsheet cell, and sets that value into a Writer variable field which is then used elsewhere in a Writer formula.

The latter formula does not compute correctly - result is 0 - because the extracted spreadsheet value (a floating point number, type Variant/Double returned by RangeTLCellValue) gets turned into a string with a period for decimal point in the Writer variable field (DependentTextField.Content = myNumericValue)

Either the implicit double-to-string conversion should be using the same punctuation that's expected elsewhere (so comma not period) or else everything should accept a period as decimal separator in number strings, and then do the local-dependent presentation only at the formatting stage (which is what OpenOffice used to do: this macro began life there).

FWIW I tried changing Language settings in the document, made no difference.  Presumably the system locale trumps that for number parsing.  This also affected static "set variable" fields in the template: I had to change decimal periods to commas in those too, to make the formulas work.
Comment 1 Xisco Faulí 2019-06-10 16:25:59 UTC
Thank you for reporting the bug.
it seems you're using an old version of LibreOffice.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 2 jsd.libreoffice 2019-06-10 18:18:30 UTC
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 :-)