Bug 117816 - FILEOPEN Cells with wrong colors in .xlsx exported document/ conditional formatting Cell value Contains "string"
Summary: FILEOPEN Cells with wrong colors in .xlsx exported document/ conditional form...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:6.2.0 target:6.1.0.2
Keywords: filter:xlsx
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2018-05-26 11:16 UTC by Christian
Modified: 2018-07-16 16:24 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Testcase (7.55 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-05-26 11:17 UTC, Christian
Details
Testcase in ods format (10.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-26 11:17 UTC, Christian
Details
Rendering in LO 6 (16.80 KB, image/png)
2018-05-26 11:17 UTC, Christian
Details
Rendering in MS-Excel (16.95 KB, image/png)
2018-05-26 11:18 UTC, Christian
Details
untouched MS Office doc (9.44 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-05-26 11:28 UTC, Christian
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Christian 2018-05-26 11:16:37 UTC
Description:
In the LO ods document certain Cells are color coded. The background color set by a pulldown choice, inherited from a second sheet.

The document was created in MS Excel originally.
When the dosument is now exported to xlsx the color codeing does not show up in MS-Excel
Openeing the .xlsx document in LO shows the expected colors though.

So the formating is not lost but not encoded in a way MS-Excel understands.

Priority
  This is pretty critical as it prevents collaborations with MS-Office users on the same documents.


Steps to Reproduce:
1. Open the provided .xlsx file in MS-Excel
2. Compare with the rendering in LO
3.

Actual Results:  
All Cell backgrounds show up in white in MS-Excel

Expected Results:
The background colors of all cells should match the colors as they show up in LO.


Reproducible: Always


User Profile Reset: No



Additional Info:
See also the screenshots provided.


User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.181 Safari/537.36
Comment 1 Christian 2018-05-26 11:17:05 UTC
Created attachment 142290 [details]
Testcase
Comment 2 Christian 2018-05-26 11:17:29 UTC
Created attachment 142291 [details]
Testcase in ods format
Comment 3 Christian 2018-05-26 11:17:53 UTC
Created attachment 142292 [details]
Rendering in LO 6
Comment 4 Christian 2018-05-26 11:18:14 UTC
Created attachment 142293 [details]
Rendering in MS-Excel
Comment 5 Christian 2018-05-26 11:28:23 UTC
Created attachment 142294 [details]
untouched MS Office doc

I created a yet another document by loading the orginal MS Office document in Excel and simplifying it to the same level as the first testcase.
However, I did this in MS Excel this time. So LO has not touched this document.
Please use this as a reference.

Note: The first testcase has a different history but was originally the same document.
Comment 6 Christian 2018-05-26 11:30:20 UTC
Same with 6.0.4
Comment 7 raal 2018-05-27 07:28:36 UTC
I can confirm with LO Version: 6.2.0.0.alpha0+
Build ID: c0fbf99e5601da27c59d131bcca24fafd259e508
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3; 
and Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

steps:
open color_coding.ods
save as xlsx
open xlsx file in excel
Comment 8 Xisco Faulí 2018-05-29 11:01:19 UTC
Also reproduced in

Version: 4.3.0.0.alpha1+
Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e


LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4
Comment 9 Commit Notification 2018-07-14 22:37:36 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e77596be879c25f5766b73a2f67cf0ddf7775ccc

tdf#117816, work around MS Excel bug with containsText cond format

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2018-07-14 22:37:43 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7e8dbbb02f25278d5afc51ae4a397831f5b21239

add test for tdf#117816

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Markus Mohrhard 2018-07-14 22:46:47 UTC
This is actually a MS Excel bug. Our output is according to the spec but I have now added a work around that fixes the import into Excel.

At the same time I have fixed a few smaller issues that I found with my test documents.
Comment 12 Commit Notification 2018-07-15 21:06:17 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6df45ae3a85b21d984605a6656282e5feaf8dea9

related tdf#117816, add more work arounds for MS Excel bugs

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2018-07-15 21:06:24 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=16048ebc230880148346f02403820b7646b2649c

related tdf#117816, only export cond format formula to xlsx when required

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2018-07-15 21:58:53 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0f0466c6498287c3eb790136655ac65f501b9f0f

related tdf#117816, the dxf ID is a global and not a sheet property

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2018-07-16 16:02:36 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=35a78c9082df46b338f0f137485a9a4cd7aede19&h=libreoffice-6-1

related tdf#117816, the dxf ID is a global and not a sheet property

It will be available in 6.1.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2018-07-16 16:24:56 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=62aae6918e33a2535462d1e7be2278a7f9745009&h=libreoffice-6-1

tdf#117816, work around MS Excel bug with containsText cond format

It will be available in 6.1.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.