Bug 57955 - FORMATTING: Conditional formatting is not seen correctly when opened in Excel 2007
Summary: FORMATTING: Conditional formatting is not seen correctly when opened in Excel...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2012-12-06 19:34 UTC by KarlM
Modified: 2017-07-17 09:21 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Example spreadsheet showing the problem. (6.50 KB, application/vnd.ms-excel)
2012-12-06 19:34 UTC, KarlM

Note You need to log in before you can comment on or make changes to this bug.
Description KarlM 2012-12-06 19:34:47 UTC
Created attachment 71092 [details]
Example spreadsheet showing the problem.

Conditional formatinng in spreadsheet is not seen correctly when opened in Excel 2007. I have this problem with version 3.6.4 and several previous versions. 3.6.4 was not available, so I selected 3.6.4rc3.

Steps to reproduce:

1) Create a spreadsheet with Calc, with three cells A1, B1 and C1, conatining "A", "B" and "C" respectively.
2) Create a new style, name it Q, and select Bold Font.
3) Select the three cells, and select conditional formatting.
4) Add a rule with a formula $Sheet1.$B1="Q", and apply the style Q.
5) Copy and paste rows to create 4 rows of these three cells.
6) Change B3 to "Q" and notice that A3, B3 and C3 are now Bold.
7) Save this spreadsheet as an xls file (97/2000/XP/2003).
8) Open this file in Excel 2007, and observe that the Bold is not shown.
9) Set B1 to "Q", and observe that all 12 cells are now bold.
Comment 1 bfoman (inactive) 2012-12-06 22:14:58 UTC
Added Summary.
Comment 2 Markus Mohrhard 2012-12-07 23:36:50 UTC
What happens if you use xlsx?
Comment 3 KarlM 2012-12-08 21:47:06 UTC
I tried saving it as an xlsx and it produced similar results. Not exactly the same, but the conditional formatting still did not work.
Comment 4 mike.hall 2012-12-17 19:00:02 UTC
Can confirm that conditional formatting is broken when opening in Excel.

Have observed this over various versions of LO for at least 12 months. It remains broken in LO 4.0.0 beta.

My additional example is for setting the colour of a cell depending on cell content (eg use the first character of a cell to apply formats with the required background colour set). Set the conditional format on the first cell in a column and then paste special the format only to the remainder of cells in the column with data.

The conditional formatting works fine if you only work with LO, but choose to work with Excel formats.

.xls format supports only up to three rules. If you try to save from Excel to this format with more than 3 rules, you get a warning. LO should also issue a warning on saving to this format when a conditional format has more than 3 rules. However that's not the main issue and the errors below apply to .xls 97/2000/XP/2003 format also.

When saved to .xlsx and opened in Excel, the addresses of the cells being tested are incorrect, in my example all addressing a single cell. LO has also generated addresses like sheet1!$B$2 in rules which Excel doesn't accept if you try to edit a rule, even if the addresses point to the same sheet.
If you set up conditional formatting in Excel (ie background colour depends on cell content) and save to .xlsx, this works fine when opened in LO. However if you then save the file and reopen in Excel without making any changes to the formatting, it is again broken when opening in Excel as above.

This is thus a significant incompatibility between LO and MSO where the two applications do not agree on how to store conditional formatting rules. Since LO recognises correctly what MSO sets, it seems likely that LO is not saving the rules correctly. There may also be restrictions because MSO doesn't accept   sheet1!cell  references in rules, ie the functionality of LO may exceed that of MSO. Again, a warning would be nice, but it would be good if the saving of rules avoided the sheet1!cell  reference method when that isn't necessary, which it shouldn't be when the rules refer only to cells on the current sheet (all mine did).
Comment 5 Markus Mohrhard 2013-01-08 23:17:34 UTC

I just checked it and in a master build the export to xlsx works fine and Excel2010 opens the file without a problem.

If someone could test it with 2007 would be great. I'll need some time to download 2007 from MSDN otherwise.

I also checked export to xls and the problem there is a missing feature in Excel. Excel does not allow sheet references in conditional format formulas while saving in xls format.
Comment 6 Markus Mohrhard 2013-01-08 23:20:37 UTC
I will not fix this problem as the underlying problem is already fixed in newer Excel versions.

If you find problems in XLSX export please open a new bug report and put me into CC.
Comment 7 mike.hall 2013-01-08 23:41:41 UTC
Happy to test this further in Excel 2007 and LO 4.0.0 RC1 (if ready) later in the week. I think there are more difficulties here than you imply, but have no time to delve deeper until Friday.
Comment 8 KarlM 2013-01-24 06:07:34 UTC
I just tried saving this as ods and an xlsx file and then tried opening each in excel 2007. Both the ods and xlsx versions opened without the conditional formatting working.

Office 2007 is still commonly used in enterprise environments. I think dropping compatibility with Office 2007 would be an unfortunate and limiting choice.
Comment 9 mike.hall 2013-02-17 14:36:30 UTC
I understand why this bug has been set as wontfix.

The concern I have is that users can lose a lot of time when LO saves to a format for another application when that application does not support some of the functionality used in the spreadsheet (or other document) being saved. I spent days finding out the limitations of conditional formatting when all I was trying to do was what should have been the very simple task of transferring a spreadsheet to another user working with Excel 2007. Apart from the waste of time, it's very bad publicity to subject users to this risk. This is why IMHO it would be better if LO refused to save to .xls when non-supported functionality is detected. The generic warning is of very limited help, because sometimes things will work and sometimes they won't. If you have been exchanging documents for years and at some point add in unsupported functionality, there is no extra warning -> lost time and frustration.

There is a more generic concern. .xls and .doc were for many years the de facto file formats for document exchange. That is evidently no longer true, but it is far from obvious what has taken their place. It seems to me that some official body needs to concentrate on choosing the best de facto file format and then influencing public and business practice so that that format is accepted and recommended everywhere. Of course, that's what ODF is attempting to do, but it may be desirable to come at it from more than one direction. I may have the opportunity to raise this in a professional UK IT group to see what degree of support there might be for an approach independent of ODF. If positive, I will do what I can to drive that forward.

See also bug 59346
Comment 10 KarlM 2013-03-27 03:03:03 UTC
I know that you don't want to do a large amount of work here.

However, here is the situation I see: I can create a spreadsheet with Excel 2007 (what I have available) with only a single conditional formatting rule. Calc will open it just fine, and translate the single formatting rule, so everything works. If I make a minor edit to the file (nothing to do with conditional formatting) and save it as an xls (2007/2010, or XP/2003), the formatting is then broken (when I open it with Excel 2007).

It seems that if the input translation handles this, the output translation should also. The formatting does not exceed the capability of the selected file format.

What does saying Calc can write an XP/2003 xls file mean then, if all formatting is broken.

The problem comes down to the "Sheet!" being added to the formula and not adjusting the starting index in the formula for the starting row of the region(in my case).

So, if the translation is handled on input, why not on output?


Comment 11 KarlM 2013-04-08 03:45:34 UTC
Hi Marcus...

Could you please clarify this for me. Initially I thought that the problem was that LibreOffice Calc formatting exceeded the capabilities of Excel 2007. But after digging deeper, it seems that the LO output filter does not have the same capability to translate the Sheet1 reference and the row offset that the input filter does.

Why does it make sends to have the input filter handle this, but not the output filter?

And why for Excel XP/2003, as well as 2007/2010?