Issue 126827 - breaks custom cell number format in ods after save
Summary: breaks custom cell number format in ods after save
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: 4.1.2
Hardware: All All
: P5 (lowest) Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-02-07 13:25 UTC by Falcon
Modified: 2016-05-16 23:38 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description Falcon 2016-02-07 13:25:03 UTC
Create custom number format for cell like: 0"A"0"B"0"C"0"D"."E"0"F"0"G"00"L"
and save document as ODS. 
When close Cals and open this document cell format is breaks and now it like: 
0"A"0"B"0"C"0.0000"DEFGL".

When unpack document in styles.xml you can see next:
<number:number-style style:name="N106">
<number:number number:decimal-places="4" number:min-integer-digits="4">
<number:embedded-text number:position="3">A</number:embedded-text>
<number:embedded-text number:position="2">B</number:embedded-text>
<number:embedded-text number:position="1">C</number:embedded-text>
</number:number>
<number:text>DEFGL</number:text>
</number:number-style>

This bug is repeated in LibreOffice Calc too: https://bugs.documentfoundation.org/show_bug.cgi?id=96723 - not fixed (2016.02.07).
Comment 1 orcmid 2016-02-07 18:02:09 UTC
This is a consequence of the ODF specification of the number:position attribute in ODF 1.2 Part 1 section 19.354.  The value must be an integer, starting with 1, and it only applies counting left from the rightmost position of the integer part of a number's presentation.  

So there is no way to put the "D" (at position 0) and anything to the right of the decimal (technically, negative positions given the number:position" definition).

The number:position definition probably codifies what the implementers of OpenOffice.org thought the behavior might have already been.  That was probably determined too long ago (i.e., 2005 or earlier).

I confirm that AOO 4.1.2 Calc allows the User-defined format code 
0"A"0"B"0"C"0"D"."E"0"F"0"G"00"L" for cells.

To be entered and that it will be usable in making cell entries.  For example, 

Entered   Displayed/Saved       Reloaded

0         0A0B0C0D.E0F0G00L     0A0B0C0.0000DEFGL
-3.14159  -0A0B0C3D.E1F4G16L    -0A0B0C3.1416DEFGL
99999     99A9B9C9D.E0F0G00L    99A9B9C9.0000DEFGL

And, when the file is saved as an .ods, the sheet remains displaying the above Displayed/Saved forms.

However, the saved .ods has changed styles that satisfy the ODF rules and the reloading is as described by Falcon and shown as the Reloaded forms.

Finally, if the document is saved as an Excel 97/.../XP .xls file, the expected formatting is seen when opening that file in Microsoft Office Excel 2016 *and* back in AOO 4.1.2 Calc.  

OBSERVATION #1: This provision in OpenOffice Calc (and there are a few others) is only implemented correctly in saving and opening in .xls format.  When the feature is used and the document is saved as an ODF Spreadsheet .ods file, there is silent transformation without any indication to the user and the saved file has a corruption of what the user saw as successful.

OBSERVATION #2: There is a policy issue here with regard to interoperability via the OpenDocument format.  We are in the peculiar position that, if this behavior were to be sustained, we need to warn users that saving as .ods may not preserve all features used in the user's document.

OBSERVATION #3: On opening the .xls version in Excel 2016 and doing a new Save As ... into .ods format from Excel 2016, there is the usual compatibility warning and, after the save, the document is still shown in Excel with the feature working.  On opening of that Excel-created .ods in AOO 4.1.2 Calc, the result is the same as the Calc Reloaded case, above.  In that case, the Excel warning about possible loss of features is accurate, even though it is a rubber-stamp message.

CONCLUSION: Giving preference to .xls fidelity and silently failing on saves to ODF formats is a nasty problem.  The preference may satisfy a majority of users who only look for Office-format interoperability.  Nevertheless the silent failing in production of the preferred/default OpenDocument format files is awful.  

DIFFICULTY: This takes a clarification of policy (perhaps the easiest step) and some remedy in the software.  It may involve half measures and there is no outlook on how long it can be before there is any remedy.
Comment 2 orcmid 2016-02-07 21:43:47 UTC
(In reply to orcmid from comment #1)
[ ... ]
> OBSERVATION #2: There is a policy issue here with regard to interoperability
> via the OpenDocument format.  We are in the peculiar position that, if this
> behavior were to be sustained, we need to warn users that saving as .ods may
> not preserve all features used in the user's document.
[ ... ]
> CONCLUSION: Giving preference to .xls fidelity and silently failing on saves
> to ODF formats is a nasty problem.  The preference may satisfy a majority of
> users who only look for Office-format interoperability.  Nevertheless the
> silent failing in production of the preferred/default OpenDocument format
> files is awful.  
> 
> DIFFICULTY: This takes a clarification of policy (perhaps the easiest step)
> and some remedy in the software.  It may involve half measures and there is
> no outlook on how long it can be before there is any remedy.

Questions on the policy aspects of this have been raised on the dev@openoffice.apache.org mailing list, where such matters are discussed and resolved.  See

<http://mail-archives.apache.org/mod_mbox/openoffice-dev/201602.mbox/%3C002b01d161e0%24fedb79a0%24fc926ce0%24%40apache.org%3E>.

Even if a clear, explicit policy emerges, putting it into practice and executing on these existing situations may require an indefinite time.

It may be that the best we can do in the immediate case is to put warnings in appropriate places on the Community Forums and the OpenOffice wikis.
Comment 3 orcmid 2016-05-16 23:38:12 UTC
A similar limitation in the apparent acceptance of custom formatting in Writer tables that are not actually preserved in saved ODF documents is demonstrated in Issue 126973.  Only in the case of Writer, there is no form of saved document that actually preserves such formatting.