Bug 156861 - FILEOPEN XLS, Function with logic answer gives back 1 or 0 in LO
Summary: FILEOPEN XLS, Function with logic answer gives back 1 or 0 in LO
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, filter:xls, regression
Depends on:
Blocks: XLS
  Show dependency treegraph
 
Reported: 2023-08-22 14:24 UTC by Justin L
Modified: 2024-06-01 01:26 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Justin L 2023-08-22 14:24:20 UTC
Function in xls with logic formula shows 0 or 1 in LO while in Excel it shows TRUE or FALSE. This is the XLS version of XLSX bug 122098 (broken from 4.2-7.3.2).

Steps to reproduce:
-Open attachment 185193 [details] (seriesexport.xlsx) from bug 153425. Notice true/false.
-export as XLS format and reload. notice 0/1.

History:
Starting in LO 4.2, importing the xlsx (or the round-tripped xls) showed general-number-formatted logic functions as 0/1 instead of true/false with
linux bibisect commit 8e7bade4e7314a340c77edd9042e230f61f0323d
    Bibisect: This commit covers the following source commit(s) which failed to build (at which point we have a total re-write of how cells store formula data)

https://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=c7bdee8dbd1cf260a8513a0d31b36f90daa70f1c..ec0080c40cfdb26896537f47a4c2e0439f9afd

However, the underlying "stuff" in xls export is unchanged because in older versions it still showed as true/false. In fact, 24.2's export to XLS looks fine in OOo 3.3 - 4.2.

FYI: In Excel 2010, the resulting XLS file opens and shows true/false. The formula in XLSX in Excel is =TRUE(), but in XLS in Excel the formula is =TRUE. LO shows the formula as =TRUE() [The difference is whether the '()' are used.]

Excel UI does not seem to have a "Boolean" format category like LO does - although the XLSX format DOES have a boolean specifier, so maybe XLS does too. Since LO only "displays" boolean when the formatting is boolean, at least import needs to be smarter about this.
Comment 1 Justin L 2023-08-22 16:15:14 UTC
for export, sc/source/filter/excel/xetable.cxx says:
        /*  Xcl doesn't know Boolean number formats, we write
            "TRUE";"FALSE" (language dependent). Don't do it for automatic
            formula formats, because Excel gets them right. */
Comment 2 ady 2023-08-22 20:28:27 UTC
FWIW...

LO does not really have a boolean or logical category of values; they are numbers really.

* Zero = FALSE; all other numbers equal TRUE.

* TRUE = 1 and FALSE = 0.

https://help.libreoffice.org/latest/en-US/text/scalc/01/04060105.html

MS Excel uses the TRUE() and FALSE() functions for compatibility with other tools. Excel does indeed have a boolean or logical category of values; they are not really numbers. Numbers are _usually_ "automatically" interpreted as needed, and logical/boolean values are _usually_ "automatically" interpreted as numbers when needed. Multiplying by 1 usually forces the logical value into a number (0 or 1, respectively). In some rare cases, the N() function might be needed to force the convertion.

https://support.microsoft.com/en-gb/office/true-function-7652c6e3-8987-48d0-97cd-ef223246b3fb
Comment 3 ady 2023-08-22 20:30:19 UTC
(In reply to ady from comment #2)
> might be needed to force the convertion.

s/convertion/conversion
Comment 4 Justin L 2024-05-27 18:26:19 UTC
repro 24.8+