Unary operators on ranges no longer allow coercion of non-numeric array elements

[martinf55]

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?

Unary operators on arrays used to allow coercible non-numeric array elements such as boolean. This is the case in the replaced Shared/JAMA code, mirroring behaviour in Excel.

What is the current behavior?

Any array elements which are non-numeric are replaced with a #VALUE error.

The simplified code which replaced JAMA in #3260 requires all array elements to be is_numeric. The old behaviour was similar to validateBinaryOperand() in that it assumed non-strings would coerce to numeric and only did further validation on strings.

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';

$inputFileName = './UnaryOperatorIssue.xlsx';

/** Load $inputFileName to a Spreadsheet object **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

$spreadsheet->getCalculationEngine()->getDebugLog()->setWriteDebugLog(true);

$blueResultCell = $spreadsheet->getActiveSheet()->getCell('E3');
$redResultCell = $spreadsheet->getActiveSheet()->getCell('F3');

$blueResultValueOld = $blueResultCell->getOldCalculatedValue();
$redResultValueOld = $redResultCell->getOldCalculatedValue();

$blueResultValue = $blueResultCell->getCalculatedValue(false);
$redResultValue = $redResultCell->getCalculatedValue(false);

print_r("Blue matches: Old {$blueResultValueOld} vs Calculated {$blueResultValue}\n");
print_r("Red matches:  Old {$redResultValueOld} vs Calculated {$redResultValue}\n");

$log = $spreadsheet->getCalculationEngine()->getDebugLog()->getLog();

print_r($log);

Running this produces:

Blue matches: Old 3 vs Calculated 0
Red matches:  Old 2 vs Calculated 0
(log omitted)

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?

All formats.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.26
PHP 8.1

UnaryOperatorIssue.xlsx

[oleibman]

Thank you for the file and the excellent documentation. I believe your suggestion to add an is_bool test is sufficient. I should have a PR tonight.