Description: Everything OK in a spreadheet under LibreOffice Calc 5.1 When opening in 5.3, I get "Erreur : type de données incorrect" (Wrong data type, I suppose, in English). The cell is the sum of three others showing no error. The error disappears if the other cells are filled. The error does not reappear if the content of the other cells is deleted. It seems that 5.3 does not consider the terms of the sum as numbers when opening the file. Steps to Reproduce: 1. Open a sample of the template http://www.d-meeus.be/verkelec/municipal/Zetelverdel-provincie.ots in 5.1 2. Open a sample of the template http://www.d-meeus.be/verkelec/municipal/Zetelverdel-provincie.ots in 5.3 Actual Results: In 5.1, no errors in D6 to D25 In 5.3, wrong data type in D6 to D25 Expected Results: 5.3 should be compatible with 5.1 Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:55.0) Gecko/20100101 Firefox/55.0
Regression introduced by: author Eike Rathke <erack@redhat.com> 2016-11-16 17:23:25 (GMT) committer Eike Rathke <erack@redhat.com> 2016-11-16 17:24:00 (GMT) commit 83cbbc6d664d949f6405409713c5bda1a5be559f (patch) tree d7d06317429e5b22c14ebe7d7522d6afcc174a90 parent ed5a8df72ac2d14aa2f5d1f87543fcfff9ad9d7d (diff) tdf#96475 restore the EmptyDisplayedAsString condition during load So also "empty" result cells with a number format applied are displayed empty, instead of 1899-12-30 for example. Bisected with: bibisect-linux-64-5.3 Adding Cc: to Eike Rathke
Hi, I did find three sheet with formatting errors. The cells A4 to T4 did have a #.### format and that caused the #value error in the results sheet. I changed the format to #.##0 and the error is gone. I am not sure but for me its not a bug, just a wrong formatting. Best regards
I am not convinced. A. To LO Calc 5.1, # ### (without zero) is considered valid number format (indeed a choice proposed in the number format dialog). Why call this wrong format? (I choose # ### because I do not like a sheet full of zeros in unused cells.) B. If (in 5.3) I change to # ###0 in cols D, G, J, M of the main sheet and in line 4 of the other sheets (Xavier’s suggestion), the error remains. In fact the error depends on line 8 in the other sheets, but changing the format or these does not do any better. C. What does make a difference is introducing the number 0 (instead of an empty cell formatted as number) in line 4 of the other sheets, even while keeping # ### as format. D. Very strange: if you delete these 0s, the error does not reappears. For 5.3, an empty number cell is not allowed but an emptied number cell is! To reproduce: open a fresh sample from the template in 5.3. Select the three sheets District-something. Enter 0 in each of A4 to T4. (Because of # ###, this 0 is invisible.) The error in the first sheet disappears. Conclusion: LO Calc 5.1 accepts an empty cell formatted as number as a valid number. The behaviour of 5.3 is different. Follow up: I have only 5.3 here. Tomorrow, as a workaround, I will put the 0s in the template under 5.1 and in the evening see the result opening in 5.3. — And keep you informed.
Thanks for your comments. I did something else, deleted two latest sheets, copied District A and renamed them to B and C, needed the reenter the formula's I did not changed the formatting. Most #value errors are gone. The only thing i then have seen is that in U1 is when the number is to high there is a #value error. I tested with LO 5.4.3. I hope i can find a older release of LO to test it Best regards
Created attachment 136668 [details] test zetelverdeling
Loading the attached test case document in 5.3.6 I don't get any #VALUE! error.
Ah that's not the original document in question..
Created attachment 136684 [details] the actual test case document
Investigating.
Fwiw, the error disappears once the document is recalculated, use Shift+Ctrl+F9 once.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=df778416981ab02d42182e5c2e46dc09ba2e2a3c Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString() It will be available in 6.0.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.
Pending review https://gerrit.libreoffice.org/43047 for 5-4 https://gerrit.libreoffice.org/43048 for 5-3
Indeed, contrarily to what I hoped for, as a workaround, introducing a numerical value zero in the cells waiting for data from the user does not solve the problem. Recalculate by Shift+Ctrl+F9 does work. I will suggest this to the potential users on my website, if they happen to have a wrong version of LibreOffice.
Confirm fixed no errors with Version: 6.0.0.0.alpha0+ Build ID: 9572e3254bf62fb658a09577e64dc591b3a06954 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8d9b236dcb3fd0f7028e4d19ede04589cf85d760&h=libreoffice-5-3 Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString() It will be available in 5.3.7. 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 "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=714ef78b8dd60020a70d2ffc364d737503c49992&h=libreoffice-5-4 Resolves: tdf#112780 no ResetDirty() after SetHybridEmptyDisplayedAsString() It will be available in 5.4.3. 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.
Confirm fixed no errors with Version: 5.4.4.0.0+ Build ID: b84cd7fb7610b0ad2c74c5a672bccc77b91a82f5 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Version: 5.3.8.0.0+ Build ID: a0fae00a2d52960eebbb14f08d2de251e0a8ff3f CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-10-05_05:58:12 Locale: nl-BE (en_US.UTF-8); Calc: group
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ca28c94bde551a07a2b61bf91b7a55c93497d451 tdf#112780: sc_subsequent_filters: Add unittest It will be available in 7.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.