Hello,

Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

You can always visit our QA channel #libreoffice-qa@freenode if you need help with bugs
https://irc.documentfoundation.org/?settings=#libreoffice-qa

I attached a sample ODS file which I created with locale like this:

`Tools` -> `Options` -> `Language Settings` -> `Languages` -> `Locale setting` is `Hungarian` and the `Decimal separator key` checkbox below it, it is ticked - `Same as local setting (,)`

#1
If I open the attached file then the initial parameters in the yellow area show comma as decimal separator, because they are formatted as Hungrian (Format -> Cells -> Numbers -> Language). This is good.

#2
The random fractional numbers in the amber area contain decimal points becuase I formatted them as `English (USA)`. This is also good.

#3
The strings in the green cells, however, contain comma again, despite the fact that I formatted them as `English (USA)`. At first glimpse, it seems to be a bug. At second glimpse, I understand that these cells are not numbers any more and `English (USA)` is meant to be applied only on numbers, not on texts. But then, how could I display numbers other way than my local setting dictates? 

#4
The complete SVG is concatenated in the red G9 cell which also shows commas, not numbers. This is also not what I wanted to have because in an SVG file, decimal comma is not premitted, just decimal point.


In order to get decimal points in my green and red cells, I would need to apply explicit formatting formulas which 
- is cumbersome to add them one by one
- would make the whole spreadsheet calculation slower
- does not work if the file is write protected

Due to bug #125484, changing the locale does not update the strings either.

Even if both #125483 and #125484 get fixed, my problem is still there. If I get a file from someone who used English locale and I have Hungarian then I see the pure number cells in Hungarian but the texts concatenating thise numbers are in the author's English.

My proposal would save the locale setting right into the file. The author could pick how to handle the locale, in the File -> Properties -> General dialog:
- "use user's default"
- "enforce English" (or whatever else) 
With this one, the author could determine how his file needs to be handled and this information would travel as part of the file.

