Download it now!
Bug 41722 - conditional formatting does not export to excel
Summary: conditional formatting does not export to excel
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2011-10-12 09:24 UTC by David Tse
Modified: 2017-07-08 10:50 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Conditional formatting example saved with LibreOffice (5.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-02-08 11:30 UTC, Duncan
Details
Conditional formatting example saved with MS Office (8.57 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-02-08 11:31 UTC, Duncan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Tse 2011-10-12 09:24:42 UTC
In Libre Office Calc, do a conditional formatting where the background will change colors.  No do an export to an .xls or .xlsx format and the formatting disappears.
Comment 1 Björn Michaelsen 2011-12-23 12:40:50 UTC Comment hidden (obsolete)
Comment 2 Florian Reisinger 2012-08-14 14:00:21 UTC Comment hidden (obsolete)
Comment 3 Florian Reisinger 2012-08-14 14:01:28 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:06:12 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:08:13 UTC Comment hidden (obsolete)
Comment 6 Duncan 2016-02-08 11:29:37 UTC
I am having an issue which matches this description. I have changed the version and hardware to refelct my system. For reference the original report was in 3.4.3 on linux.

I will redescribe the issue:
When saving a document in xlsx (office open version) format with conditional formatting that changes the style of cells based on their text content (contains string), then opening the document in MS Excel (tested with 2007 and 2010), the conditional formatting is not shown.

If the user creates conditional formatting in msxl and opens it in calc the formatting is shown correctly. If the user changes anything on this page in calc and then saves, then opens again in msxl the formatting is gone again.

In the msxl conditional formatting 'manage rules' menu all the rules are present as normal, but none of them are working. If the edit rule screen is opened for each rule and then closed again with the 'ok' button (no actual changes necessary), the formatting reappears and shows correctly.

Saving in msxl and opening in calc does not result in any issue.

This feels like a microsoft bug to me but if it is then I am out of luck so here's hoping.
Comment 7 Duncan 2016-02-08 11:30:48 UTC
Created attachment 122435 [details]
Conditional formatting example saved with LibreOffice

Opening this in MS Excel does not show any conditional formatting
Comment 8 Duncan 2016-02-08 11:31:45 UTC
Created attachment 122436 [details]
Conditional formatting example saved with MS Office

Opening this in either office program shows the formatting correctly
Comment 9 Duncan 2016-02-08 15:09:52 UTC
I updated to the newest stable (5.0.4.2 (x64)) and was still able to reproduce the issue as described
Comment 10 Duncan 2016-02-10 10:26:31 UTC
I checked inside the save files to see what differences there are in the way the conditional formatting is saved. Here is the result:
http://pastebin.com/D5Xksmj2

The excel version is from both 2007 and 2010 versions, I was unable to test with a less messed up version of MSO because I dont have access to one
Comment 11 Duncan 2016-02-10 13:54:52 UTC
Possibly related to #65371
Comment 12 Buovjaga 2016-02-12 14:23:11 UTC
(In reply to Duncan from comment #7)
> Created attachment 122435 [details]
> Conditional formatting example saved with LibreOffice
> 
> Opening this in MS Excel does not show any conditional formatting

Opening this in LibO shows the colors. It has named the styles to ConditionalStyle_X
I tried saving it to ods in Libo and then to xlsx. Now the colors were not shown in LibO.
I tried doing a simple condition from scratch (if contains "a", change style to Heading). Saving as .xlsx and opening in LibO, it doesn't show the heading formatting (again a ConditionalStyle_1 was created).

Confirmed that MSO doesn't show the formatting either.

It seems something went worse after attachment 122435 [details] was created, as now even LibO doesn't preserve the formatting in a way that it would understand.

Let's keep this one open for now.

Win 8.1 32-bit
MSO 2013
Version: 5.2.0.0.alpha0+
Build ID: a6f876d45bd4e41a7143594a6cb11b6893a0f620
CPU Threads: 4; OS Version: Windows 6.29; UI Render: GL; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-02-11_00:07:38
Locale: fi-FI (fi_FI)
Comment 13 Markus Mohrhard 2016-09-19 17:44:15 UTC
This seems to be related to a bug in the OOXML specification.

§18.3.1.10 says for the operator attribute: "The operator in a "cell value is" conditional formatting rule. This attribute is ignored if
type is not equal to cellIs"

but checking §18.18.15 there are at least 4 attribute values that are not related to cellIs. I have added them now but need to test that this really helps.
Comment 14 Commit Notification 2016-09-19 21:25:46 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

try to generate more excel compliant files, tdf#41722

It will be available in 5.3.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 Xisco Faulí 2017-03-01 11:09:33 UTC
Hello Markus,
Is this bug fixed?
If so, could you please close it as RESOLVED FIXED?