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.
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
(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.
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.
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)
@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.
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)
(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.
Re 3) please see bug 59288
** 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 on a currently supported version of LibreOffice (4.4.0.3 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-02-19
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
** 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 on a currently supported version of LibreOffice (5.0.5 or 5.1.2 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) 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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16
(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.
** 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
Dear mike.hall, 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 https://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
Bug is still present, conditional formatting is lost when save to different format. Version: 7.1.1.2 (x64) / LibreOffice Community Build ID: fe0b08f4af1bacafe4c7ecc87ce55bb426164676 CPU threads: 12; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: cs-CZ (cs_CZ); UI: cs-CZ Calc: threaded
Dear mike.hall, 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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
It's been made clear (eg comment 12) that nothing is to be done. Can't see the point of testing again. Calc and Excel both support conditional formatting (I've worked with both using that capability over the years). Is it the case that implementations are incompatible so it will never be possible to go between the two? If so let's close this issue as won't fix.