"!" characters in Worksheet title will make reference formulas crash

[hampusiggstrom]

This is:

- [X] a bug report
- [ ] a feature request
- [X] **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 calculation should be valid. The "!" needs to be added to $invalidCharacters or calculations needs to be updated to support exclamation mark characters in sheet titles.

What is the current behavior?

Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Title!!C1 -> Cell coordinate can not be zero-length string

What are the steps to reproduce?

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Title!');
$sheet->fromArray([1, 2,'=SUM(A1:A2)']);

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

all

[hampusiggstrom]

Relevant code lines seem to be:

PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php

Lines 3462 to 3471 in 4635d39

	if (strpos($operand1Data['reference'], '!') !== false) {
	list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']);
	} else {
	$sheet1 = ($pCellParent !== null) ? $pCellWorksheet->getTitle() : '';
	}
	if (strpos($operand2Data['reference'], '!') !== false) {
	list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']);
	} else {
	$sheet2 = $sheet1;
	}

$operand1Data['reference'] and $operand2Data['reference'] will be calculated to empty strings. Given the sheet title Title!

[PowerKiKi]

Can confirm it will crash even wihtout saving like so:

<?php

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

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Title!a');
$sheet->fromArray([1, 2,'=SUM(A1:A2)']);
$sheet->getCell('C1')->getCalculatedValue();

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[hampusiggstrom]

Bug still present in latest release

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[catseyes250]

I'm having this issue as well.
When trying to reference another worksheet within a formula, I get an exception thrown.

$spreadsheet->getActiveSheet() ->setCellValue('A2', '=SUM("Handset Activity"!C:C)*"Handset Activity"!D:D');

Exception:
Fatal error: Uncaught exception 'PhpOffice\PhpSpreadsheet\Calculation\Exception' with message 'Totals!A2 -> Totals!C2 -> undefined variable '!''

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.