Bug 106734 - Incorrect colors imported from XLS with conditional formatting (XLSX is okay)
Summary: Incorrect colors imported from XLS with conditional formatting (XLSX is okay)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Whiteboard: interoperability
Keywords: filter:xls
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2017-03-24 01:58 UTC by Aron Budea
Modified: 2019-09-02 18:19 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

XLS sample (27.50 KB, application/vnd.ms-excel)
2017-03-24 01:58 UTC, Aron Budea
XLSX sample (8.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-24 01:59 UTC, Aron Budea
Comparison of colors between Excel and Calc (XLS) (97.60 KB, image/png)
2017-03-24 02:02 UTC, Aron Budea

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2017-03-24 01:58:16 UTC
Created attachment 132111 [details]
XLS sample

The attached XLS contains conditionally formatted cells with the predefined color schemes in Excel 2013. (Light Red Fill with Dark Red Text etc.)

A lot of those colors are imported incorrectly, especially Dark Red, which is imported as purple. The rest are only slightly different. The same colors look okay when imported from XLSX.

Color codes from Excel (text and background):
A2: 156 / 0 / 6		255 / 199 / 206
B2: 156 / 101 / 0	255 / 235 / 156
C2: 0 / 97 / 0		198 / 239 / 206
D2: Automatic		255 / 199 / 206
E2: 156 / 0 / 6		No fill
F2: Automatic		No fill		border 156 / 0 / 6

Color codes/names from Calc (text and background):
A2: #800080		Pink 10
B2: #993300		Yellow
C2: Green 5		Green 10
D2: Black		Pink 10
E2: #800080		No fill
F2: Black		No fill		border #800080

Reproduced with v5.3.1.2 & v3.3.0 / Windows 7.
Comment 1 Aron Budea 2017-03-24 01:59:14 UTC
Created attachment 132112 [details]
XLSX sample
Comment 2 Aron Budea 2017-03-24 02:02:23 UTC
Created attachment 132113 [details]
Comparison of colors between Excel and Calc (XLS)
Comment 3 Xisco Faulí 2017-03-24 10:12:38 UTC
Confirmed in

Build ID: 1670cc25bc2771e87f7956a4b0dd634abaa4128b
CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group
Comment 4 Markus Mohrhard 2017-04-09 23:44:18 UTC
Seems to be related to missing CFEx and similar elements and structs in the xls import and export filter.

I'm not sure if we want to implement these records. It looks like a huge mess and my documentation effort in oletoy shows that you most likely need some reverse engineering to implement them.
Comment 5 tom1williams 2017-08-14 16:16:59 UTC
I have a similar, maybe more serious problem. In my job I must add data to a corporate XLSX file with color related conditional formatting. Every time I saved that file as XLSX in any version of Libre Calc prior to 5.4, all of the colors and conditional formatting were lost.

My work around was/is awful. I have to download the file on my Linux system, yank out the cat5 cable, reboot into an unpatched copy of Windows, use an unpatched copy of MS Office to enter the data, reboot into Linux, re-insert the cat5 cable, upload the file, and repeat this process at least monthly.

In my daily work all other XLSX files have no issues, but then they have no colors related conditional formatting. Recently I upgraded to Libre Office 5.4. Then I accidentally edited and saved that one special corporate XLSX file. I was shocked to find that the colors related conditional formatting seems to be intact!

Could someone please confirm that there was a fix made to saving colors related conditional formatting in XLSX files between 5.3 and 5.4? If confirmed this may be my big chance to get rid of the old MS software.
Comment 6 Aron Budea 2017-08-16 02:13:19 UTC
Your issue is certainly unrelated to this one.

Why don't you conduct further tests? No one will be able to tell you what kind of special bug your spreadsheet had, and how saving got fixed without looking at it. You could try creating a minimal sample for your tests, and work with that.
Comment 7 QA Administrators 2018-08-17 02:38:14 UTC Comment hidden (obsolete)
Comment 8 Aron Budea 2018-08-19 05:00:24 UTC
Still repro in 6.2 master build (8af4ea707e7ed63e65d75fd63cca02e018e4e16a) / Windows 7.
Comment 9 QA Administrators 2019-09-02 09:20:41 UTC Comment hidden (obsolete)
Comment 10 Aron Budea 2019-09-02 18:19:43 UTC
Still the same in LO (a40fbd031de042b0181dc5570164ae8ce0abb0f1) / Ubuntu 19.04.