Bug 139163 - FILESAVE XLSX Conditional formatting give error by opening in excel
Summary: FILESAVE XLSX Conditional formatting give error by opening in excel
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: XLSX-Conditional-Formatting
  Show dependency treegraph
 
Reported: 2020-12-22 16:35 UTC by VLB
Modified: 2021-01-07 08:34 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
sheet with conditinal formatting (51.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-22 16:35 UTC, VLB
Details
sheet without conditional formatting (47.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-22 16:36 UTC, VLB
Details
sheet with redused conditional formatting (47.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-23 22:23 UTC, VLB
Details
Example file created from scratch to demonstrate the problem (13.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-01-07 08:25 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description VLB 2020-12-22 16:35:16 UTC
Created attachment 168418 [details]
sheet with conditinal formatting

Test in LO 7.0.4.2 wi (x64)

When I have a sheet with multiple conditional formatting and save it in excel 2013 as *.xlsx and then open in excel 2013, it gives an error.
When removing all conditional formatting and saving again as *.xlsx, the file will still open without an error message.
Comment 1 VLB 2020-12-22 16:36:17 UTC
Created attachment 168419 [details]
sheet without conditional formatting
Comment 2 VLB 2020-12-22 18:55:53 UTC
(In reply to VLB from comment #0)

> When I have a sheet with multiple conditional formatting and save it in
> excel 2013 as *.xlsx and then open in excel 2013, it gives an error.
> When removing all conditional formatting and saving again as *.xlsx, the
> file will still open without an error message.

The sheet is saved in LO and when opening in excel 2013 give the error.
Comment 3 VLB 2020-12-22 18:59:11 UTC
Step to reproduce;

1) open file "sheet with conditional formatting" in LO
2) save as XSLX and open in excel and there are error present
Comment 4 Xisco Faulí 2020-12-23 09:43:11 UTC
Reproduced in

Version: 7.2.0.0.alpha0+
Build ID: 6156a419a2d3f09d91afd00f84c84ba717442f43
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 5 Xisco Faulí 2020-12-23 09:44:43 UTC
Also reproduced in

Version: 5.2.0.0.alpha0+
Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53
Threads 4; Ver: 5.7; Render: default; 

Locale: en-US (en_US.UTF-8)
Comment 6 Xisco Faulí 2020-12-23 09:45:39 UTC
and

Version: 4.3.0.0.alpha1+
Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e
Comment 7 VLB 2020-12-23 22:23:09 UTC
Created attachment 168460 [details]
sheet with redused conditional formatting

I checked the conditions and there were 2 conditions that referred to a text.
This formula did not have to be stated med/mv;rd>1 , but "med/mv;rd>1". This is an incorrect condition that must be stated with quotation marks, of which no message is given and the error message is only mentioned when opened in Excel.
Comment 8 VLB 2020-12-24 14:47:15 UTC
The problem also occurs with wrong ranges, ranges and names.
Comment 9 NISZ LibreOffice Team 2021-01-07 08:25:24 UTC
Created attachment 168736 [details]
Example file created from scratch to demonstrate the problem
Comment 10 NISZ LibreOffice Team 2021-01-07 08:34:37 UTC
(In reply to NISZ LibreOffice Team from comment #9)
> Created attachment 168736 [details]
> Example file created from scratch to demonstrate the problem

Oops, too soon.

This file shows that a conditional formatting First/Second;Third>1 in B3 is valid conditional format in ODS without quotes, if First/Second/Third are valid named ranges (pointing to A1/A2/A3).

Also fff/sss;ttt>1 in B5 is valid in ODS without quotes when fff/sss/ttt are not valid named ranges.

Saving this file to XLSX will generate invalid contents detected error in Excel upon opening.

Putting these conditions into quotes as shown in B4 and B6 does not generate invalid contents in XLSX.

B1 and B2 shows that the two parts of the condition "First/Second;Third>1" are valid without quotes in XLSX too, only connecting them with the semicolon makes it necessary to quote them.

D3 shows that using direct cell references instead of named ranges like $Munkalap1.$A$1/$Munkalap1.$A$2;$Munkalap1.$A$3>1 
is also valid without quotes when saved in XLSX.