Bug 99917 - EDITING: save file as XLSX with wrong conditional formatting give error in excel
Summary: EDITING: save file as XLSX with wrong conditional formatting give error in excel
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: XLSX-Corrupted XLSX-Conditional-Formatting
  Show dependency tree / graph
 
Reported: 2016-05-17 18:27 UTC by VLB
Modified: 2018-07-14 21:58 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
test file (7.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-17 18:27 UTC, VLB 		Details
save file as XSLX LO 5.2.0 with error (4.72 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-22 08:43 UTC, VLB 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description VLB 2016-05-17 18:27:39 UTC 
Created attachment 125118 [details]
test file

wi 10 64 bits
Versie: 5.2.0.0.alpha1  (x64)
Build ID: 902b28a39528b6c92602e9b521a1d0861be1caf9
CPU Threads: 8; Versie besturingssysteem:Windows 6.19; UI Render: standaard; 
Locale: nl-NL (nl_NL)

In the test file there is wrong conditional formatting for cell A1:
Condition 1: Cell value is - equal to - text

The cell value is a text and must with a string -> "Text"

so the right Condition 1 must:
Cell value is - equal to - "Text"

In LO there is no mention and you can save as ods.

When you save the file as *.xslx and open the file in excel there is a error present.
Comment 1 Eike Rathke 2016-05-17 19:31:25 UTC 
Giving text without quotes could be a cell named 'text' (or a column or row name, which LO actually points out when entering text without quotes) which Excel also allows. The actual difference (i.e. one of the additional attributes of the cfRule element we write) that causes Excel to complain must be something else. Btw, tested with Excel2010.
Comment 2 Eike Rathke 2016-05-19 17:11:04 UTC 
@moggi: an idea about this one?
Comment 3 m_a_riosv 2016-05-19 21:56:41 UTC 
*** Bug 99947 has been marked as a duplicate of this bug. ***
Comment 4 Markus Mohrhard 2016-05-21 02:09:45 UTC 
Might be the same problem as Bug 99866.

I can't reproduce the error here when I export the file myself. Excel imports the file correctly and also treats it as a label/named range that is not defined.

I currently only have master builds so it would be nice if someone can check if that still affects current master.
Comment 5 VLB 2016-05-21 12:16:01 UTC 
I have test in
Version: 5.2.0.0.alpha1+
Build ID: 7b704dfbdb23540ff6366fa60c73474bbda9dc26
CPU Threads: 8; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-05-20_05:45:40
Locale: nl-NL (nl_NL)

and there isn't a error message.

Now i test again in 5.1.3 and now there isn't a message...
Comment 6 VLB 2016-05-22 08:31:33 UTC 
I think it is the same bug as 99866 when i do it with the steps in comment 6 there is a error present.
Comment 7 VLB 2016-05-22 08:43:59 UTC 
Created attachment 125222 [details]
save file as XSLX LO 5.2.0 with error
Comment 8 Markus Mohrhard 2017-03-24 01:19:45 UTC 
(In reply to vlb from comment #7)
> Created attachment 125222 [details]
> save file as XSLX LO 5.2.0 with error

I'm still unable to reproduce any problems on the Excel side after saving this file in LibreOffice. The file itself looks ok except for the column/row label which seems to be just not imported by Excel at all.

Basically my advice is not to use column/row labels in conditional format formulas. They are most likely not portable across all formats. (same might apply for range names).
Comment 9 VLB 2017-12-20 13:31:17 UTC 
I have test in the latest LO 5.4.3.2/6.0.0.0beta2 (x64)and there isn't still a error present. I think it is solved.