Global cell names are not handled properly when the title of the sheet contains an apostrophe / single quote.

[Awilen-Bernkastel]

This is:

• 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 global named cells reference the cells in the sheet containing a quote in its title when opened.

What is the current behavior?

The names of the named cells do not reference the cells they were referencing.

What are the steps to reproduce?

Open an Xlsx file containing a named cell in a sheet with a title containing an apostrophe, and call Spreadsheet::getNamedRange("CELLNAME");

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);
$cellrange = $spreadsheet->getNamedRange("CELLNAME");
var_dump($cellrange);

Observe the empty result.

Here is an Excel file to reproduce this behavior with: Problematic sheet name.xlsx

What features do you think are causing the issue

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

The problematic behavior is located in the method Xlsx::loadSpreadsheetFromFile in the file Reader/Xlsx.php. Adding the following code at line 1825 crudely fixes the problem:

$extractedSheetName = str_replace("''", "'", $extractedSheetName);

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

Not to my knowledge. The affected file format is Xlsx.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 3.9.1
PHP: 8.1

[Awilen-Bernkastel]

Update 1

As of Phpspreadsheet 4.0.0, the crude fix in section "What features do you think are causing the issue" doesn't work anymore, as Style::applyFromArray() throws the "Invalid Worksheet for specified Range" exception at line 194.

With the fix, $pRangeWorksheet contains the doubled-up single quote and $this->getActiveSheet()->getTitle() doesn't. There is an issue somewhere else when handling either ranges or sheet names that include doubled-up single quotes (my bet is on global defined name references), and I am yet to pinpoint it.

Without the fix, the defined names still disappear from the sheets including single quotes in their names.

[oleibman]

Please test against PR #4360 if possible.

[Awilen-Bernkastel]

Hi, thank you for your reply.

I have tested the PR. The cell is referenced correctly by name, its value can be modified that way, and it is written properly back to file.

I will open a new issue for the crash I encountered in my "Update 1" reply.