Description: Function in xlsx with logic answer gives 0 or 1 in LO while in MSO it gives TRUE or FALSE this can lead to differences when using conditional formatting. Steps to Reproduce: 1. Create a new xlsx in Excel. 2. Create a function which gives back TRUE or FALSE. 3. Save it and open it in LO. 4. Notice the difference. Actual Results: The function gives back 1 or 0. Expected Results: It should give back TRUE OR FALSE just as it does in MSO. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 147531 [details] The original document.
Created attachment 147532 [details] Screenshot of the original document side by side in Excel and Calc.
confirm in Version: 6.3.0.0.alpha0+ Build ID: 3c964980da07892a02d5ac721d80558c459532d0 CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86@42, Branch:master, Time: 2018-12-12_02:07:45 Locale: ru-RU (ru_RU); UI-Language: en-US Calc: threaded from scratch for ODS Calc shows in cells True or False
Also reproduced in Version: 5.2.0.0.alpha0+ Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53 Threads 4; Ver: 4.15; Render: default; but not in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
I still haven't checked if the following lines are the culprit, but I suspect so. https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/sheetdatacontext.cxx?r=76fbe494#119 https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/sheetdatacontext.cxx?r=76fbe494#145 Logicfunction/xl/worksheets/sheet1.xml <sheetData> <row r="1" spans="1:2" x14ac:dyDescent="0.25"> <c r="A1" t="b"> <f>EXACT(B1,C1)</f> <v>1</v> </c> </row> <row r="2" spans="1:2" x14ac:dyDescent="0.25"> <c r="A2" t="b"> <f>EXACT(B2,C2)</f> <v>0</v> </c> <c r="B2" t="s"> <v>0</v> </c> </row> </sheetData>
This seems to have begun at the below commit. Adding Cc: to Kohei Yoshida ; Could you possibly take a look at this one? Thanks b24c5c64280698417f54375715aa07370f0d47d0 is the first bad commit commit b24c5c64280698417f54375715aa07370f0d47d0 Author: Matthew Francis <mjay.francis@gmail.com> Date: Sat Sep 5 22:39:37 2015 +0800 source-hash-07b66cd3ac1a9f6c7b61a1d7da6e9d266e6de92d commit 07b66cd3ac1a9f6c7b61a1d7da6e9d266e6de92d Author: Kohei Yoshida <kohei.yoshida@collabora.com> AuthorDate: Thu Oct 31 17:53:02 2013 -0400 Commit: Kohei Yoshida <kohei.yoshida@collabora.com> CommitDate: Mon Nov 4 13:59:17 2013 -0500 Insert matrix formula vis ScDocumentImport, and more formula imorts. Now SetGroupFormulaCell() is no longer used. Change-Id: I10a387da04724794974eaf491a8efa4cda09d82a
Dear NISZ LibreOffice Team, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Still a problem in: Version: 7.2.0.0.alpha0+ (x64) Build ID: e97a81e94511b52987a50b7bdb72c922899da588 CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: en-US Calc: CL
repro 7.4+ and confirmed the identified commit in comment 6 with bibisect-42max. This is in some way just a cell format issue. Prior to the regression, this was formatted as a Boolean Value, but afterwards as a Number-General. There is some kind of automatic formatting going on here though, because we don't have any formatting defined in the xlsx. Specifically, there is no <numFmts count="2"> <numFmt numFmtId="164" formatCode="GENERAL"/> <numFmt numFmtId="165" formatCode=""TRUE";"TRUE";"FALSE""/> </numFmts> and the cell references style s=0 (which seems to be a special nothing-default?) Interestingly, if I add a new function [=Exact(b6,c6)], then it automatically uses a boolean value that appears to be directly formatted (because clear direct formatting results in General format - aka "1"). However, on a round-trip that formatting is lost. But if I explicitly set the cell as Boolean Value, then it is retained. Exporting this broke in LO 4.3, with author Kohei Yoshida on 2014-03-11 19:09:41 commit 69ecdad805281b2cb6ec2437da18daa19576deae fdo#74747: Correctly inspect formula result value for xlsx export. ------------------------------------------------------------------------------- INTERESTING: if I save as ODS, then (even though it originally only showed 1,0) it imports as true/false. In fact the opposite is true. Clearing the direct format settings in A1 shows "1", but on a round-trip it again shows TRUE. This comes from sc/source/filter/xml/xmlcelli.cxx SetFormulaCell() if (bMayForceNumberformat) // Re-calculate to get number format only when style is not set. pFCell->SetNeedNumberFormat(!mbHasStyle);
Yet another thing I broke, among many. :-P
Justin Luth committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8f5d8669ca74fed8608e438a8436c173e35d43a6 tdf#122098 xlsx im/export: let formulas guess their number format It will be available in 7.4.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.
*** Bug 127822 has been marked as a duplicate of this bug. ***
Justin Luth committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8f2b1b1cb84e1ae3139eb90b8efdf61e608adbad followup tdf#122098 xlsx export: avoid dataloss if not bool value It will be available in 7.4.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.
Justin Luth committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5a64b618d84edb65465950d54e3b4afc952a301c followup tdf#122098 xlsx export: avoid corrupt format if not bool value It will be available in 7.4.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.
Justin Luth committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/e4f76cd9a4814ce6cc4795e5e1a5c74d54cb7513 tdf#122098 xlsx im/export: let formulas guess their number format It will be available in 7.3.2. 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.