Created attachment 136864 [details] The problematic document made with Excel 2013 The IF(A1<>Sheet2!A1;1;0) conditional formatting rule applied to a range of cells compares the same cells on two different worksheets, and if the value of the same cells on the two different doesn't match it applies the conditional formatting. LibreOffice Calc is unable to interpret this rule, and the condition is lost when the document is saved as XLSX. Steps to reproduce: 1. Create a new spreadsheet in Microsoft Excel. 2. Give values for a range of cells. 3. Create a new worksheet. 4. Copy the content from the first worksheet to the second worksheet to the same range of cells. 5. Change a few values on the second worksheet. 6. Select the cells with the values on the original (first) worksheet. 7. Create a new conditional formatting with the following rule: IF(A1<>Sheet2!A1;1;0) 8. Save the document as XLSX and close Excel. 9. Open the newly created file in LibreOffice Calc. 10. Save the file as XLSX with a different filename. 11. Open the XLSX file that was created with Calc in Excel. Actual results: Calc is unable to interpret the conditional formatting rule, and the condition is lost when the document is saved as XLSX. Expected results: Calc should be able to interpret and apply the conditional formatting rule and the rule should be preserved when the spreadsheet is saved as XLSX.
Created attachment 136865 [details] The example file saved with LO 6.0 alpha from 10-03
Created attachment 136866 [details] The file in LO and Excel 2013 side by side
Created attachment 136867 [details] Example file with similar conditional formatting made in LO 5.3.3 Similar example file made in LO 5.3.
Created attachment 136868 [details] The example file saved by LO 5.3 LO is able to export the same conditional format to xlsx.
Created attachment 136869 [details] Screenshot of the LO-made xlsx in Excel 2013 and LO 5.3 As a reference, the LO-made xlsx with similar conditional formatting works fine in Excel 2013 and LO both. So this is just a fileopen issue related to Excel 2013.
Repro with attachment 136864 [details] Win 8.1 32-bit LibO Version: 6.0.0.0.alpha1+ Build ID: a5af0fd9f27af42cf2e8571f659cdad6e606215b CPU threads: 4; OS: Windows 6.3; UI render: default; TinderBox: Win-x86@42, Branch:master, Time: 2017-11-06_23:18:19 Locale: fi-FI (fi_FI); Calc: group MSO 2013
Seems that MSO requires non-sheet local formulas in conditional formats to be inside of the extLst part of the conditional formatting code. I'm not sure if we can easily implement this as that requires quite some rework of the conditional format export code. The Excel generated content: <x14:conditionalFormattings> <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"> <x14:cfRule type="expression" priority="1" id="{77AD90AA-B4EF-4A1E-BD56-D2B295348952}"> <xm:f>IF(A1<>Sheet2!A1,1,0)</xm:f> <x14:dxf> <fill> <patternFill> <bgColor rgb="FFFF0000"/> </patternFill> </fill> </x14:dxf> </x14:cfRule> <xm:sqref>A1:E5</xm:sqref> </x14:conditionalFormatting> </x14:conditionalFormattings> The LibreOffice generated content: <conditionalFormatting sqref="A1"> <cfRule type="expression" priority="2" aboveAverage="0" equalAverage="0" bottom="0" percent="0" rank="0" text="" dxfId="0"> <formula>IF(A1<>Sheet2!A1,1,0)</formula> </cfRule> </conditionalFormatting>
*** Bug 91255 has been marked as a duplicate of this bug. ***
*** Bug 124282 has been marked as a duplicate of this bug. ***
Tibor Nagy committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/fcd96df8f648439ea191d8c2070e8b21ff0b1001 tdf#113013 XLSX import: fix "Formula is" type conditional formatting It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Tibor Nagy committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/40cb04a088e4fcf2b76232d080b3a5aaf5f5a675 tdf#113013 XLSX import: fix "Formula is" type conditional formatting It will be available in 7.1.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified in Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 44b914b1e2616ca8f70e896e653a46ca93610234 CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: hu-HU Calc: threaded