Conditionals formatting rules applied to columns are removed

[sdespont]

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?

When defining a conditional behavior applied to column, the conditional rules must not be automatically removed by the library.

What is the current behavior?

The conditionals rules applied to columns are removed. All conditionals' rules based on cells number or rows are still existing and working fine.

Template file

After using it

What are the steps to reproduce?

Create an Excel file, create a conditional rule applied to columns. Load it and insert some cells contents, then save the file. The output file no longer contents the conditional rules applied to columns.

<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$spreadsheet = IOFactory::createReader('Xlsx');
$spreadsheet ->setIncludeCharts(true);
$spreadsheet  = $objReader->load($filename);

$spreadsheet  = $phpExcel->getActiveSheet();
$activeSheet->setCellValue('A1', 'ABCDE');

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

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

### 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?


### Which versions of PhpSpreadsheet and PHP are affected?
1.25.2

[MarkBaker]

Unable to replicate

This is the file that I'm reading:

ConditionalFormat_Ranges.xlsx

Before

After

[sdespont]

@MarkBaker Thank you for your tests.

I have spotted the problem, try to execute $activeSheet->insertNewRowBefore(1, 1); before saving. The conditional rules applied to columns will be removed.

[MarkBaker]

Ah, understood; and I can see why that would cause a problem. Excel doesn't store the rule range as a row or column range (despite what it displays) but as a full cell range like A7:XFD8, and that's how we store it internally. Adding a new column will adjust that range to A7:XFE8, which exceeds Excel's column limit, so it discards the entry as invalid when it's saved and re-loaded. So I need to add some range check logic to the ReferenceHelper code that adjusts the range.

[MarkBaker]

That PR #3213 should resolve the issue for Xlsx files; but the Xls Writer has different limits; and it also breaks Xls if you have both a row range and a column range for CF ranges in the same worksheet... I've raised a separate Issue #3185 for Xls

[sdespont]

Thank you so much ! Xlsx is fine for me, I am not using Xls format for years.