Bug 40517 - Decimal alignment (user defined number formats with question marks) lost in ODF
Summary: Decimal alignment (user defined number formats with question marks) lost in ODF
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All Windows (All)
: medium normal
Assignee: Laurent BP
QA Contact:
URL:
Whiteboard: target:5.2.0 target:5.1.0 target:5.1.1
Keywords:
: 93469 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-08-31 09:27 UTC by GerardF
Modified: 2016-10-25 19:11 UTC (History)
9 users (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 GerardF 2011-08-31 09:27:26 UTC
LibreOffice 3.4.3 under Windows vista. (French UI)

Step to reproduce :
1. In cells A1 to A3, enter decimal number like
1,1
1,12
1,123
(comma is decimal separator in french)

2. Format > Cell > Numbers, format code :
0,???
Police : Courier New

Numbers are aligned with comma.

3. Save.

Re-open the file, format is loss.
replaced by 0,000
Comment 1 Thomas Arnhold 2013-05-01 00:42:34 UTC
Can confirm the behavior with "0,???" in German and "0.000" with US English.

But why don't you use "#,###" which is the right placeholder? With this it works.

I don't know what the exact intention of the question mark is here.
Comment 2 Urmas 2013-05-01 03:19:47 UTC
Question mark is used for alignment at decimal separator.
Comment 3 Laurent BP 2014-12-30 11:30:48 UTC
Confirmed with Version: 4.4.0.1.0+
Build ID: 4774b2f0e6a0e895d1e63256147eaff59efd0eda
TinderBox: Win-x86@51-TDF, Branch:libreoffice-4-4, Time: 2014-12-28_16:31:35
Locale: fr_FR
Comment 4 Laurent BP 2015-12-06 20:48:13 UTC
The problem is that ODF don't know how to save this format. If you save in XLS, format 0,??? is preserved.

This bug requires first to enhance ODF.
Change title accordingly.
Comment 5 Laurent BP 2015-12-30 21:16:42 UTC
Would it be possible to use number:decimal-replacement=" " to code '?' instead of '#'?
Comment 6 A (Andy) 2015-12-31 09:39:50 UTC
Reproducible with LO 5.1.0.1, Win 8.1

saved as xls the format is kept
saved as ods the format is changed

Remark: In my case the three numbers from step 1 are not fully "aligned" with the comma.  The comma is horizontally not at the same position in the three cells.  It is more right aligned the shorter the number.  Should it be that way?
Comment 7 GerardF 2015-12-31 10:57:30 UTC
(In reply to A (Andy) from comment #6)

> Remark: In my case the three numbers from step 1 are not fully "aligned"
> with the comma.  The comma is horizontally not at the same position in the
> three cells.  It is more right aligned the shorter the number.  Should it be
> that way?

You need to use a "Monospaced" / "Fixed-Width" font in order to have a good alignment.
With Proportional font, numbers and spaces don't have the same width, so alignment is not correct.
Comment 8 Katarina Behrens (CIB) 2016-01-07 13:41:30 UTC
*** Bug 93469 has been marked as a duplicate of this bug. ***
Comment 9 Commit Notification 2016-01-11 13:23:39 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ee66e306cf0ca9c2b56de26c28e8130d7b72f64

tdf#40517 Export/Import in ODF decimal replacement with space

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Yousuf Philips (jay) 2016-01-19 09:52:10 UTC
Confirmed saving attachment 117948 [details] to ODS works now on reopen. It would be good to push the fix to 5.1 and 5.0.

Version: 5.2.0.0.alpha0+
Build ID: d95d9d7f908419f397941ef60ac6ced3261c9b87
CPU Threads: 2; OS Version: Linux 4.2; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-01-19_00:40:21
Locale: en-US (en_US.UTF-8)
Comment 11 Laurent BP 2016-01-19 20:29:52 UTC
(In reply to Yousuf (Jay) Philips from comment #10)
> Confirmed saving attachment 117948 [details] to ODS works now on reopen. It
> would be good to push the fix to 5.1 and 5.0.
This modification of ODF is not supported by previous versions: they are treating "0.???" as "0.--", so making more confusion than "0.000". So I think it can not be backported to 5.0. It may be possible for 5.1 if not too late.
Comment 12 Yousuf Philips (jay) 2016-01-20 20:21:53 UTC
(In reply to Laurent BP from comment #11)
> This modification of ODF is not supported by previous versions: they are
> treating "0.???" as "0.--", so making more confusion than "0.000". So I
> think it can not be backported to 5.0. It may be possible for 5.1 if not too
> late.

Definitely not to late to backport into 5.1 through gerrit. :D
Comment 13 Laurent BP 2016-01-20 20:57:45 UTC
Proposed commit for 5.1 branch
https://gerrit.libreoffice.org/21613/
Comment 14 Commit Notification 2016-01-21 07:23:38 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=50a3c6b2f3b849885580b304929eabe4a71b9162&h=libreoffice-5-1

tdf#40517 Export/Import in ODF decimal replacement with space

It will be available in 5.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2016-01-25 21:04:16 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-1-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f3a1b5ef1d5d8995156092dcbd8ebc39608ff3bd&h=libreoffice-5-1-0

tdf#40517 Export/Import in ODF decimal replacement with space

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Yousuf Philips (jay) 2016-01-26 09:35:46 UTC
(In reply to Laurent BP from comment #11)
> This modification of ODF is not supported by previous versions: they are
> treating "0.???" as "0.--", so making more confusion than "0.000". So I
> think it can not be backported to 5.0. It may be possible for 5.1 if not too
> late.

Just to make sure i got what you meant here. You modified LO's reading and writing of valid ODF with regards to questions marks in a custom format, which makes this change incompatible with older versions of LO.
Comment 17 Laurent BP 2016-01-26 11:23:17 UTC
(In reply to Yousuf (Jay) Philips from comment #16)
> (In reply to Laurent BP from comment #11)
> > This modification of ODF is not supported by previous versions: they are
> > treating "0.???" as "0.--", so making more confusion than "0.000". So I
> > think it can not be backported to 5.0. It may be possible for 5.1 if not too
> > late.
> 
> Just to make sure i got what you meant here. You modified LO's reading and
> writing of valid ODF with regards to questions marks in a custom format,
> which makes this change incompatible with older versions of LO.
No. Valid ODF are opened and saved in the same way.
I just wanted to emphasise that this modification of ODF can not be correctly interpreted with previous versions. So I think it is not a good idea to introduce such a modification in the middle of a branch.
Comment 18 Eike Rathke 2016-01-26 18:47:22 UTC
I agree, this should not be backported to 5-0

To clarify, version 5.0.x (and some earlier) store a number format code of "0.??" in ODF as

<number:number number:decimal-places="2" loext:min-decimal-places="2" number:min-integer-digits="1"/>

which when reading the file results in a number format code of "0.00" instead, not preserving the original meaning.

A number format code of "0.--" is stored in ODF as

<number:number number:decimal-places="2" loext:min-decimal-places="0" number:min-integer-digits="1" number:decimal-replacement="--"/>

and preserved when reading.


With the change 5.1 will store "0.??" in ODF as

<number:number number:decimal-places="2" loext:min-decimal-places="0" number:min-integer-digits="1" number:decimal-replacement=" "/>

However, 5.0.x and earlier ignore the decimal-replacement character here and still produce a wrong "0.--" number format code.
Comment 19 Commit Notification 2016-01-27 16:03:30 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=ed8d3d936d9008a34a1f59baee2c241157588dba

tdf#40517 Update for '?' in decimal number format
Comment 20 Commit Notification 2016-01-29 13:33:43 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=0caa881976b2213448b33fa5c31741759a5e4458&h=libreoffice-5-1

tdf#40517 Update for '?' in decimal number format
Comment 21 Commit Notification 2016-02-17 08:55:18 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d977a49ef0fb5377379dbb7516cad3ee8ecead41

tdf#40517 Unit test for question marks in number format

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.