Bug Hunting Session
Bug 81350 - FILEOPEN: XLSX file: boolean values imported wrongly, consecutive values hidden
Summary: FILEOPEN: XLSX file: boolean values imported wrongly, consecutive values hidden
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2014-07-14 14:15 UTC by IagoSRL
Modified: 2019-02-25 21:39 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Boolean values created on Excel 2007 (8.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-14 14:15 UTC, IagoSRL
Details
Boolean values created on Excel compatible app (25.30 KB, application/vnd.openxmlformats)
2014-07-14 14:16 UTC, IagoSRL
Details
Boolean values created on LibreOffice 4.2.5.2 as ODS (35.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-14 14:17 UTC, IagoSRL
Details
Boolean values created on LibreOffice 4.2.5.2 exported as XLSX (4.85 KB, application/vnd.openxmlformats)
2014-07-14 14:17 UTC, IagoSRL
Details

Note You need to log in before you can comment on or make changes to this bug.
Description IagoSRL 2014-07-14 14:15:52 UTC
Created attachment 102774 [details]
Boolean values created on Excel 2007

An XLSX file with well formatted boolean values is showed as numbers 1 or 0 rather than special words TRUE or FALSE and boolean formatting, and any consecutive values (sibling rows) are not showed except when the value is different from previous row.

Example (extra padding added for readability):
one column and several rows with values:
[ TRUE   , TRUE   , FALSE   , FALSE   , FALSE   , FALSE   , TRUE   , TRUE   , TRUE   , TRUE    ]
are showed by LibreOffice as:
[ 1      ,        , 0       ,         ,         ,         , 1      ,        ,        ,         ]
and clicking on every cell, they shows values:
[ =TRUE(), =TRUE(), =FALSE(), =FALSE(), =FALSE(), =FALSE(), =TRUE(), =TRUE(), =TRUE(), =TRUE() ]

In the given attachments, click on every cell, specially the empty one, to see that there is a value but nothing showed on cells when previous showed value is the same.

I upload several attachments as examples:
- File created from scratch with Excel 2007
- File created from by a third party app that exports a datatable as XLSX, basic type formatting only; I changed the column name with Excel2007, but using the source files gives the same results and the same internal XML (I check it the unzipped content before and after the column name).
- File created with LibreOffice 4.2.5.2 and saved as ODS, everything fine in this case even reopening the file.
- File created with LibreOffice 4.2.5.2 and saved as XLSX; when you close and open the file with LibreOffice, the problem appear.
Comment 1 IagoSRL 2014-07-14 14:16:34 UTC
Created attachment 102775 [details]
Boolean values created on Excel compatible app
Comment 2 IagoSRL 2014-07-14 14:17:11 UTC
Created attachment 102776 [details]
Boolean values created on LibreOffice 4.2.5.2 as ODS
Comment 3 IagoSRL 2014-07-14 14:17:38 UTC
Created attachment 102777 [details]
Boolean values created on LibreOffice 4.2.5.2 exported as XLSX
Comment 4 ign_christian 2014-07-19 10:18:13 UTC
(In reply to comment #0)
> one column and several rows with values:
> [ TRUE   , TRUE   , FALSE   , FALSE   , FALSE   , FALSE   , TRUE   , TRUE  
> , TRUE   , TRUE    ]
Confirmed with:
4.1.6.2 (Format Cells: BooleanValue|TRUE)
4.0.6.2 (Format Cells: Number|Standard)

> are showed by LibreOffice as:
> [ 1      ,        , 0       ,         ,         ,         , 1      ,       
> ,        ,         ]
Confirmed with: LO 4.3.0.3, 4.2.6.1, 4.2.0.0.beta1 (Format Cells: Number|Standard)
Set Version to 4.2.0.0.beta1: the first version which wrong viewed cells appear

> and clicking on every cell, they shows values:
> [ =TRUE(), =TRUE(), =FALSE(), =FALSE(), =FALSE(), =FALSE(), =TRUE(),
> =TRUE(), =TRUE(), =TRUE() ]
Those shown in all versions tested. I don't set regression since I don't know what is the correct format setting.
 
> - File created with LibreOffice 4.2.5.2 and saved as XLSX; when you close
> and open the file with LibreOffice, the problem appear.
Filesave problem with xlsx : Bug 68117

Tested on Ubuntu 12.04 32bit
Comment 5 tommy27 2016-04-16 07:26:15 UTC Comment hidden (obsolete)
Comment 6 Bartosz 2016-07-06 14:48:17 UTC
I think this bug is highly related to:
https://bugs.documentfoundation.org/show_bug.cgi?id=70565

Could you please describe (step by step), how are you creating .xlsx file via MS Excel?

It will dramatically simplify testing process.
Comment 7 Xisco Faulí 2016-09-10 22:32:51 UTC
Hi,
Please, next time you assign a bug to yourself, remember to change the status and the assignee.
Regards
Comment 8 IagoSRL 2017-08-27 10:49:14 UTC
Hi,

Sorry to don't answer to previous requests of re-testing.

As seen at the related bug #86456, the problem of 'hidden values' at consecutive rows with same value seems solved at version 5.4.0.3 (x64) Windows 7 SP1. From about me dialog:
Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c
CPU threads: 4; SO: Windows 6.1; UI render: default; 
Locale: es-ES (es_ES); Calc: group.

I tested both the files I originally attached and the two files attached at #86456, and just like eisa01 commented there 'shows 0/1 with =FALSE()/TRUE(), so this may just be a presentation issue now?'

Current fix is a good step forward, though.

And yes, the bug #86456 and this older seems the same.
But about the bug https://bugs.documentfoundation.org/show_bug.cgi?id=70565 , don't know exactly what has fixed, but it seems a formatting bug still exist here.

Steps to reproduce:
- At Excel 2007
- New document, at different rows type values 'TRUE' and 'FALSE'.
- Save as XLSX
- Open In LibreOffice
- It displays 1, 0
- Note: the formula bar at LibreOffice says =TRUE() , =FALSE() while at Excel still says TRUE , FALSE.
- Additionally: if at Excel we type '=TRUE' and '=FALSE' (adding the equals sign), it displays the same both at Excel and LibreOffice, but LibreOffice shows at the formula bar =1 , =0 on this case.
Comment 9 QA Administrators 2018-08-28 02:42:25 UTC Comment hidden (obsolete)
Comment 10 IagoSRL 2018-08-28 10:36:16 UTC
Hi,

I can confirm this still happens as of today latest version in same way as last update at Comment #8.

Versión: 6.1.0.3 (x64)
Id. de compilación: efb621ed25068d70781dc026f7e9c5187a4decd1
Subprocs. CPU: 4; SO: Windows 10.0; Repres. IU: predet.; 
Configuración regional: es-ES (es_ES); Calc: CL
Changelog: https://gerrit.libreoffice.org/gitweb?p=core.git&a=log&h=efb621ed25068d70781dc026f7e9c5187a4decd1


In addition, I just verified that the values showed at the formula bar are the same in the XLSX file created with Excel 2007 (first attachment) and LibreOffice (fourth attachment), but the formatting is different:
- For the one created with Excel: Libreoffice displays "1, 1, 0, 0, 0..."
- For the one created with LO: Libreoffice displays "TRUE, TRUE, TRUE, FALSE, FALSE,..."

I reviewed the cell formatting options, and they have:
- For the one created with Excel: Standard number formatting
- For the one created with LO: User defined formatting, with format "TRUE";"TRUE";"FALSE"

It seems weird that no file have chosen the 'boolean formatting' option. If I apply that manually to both file, the display is the correct one, identical on both.

What I cannot confirm now is if the XLSX format, or that Excel 2007 version, supports the 'boolean bormatting', in case that happens and is the issue (and the reason why LO chose to save the file with a custom formatting). If that's the case, maybe have changed in recent format/program versions, someone knows?
Comment 11 Timur 2018-12-19 17:33:39 UTC
*** Bug 120664 has been marked as a duplicate of this bug. ***