Bug 118324 - Certain Number Formats not saved correctly in ODF spreadsheets (works in .xlsx)
Summary: Certain Number Formats not saved correctly in ODF spreadsheets (works in .xlsx)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0
Keywords:
Depends on:
Blocks: Number-Format Save
  Show dependency treegraph
 
Reported: 2018-06-22 16:01 UTC by Jim Avera
Modified: 2020-09-13 14:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
formatMS.xlsx demo file (5.80 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-06-22 16:02 UTC, Jim Avera
Details
formatODF.ods (with corrupted formats) (15.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-06-22 16:02 UTC, Jim Avera
Details
formatMS.xlsx demo file (6.41 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-06-22 16:14 UTC, Jim Avera
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Avera 2018-06-22 16:01:06 UTC
Description:
Many number formats are corrupted when a spreadsheet is saved in ODF (.ods file).  

These problems do not exist when saving in .xslx format.


Steps to Reproduce:
1. Open the attached  formatMS.xlsx  file
2. File-SaveAs
       Select ODF Spreadsheet (.ods) format
       Change name to formatODF.ods or similar
       Save
3. File->Reload

Actual Results:
Formats are corrupted.   Select a cell and Rightclick->Format Cells
to view the saved/reloaded format.  Here is what I got:
  
  ###,###,##0.00 changed to #,##0.00
  ########0.00 change to 0.00
  ########.##### changed to #.#####
  ???????0.00??? changed to #,###.00
  ???,???,??0.00??? changed to #,###.00???

Expected Results:
Formats saved correctly.  Or, if all formats used in a document can not be saved, the warn the user when saving (or better, when initially setting them).


Reproducible: Always


User Profile Reset: No



Additional Info:
See also: https://bugs.documentfoundation.org/show_bug.cgi?id=117575

The present bug was observed using Version: 6.2.0.0.alpha0+
2018-06-22_05:00:33 Locale: en-US (en_US.UTF-8); Calc: group threaded
(which has the fixes for the above-referenced bug 117575)
Comment 1 Jim Avera 2018-06-22 16:02:16 UTC
Created attachment 143044 [details]
formatMS.xlsx demo file
Comment 2 Jim Avera 2018-06-22 16:02:55 UTC
Created attachment 143045 [details]
formatODF.ods (with corrupted formats)
Comment 3 Jim Avera 2018-06-22 16:14:37 UTC
Created attachment 143047 [details]
formatMS.xlsx demo file
Comment 4 Buovjaga 2018-06-22 17:09:03 UTC
Setting immediately to NEW per consensus in bug 117575
Comment 5 Laurent Balland 2018-06-23 09:16:24 UTC
(In reply to Jim Avera from comment #0)
>   ###,###,##0.00 changed to #,##0.00
This is intentional: format is simplified and display is the same
>   ########0.00 change to 0.00
same
>   ########.##### changed to #.#####
same: optional figures with '#' are automatically added if necessary, so no need to add more than 1 '#', except with thousand separator.

>   ???????0.00??? changed to #,###.00
This would be a bug:
- decimal part is modified
- thousand separator is added
However, I can NOT reproduce. I get #.00??? which is "correct" as '?' cannot yet be saved in integer part (but '0' should)
>   ???,???,??0.00??? changed to #,###.00???
This should also be enhanced to take into account the number of '?' in addition to the number of '0' in decimal part.

About attachment 143047 [details] formatMS.xlsx demo file: '?' are intended to get alignment of digits, so you should use default horizontal alignment of cell (not center)
Comment 6 Laurent Balland 2018-06-23 09:18:07 UTC
(In reply to Laurent BP from comment #5)
> >   ???,???,??0.00??? changed to #,###.00???
> This should also be enhanced to take into account the number of '?' in
> addition to the number of '0' in INTEGER part.
Comment 7 Jim Avera 2018-06-23 16:56:23 UTC
> About attachment 143047 [details] formatMS.xlsx demo file: '?' are intended
> to get alignment of digits, so you should use default horizontal alignment
> of cell (not center)

No, if using a fixed-width font (e.g. Courier) then any alignment is okay.  So, for "Center" horizontal alignment, the space-padded numbers are centered and the decimal points will align (assuming all values fit in the number of digit positions specified in the Number format). 

Using "Left" or "Right" alignment would produce a different-looking result, possibly not what the user wants (e.g. little or no side margin on one side).
Comment 8 Commit Notification 2018-07-10 12:54:17 UTC
Laurent BP committed a patch related to this issue.
It has been pushed to "master":

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

tdf#118324 Extend ODF for blank in integer

It will be available in 6.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 9 Commit Notification 2018-07-10 16:41:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Revert "tdf#118324 Extend ODF for blank in integer"

It will be available in 6.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 Xisco Faulí 2018-11-28 12:58:45 UTC
Dear Laurent BP,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 11 Regina Henschel 2020-09-12 23:46:43 UTC
Cells D2:D9  Excel shows decimal delimiter, LibreOffice not.

Cells E2:G2  The zero left from the decimal delimiter is missing.

Tested with Version: 7.1.0.0.alpha0+ (x64)
Build ID: 1e0cfd5662d95cea84e80e4fe10d52c3b1101ae6
CPU threads: 8; OS: Windows 10.0 Build 18362; UI render: Skia/Vulkan; VCL: win
Locale: de-DE (en_US); UI: en-US
Calc: CL
Comment 12 Regina Henschel 2020-09-12 23:58:46 UTC
This is not a problem of ODF. The needed attributes exists in ODF 1.3. Therefore I remove block 108198.
Comment 13 Thomas Lendo 2020-09-13 13:36:24 UTC
(In reply to Regina Henschel from comment #12)
> This is not a problem of ODF. The needed attributes exists in ODF 1.3.
> Therefore I remove block 108198.
@Regina, thanks for seeing through the ODF export invalid meta bug. It was misused in the past for issues where LibreOffice makes something wrong when saving in OpenDocument format but the xml file itself was valid anyway. I will add these issues to the 'Save' meta bug. Or do you think a meta bug is useful which lists ODF saving problems without being invalid regarding the xml scheme?
Comment 14 Regina Henschel 2020-09-13 14:07:44 UTC
It is not a problem of saving, but the Excel number format codes are not correctly translated on import.