Conditional formats applying to multiple cells are duplicated when reading and writing an excel file

[ndench]

This is:

- [x] 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?

Conditional formats should not be changed when reading then writing an excel file.

What is the current behavior?

Reading an excel file that contains a conditional formatting rule which applies to multiple cells (ie. one rule: $A$1,$C$1,$E1) and then immediately writing the file without making any changes will result in the rule being duplicated so that it only applies to single cells (ie. 1 rule: $A$1, another rule $C$1 and another $E$1).

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

// Read the excel file that has a single conditional format applying to multiple cells
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$excel = $reader->load('multili-format.xlsx');

// Immediately write the excel file back out without making any changes
$writer   = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel);
$writer->save('multi-format-output.xlsx');

Here is the original file that has no content, and only a a single conditional formatting rule for A1 contains a blank value with the format Not format set
multi-format.xlsx

Here is the output file after running the above script, you can see the conditional formatting rule now has a format of light red fill, dark red text.
multi-format-output.xlsx

What features do you think are causing the issue

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

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

• XLSX

Which versions of PhpSpreadsheet and PHP are affected?

• PHP 8.1.2
• PhpSpreadsheet 1.25.2 (1.23.0 also affected)

Anything else?

I'm unsure if this related to #3202 but I imagine the issue is located in the same area?

[MarkBaker]

This is a throwback 18 years ago to the early days of PHPExcel, when we implemented conditional styles for individual cells (A1) or for discrete ranges. (A1:C3), breaking "complex" ranges up against the union operator (,) into discrete ranges, and largely ignoring the intersection operator ( ) because of its complexity.
18 years on, and "complex" ranges are still handled in the same way; while some improvements have been made, the internal representation is still the same. The Wizards make it easier to define rules; and we also have better handling for the intersection operator now, trying to identify the intersect and setting the rule purely for that cell or range (which is more of a break from Excel).

In this case, while it looks different, the conditional rules still behave in the same way as they do in Excel; the same conditional formatting rules apply to the same cells: it's simply that there are three rules for three individual cells/ranges rather than a single rule encompassing all three unions.

From a UX perspective, this does make things easier if you want to update a rule (to edit the range(s), or to change the styling for one of those ranges). Most users don't understand unions and intersections in ranges in Excel, and only think in terms of individual cells and simple ranges.

---

While I'd like to make the handling of "complex" Conditional Format ranges closer to Excel, I also want to ensure that it's still relatively easy for users to manage those ranges. The UX for maintaining "complex" ranges needs to be simplified. The CellAddress and CellRange objects that were introduced earlier this year are a step toward that goal, but don't yet provide the functionality for unions and intersections. But my suspicion is that most users still use simple strings for simple cells and cell/row/column ranges rather than the new objects.

Providing better range handling with full support for unions and intersections will come, but it isn't yet high up on the list of priorities. The biggest priorities at the moment include full table support with, full handling for Structured References in the Calculation Engine; full support for Array Formulae (which entails a BC break, so it will come in PhpSpreadsheet 2.0 which is already 4 months overdue); support for objects as cell content, and not simply scalar values (for the new Excel DataTypes like Exchange Rates); support for loading/saving from/to streams; support for Shapes; and improved support for form objects.

Unfortunately, with just two of us working on this project in our free time, that's a lot of work already prioritised for next year.

[ndench]

Thanks for the response, that's completely understandable. No need to add unnecessary complexity when the simple solution does the same job.

The only downside is that if a user wants to change the conditional formats in excel afterward, they will have to edit many different formats instead of just one.

But that's fine (for my use case, at least). I'll close out this issue as a known limitation.