Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

The COUNTIF function crashes PhpSpreadsheet if there is a #REF! error in the range argument. #4381

Open
3 of 11 tasks
Awilen-Bernkastel opened this issue Feb 24, 2025 · 0 comments · May be fixed by #4382
Open
3 of 11 tasks

The COUNTIF function crashes PhpSpreadsheet if there is a #REF! error in the range argument. #4381

Awilen-Bernkastel opened this issue Feb 24, 2025 · 0 comments · May be fixed by #4382

Comments

@Awilen-Bernkastel
Copy link

First of all, let it be made clear this is low-priority, such errors are correctable in-workbook to not make the application crash anymore. This bug report is made only with feature parity in mind.

This is:

What is the expected behavior?

The COUNTIF function fails gracefully and returns "#REF!" too.

What is the current behavior?

Err. 500 with message:

AH01071: Got error 'PHP message: PHP Fatal error:  Uncaught TypeError: PhpOffice\\PhpSpreadsheet\\Calculation\\Statistical\\Conditional::COUNTIF(): Argument #1 ($range) must be of type array, string given in .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php:82
Stack trace:
#0 [internal function]: PhpOffice\\PhpSpreadsheet\\Calculation\\Statistical\\Conditional::COUNTIF()
#1 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5163): call_user_func_array()
#2 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3743): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->processTokenStack()
#3 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3543): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->_calculateFormulaValue()
#4 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(428): PhpOffice\\PhpSpreadsheet\\C...

What are the steps to reproduce?

$filePath='REF in COUNTIF.xlsx';

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);
echo $spreadsheet->getSheet(1)->getCell("A1")->getCalculatedValue();

Here is an Excel file to reproduce this behavior with: REF in COUNTIF.xlsx

There are probably more issues regarding errored-out range inputs in COUNTIF.

What features do you think are causing the issue

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

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

Not tested, but probably.

Which versions of PhpSpreadSheet and PHP are affected?

PhpSpreadSheet: 4.0.0
PHP: 8.1
oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Feb 25, 2025
@oleibman
Handle #REF! As Argument to COUNTIF, AVERAGEIF, SUMIF
990e3ec 
Fix PHPOffice#4381. The report refers to COUNTIF, but AVERAGEIF and SUMIF, which are implemented in the same module, exhibit the same behavior. (There may be others, but, for now, I will just fix those 3.)

Most methods which implement Excel functions should accept mixed arguments, so that they won't throw exceptions when calculated. Of course, MS often doesn't give much guidance as to how unexpected arguments should be handled. It at least seems clear that MS will often substitute #REF! for some arguments, and will return #REF! as the result in such cases. My test indicates that a formula using, say, #DIV/0! in lieu of #REF! will cause Excel to deem the spreadsheet corrupt. So, I think I am just going to deal with #REF! and let other unexpected values continue to throw exceptions.
@oleibman oleibman linked a pull request Feb 25, 2025 that will close this issue
Open
11 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Handle #REF! As Argument to COUNTIF, AVERAGEIF, SUMIF oleibman/PhpSpreadsheet
1 participant
@Awilen-Bernkastel