Description: After saving and closing an .ods file in Calc, when the file is opened again the custom Format Code that was used before has now changed. Either it was not saved correctly, or some "automatic" process is changing it when opening the file. Steps to Reproduce: 1_ Fill a column with the following values (one value in each row/cell): -12 12 0 1 2 -1 -2 2_ Select the column and change the font type to some mono-spaced (i.e. fixed-pitch, fixed-width, or non-proportional) font (e.g. Liberation Mono, Source Code Pro, Courier New, Consolas, Lucida Console, ...). 3_ Select "Align center" for the column. 4_ (Ctrl+1) Format -> Cell -> Numbers -> (custom) Format Code field: _-?0;-?0 (underscore, minus, question mark, zero, semicolon, minus, question mark, zero) 5_ Please note that, under the above conditions, these (whole) numbers are (correctly) shown "aligned", even-though the font alignment for the cells is (intentionally) "center", with the negative sign also aligned, and the zero value is also displayed. At this point, save and _close_ the workbook. 6_ Reopen the workbook and notice the following changes: 6.1_ The values are not shown "aligned" (in the sense they were before). The font alignment is still "center", but the contents are not shown in the same way they were before saving_and_closing the file. 6.2_ The zero value is not shown. 6.3_ The custom Format Code (Ctrl+1) has changed, without user knowledge / intervention: 6.3.1_ Before: _-?0;-?0 6.3.2_ After: " "#;-# 7_ Select the column. 8_ (Ctrl+1): change the Format Code to: _-?0;-?0;_-?0 (please note the additional third section, for zero). 9_ Save and Close. 10_ Reopen the workbook and notice the following changes (again): 10.1_ The values are not shown "aligned" (in the sense they were before). The font alignment is still "center", but the contents are not shown the same way they were before saving_and_closing the file. 10.2_ The zero value is not shown. 10.3_ The custom Format Code (Ctrl+1) has changed, without user knowledge / intervention: 10.3.1_ Before: __-?0;-?0;_-?0 10.3.2_ After: [>0]" "#;[<0]-#;" "# At the moment of writing, the reference for "Number Format Codes" in LO 7.4 is: https://help.libreoffice.org/7.4/en-US/text/shared/01/05020301.html Actual Results: Custom Format Code is not preserved after closing the file. In particular, the underscore_and_minus is being changed, and the "question_mark" and "zero" are being changed to "#". Expected Results: Custom Format Code should be preserved as the user introduced it and saved, not changed. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.3.2 (x64) / LibreOffice Community Build ID: 1048a8393ae2eeec98dff31b5c133c5f1d08b890 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: es-AR (es_AR); UI: en-US Calc: CL
Simplified test to reproduce. 1_ New Calc spreadsheet; enter the numbers (one in each cell): -12 0 1 2_ Select the cells, Ctrl+1, type in the following custom Format Code in the Numbers Tab: _-?0;-?0;_-?0;@ (underscore, minus, question mark, zero, semicolon, minus, question mark, zero, semicolon, underscore, minus, question mark, zero, semicolon, at sign) and accept/OK the new format. 3_ Save the file. Note that the format is still the same as it was just introduced and it is displayed accordingly. 4_ Close the file. Reopen it. The cells are displayed differently (e.g. the zero value is not shown). 5_ Ctrl+1. See that the Format Code is changed, and it is NOT equivalent to what was introduced: ? -> # 0 -> # _- -> " " Less than 2 minutes to reproduce and confirm.
With steps in comment 1, I can reproduce. After save a reload, I get the following format code: [>0]" "#;[<0]-#;" "#;@ The 0 is not displayed anymore. Version: 7.4.3.2 / LibreOffice Community Build ID: 1048a8393ae2eeec98dff31b5c133c5f1d08b890 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
Inherited from OOo, although I don't get the double-quoted spaces replacing the underscores: [>0]#;[<0]-#;#;@ OpenOffice.org 3.3.0 OOO330m20 (Build:9567)
(In reply to Stéphane Guillou (stragu) from comment #3) > I don't get the double-quoted spaces replacing > the underscores: TY for testing and confirming. There are at least 3 inconsistencies when applying the custom Format Code: ? -> # 0 -> # _- -> " " As mentioned above, at the moment of writing, the reference for "Number Format Codes" in LO 7.4 is: https://help.libreoffice.org/7.4/en-US/text/shared/01/05020301.html
That "UI visible space width" is not defined/supported by ODF https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#__RefHeading__1416346_253892949 hence is replaced with a literal space when saving. The ?0 apparently isn't supported either, currently it's saved as <number:number number:decimal-places="0" number:min-decimal-places="0" number:min-integer-digits="0"/> hence loaded as # That should be at least number:min-integer-digits="1" for the 0. For everything else an ODF file format extension would be needed.
That´s somewhat confusing. I mean, a custom Format Code should allow for one “0” to be displayed while leaving additional leading zeros as not_forced, and places should be respected, all as described by the official help files. Do I understand your reply correctly? #0 is supported but ?0 is not supported? And, if the underscore is not supported, then how built-in codes such as: #,##0_);(#,##0) are indeed supported (although, I’ve not tested it specifically)? (I am not doubting you; I’m simply confused). > For everything else an ODF file format extension would be needed. Is _another_ (new) bug report needed for such enhancement request? To be honest, I’m not sure what such enhancement request would or should convey. Or maybe there is already such enhancement request somewhere?
(In reply to ady from comment #6) > That´s somewhat confusing. I mean, a custom Format Code should allow for one > “0” to be displayed while leaving additional leading zeros as not_forced, > and places should be respected, all as described by the official help files. These format codes are an Excel thing. For saving in ODF they are transformed into an XML description of the properties of the format, the format code itself is not saved; and when loading, a format code is assembled from the XML elements. That ? and _x are preserved only for Excel file formats (until implemented for ODF as well) should probably be mentioned in the help text. > Do I understand your reply correctly? > > #0 is supported but > ?0 is not supported? Yes. > And, if the underscore is not supported, then how built-in codes such as: > #,##0_);(#,##0) > are indeed supported They are equally not, in ODF they get saved/loaded as #,##0" ";(#,##0) > > For everything else an ODF file format extension would be needed. > Is _another_ (new) bug report needed for such enhancement request? No. It means that we'd have to come up with an extension to the file format, and if that works propose it to the OASIS OpenDocument Format standard body.
(In reply to Eike Rathke from comment #5) > That "UI visible space width" is not defined/supported by ODF > https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/ > OpenDocument-v1.3-os-part3-schema.html#__RefHeading__1416346_253892949 > hence is replaced with a literal space when saving. So, IIUC, there seem to be several steps needed: 1_ > The ?0 apparently isn't supported either, currently it's saved as > > <number:number number:decimal-places="0" number:min-decimal-places="0" > number:min-integer-digits="0"/> > > hence loaded as # > > That should be at least number:min-integer-digits="1" for the 0. That would be a first step, IIUC (i.e. whatever is already supported, correct the "translation" to be as "accurate" as possible). 2_ Add the relevant information / details in the Help and wiki text about which codes are currently supported by/for .xls* format (i.e. when originally saved first as .xls* file) and which ones are supported by/for .ods format. > > For everything else an ODF file format extension would be needed. 3_ That would be step(s) 3+. Follow all the procedure(s) required so as to effectively implement the codes that are currently not "translated" appropriately (and their _combinations_), such as: 0 (zero) ? (question mark) _ (underscore) as they are present in the official LO Help, to be at par with .xls* file format. If it is not with their own bug reports, I am not sure how all these different steps should be managed so as to not let them fall between the cracks (especially since this one has no assignee yet).
If I remember well, ? in integer part was max-blank-integer-digits in the abandoned change https://gerrit.libreoffice.org/58595 I could update this change, without the decimal part of engineering notation ;-)
Actually, "? in integer part" is bug 118324, where a first attempt to fix it was reverted. I will continue on bug 118324. This bug report should only treat saving underscore in ODF
(In reply to Laurent Balland from comment #10) > This bug report should only treat saving underscore in ODF There are at least 3 codes (and their combinations) that are not saved correctly in ods. Currently, the custom Format Codes are being saved as follows: ? -> # 0 -> # _- -> " " See comment #8 for sum-up.
(In reply to ady from comment #11) > There are at least 3 codes (and their combinations) that are not saved > correctly in ods. Currently, the custom Format Codes are being saved as > follows: > > ? -> # > 0 -> # > _- -> " " > > > See comment #8 for sum-up. ? is now fixed in master (see bug 118324) 0 was already treated, and any "reasonable" format combining #, ? and 0 should be preserved in ODF in integer and decimal part. _x still needs an ODF extension. I accordingly modified the summary.
(In reply to Laurent Balland from comment #12) > ? is now fixed in master (see bug 118324) TY. > 0 was already treated I'd like to emphasize that combining "?0" was not working in 7.4.4.2 for ods. I understand that you worked on the "?" part for 7.6; my point is that saying "0 was already treated" might be confusing to users, since the combination of "0" with some of the other codes was not working. > 0 was already treated, and any "reasonable" format combining #, ? and 0 > should be preserved in ODF in integer and decimal part. Would you please be so kind and mention here some example of "unreasonable" combination of "#, ? and 0"? Not only it would help for testing (now and in future versions), but especially to be aware and to be able to provide such information to other users. > _x still needs an ODF extension. I accordingly modified the summary. Would this RFE be assigned to someone for that? I compared the following 2 versions with an .ods file: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: d8e6b488ceaff7c88856ebcfcfec14d2d8cd7652 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded Version: 7.4.4.2 (x64) / LibreOffice Community Build ID: 85569322deea74ec9134968a29af2df5663baa21 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: default; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL Steps and Results (in the same order as described here): With 7.4.4.2: Saved as: _-?0;-?0;_-?0;@ Opened as: [>0]" "#;[<0]-#;" "#;@ Then with 7.6.0.0.alpha0+... Saved as: _-?0;-?0;_-?0;@ Opened as: [>0]" "?0;[<0]-?0;" "?0;@ Then closed without saving, and opened it in 7.4.4.2: [>0]" "00;[<0]-00;" "00;@ Please note the double zeros in the last one. Whether this could generate problems with users sharing files between different versions of LO, IDK. Whether this can be avoided, IDK either. (On a side note, the ods file seemed to be opened slightly slower in the aforementioned 7.6.0.0.alpha0+ than in 7.4.4.2.)
(In reply to ady from comment #13) > I'd like to emphasize that combining "?0" was not working in 7.4.4.2 for > ods. I understand that you worked on the "?" part for 7.6; my point is that > saying "0 was already treated" might be confusing to users, since the > combination of "0" with some of the other codes was not working. As ? was not working, any combination with ? was not working. Combining # and 0 was working fine. > Would you please be so kind and mention here some example of "unreasonable" > combination of "#, ? and 0"? Not only it would help for testing (now and in > future versions), but especially to be aware and to be able to provide such > information to other users. Sorry I went too fast. The format code of integer part must be in the following order: - zero or any number of "#" - zero or any number of "?" - zero or any number of "0" For the decimal part, it is the opposite order. For instance, these formats are correct: ##??00.00??## ?????0.# But if you mix order of coding characters, format will be non sense. These formats cannot be saved in ODF (even if you can save them in XLSX) because they are not coherent: 00?#?.#0?0 ?#0.?0 > > _x still needs an ODF extension. I accordingly modified the summary. > > Would this RFE be assigned to someone for that? There may be someone who could assigned it to him/herself, but nobody will assigned to someone else. It's a voluntary project. As far as I am concerned, I could have a look in few weeks, if nobody takes care of it. > Please note the double zeros in the last one. Whether this could generate > problems with users sharing files between different versions of LO, IDK. > Whether this can be avoided, IDK either. Replacing ? with 0 is the best choice for versions unable to interpret ? in integer part: a place is reserved for the digit.
An interesting point that may or may not influence this RFE... The TEXT() function in LO Calc "partially" supports "_x": * when saving as ods, the "_x" format in TEXT() can be seen in the resulting cell. * when saving as xls, the "_x" format in TEXT() can be seen in the resulting cell. But: * when saving as XLSX, the "_x" format in TEXT() is NOT seen in the resulting cell. So maybe there is some code that can be re-used for the Custom Format Code field in the Format > Cell options, and there is also some exporting improvements to make too, for XLSX (which would need its own bug report, I assume). This is interesting, considering that the problems with "_", "?" and "0" were all for .ods files, not for Excel files (from comment 7).
(In reply to ady from comment #15) > The TEXT() function in LO Calc "partially" supports "_x": > > * when saving as ods, the "_x" format in TEXT() can be seen in the resulting > cell. > > * when saving as xls, the "_x" format in TEXT() can be seen in the resulting > cell. > > But: > > * when saving as XLSX, the "_x" format in TEXT() is NOT seen in the > resulting cell. I do not reproduce any bug with TEXT() function, saved in any format. My procedure: - enter a formula =TEXT( PI() ; "0_i0_M€" ) result is correctly 0 3 € 0, blank, 3, blank, blank blank, € - save to a format: ODS, XLSX or XLS - reload formula is preserved (except in XLS where extra spaces in formula text are removed) and result is always 0 3 € What is your exact procedure?
(In reply to Laurent Balland from comment #16) > What is your exact procedure? My apologies. I took what I initially did for this bug report and added a couple of columns to test TEXT() with the same format. When you reported a different result, I went back and rechecked. Something between text alignment, number format and the function itself generated what seemed to be the result I reported in comment 15. This is my mistake. I'll re-test this more thoroughly, and if it is relevant I'll open a separate bug report for it. Let's keep the focus here on the relevant issue: the (custom) number format code. Again, my apologies.
*** Bug 140052 has been marked as a duplicate of this bug. ***
*** Bug 144446 has been marked as a duplicate of this bug. ***
*** Bug 108338 has been marked as a duplicate of this bug. ***
Laurent Balland committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/74d9da037cac01c5abd768a99b2f948553fbf144 tdf#152724 Extend ODF for blank width "_x" It will be available in 24.2.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.