PhpSpreadsheet: [Bug] When a cell with an empty string is used in a formula, #VALUE! is returned, while in Excel and other calc software, the empty string is considered as 0 and the formula works.

This is:

- [x] a bug report

What is the expected behavior?

The cell holding an empty string value '' \ "" is treated as a 0

What is the current behavior?

The cell holding the empty string is considered invalid and when the formula cell value is attempted to be calculated #VALUE! is returned.

What are the steps to reproduce?

If you want to reproduce this, try to get the calculated values of the cells by calling getCalculatedValue(). You can also open it on your favorite calc program and check the behavior there and how it does not match. Formula_Test.xlsx

For clarity:

The first row does not work in PHPSpreadsheet The second row does work in PHPSpreadsheet

Both rows work in Excel/Calc

Proposed solution

The culprit of this behavior is this function

https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/Calculation/Calculation.php#L4767-L4803

My proposal is modifying the following if statement

https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/Calculation/Calculation.php#L4777-L4799

To look as follows

        //    Numbers, matrices and booleans can pass straight through, as they're already valid
        if (is_string($operand)) {
            //    We only need special validations for the operand if it is a string
            //    Start by stripping off the quotation marks we use to identify true excel string values internally
            if ($operand > '' && $operand[0] == self::FORMULA_STRING_QUOTE) {
                $operand = self::unwrapResult($operand);
            }

            //    If the string is empty, treat it as a 0, as per Excel's behavior
            $operand = $operand > '' ? $operand : 0; 

            //    If the string is a numeric value, we treat it as a numeric, so no further testing
            if (!is_numeric($operand)) {
                //    If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
                if ($operand[0] == '#') {
                    $stack->push('Value', $operand);
                    $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($operand));

                    return false;
                } elseif (!Shared\StringHelper::convertToNumberIfFraction($operand)) {
                    //    If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
                    $stack->push('Error', '#VALUE!');
                    $this->debugLog->writeDebugLog('Evaluation Result is a ', $this->showTypeDetails('#VALUE!'));

                    return false;
                }
            }
        }

More in detail, It would check if the string value is empty and if it is, set 0 as the operand value, this is done in the following line

            //    If the string is empty, treat it as a 0, as per Excel's behavior
            $operand = $operand > '' ? $operand : 0; 

Furthermore, the following empty string check wouldn’t be necessary.

https://github.com/PHPOffice/PhpSpreadsheet/blob/17af13281b7f10294a5480704b684c5a793a5a0c/src/PhpSpreadsheet/Calculation/Calculation.php#L4786

and would simply be replaced for

                //    If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
                if ($operand[0] == '#') {

@MarkBaker pinging you again, I saw you’re mentioned in the blame of this file as well and wanted your input on this proposed solution. I’m also not sure if this would suffice for the behavior to match Excel’s on all cases, or if this only cover Numeric Binary Operations and other situations would have to also be taken into account, so just wanted to raise that point as well.

Which versions of PhpSpreadsheet and PHP are affected? I tested this on 1.16 (via Laravel Excel) but the problematic code, which I linked here is clearly still present on the latest version.