Bug 156861

Summary:	FILEOPEN XLS, Function with logic answer gives back 1 or 0 in LO
Product:	LibreOffice	Reporter:	Justin L <jluth>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	normal	CC:	aron.budea, jluth
Priority:	medium	Keywords:	bibisected, bisected, filter:xls, regression
Version:	4.2 all versions
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=122098
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	109072

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+