Apache OpenOffice (AOO) Bugzilla – Issue 122856
Conditional formatting references wrong cells
Last modified: 2017-05-20 10:45:02 UTC
Created attachment 81168 [details] Test file After upgrading to 4.0.0, and opened a file I'd been working in previous versions it appears that my conditional formatting broken. All the rules were there, but they were all referencing the wrong cells. I went in and changed everything back to the way it should have been, saved the file and closed. When I opened the file back up again, everything was back referencing the wrong cells again. No matter how many times I update and save, it always goes back to being wrecked. I reinstalled and opened the file in 3.4.1, and everything was referencing the correct cells. Here are a couple examples of the same cells in 3.4.1 (how they should be) and 4.0.0 (wrecked) 3.4.1 - Cell A1997 COUNTIF($Sheet2.$A$1:$A$75;$Sheet1.A1997)>0 IF(VLOOKUP($Sheet1.A1997;$Sheet2.$H$2:$I$44;2;0)<>$Sheet1.I1997;TRUE()) 3.4.1 - Cell D1997 SUM($Sheet1.B1997:C1997)<>$Sheet1.D1997 ---- 4.0.0 - Cell A1997 COUNTIF($Sheet2.$A$1:$A$75;$Sheet1.A300)>0 IF(VLOOKUP($Sheet1.A300;$Sheet2.$H$2:$I$44;2;0)<>$Sheet1.I300;TRUE()) 4.0.0 - Cell D1997 SUM($Sheet1.IU300:IV300)<>$Sheet1.A300 I have also uninstalled and reinstalled 4.0 a couple times, and it always does the same thing. I have attached a small test file created from the file I have been working on. The conditional formatting is located in cells A, D, and I. I should also not that this only appears to happen with the .xls extension. If I re-save the file as .ods in 3.4.1, the problem does not occur in 4.0.0.
Created attachment 81169 [details] Screen shot: 3.4.1 vs. 4.0.0
.
@Ariel, so are you confirming? These screenshots are ones you have taken? If so, feel free to mark the issue as CONFIRMED.
Confirming (There are too many regression in Calc 4.0.0)
I will have a deeper investigation on this.
Created attachment 81431 [details] Comparison between MS Excel 2003 and 2010 I find a strange problem. Even the result of the same XLS is different between Excel 2003 and Excel 2010. Open the sample by Excel 2003 and Excel 2010, you can find the result is completely different in column A, D and I. And select I2:I12, open conditional formatting, you can also find the referenced cell is completely different. The result on AOO4.0 is more like assemble of that on Excel 2003 and Excel 2010, column A is like 2010, column D and I are like 2003
I have made the following strange observations: - Opening the given sample file in Microsoft Excel 2010 and Microsoft Excel 2013 reveals the same results as in AOO 3.4.1, AOO 3.4.0 and OOo 3.3.0 - Opening the given sample file in Microsoft Excel 2007 reveals no conditional formatting. When trying the edit the imported conditional formatting rules Microsoft Excel 2007 tells me that references to other sheets are not allowed in conditional formatting rules. - Opening the given sample file in Microsoft Excel 2003 reveals no conditional formatting in columns A and I. The one in D is the same as in AOO 4.0.0 - wrong cells are referenced. For the conditional formatting in columns A and I the same holds as for Microsoft Excel 2007 --> The different Microsoft Excel versions interpret the its binary format differently. --> Our Microsoft Excel binary file format export supports features which are not available in Microsoft Excel 2003 and 2007 - I saved the given sample file in AOO 3.4.1 in ODF file format. Opening this document in AOO 4.0.0 and exporting it to Microsoft Excel binary format seems to create the same file as the given one. --> Our Microsoft Excel binary file format export does not seem to be changed compared to the one in AOO 3.4.1 ---> Our Microsoft Excel binary file format import seems to be changed compared to the one in AOO 3.4.1 - it looks like it is now more like the one in Microsoft Excel 2003/2007 @Pat: Do you know by which application the original given sample file had been created?
(In reply to Clarence GUO from comment #6) > Created attachment 81431 [details] > Comparison between MS Excel 2003 and 2010 > > I find a strange problem. Even the result of the same XLS is different > between Excel 2003 and Excel 2010. > Open the sample by Excel 2003 and Excel 2010, you can find the result is > completely different in column A, D and I. And select I2:I12, open > conditional formatting, you can also find the referenced cell is completely > different. > The result on AOO4.0 is more like assemble of that on Excel 2003 and Excel > 2010, column A is like 2010, column D and I are like 2003 We have made more or less the same observation.
Created attachment 81432 [details] simplified sample file - Open this simplified sample file in AOO 3.4.1 and/or AOO 4.0.0 - it contains conditional formatting referencing the cells inclusive the sheet name. - Export it to Microsoft Excel binary file format (*.xls) - Open it in Microsoft Excel 2003, 2007, 2010 and/or 2013 --> you will see one part of my observation
Created attachment 81433 [details] another simplified sample containing references to other sheets in the conditional formatting criteria
@(In reply to Oliver-Rainer Wittmann from comment #7) > @Pat: Do you know by which application the original given sample file had > been created? The file was originally created in Open Office, although I don't know what version for sure. We have been using the file for at least 3 years now.
Once resave the sample by MS Excel without any change, reopen the sample by MS Excel, you can see all conditional formatting lost by both MS Excel 2003 and 2010. Then reopen this file by AOO3.4.0 or AOO4.0, now their results are same, don't show any number formats like that in Excel. So seems Excel doesn't support such formula usage on conditional formatting then Excel abandon them. With Oliver's simple ODS, even resave to XLS by AOO3.4.0, I can see conditional formatting different between Excel 2003 and 2010. So the issue of conditional formatting difference on different Excel version is not introduced by AOO3.4.1. Furthermore, I copy the cells from Oliver's simple ODS to Excel, and try to create same conditional formatting, but I failed on both Excel 2003 and 2010, no conditional formatting show on these cells. Maybe that can illustrate again that such formula usage on conditional formatting cannot support by Excel. But I'm not expert on conditional formatting, is anybody can confirm my conclusion? If my conclusion is true, then there's feature difference between AOO and Excel. Then as Pat said the sample was converted from ODS, maybe in this case it is an invalid sample.
Looks like this bug was introduced by SVN revision 1381452 for bug 117283 (adding filter support for functions AVERAGEIF, AVERAGEIFS, COUNTIFS, SUMIFS, XOR).
Yes, undoing changes in sc/source/filter/excel/xicontent.cxx by converting back ExcelToSc.Convert(..., FT_Conditional) to ExcelToSc.Convert(..., FT_RangeName) loads the bugdoc correctly.
Reset the assignee to the default "issues@openoffice.apache.org".