Download it now!
Bug 115349 - Conditional Formatting is completely lost on save to XLS format FILESAVE FILEOPEN FORMATTING
Summary: Conditional Formatting is completely lost on save to XLS format FILESAVE FILE...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected
Depends on:
Blocks: Conditional-Formatting XLS-Limitations
  Show dependency treegraph
 
Reported: 2018-01-31 20:41 UTC by C Hemingway
Modified: 2018-02-05 18:46 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet as ODS with Conditional Formatting (prior to save as XLS) (18.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-01 06:26 UTC, C Hemingway
Details
Spreadsheet as XLS; Conditional Formatting as beenlost (just used save-as to XLS) (133.00 KB, application/vnd.ms-excel)
2018-02-01 06:28 UTC, C Hemingway
Details
Spreadsheet as XLSX; Conditional Formatting partial corruption (ODS to XLSX) (38.89 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-02-01 06:34 UTC, C Hemingway
Details

Note You need to log in before you can comment on or make changes to this bug.
Description C Hemingway 2018-01-31 20:41:18 UTC
Description:
LibreOffice strips all conditional formatting from a document if saved into the XLS format.  For triage, this bug began occurring in LO 5, and does not occur in LO Portable

Steps to Reproduce:
1. Create a LO spreadsheet with some conditional formatting
   Format - Conditional Formatting - Manage
2. Save spreadsheet as XLS (test.xls)
3. Close window (Ctrl W) or Close application (Alt-F4)
4. Open spreadsheet (test.xls)
5. Go to Format - Conditional Formatting - Manage
6. -_- No conditional formatting

Actual Results:  
Document opens, no conditional formatting is executed
Opening conditional formatting has no elements in it.

Expected Results:
Document opens and executes conditional formatting.
Opening conditional formatting should conditional formatting elements in it.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Well, keep conditional formatting, for one :)

Cross platform bug.  Tested broken on Win 10 64-bit and Ubuntu 17.10 64 bit.
LibreOffice Portable 4.4.7.2 is the last known working version that produces CF in XLS documents.  I hope that helps you triage this bug.

OpenGL is NOT enabled


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:58.0) Gecko/20100101 Firefox/58.0
Comment 1 Aron Budea 2018-01-31 21:36:54 UTC
Can you please upload a sample ODT which loses conditional formatting when saved as XLS?
Comment 2 C Hemingway 2018-02-01 06:26:38 UTC
Created attachment 139487 [details]
Spreadsheet as ODS with Conditional Formatting (prior to save as XLS)
Comment 3 C Hemingway 2018-02-01 06:28:42 UTC
Created attachment 139488 [details]
Spreadsheet as XLS; Conditional Formatting as beenlost (just used save-as to XLS)
Comment 4 C Hemingway 2018-02-01 06:34:05 UTC
Created attachment 139489 [details]
Spreadsheet as XLSX; Conditional Formatting partial corruption (ODS to XLSX)

Also note!  There is a Bug in LO that - when a spreadsheet is saved to XLS/XLSX, the column format of the BOOLEAN VALUE column to custom format TRUE;TRUE;FALSE

As noted in my other bug, this breaks the ISLOGICAL function, which factors heavily in my conditional formatting functions.
Workaround - select all of Column S2 and convert back to BOOLEAN type.
Comment 5 Aron Budea 2018-02-02 02:22:10 UTC
Thanks for the sample. The conditions are quite complicated.
I wouldn't say it used to be perfect in 4.4, but until 5.3.0.3 some kind of conditional formatting was saved, and starting with 5.4.0.3 none of that is saved. At least that change could be bibisected.
Comment 6 C Hemingway 2018-02-02 15:06:57 UTC
Thank you for your prompt attention in this matter.  If you need any further testing, I'll be happy to do so for you.  Just let me know when the change is committed.
Comment 7 C Hemingway 2018-02-02 15:13:52 UTC
For reference, the files generated from LibreOffice Still Portable 4.4.7.2 with conditional formatting are being opened on Mac Office 2004 (maybe 2008).  That is why I have to use xls format instead of xlsx.  The xlsx format is opens, but is not editable according to the staff.  It may even be a PPC Mac.
Comment 8 Aron Budea 2018-02-05 17:49:35 UTC
Bibisected to the following commit using repo bibisect-linux-64-5.4. It appears to be a limitation of XLS format, see the following link:
https://msdn.microsoft.com/en-us/library/03AE6098-BDC2-475B-BA2C-B8AEF7882174

Closing as NOTABUG.
The solution is the reduce the conditional formatting applied to any range to 3 or less.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=7ceda09f6780c954fedc49764d5457aa2616b39a
author		Tor Lillqvist <tml@collabora.com>	2017-03-07 22:40:55 +0200
committer	Tor Lillqvist <tml@collabora.com>	2017-03-08 00:44:01 +0200

A 'CondFmt' record can have a maximum of three CF records following (eek)
Comment 9 Aron Budea 2018-02-05 18:46:02 UTC
The XLSX part could probably be worth a bug report, though.