After patching PHPSpreadsheet 4.0.0 with PR #4360, in a sheet with a title containing an apostrophe, applying a style format to a named cell crashes the application.

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

Follow-up issue to issue #4356, PR #4360 is applied.

The application of a number format to a cell in a sheet containing an apostrophe should apply the format properly.

What is the current behavior?

The application crashes with exception 'Invalid Worksheet for specified Range' (Style/Style.php line 194).

What are the steps to reproduce?

$filePath='Problematic sheet name.xlsx';

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);

$cellName = 'CELLNAME';

$namedRange = $spreadsheet->getNamedRange($cellName);
if (!is_null($namedRange)) {
    $namedRange->getWorksheet()->getCell($namedRange->getCellsInRange()[0])->setValue(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(new DateTime(date('Y-m-d'))));
    $sheet = $namedRange->getWorksheet();
    $style = $sheet->getStyle($cellName);
    $numberFormat = $style->getNumberFormat();

    $numberFormat->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);
}

Observe the crash.

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

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: 4.0.0
PHP: 8.1

[oleibman]

The code sample you show above has the following statement:

$style = $sheet->getStyle($cellName);

However, I see nothing in the code which initializes $cellName. Please supply new code with this problem corrected.

[Awilen-Bernkastel]

Thank you for your reply!

My mistake. I have now updated the code sample in the original post.

[oleibman]

I don't think this combination will work:

$cellName = 'CELLNAME';
$style = $sheet->getStyle($cellName);

Might you have meant the following?

$style = $sheet->getStyle($namedRange->getRange());

That also has problems, with or without an apostrophe, and I am researching that. There also appear to be several other areas of code which might have similar problems. I am researching those as well. It might take a couple of days.

[Awilen-Bernkastel]

Well, these calls

$cellName = 'CELLNAME';
$style = $sheet->getStyle($cellName);

have worked for us so far. The call in Worksheet/Worksheet.php at line 1404 $this->setSelectedCell($cellCoordinate); works for defined names as it contains a call to Validation::definedNameToCoordinate().

It may be a series of fortunate unintended consequences of the series of calls in Validation::validateCellOrCellRange that allow the defined name to flow through them unchanged, but the fact remains it has worked for us and applied styles properly on cells that are in named sheets without apostrophes. I understand your point though and will take the appropriate measures to avoid using an undocumented, unintended behavior.

[oleibman]

Live and learn - no need for you to change your code. I thought my testing indicated that it didn't work, but I was up late so probably wasn't paying enough attention.

[oleibman]

Please try 4360 again.

[Awilen-Bernkastel]

Hi! I've tested the new patches in PR #4360 and I can confirm applying a style to the named cells in a sheet containing an apostrophe in its title works!

I believe this issue can now be closed.