Bug Hunting Session
Bug 59346 - FILESAVE: FILEOPEN: Certain conditional formatting is broken
Summary: FILESAVE: FILEOPEN: Certain conditional formatting is broken
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.1 rc
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2013-01-14 09:36 UTC by mike.hall
Modified: 2018-03-25 02:29 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Background colour conditional formatting spreadsheet (12.23 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-01-14 09:36 UTC, mike.hall
Details
Conditional Formatting - Colour Styles (14.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-17 13:13 UTC, mike.hall
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mike.hall 2013-01-14 09:36:07 UTC
Created attachment 72994 [details]
Background colour conditional formatting spreadsheet

Conditional formatting setting colour background does not work fully for any file format.

@Marcus
As follow up of bug 57955 I have retested with 4.0.0RC1 and with Excel 2007. In my tests nothing works fully in any file format. Here is the new report and as requested I have copied you.

Summary:
.xls Round trips correctly in LO, but a large number of new styles are created
     Opens with a warning in Excel 2007, conditional formatting works, rules beyond the 3rd lost on save from Excel
	 Save to .xlsx from Excel - conditional formatting continues to work
Issues: 
A warning when saving to .xls with more than three rules would be nice, even though it works
In any reasonably sized sheet (ours was 500 rows), the multiple styles created by saving to .xls (5 rules x 500 rows x 2 cells = 5000 styles) the resulting object is very difficult to use because opening and saving (including auto save) takes a very long time. Is it possible to create fewer styles.

.ods Opens in Excel 2007, but conditional formatting is lost. Not sure whether that could be fixed at the LO end

2007/2010 XML 'Save as' from .ods. Close. Reopen. Conditional formatting is lost (ie does not round trip within LO) - bug

2003 XML Cannot save from .ods - ?? bug

OO XML 'Save as' from .ods. Close. Reopen. Conditional formatting is lost (ie does not round trip within LO) - bug

Attachment has the conditional formats giving the trouble, with some more details. If you prefer separate bug reports for any of the above, please let me know and I can do that. Worth perhaps saying that I've been struggling with this issue for more than 12 months attempting to find a solution for moving between LO and Excel. Nothing works except redoing the conditional formatting every time.
Comment 1 mike.hall 2013-01-14 09:45:51 UTC
Forgot to specify the initial document:

New worksheet
Create 4 lines of two cells of data and 4 new styles with different background colours	
Create conditional formatting separately on A1 and B1 with formulas like left(b1)=”s” - four conditions
copy formats only to other three rows
save .ods
Comment 2 Markus Mohrhard 2013-01-14 10:20:54 UTC
(In reply to comment #0)
> Created attachment 72994 [details]
> Background colour conditional formatting spreadsheet
> 
> Conditional formatting setting colour background does not work fully for any
> file format.
> 
> @Marcus
> As follow up of bug 57955 I have retested with 4.0.0RC1 and with Excel 2007.
> In my tests nothing works fully in any file format. Here is the new report
> and as requested I have copied you.
> 
> Summary:
> .xls Round trips correctly in LO, but a large number of new styles are
> created
>      Opens with a warning in Excel 2007, conditional formatting works, rules
> beyond the 3rd lost on save from Excel
> 	 Save to .xlsx from Excel - conditional formatting continues to work
> Issues: 
> A warning when saving to .xls with more than three rules would be nice, even
> though it works

Will not be implemented! There is already the general warning about lost information when saving to xls and xlsx! The number of error/warning messages that we would need to implement for different features is just too large.

> In any reasonably sized sheet (ours was 500 rows), the multiple styles
> created by saving to .xls (5 rules x 500 rows x 2 cells = 5000 styles) the
> resulting object is very difficult to use because opening and saving
> (including auto save) takes a very long time. Is it possible to create fewer
> styles.

Not with some rework and I will not spend unnecessary time in the xls import/export filter.

> 
> .ods Opens in Excel 2007, but conditional formatting is lost. Not sure
> whether that could be fixed at the LO end

No! Excel 2007 does not support OpenFormula! 2010 does not support it either. They started supporting it with 2013.

> 
> 2007/2010 XML 'Save as' from .ods. Close. Reopen. Conditional formatting is
> lost (ie does not round trip within LO) - bug
> 
> 2003 XML Cannot save from .ods - ?? bug

I will not look into that format either.

> 
> OO XML 'Save as' from .ods. Close. Reopen. Conditional formatting is lost
> (ie does not round trip within LO) - bug

OOXML and 2007/2010 Excel XML are the same for us. Only Excel 2007 can't handle OOXML files!

You can try with the new startsWith format in LibO 4 that is equivalent to the corresponding OOXML format. It is saved in an ODF extension and should export to OOXML fine.

> 
> Attachment has the conditional formats giving the trouble, with some more
> details. If you prefer separate bug reports for any of the above, please let
> me know and I can do that. Worth perhaps saying that I've been struggling
> with this issue for more than 12 months attempting to find a solution for
> moving between LO and Excel. Nothing works except redoing the conditional
> formatting every time.

As mentioned I will only care about the problems with ODF and OOXML.
Comment 3 mike.hall 2013-01-14 15:22:24 UTC
Thanks for a speedy response.

My view is that if conditional formatting cannot be correctly saved to a format, the user should get an error message and LO should refuse to save the object, ie the 2003 XML behaviour applied to .xls. That would have saved me days of work and will help others too.

It still seems to me there must be a bug in OO XML and 2007/2010 XML FILESAVE. Save from .ods and reopen in LO (NB LO not Excel) - conditional formatting is broken as per my example. 

If I save my example from .ods to .uos, close and reopen in LO, all I get is a completely blank sheet. That looks like a stopper to me, complete data loss.

It's very helpful to know that Excel 2013 is needed to successfully open OO XML or 2007/2010 with conditional formatting (if I have interpreted your comments correctly). My users probably don't have that though!

I have no idea what you mean by startsWith format. Have not found it in the dialogues and can't find it in the documentation.
Comment 4 Joel Madero 2013-01-14 16:08:36 UTC
As Markus has already stated he's not implementing any of these things, I'm going to mark as WONTFIX.

@Markus - if you decide to tackle any of these please reopen.

@Reporter - as stated, there is already a big warning message that comes up ANY TIME you save as .xls, anything beyond this point is a risk the user accepts by clicking "yes". If you want Microsoft to implement changes to support open formats, I suggest you email them and see if they will patch 2010/2007 with their next Service Pack (....I doubt that they'll listen but you never know)
Comment 5 Markus Mohrhard 2013-01-14 16:12:15 UTC
@Joel: There might be an issue with formulas between our OOXML export and what Excel accepts as input. Sadly it might be that we are writing correct output and are hit again by one of these nasty import problems in Excel when you export too many option parameters in the cond format elements.

I'll look into this later as I have a bunch of formula engine and drawing layer import problems with high priority until the 4.0 release.
Comment 6 mike.hall 2013-02-17 13:13:51 UTC
Created attachment 74983 [details]
Conditional Formatting - Colour Styles

@Marcus
Retested with 4.0.0.3.

Believe there are still 3 issues with OOXML

	Save attachment as OOXML, close and reopen (in LO)
1)	formula in column C does not re-calculate on reopen (bug 60215)
2)	after recalculation, the T() part works differently (text of style is incorrectly appended to the end of column C) (??new bug)
3)	the conditional colour background styles in columns A & B no longer work (this bug)
Comment 7 mike.hall 2013-02-22 11:37:30 UTC
(In reply to comment #6)
> Created attachment 74983 [details]
> Conditional Formatting - Colour Styles
> 
> @Marcus
> Retested with 4.0.0.3.
> 
> Believe there are still 3 issues with OOXML
> 
> 	Save attachment as OOXML, close and reopen (in LO)
> 1)	formula in column C does not re-calculate on reopen (bug 60215)
> 2)	after recalculation, the T() part works differently (text of style is
> incorrectly appended to the end of column C) (??new bug)
> 3)	the conditional colour background styles in columns A & B no longer work
> (this bug)

