The original conditional formatting rule priorities get reordered when overwriting an existing Xlsx file containing unsorted rules and rules overlapping cell ranges.

[Awilen-Bernkastel]

This is:

• a bug report
• a feature request
• not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

The original conditional formatting rule priorities are respected when writing an Xlsx file in spite of the overlaps.

What is the current behavior?

The original conditional formatting rule priorities get reordered by order of encounter in the writing loops, leading to the application of rules in the wrong order in various spreadsheet editors (Excel, LibreOffice, OnlyOffice).

What are the steps to reproduce?

Open an Xlsx file containing rules in an unsorted priority order using

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);

Write the same file using

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($filePath);

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

One of the worksheets I'm working with contains this ruleset of conditional formattings, in this order:

    <conditionalFormatting sqref="A40:B40">
        <cfRule dxfId="339" priority="27" type="expression">
            <formula>$C$39=&quot;YES&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C36">
        <cfRule dxfId="338" priority="13" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="337" operator="equal" priority="19" type="cellIs">
            <formula>&quot;NO&quot;</formula>
        </cfRule>
        <cfRule dxfId="336" operator="equal" priority="26" type="cellIs">
            <formula>&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C40">
        <cfRule dxfId="335" operator="equal" priority="15" type="cellIs">
            <formula>&quot;YES&quot;</formula>
        </cfRule>
        <cfRule dxfId="334" priority="20" type="expression">
            <formula>$A$40=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="333" operator="equal" priority="23" type="cellIs">
            <formula>&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C8">
        <cfRule dxfId="332" operator="equal" priority="22" type="cellIs">
            <formula>&quot;[redacted]&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C19">
        <cfRule dxfId="331" operator="equal" priority="14" type="cellIs">
            <formula>&quot;&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C37:C39">
        <cfRule dxfId="330" priority="10" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="329" operator="equal" priority="11" type="cellIs">
            <formula>&quot;YES&quot;</formula>
        </cfRule>
        <cfRule dxfId="328" operator="equal" priority="12" type="cellIs">
            <formula>&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="A33:C34 A36:C40">
        <cfRule dxfId="327" priority="8" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="326" priority="9" type="expression">
            <formula>$C$32=&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="C35">
        <cfRule dxfId="325" priority="5" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="324" operator="equal" priority="6" type="cellIs">
            <formula>&quot;YES&quot;</formula>
        </cfRule>
        <cfRule dxfId="323" operator="equal" priority="7" type="cellIs">
            <formula>&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="A35:C35">
        <cfRule dxfId="322" priority="3" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
        <cfRule dxfId="321" priority="4" type="expression">
            <formula>$C$32=&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="A33:C40">
        <cfRule dxfId="320" priority="2" type="expression">
            <formula>$C$32=&quot;NO&quot;</formula>
        </cfRule>
    </conditionalFormatting>
    <conditionalFormatting sqref="A33:D40">
        <cfRule dxfId="319" priority="1" type="expression">
            <formula>$C$32=&quot;&quot;</formula>
        </cfRule>
    </conditionalFormatting>

The file this extract comes from was written using Microsoft Excel 2016.

• The rule priorities are in reverse order from one conditionalFormatting block to the next.
• Within the same conditionalFormatting block, the rules are ordered by priorities but are not necessarily successive.
• Different conditionalFormatting blocks overlap cell ranges.

Overwriting this file with Phpspreadsheet mangles the priorities such that the first cfRule encountered is now priority 1 instead of 27, the second is priority 2 instead of 13, the third is priority 3 instead of 19, etc... leading to formatting glitches when opening the resulting file in a spreadsheet editor.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

The behavior is located in the method Worksheet::writeConditionalFormatting in the file src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php. The use of a local $id variable internal to the method to write the 'priority' attribute leads to the rule priorities getting reordered.

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

As far as I'm concerned, I've only tested and checked against the Xlsx file format. This behavior may be present in other file format writers.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 1.29.5 (version I was using) to 3.7.0 (version I have verified contains the same code.)
PHP: 8.1

[oleibman]

I tried to insert your xml into an Excel workbook, but Excel complained of corruption when I tried to open it. Is it possible for you to upload a spreadsheet which has the conditionals defined as above, and which demonstrates your problem? You should be able to clear all the data - I just want to make sure its conditional definitions match what you've reported, and then use it as a basis to investigate the problem.

[Awilen-Bernkastel]

Thank you for your reply!

The corruption probably happened because the workbook didn't contain the corresponding dxfId in /xl/styles.xml.

Here's a stripped-down version of the spreadsheet I'm working with. I have checked the order of priorities I mentioned above was preserved after saving the file, and will be reordered through saving with Phpspreadsheet. Sorry for the French.

I understand some of the rules are... counter-intuitive at best as some defined conditional styles are never shown in the original file considering the given formulae and order of priorities. This is not my file and I'll deal with that with the original author, and I don't believe this has bearing on the issue I'm describing.

Test_Spreadsheet.xlsx