Xlsx::save throws exception when cell contains '=COUNTA(INDIRECT("A2:A" & ROWS(A:A)))'

[hikaen2]

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?

Xlsx::save does not throw exception when cell contains '=COUNTA(INDIRECT("A2:A" & ROWS(A:A)))'

What is the current behavior?

Xlsx::save throws exception when cell contains '=COUNTA(INDIRECT("A2:A" & ROWS(A:A)))':

$ php run.php 
PHP Fatal error:  Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Sheet1!A1 -> Invalid range: "A2:A1" in /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:390
Stack trace:
#0 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1213): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1282): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCellFormula()
#2 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1134): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell()
#3 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(71): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData()
#4 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(394): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet()
#5 /home/taro/src/test/phpspreadsheet-test/run.php(8): PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()
#6 {main}
  thrown in /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 390

What are the steps to reproduce?

test code: https://github.com/hikaen2/phpspreadsheet-test

in.xlsx:

	A	B
1	=COUNTA(INDIRECT("A2:A" & ROWS(A:A)))	<- count from A2 to end of A

run.php:

<?php
require './vendor/autoload.php';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$book = $reader->load('in.xlsx');

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($book);
$writer->save('out.xlsx');

What features do you think are causing the issue

• [ ] Reader
• [ ] Writer
• [ ] Styles
• [ ] Data Validations
• [x] Formula Calculations
• [ ] Charts
• [ ] AutoFilter
• [ ] Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

only tested in .xlsx.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 1.27.1

PHP: 8.1.2

[hikaen2]

p.s.

detailed stack trace is:

diff --git a/src/PhpSpreadsheet/Calculation/Calculation.php b/src/PhpSpreadsheet/Calculation/Calculation.php
--- a/src/PhpSpreadsheet/Calculation/Calculation.php	(revision ef4e6ef74990239946d3983451a9bbed5ef1be5d)
+++ b/src/PhpSpreadsheet/Calculation/Calculation.php	(date 1676451413402)
@@ -3556,7 +3556,7 @@
                 }
             }
 
-            throw new Exception($e->getMessage());
+            throw new Exception($e);
         }
 
         if ((is_array($result)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {

$ php run.php 
PHP Fatal error:  Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Sheet1!A1 -> PhpOffice\PhpSpreadsheet\Exception: Invalid range: "A2:A1" in /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php:601
Stack trace:
#0 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php(464): PhpOffice\PhpSpreadsheet\Cell\Coordinate::validateRange()
#1 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php(371): PhpOffice\PhpSpreadsheet\Cell\Coordinate::getReferencesForCellBlock()
#2 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5471): PhpOffice\PhpSpreadsheet\Cell\Coordinate::extractAllCellReferencesInRange()
#3 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php(110): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange()
#4 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php(98): PhpOffice\PhpSpreadsheet\Calculation\LookupRef\Indirect::extractRequiredCells()
#5 [internal function]: PhpOffice\PhpSpreadsheet\Calculation\LookupRef\Indirect::INDIRECT()
#6 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5125): call_user_func_array()
#7 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3759): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack()
#8 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3534): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue()
#9 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(373): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue()
#10 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1213): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#11 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1282): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCellFormula()
#12 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1134): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell()
#13 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(71): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData()
#14 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(394): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet()
#15 /home/taro/src/test/phpspreadsheet-test/run.php(8): PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()
#16 {main} in /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:390
Stack trace:
#0 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1213): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1282): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCellFormula()
#2 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1134): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell()
#3 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(71): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData()
#4 /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(394): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet()
#5 /home/taro/src/test/phpspreadsheet-test/run.php(8): PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()
#6 {main}
  thrown in /home/taro/src/test/phpspreadsheet-test/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 390

[MarkBaker]

There are days when I seriously hate everybody that ever worked at Microsoft.

As a temporary workround, you can disable formula calculation before saving; while I try to decide if we actually want to fix this, and to allow formula to contain references to an invalid range.

[hikaen2]

you can disable formula calculation before saving

Thank you for your help. the workaround is working fine:

$writer->setPreCalculateFormulas(false)->save('out.xlsx');