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)
Created attachment 143044 [details] formatMS.xlsx demo file
Created attachment 143045 [details] formatODF.ods (with corrupted formats)
Created attachment 143047 [details] formatMS.xlsx demo file
Setting immediately to NEW per consensus in bug 117575
(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)
(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.
> 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).
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.
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.
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.
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
This is not a problem of ODF. The needed attributes exists in ODF 1.3. Therefore I remove block 108198.
(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?
It is not a problem of saving, but the Excel number format codes are not correctly translated on import.
Laurent Balland committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/46b7fd59f9d0dfb5544df99494d3a3d3a372ae8b tdf#118324 Treat blank ? in integer It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Confirming fixed in master. Thanks for working all this out! Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 529dce3d5b695637a1ccc8b4b697d87c5db0d3a2 CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded