Phpspreadsheet: Error/Exception using reader since upgrading to 1.15 - "Tou must specify a Formula value for a Named Formula"

[mtindall89]

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?

Loading a spreadsheet via reader

What is the current behavior?

PHP Exception

What are the steps to reproduce?

Since upgrading to v 1.15 from v1.5, the following exception happens when trying to load files:
"Tou must specify a Formula value for a Named Formula"

This is traced to NamedFormula.php lines 20-22.

Very similar to Issue #1723

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

// Create new Spreadsheet object
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($filePath);

Which versions of PhpSpreadsheet and PHP are affected?

PHPSpreadsheet - 1.15
PHP v 7.4.12

[mtindall89]

Can anyone address this? I can provide an excel sheet example if needed. I think it may be any excel sheet that has formulas in it.

[syslord2k2]

I got the same error after upgrading.

[sussh]

Here are my logs of a similar error.
`` array(2) {
'error' =>
string(60) "Alsma:: You must specify a Formula value for a Named Formula"
'traces' =>
array(9) {
[0] =>
array(6) {
'file' =>
string(84) "pp55/api/application/classes/PhpSpreadsheet/DefinedName.php"
'line' =>
int(94)
'function' =>
string(11) "__construct"
'class' =>
string(37) "PhpOffice\PhpSpreadsheet\NamedFormula"
'type' =>
string(2) "->"
'args' =>
array(5) {
[0] =>
string(7) "HTML_OS"
[1] =>
NULL
[2] =>
string(1) "0"
[3] =>
bool(false)
[4] =>
NULL
}
}
[1] =>
array(6) {
'file' =>
string(84) "pp55/api/application/classes/PhpSpreadsheet/Reader/Xlsx.php"
'line' =>
int(1404)
'function' =>
string(14) "createInstance"
'class' =>
string(36) "PhpOffice\PhpSpreadsheet\DefinedName"
'type' =>
string(2) "::"
'args' =>
array(4) {
[0] =>
string(7) "HTML_OS"
[1] =>
NULL
[2] =>
string(1) "0"
[3] =>
bool(false)
}
}
[2] =>
array(6) {
'file' =>
string(79) "pp55/api/application/classes/Concept/Craulers/Iowa.php"
'line' =>
int(44)
'function' =>
string(4) "load"
'class' =>
string(36) "PhpOffice\PhpSpreadsheet\Reader\Xlsx"
'type' =>
string(2) "->"
'args' =>
array(1) {
[0] =>
string(74) "pp55/api/tmp/55c19634bb46cf577264f94e8f131003.xls"
}
}
[3] =>
array(6) {
'file' =>
string(79) "pp55/api/application/classes/Concept/Craulers/Iowa.php"
'line' =>
int(21)
'function' =>
string(8) "loadData"
'class' =>
string(27) "Alsma\Concept\Craulers\Iowa"
'type' =>
string(2) "->"
'args' =>
array(1) {
[0] =>
string(92) "https://tax.iowa.gov/sites/default/files/2021-01/Status%20of%20All%20IA%20Juris%20Jan21.xlsx"
}
.....

The logs show which file I'm trying to parse. On the logs you can see where it falls.

God help you, comrades.

[dsuurlant]

We're running into this error as well after upgrading from 1.14.1 to 1.16.0 (and I can confirm this happens in 1.15.0 as well). I'd like to upgrade to the latest version given the security concerns, but this is a blocking issue.

[Flexionn]

I also get the same error after upgrading.

[bivanbi]

While I do not fully understand Xlsx inner logic, I believe it is an if condition that is not working as expected, In class PhpOffice\PhpSpreadsheet\NamedFormula method __construct():

    if (empty($formula)) {
        throw new Exception('You must specify a Formula value for a Named Formula');
    }

I have an Xlsx file where the value of $formula is, as returned by var_dump: string(1) "0" when this condition is triggered and an exception is thrown.

I do not know a string '0' is really an error that should trigger an exception, or the empty($formula) condition is too broad.
I replaced it to:

    if ($formula == '') {
        throw new Exception('You must specify a Formula value for a Named Formula');
    }

and now it seems to load the file without throwing an exception. Again, I do not know if it 'breaks' some formula / calculated values / whatever, or the condition $formula == '' is correct in this situation, so use this information at your own risk.

The 'patched' constructor of the class with the class declaration itself for reference:

class NamedFormula extends DefinedName
{
    /**
     * Create a new Named Formula.
     */
    public function __construct(
        string $name,
        ?Worksheet $worksheet = null,
        ?string $formula = null,
        bool $localOnly = false,
        ?Worksheet $scope = null
    ) {
        if ($formula == '') {
            throw new Exception('You must specify a Formula value for a Named Formula');
        }
        parent::__construct($name, $worksheet, $formula, $localOnly, $scope);
    }

[MarkBaker]

I've done some testing using the latest master branch, and this appears to have been resolved. Spreadsheets no longer error on loading; and the named values can be used successfully in formulae.

I'm planning on releasing the new version over the weekend (or Monday 1st March at the latest)

[MarkBaker]

If you are able to test currently failing code against the latest master, then please do so

[Flexionn]

Latest master fixed it for me! Thanks :)

[dsuurlant]

Can confirm, our tests pass again when using dev-master.

[MarkBaker]

I guess it's going to be tomorrow rather than tonight.... I'm exhausted, and making too many silly mistakes to risk doing a release

[SimplyCorey]

@MarkBaker Anything I can do to help out here? We are seeing the same issue on v1.17.1.

I'd be happy to open up a PR with the above fix. I do not have a detailed knowledge of the inner workings of the project and I am not exactly sure what the above fix will break but I'm happy to help out however is needed.