Exception for invalid range for XLS template export after update from 1.25.0 to 1.26.0

[franz-josef-kaiser]

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?

We are exporting rows of data in an existing XLS, which we use as a template. We search a specific row, insert another row below it and fill it with data.
Until 1.25.0 this worked flawless. With 1.26.0 we are running into an Exception.
It looks like the source is PR #3213.

What is the current behavior?

The writer tries to access an invalid range "XFD2:CZ2".

PhpOffice\PhpSpreadsheet\Exception: Invalid range: "XFD2:CZ2" in PhpOffice\PhpSpreadsheet\Cell\Coordinate::validateRange() (line 600 of /drupal/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php).
PhpOffice\PhpSpreadsheet\Cell\Coordinate::getReferencesForCellBlock('XFD2:CZ2') (Line: 370)
PhpOffice\PhpSpreadsheet\Cell\Coordinate::extractAllCellReferencesInRange('XFD2:CZ2') (Line: 736)
PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->calculateColumnWidths() (Line: 366)
PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCols(Object, Object) (Line: 68)
PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet(Object, Array, ) (Line: 394)
PhpOffice\PhpSpreadsheet\Writer\Xlsx->save('/tmp/eELKFx') (Line: 398)
Drupal\custom_module_name\Table\ExcelProcessor->save(Object) (Line: 256)

What are the steps to reproduce?

As we are working in a quite grown up Drupal module and PHPOffice/PhpSpreadsheet is used for just the writing, it's not really possible to give a reproducible example without building up templates, etc.

$writer = PhpOffice\PhpSpreadsheet\IOFactory\IOFactory::createWriter(Spreadsheet $spreadsheet, 'Xlsx');
// Data extraction, transformation, processing
// Insert data based on an map, where the last col is `CZ`
// …
$writer->save( '/tmp/path' );

I assume that the reason is that I do not have data and therefore no range for a row, while it is validated. We are generating the data during iteration and writing through a cascade of observers and listeners. Range will therefore probably not be available at this point in time and falling back to the MAX fails hard.

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?

It affects .xls. No other formats were tested.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet
1.26.0

PHP

$ container-foo-cmd php -v
PHP 8.0.21 (cli) (built: Jul 13 2022 08:26:22) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.21, Copyright (c) Zend Technologies
    with Zend OPcache v8.0.21, Copyright (c), by Zend Technologies

[oleibman]

Can you test against master? PR #3264, which is part of master but not yet a release, fixes a problem that might be related to yours.

[franz-josef-kaiser]

Hey @oleibman , thanks for the response!
I upgraded to current latest and the fix was somewhere in between 5b6ceea (1.26.0) and 80e270b (current master).

- Upgrading phpoffice/phpspreadsheet (1.25.0 => dev-master 80e270b): Extracting archive

So the fix is confirmed. Do you have an ETL for 1.27.0 or would you mind tagging 644547fb86e2a9d66d0ef1019a1839ccb8882617 as 1.26.1 for the fix so we can pin down the version again? Thanks!

[rvvincelli]

Hey @oleibman , thanks for the response! I upgraded to current latest and the fix was somewhere in between 5b6ceea (1.26.0) and 80e270b (current master).

- Upgrading phpoffice/phpspreadsheet (1.25.0 => dev-master 80e270b): Extracting archive

So the fix is confirmed. Do you have an ETL for 1.27.0 or would you mind tagging 644547fb86e2a9d66d0ef1019a1839ccb8882617 as 1.26.1 for the fix so we can pin down the version again? Thanks!

Yes please, it would be great to have a new tag or release for this one. Thank you! cc @horlathunbhosun.

[MarkBaker]

I want to get a 1.27.0 release this weekend; but I want to include a change for the Cell Iterators to allow returning a null as an alternative to creating a new cell before I'm ready for that release

[franz-josef-kaiser]

@MarkBaker I completely understand this.

Having 1.26.1 would be valid, as the fix is a direct ancestor of the 1.26.0 tagged commit. While it doesn't matter much to me personally (I just rolled back for now), it would be nice from a general point of clean-commits-flow to see such fixes being added in dedicated tags as the commits are already in place. Anyway, thanks for your work on this repo!

[MarkBaker]

Released now as 1.27.0... it isn't purely a bugfix release; there's a few new features included to justify the minor version release