No Support For R1C1 Style Address in INDIRECT Function

[hubobbb]

This is:

- [X ] a bug report


### What is the expected behavior?
fixed

### What is the current behavior?

save file error 
{ "code": 500, "message": "error", "data": { "name": "Exception", "message": "Argument 2 passed to PhpOffice\\PhpSpreadsheet\\Calculation\\LookupRef::INDIRECT() must be an instance of PhpOffice\\PhpSpreadsheet\\Cell\\Cell or null, bool given", "code": 0, "type": "TypeError", "file": "/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef.php", "line": 276, "stack-trace": [ "#0 [internal function]: PhpOffice\\PhpSpreadsheet\\Calculation\\LookupRef::INDIRECT()", "#1 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4712): call_user_func_array()", "#2 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3445): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->processTokenStack()", "#3 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3235): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->_calculateFormulaValue()", "#4 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(257): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->calculateCellValue()", "#5 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1223): PhpOffice\\PhpSpreadsheet\\Cell\\Cell->getCalculatedValue()", "#6 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1293): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet->writeCellFormula()", "#7 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1150): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet->writeCell()", "#8 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(70): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet->writeSheetData()", "#9 /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(274): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet->writeWorksheet()", "#10 /api/modules/tools/Excel.php(576): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx->save()", ..... ] } }

```php
<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$formula2= '=SUM(COUNTIFS(INDIRECT("list!C"&MATCH("teacher_id",INDIRECT("list!R1",FALSE),0),FALSE),">0",INDIRECT("list!C"&MATCH("user_id",INDIRECT("list!R1",FALSE),0),FALSE),">0",INDIRECT("list!C"&MATCH("filed_title",INDIRECT("list!R1",FALSE),0),FALSE),"AAABBB"))';
$spreadsheet ->getActiveSheet()->setCellValueExplicitByColumnAndRow($c, $r, $formula, DataType::TYPE_FORMULA);
$objWrite = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet , 'Xlsx');
$objWrite->save('php://output')；

// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

PHP7.42, PhpSpreadsheet tag 1.17.1

[oleibman]

Second argument of INDIRECT is not yet supported "* @todo Support for the optional a1 parameter introduced in Excel 2010". Since it therefore effectively defaults to TRUE where you want FALSE, PhpSpreadsheet could easily be changed to issue a clearer error message, but it will be much harder to get it to support the missing parameter. Would it be difficult for you to use A1 style references rather than R1C1 style, eliminating the need for the optional parameter?

[oleibman]

Not the same problem as #1993, but same function is involved.

[oleibman]

I believe I have a solution. Your sample code is very complicated and incomplete, in that it contains the formula but not the data. If you were to upload (a subset of) the spreadsheet that is giving you this problem, I could try to incorporate that into my unit tests.

[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.

[oleibman]

Should have been fixed by PR 2004, which was part of Release 18.