Bug 113013 - FILEOPEN: XLSX - Unable to interpret 'Formula is' type conditional formatting rule
Summary: FILEOPEN: XLSX - Unable to interpret 'Formula is' type conditional formatting...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.1.2 release
Hardware: All All
: medium normal
Assignee: Tibor Nagy (NISZ)
URL:
Whiteboard: target:7.2.0 target:7.1.0.2
Keywords: filter:xlsx
: 91255 124282 (view as bug list)
Depends on:
Blocks: XLSX-Conditional-Formatting
  Show dependency treegraph
 
Reported: 2017-10-09 12:59 UTC by Gabor Kelemen
Modified: 2021-02-08 11:37 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
The problematic document made with Excel 2013 (18.76 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-09 12:59 UTC, Gabor Kelemen
Details
The example file saved with LO 6.0 alpha from 10-03 (15.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-09 13:02 UTC, Gabor Kelemen
Details
The file in LO and Excel 2013 side by side (136.32 KB, image/png)
2017-10-09 13:03 UTC, Gabor Kelemen
Details
Example file with similar conditional formatting made in LO 5.3.3 (10.22 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-09 13:17 UTC, Gabor Kelemen
Details
The example file saved by LO 5.3 (7.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-09 13:22 UTC, Gabor Kelemen
Details
Screenshot of the LO-made xlsx in Excel 2013 and LO 5.3 (110.72 KB, image/png)
2017-10-09 13:25 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2017-10-09 12:59:41 UTC
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.
Comment 1 Gabor Kelemen 2017-10-09 13:02:32 UTC
Created attachment 136865 [details]
The example file saved with LO 6.0 alpha from 10-03
Comment 2 Gabor Kelemen 2017-10-09 13:03:25 UTC
Created attachment 136866 [details]
The file in LO and Excel 2013 side by side
Comment 3 Gabor Kelemen 2017-10-09 13:17:57 UTC
Created attachment 136867 [details]
Example file with similar conditional formatting made in LO 5.3.3

Similar example file made in LO 5.3.
Comment 4 Gabor Kelemen 2017-10-09 13:22:22 UTC
Created attachment 136868 [details]
The example file saved by LO 5.3

LO is able to export the same conditional format to xlsx.
Comment 5 Gabor Kelemen 2017-10-09 13:25:10 UTC
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.
Comment 6 Buovjaga 2017-11-11 13:07:49 UTC
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
Comment 7 Markus Mohrhard 2018-09-26 09:51:39 UTC
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&lt;&gt;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&lt;&gt;Sheet2!A1,1,0)</formula>
    </cfRule>
  </conditionalFormatting>
Comment 8 Gabor Kelemen 2018-10-01 15:11:19 UTC
*** Bug 91255 has been marked as a duplicate of this bug. ***
Comment 9 Gabor Kelemen 2019-05-09 14:14:45 UTC
*** Bug 124282 has been marked as a duplicate of this bug. ***
Comment 10 Commit Notification 2021-01-13 10:47:43 UTC
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.
Comment 11 Commit Notification 2021-01-14 07:37:08 UTC
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.
Comment 12 NISZ LibreOffice Team 2021-02-08 11:37:16 UTC
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