After some more delving re these comments:
Issue 2) results from the STYLE() function being silently deleted from the formula in column C. The reason for the deletion is evidently that STYLE() has no equivalent function in Excel. Suggest that when this occurs, as a minimum the user should be informed with a warning message about what has been dropped and why.
Issue 3) The reason conditional colour background styles aren't working correctly in attachment 74983 [details] is because of the way LO copies and pastes conditional formats. This is leading to inconsistencies in the resulting formulas. If the conditional formatting formulas do not have any inconsistencies, the conditional formatting does indeed transfer to Excel 2007 correctly. I need to do some more work to tie this down more exactly, and it might overlap with another bug.
Comment 8 mike.hall 2013-02-22 20:59:26 UTC
Re 3) please see bug 59288
Comment 9 QA Administrators 2015-02-19 15:40:28 UTC Comment hidden (obsolete)
Comment 10 mike.hall 2015-02-24 13:05:56 UTC
The bug is similar to what it was before.

LO 4.4.1.2 Win7 64 bit

1) Formula in column C no longer works
2) The appending of text of style does not appear, but the formula in column C aren't working
3) The conditional colour backgrounds in A & B now all depend on B1 - B2 etc changes have no effect
Comment 11 tommy27 2016-04-16 07:28:18 UTC Comment hidden (obsolete)
Comment 12 Markus Mohrhard 2016-09-19 21:41:30 UTC
(In reply to mike.hall from comment #7)
> (In reply to comment #6)
> > Created attachment 74983 [details]
> > Conditional Formatting - Colour Styles
> > 
> > @Marcus

> After some more delving re these comments:
> Issue 2) results from the STYLE() function being silently deleted from the
> formula in column C. The reason for the deletion is evidently that STYLE()
> has no equivalent function in Excel. Suggest that when this occurs, as a
> minimum the user should be informed with a warning message about what has
> been dropped and why.

As I mentioned before we are currently not planning to implement something like that.
Comment 13 QA Administrators 2018-03-25 02:29:37 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug