Cyclic reference with sharepoint document causes infinite loops

[HMartusewiczVox]

Client has managed to timeout our system with their XLSX file. I was able to recreate the error and I am attaching a minimal file that causes it. It has two cells:

• A1 creates a cycle =A1:A2
• A2 uses external document ='https://example.com/test.xlsx'#$Test.A2

cyclic-sharepoint.xlsx

The stack goes like this:

Error: Xdebug has detected a possible infinite loop, and aborted your script with a stack depth of '512' frames in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Validations.php:116
Stack trace:
#0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Validations.php(116): strtoupper('...')
#1 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(1422): PhpOffice\PhpSpreadsheet\Worksheet\Validations::definedNameToCoordinate('...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet))
#2 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(890): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->getStyle('...')
#3 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(656): PhpOffice\PhpSpreadsheet\Cell\Cell->getStyle()
#4 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(503): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('...', '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#5 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(460): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#6 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(2590): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#7 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(2048): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange('...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#8 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(708): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#9 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(503): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('...', '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#10 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(460): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#11 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(2611): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#12 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(1835): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange('...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#13 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(708): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#14 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(503): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('...', '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#15 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(460): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#16 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(2611): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#17 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(1835): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange('...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#18 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(708): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#19 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(503): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('...', '...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#20 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(460): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)

[HMartusewiczVox]

Btw, I just want this to throw an exception, I don't care about external documents. I just can't catch an infinite loop->timeout like an exception.
Also it seems to me that cycle detection is done way too late, when formula is A1:A2, why A2 is even computed? Does excel allow like recursion with base cases?

[oleibman]

I am not familiar with the cyclic formula logic. In Calculation/CyclicTest, we catch such a loop quickly with the following statement:

Calculation::getInstance($spreadsheet)->cyclicFormulaCount = 0;

And that works for your spreadsheet too:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

        $infile = 'issue.4791.xlsx';
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($infile);
        $spreadsheet->getCalculationEngine()->cyclicFormulaCount = 0;
        $writer = new XlsxWriter($spreadsheet);
        $outfile = 'issue.4791.3.xlsx';
        $writer->save($outfile);
        echo "saved $outfile\n";

This receives PhpOffice\PhpSpreadsheet\Calculation\Exception: Cyclic Reference which can be caught. You can use this as a workaround for now. Because I am unfamiliar with the logic, I don't know why cyclicFormulaCount isn't automatically set to 0 whenever a calculation begins, and I have no idea what might break if I change the logic to do so. I will need time to think about it.

[oleibman]

12 errors (all Cyclic-related) and 1 failure in the test suite when making that change. I think the errors happen mostly to cross-worksheet formulas, INDIRECT function, and references to whole columns/rows. It looks like it could take a long time to figure out what needs to be done in the general case.

[HMartusewiczVox]

Thank you, I'll use that workaround. I'm not really familiar with advanced formulas, but we should be fine, as we only need to allow formulas that compute like net/gross price