Uncaught exception raised for relative range in different sheet

[blacknell]

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 whole column reference in a cell to a relative range in another sheet 'data'!$B:$B should evaluate just as it does if in the current sheet

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

The $ symbol is, of course, used in Excel to indicate a relative range

What is the current behavior?

=SUM('data'!$B:$B) throws an uncaught exception Formula Error: An unexpected error occurred from method _parseFormula() vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3710

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 __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$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 relative range 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 relative range 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 relative range 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;

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.2.21 PhpSpreadsheet 1.9.0

[blacknell]

I have tracked this down to the formula parser in src/PhpSpreadsheet/Calculation/Calculation.php:3710 which checks all known cases operator by operator and then complains if the operator is unknown.

The operator being checked is a single quote in =SUM('data'!$B:$B) at position 4 (so the opening quote mark here).

Although I can see the failure I really don't understand from the code the logic of how it is parsing and therefore how to fix

[stale[bot]]

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.

[arnisjuraga]

I had the same problem... Changing =VLOOKUP(I2;Sheet10!$B:$Z;4;FALSE) to =VLOOKUP(I2;Sheet10!$B1:$Z100;4;FALSE)

is a workaround. But I dont know, what's the performance for this and it's hard to have fixed amount of lookup array.

Would it be possible to fix?

[oleibman]

Supplied code now works correctly. Removing stale label, leaving issue closed.