Description: Boolean cells in an XLSX spreadsheet which display as expected in Excel (as "TRUE" when true and as "FALSE" when false) do not display as expected in LibreOffice Calc. In Calc, booleans are displayed as "FALSE". They are never "TRUE". Here is an XLSX where I have observed this problem in LibreOffice Calc: http://files.pineapplemachine.com/public/logs/2023-04-27-libreoffice-boolean-bug.xlsx Here is a screenshot of what I see when opening this XLSX file in LibreOffice Calc (Note that all booleans are "FALSE"): http://files.pineapplemachine.com/public/logs/2023-04-27-libreoffice-boolean-bug-calc.png Here is a screenshot that a colleague provided when opening the same file in Microsoft Excel (Note that booleans are variously "TRUE" or "FALSE"): http://files.pineapplemachine.com/public/logs/2023-04-27-libreoffice-boolean-bug-excel.png --- Sometimes, unpredictably, booleans display as "0". Prior to reporting this bug, I observed that booleans in columns A-L of the linked spreadsheet displayed as "FALSE" whereas booleans in columns N-Q displayed as "0". Reviewing this behavior again to create the bug report, I observed that all of them are "FALSE" and none of them are "0". However, I did uninstall and reinstall LibreOffice Calc a few times in the course of troubleshooting, and so it is possible this behavior differs between recent 7.5.2 builds. Please specifically note that this behavior is regardless of the actual value of the boolean. In Calc, booleans are always being displayed as "FALSE" or "0", even for true values. They are never "TRUE" or "1". --- Keywords: FORMATTING, VIEWING Steps to Reproduce: 1. Obtain an XLSX spreadsheet containing booleans, such as: http://files.pineapplemachine.com/public/logs/2023-04-27-libreoffice-boolean-bug.xlsx 2. Open the spreadsheet in Excel to observe how booleans are displayed (Or refer to the screenshot linked in the description) 3. Open the spreadsheet in LibreOffice Calc to observe how booleans are displayed differently from Excel (Or refer to the screenshot linked in the description) Actual Results: Boolean cells show "FALSE", or sometimes "0", regardless of whether the boolean value was true or false. Expected Results: Boolean cells with a true value should show "TRUE" and boolean cells with a false value should show "FALSE". Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.5.2.2 (X86_64) / LibreOffice Community Build ID: 50(Build:2) CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: en-US (en_US.UTF-8); UI: en-US Ubuntu package version: 4:7.5.2~rc2-0ubuntu0.22.04.1~lo1 Calc: threaded Kubuntu 22.04
Created attachment 186958 [details] XLSX spreadsheet containing boolean cells. Displays correctly in Excel and incorrectly in LibreOffice Calc.
Created attachment 186959 [details] Screenshot of attached XLSX as viewed in Microsoft Excel. Note that booleans are variously "TRUE" or "FALSE".
Created attachment 186960 [details] Screenshot of attached XLSX as viewed in LibreOffice Calc. Note that all booleans are "FALSE", and none are "TRUE".
The document generator (whatever it might be, which is it?) is odd.. it writes the literal true and false strings as cell values instead of numeric 1 and 0, which Excel does. Like <c r="C3" s="0" t="b"><v>true</v></c> where taking the numeric value from string "true" results in 0 of course. I'm not sure this even complies with the OOXML "standard". Would need a special treatment. Funny enough, loading that document in Gnumeric results in all TRUE values instead of FALSE values.. probably because anything not 0 is true there. Apart from that, the generator doesn't write a <?xml version="1.0" encoding="UTF-8" ...?> to the xl/worksheets/sheet*.xml streams either, so some XML tools might treat them as text/plain content unless told different.
> The document generator (whatever it might be, which is it?) is odd.. The XLSX document linked and attached in the report was generated using the excel4node NPM package: https://www.npmjs.com/package/excel4node The software that generated this document currently depends on excel4node@1.7.2, but I can see in the repository that latest excel4node@1.8.1 appears to have the same issue, where a true or false boolean value is inserted as the text "true" or "false": https://github.com/advisr-io/excel4node/blob/main/source/lib/cell/cell.js#L74 I'll submit a PR to that repository to improve this behavior for excel4node. However, it would still be helpful for me if Calc's behavior was like Excel's behavior for this kind of nonstandard input.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e59fdc8b800aa3ab551b3f4fec4bc58366df582e Resolves: tdf#155046 Accept true and false as 1 and 0 for OOXML boolean cells 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.
Pending review https://gerrit.libreoffice.org/c/core/+/151119 for 7-5
Verified. Everything like in Excel image. Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: b88d11ba05085002cf847d4828ded52a3dfb3b09 CPU threads: 16; OS: Linux 5.19; UI render: default; VCL: gtk3 Locale: en-US (ro_RO.UTF-8); UI: en-US Calc: threaded
Thank you!
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/3e7702774b7309e8fa07930d2960e4e619bfb291 Resolves: tdf#155046 Accept true and false as 1 and 0 for OOXML boolean cells It will be available in 7.5.4. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0e55c5dd7b72c7da54a38ea4dcfc17069f311b0f tdf#155046: sc_subsequent_filters_test4: Add unittest 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.