Referencing absolute column range in different sheet fails

[blacknell]

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?

Works in Excel fine - would expect it to add the values in the range

=SUM($B:$B) works
=SUM('data'!$B1:$B3) works
=SUM('data'!B:B) works
=SUM('data'!$B:$B) should work but fails

What is the current behavior?

Exception thown from Calculation.php internalParseFormula() as it doesn't like the $opCharacter ' and fails at line 4180

The absolute column reference in another sheet fails (not just here but also if a named range contains such a range definition)

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require "vendor/autoload.php";

ini_set('memory_limit', '3072M');
ini_set('display_errors', 1);
ini_set('log_errors', true);

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$calculationEngine = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance($spreadsheet);
$debugLog = $calculationEngine->getDebugLog();
$calculationEngine->flushInstance();
$debugLog->setWriteDebugLog(true);
$debugLog->setEchoDebugLog(true);

// some test data
$dataSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'data');
$spreadsheet->addSheet($dataSheet, 0);
$dataSheet->setCellValue('A1','ABC');
$dataSheet->setCellValue('A2','DEF');
$dataSheet->setCellValue('A3','GHI');
$dataSheet->setCellValue('B1',1.0);
$dataSheet->setCellValue('B2',2.0);
$dataSheet->setCellValue('B3',4.0);

$calcSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'summary');
$spreadsheet->addSheet($calcSheet, 1);

echo PHP_EOL."whole column range with $ lock within same sheet works".PHP_EOL;
$dataSheet->setCellValue('A1', "=SUM(\$B:\$B)");
echo $dataSheet->getCell('A1')->getValue() . PHP_EOL;
echo $dataSheet->getCell('A1')->getCalculatedValue() . PHP_EOL;

echo PHP_EOL."specific cell range with $ lock in different sheet works".PHP_EOL;
$calcSheet->setCellValue('A1', "=SUM('data'!\$B1:\$B3)");
echo $calcSheet->getCell('A1')->getValue() . PHP_EOL;
echo $calcSheet->getCell('A1')->getCalculatedValue() . PHP_EOL;

echo PHP_EOL."whole column range in different sheet works".PHP_EOL;
$calcSheet->setCellValue('A3', "=SUM('data'!B:B)");
echo $calcSheet->getCell('A3')->getValue() . PHP_EOL;
echo $calcSheet->getCell('A3')->getCalculatedValue() . PHP_EOL;

echo PHP_EOL."whole column range with $ lock in different sheet FAILS".PHP_EOL;
$calcSheet->setCellValue('A2', "=SUM('data'!\$B:\$B)");
echo $calcSheet->getCell('A2')->getValue() . PHP_EOL;
echo $calcSheet->getCell('A2')->getCalculatedValue() . PHP_EOL;

Output for the fail scenario is as follows:-

whole column range with $ lock in different sheet FAILS
=SUM('data'!$B:$B)
Testing cache value for cell summary!A2
Evaluating formula for cell summary!A2
Formula for cell summary!A2 is SUM('data'!$B:$B)

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: summary!A2 -> Formula Error: An unexpected error occurred in /Users/Paul/Documents/GitHub/investments/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:272
Stack trace:
#0 /Users/Paul/Documents/GitHub/investments/rangebug.php(50): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 {main}
  thrown in /Users/Paul/Documents/GitHub/investments/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 272

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.3.8
This affected PhpSpreadsheet 1.8.2 but I was able to work around it in formulae. Since 1.15.0, named ranges also now expose problem.

[jamesfraser]

This is still an issue in PhpSpreadsheet 1.16.0

I encountered this with a named range containing a whole column range with $ lock.
The named range and formula in my scenario are within the same sheet.

[gbradley]

I'm also experiencing this same problem in 1.17.

[MarkBaker]

The PHPSpreadsheet Calculation engine was never originally designed to handle row or column ranges, just cell ranges, and it was more by chance that they could sometimes be used in formulae; which is why I've always recommended changing formulae to use cell ranges instead.

The PR that I've just submitted now provides that support for row/column ranges.

Note that 3d range references are NOT supported by these changes.

[jamesfraser]

The PHPSpreadsheet Calculation engine was never originally designed to handle row or column ranges, just cell ranges, and it was more by chance that they could sometimes be used in formulae; which is why I've always recommended changing formulae to use cell ranges instead.

The PR that I've just submitted now provides that support for row/column ranges.

Note that 3d range references are NOT supported by these changes.

Thanks Mark - appreciate your efforts on this one!