[XLSX Reader] Print Area XML parsing regex

[guillaume-ro-fr]

Hello everyone 👋

When I try to load an existing Excel template, the regex used for the print area parsing in the XLSX reader is wrong : the XML does not contain the quote for the worksheet.

Example with an existing Excel template (created in Excel, unzipped) :

xl/workbook.xml :

<definedNames>
...
     <definedName localSheetId="0" name="_xlnm.Print_Area">Synthèse!$A$5:$G$18,Synthèse!$I$6:$O$18</definedName>
...
</definedNames>

If I try to force Excel to place the quotes around the worksheet name, Excel ignore it and do not save the modifications.

Here a dump of the variables :

$rangeSets = [0 => "Synthèse!A5:G18,Synthèse!I6:O18"];
$sheetName = "Synthèse!A5:G18,Synthèse";
$rangeSet = "I6:O18";

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?

The print area must be correctly parsed : the sheet name variable must contain only the sheet name and range set variable must be correct.

What is the current behavior?

The sheet name variable contains the start of the string and the range set variable contain only the end of the chain.

What are the steps to reproduce?

• Create an Excel file with a print area
• Use the script bellow :

<?php

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

// Create new Spreadsheet object
$reader = IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load('template.xlsx');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->setPreCalculateFormulas(false);
$writer->save('new.xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.1
PhpSpreadsheet 1.5.0

[guillaume-ro-fr]

• An explode call on , can not work because of the possibility to create a worksheet title with ,.
• The limit and flags parameters were inverted : http://php.net/manual/en/function.preg-split.php