And even if the author adjusts something the receiver does not like, it is easy to change this setting only for this particular file. (By contrast, if I change the LO locale then it affects all opened file right once, which I normally dont't want.)

Created attachment 151671 [details]
text cells keep original formatting

The request might be valid, but I would like some developer input before accepting it.

(In reply to csongor from comment #2)
> In order to get decimal points in my green and red cells, I would need to
> apply explicit formatting formulas which 
> - is cumbersome to add them one by one
> - would make the whole spreadsheet calculation slower
> - does not work if the file is write protected

It is not so cumbersome. You can add the formatting to the source formulas like so:
=TEXT((ROUND(RAND()*(G$4-2*C5)+C5;2));"###.##")

From help:

TEXT

Converts a number into text according to a given format.
Syntax

TEXT(Number; Format)

Number is the numerical value to be converted.

Format is the text which defines the format. Use decimal and thousands separators according to the language set in the cell format.

Example

=TEXT(12.34567;"###.##") returns the text 12.35

=TEXT(12.34567;"000.00") returns the text 012.35

(In reply to Buovjaga from comment #5)
> The request might be valid, but I would like some developer input before
> accepting it.
> 
> (In reply to csongor from comment #2)
> > In order to get decimal points in my green and red cells, I would need to
> > apply explicit formatting formulas which 
> > - is cumbersome to add them one by one
> > - would make the whole spreadsheet calculation slower
> > - does not work if the file is write protected
> 
> It is not so cumbersome. You can add the formatting to the source formulas
> like so:
> =TEXT((ROUND(RAND()*(G$4-2*C5)+C5;2));"###.##")
> 
> From help:
> 
> TEXT
> 
> Converts a number into text according to a given format.
> Syntax
> 
> TEXT(Number; Format)
> 
> Number is the numerical value to be converted.
> 
> Format is the text which defines the format. Use decimal and thousands
> separators according to the language set in the cell format.
> 
> Example
> 
> =TEXT(12.34567;"###.##") returns the text 12.35
> 
> =TEXT(12.34567;"000.00") returns the text 012.35

Hello csongor@halmai.hu,
Does it fulfill your expectations ?

(In reply to Xisco Faulí from comment #6)

> > Example
> > 
> > =TEXT(12.34567;"###.##") returns the text 12.35
> > 
> > =TEXT(12.34567;"000.00") returns the text 012.35
> 
> Hello csongor@halmai.hu,
> Does it fulfill your expectations ?

My answer has two parts.

Part #1.

No, it doesn't work this way. The following formula produces 3,14, not 3.14 ("SZÖVEG" means "TEXT" in Hungarian):

=SZÖVEG(PI();"###.##")

More precisely, it works... weird... I attach a new file pi_text.ods. Both A1 and A2 have the same formula:

=SZÖVEG(PI();"###.##")

but in A1 there is 3,14 displayed (with comma) while in A2 there is 3.14 (with decimal point). I cannot see any difference between the two cells, even the formatting seems to be the same. If I am right and there is no difference between the cells then it seems to be a bug in my LO which is:

Verzió: 6.3.3.2 (x64)
Build az.: a64200df03143b798afd1ec74a12ab50359878ed 




Part #2:

Even if it would work as expected, the sample spreadsheet should be changed a lot. For example, instead of 

="<circle cx='"&A5&"' cy='"&B5&"' r='"&C5&"' stroke='black' stroke-width='3' fill='red' />"

in D4, the following formula should be used:

="<circle cx='"&TEXT(A5;"###.##")&"' cy='"&TEXT(B5;"###.##")&"' r='"&TEXT(C5;"###.##")&"' stroke='black' stroke-width='3' fill='red' />"

I am not saying it is an impossible change but it requires a deep understanding about the calculations in the spread sheet and also requires a lot of cells to modify. 

The following solutions would be better:

a) 
There was a document-wide setting how the numbers should be converted to string. If the ODS file contains that the original author used English (USA) then I can decide if I want to use their setting or use my own default.

b)
The cells that contain concatenation (like D5), should respect the formatting setting. For example, if I format the cell this way:

in the Format -> Cells -> Cells dialog
- Category = Number
- Format = General
- Language = English (USA)

then the concatenation should display the numbers with English formatting, not Hungarian. It should be "<circle cx='4.5'...>", not "<circle cx='4,5'...>"

Created attachment 163189 [details]
Same content is A1 and A2, displayed 3,14 vs. 3.14 (comma versus point)

In your latest sample file, if you recompute the cell by using F9 on it (or Ctrl+Shift+F9 anywhere), the formatting becomes consistent.

(In reply to Leyan from comment #10)
> In your latest sample file, if you recompute the cell by using F9 on it (or
> Ctrl+Shift+F9 anywhere), the formatting becomes consistent.

That's true but it is still a weird behaviour. I would classify it as a bug but unrelated to this ticket. 

What is related to this ticket is the original problem which "Part #2" is reflecting to.

(In reply to csongor from comment #7)
> Part #2:
> 
> Even if it would work as expected, the sample spreadsheet should be changed
> a lot. For example, instead of 
> 
> ="<circle cx='"&A5&"' cy='"&B5&"' r='"&C5&"' stroke='black' stroke-width='3'
> fill='red' />"
> 
> in D4, the following formula should be used:
> 
> ="<circle cx='"&TEXT(A5;"###.##")&"' cy='"&TEXT(B5;"###.##")&"'
> r='"&TEXT(C5;"###.##")&"' stroke='black' stroke-width='3' fill='red' />"
> 
> I am not saying it is an impossible change but it requires a deep
> understanding about the calculations in the spread sheet and also requires a
> lot of cells to modify.

I disagree. An easy way to change your spreadsheet is to add 3 intermediate columns formatted in English corresponding to columns A, B and C in which you compute the corresponding text value. Then in column D you have only to change to columns.

For example, if you use columns AA, AB and AC:
AA5=text(A5;"0.00")
AB5=text(B5;"0.00")
AC5=text(C5;"0.00")
D5=="<circle cx='"&AA5&"' cy='"&AB5&"' r='"&AC5&"' stroke='black' stroke-width='3' fill='red' />"
Work to do:
1/ create the formula in AA5
2/ propagate it to AC5, then to AC24
3/ change the formula in D5
4/ propagate it to D24.

It works for me in French where the decimal separator is the comma too.

> 
> The following solutions would be better:

Changing the software is far more complex than changing your spreadsheet.
On the contrary, modifying your spreadsheet is much faster and easier and does not depend on someone else's good will.

I propose to close this enhancement request as WontFix.

Best regards. JBF

Created attachment 174261 [details]
Corrected sample file

(In reply to Jean-Baptiste Faure from comment #12)
> An easy way to change your spreadsheet is to add 3 intermediate
> columns formatted in English corresponding to columns A, B and C in which
> you compute the corresponding text value. Then in column D you have only to
> change to columns.
> 
> For example, if you use columns AA, AB and AC:
> AA5=text(A5;"0.00")
> AB5=text(B5;"0.00")
> AC5=text(C5;"0.00")
> D5=="<circle cx='"&AA5&"' cy='"&AB5&"' r='"&AC5&"' stroke='black'
> stroke-width='3' fill='red' />"
> Work to do:
> 1/ create the formula in AA5
> 2/ propagate it to AC5, then to AC24
> 3/ change the formula in D5
> 4/ propagate it to D24.
> 
> It works for me in French where the decimal separator is the comma too.
> 

I agree. This solution appears quite easy and natural to me. Using intermediate conversions is a common technique for changing between formats not just in spreadsheets. Generating intermediate/partial results for consumption in cells elsewhere is standard spreadsheet practice.

I'm going to change status to RESOLVED/WONTFIX.

It does not work exactly this way in my Calc. Here is what I do:

A1==pi()
B1==TEXT(A1;"0.00")
C1=="<circle cx='8' cy='9' r='"&B1&"' />"

A1, B1 and C1, they all show decimal commas, not point. Even if I format all the three cells via Format -> Cells -> Numbers -> Language to English, the commas remain. As @Leyan mentioned, I need to do a manual refresh to enforce the decimal _point_ to appear. 

This is not a big deal, I could live with this. But my original problem was how much work is needed to fix a spreadsheet. 


My original SVG example was a simplified example. It is quite easy to imagine a spreadsheet that has much more complex relationships between various cells located on different worksheets.

Let me show another example, which is just slightly different from my original one. Let's say the author produces this spreadsheet in order to create SVG circles from a given diameter and then they want to parse the computed radius via regular expression:

A1=3.8
B1=="<circle cx='8' cy='9' r='"&ROUND(A1/2;1)&"' />"
C1==REGEX(B1;"r\=\'[0-9.]+\'")
D1==MID(C1;4;LEN(C1)-4)

You can ask why the author made it this way, rather than computing the radius in B1 and use it in C1. It can have several reasons: laziness, incompetence, historic reasons, other systems are depending on the existing columns, etc. Spreadsheets are not always suboptimal. 

If this spreadsheet is opened in an English LO, the parsed radius is displayed nicely. However, if I open it in a non-English Calc, you will see "#N/A" errors in D1 and E1 because the decimal value the regexp looks for, does not exist in the string.

In order to understand why it doesn't work, 
- I need to understand what the purpose of the cells E1 and D1 are, 
- I need to be aware that a localisation problem can cause the failure in LibreOffice

In order to fix it, 
- I need to add the extra column to do the TEXT conversion, E1==TEXT(A1;"0.0")
- I need to change the formula in B1 to use the value from E1 instead of A1... 
- Oops, it doesn't work, E1 is already a text, I cannot use it in computations, so I need to move the computation from B1 into E1. E1==TEXT(ROUND(A1/2;1);"0.0") 
- Oops, there is a funny thing. For some weird reason, ROUND("1.9",2) is 22103 (honestly, WHY???:)). 
- I also need to format all the important cells, including the newly created one to language=English
- Still wrong? Never mind, just press Shift+Ctrl+F9

After this, the D1 cell will show the correctly parsed rounded radius. It works how the author planned.

A long story short: figuring out why the spreadsheet doesn't work is not easy. But fixing it is even more complicated, even in this very simple case. If there are more complex computations, it can be a huge task.

Most of the users would say:
- "the author sent me a wrong spreadsheet" (and the author would say the other party cannot use LO)
- LibreOffice is baaad. What works for them, doesn't work on my computer.

Most of the users wouldn't work so much on a spreadsheet. They would just simply be unhappy and frustrated.

By contrast, if Calc would tell them when they open the file:
"The file has been created in English but your system is Hungarian. Some computations can work unexpectedly. Do you you want to open it in English or Hungarian?"

Well, in this case the user could have a guess what is going wrong and they could simply reopen the file with the original language settings.

I reopen it because I hope you will reconsider this.

(In reply to csongor from comment #15)
> By contrast, if Calc would tell them when they open the file:
> "The file has been created in English but your system is Hungarian. Some
> computations can work unexpectedly. Do you you want to open it in English or
> Hungarian?"

While it doesn't do what I think you originally proposed, there is the "Tools - Options - Language Settings - Languages - Default Languages for Documents" setting, which is saved in the file and can optionally be applied to just the current file.

It's help page actually says it should determine the language used for spell checking, hyphenation and also the decimal separator. It certainly reads as if it should change the locale formatting generally, overriding the default formatting options of the user.

So it would seem this general issue has been considered in the past, with at least some work done.

I tried it in various situations in Writer (the most likely place it should work) but it appears to be somewhat incomplete there, and had no effect in Calc AFAICS.

I also think that bug #34142 is not completely implemented. There are certainly beginnings that are visible but nothing functional.

IMO if either of these lines of work were fully complete, one of them aught to resolve this issue. That at least should be determined before starting a new line of work.