Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
3 of 11 tasks
Awilen-Bernkastel opened this issue Jan 9, 2025 · 2 comments · May be fixed by #4314
Open
3 of 11 tasks

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

Awilen-Bernkastel opened this issue Jan 9, 2025 · 2 comments · May be fixed by #4314

Comments

@Awilen-Bernkastel
Copy link

Awilen-Bernkastel commented Jan 9, 2025 •
edited
Loading

This is:

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
Copy link
Collaborator

oleibman commented Jan 9, 2025

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
Copy link
Author

Awilen-Bernkastel commented Jan 10, 2025 •
edited
Loading

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

@oleibman oleibman linked a pull request Jan 12, 2025 that will close this issue
Open
11 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add "Priority" Property for Conditional Formatting oleibman/PhpSpreadsheet
2 participants
@oleibman @Awilen-Bernkastel