PHPOffice/PhpSpreadsheet · error · CalculationException

$title!$coordinate -> $message

Error message

$title!$coordinate -> $message

What it means

When a formula cell is calculated (getCalculatedValue()), errors raised inside the Calculation engine are rethrown as a CalculationException whose message is prefixed with the worksheet title and cell coordinate (e.g. 'Sheet1!B2 -> ...'), with the original SpreadsheetException chained as getPrevious(). The prefix tells you which cell failed; the chained exception carries the real cause. A few engine errors are intercepted first: inaccessible external workbooks fall back to the cached value, and undefined names/offsets map to the #NAME? Excel error.

Source

Thrown at src/PhpSpreadsheet/Cell/Cell.php:608

                            if ($row !== $newRow || $column !== $newColumn) {
                                $thisworksheet->getCell($newColumn . $newRow)->setValue($resultRow);
                            }
                            StringHelper::stringIncrement($newColumn);
                        }
                    }
                    $thisworksheet->getCell($column . $row);
                    $this->value = $originalValue;
                    $this->dataType = $originalDataType;
                }
            } catch (SpreadsheetException $ex) {
                SharedDate::setExcelCalendar($currentCalendar);
                if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
                    return $this->calculatedValue; // Fallback for calculations referencing external files.
                } elseif (preg_match('/[Uu]ndefined (name|offset: 2|array key 2)/', $ex->getMessage()) === 1) {
                    return ExcelError::NAME();
                }

                throw new CalculationException(
                    $title . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage(),
                    $ex->getCode(),
                    $ex
                );
            }
            SharedDate::setExcelCalendar($currentCalendar);

            if ($result === Functions::NOT_YET_IMPLEMENTED) {
                $this->formulaAttributes = $oldAttributes;

                return $this->calculatedValue; // Fallback if calculation engine does not support the formula.
            }

            return $result;
        } elseif ($this->value instanceof RichText) {
            return $this->value->getPlainText();
        }

View on GitHub (pinned to 65b080eef4)

Solutions

1. Inspect $e->getPrevious()->getMessage() - the root cause precedes the 'Sheet1!B2 ->' location prefix
2. Pre-validate references: sheet titles via $spreadsheet->getSheetNames(), defined names via $spreadsheet->getDefinedNames() before recalculating
3. Wrap per-cell calculation in try/catch so one failure does not abort a batch, logging the location from the message
4. For external workbook links, make the linked files available or replace the formula with its cached value via $cell->setCalculatedValue($cached)

Example fix

// before
$value = $cell->getCalculatedValue(); // CalculationException: Sheet1!B2 -> ...

// after
try {
    $value = $cell->getCalculatedValue();
} catch (CalculationException $e) {
    $root = $e->getPrevious(); // original engine error
    error_log('calc failed at ' . $e->getMessage());
    $value = null; // or a marker like '#ERROR!'
}

$formula = $cell->getValue();
if (is_string($formula) && str_starts_with($formula, '=')) {
    // cheap pre-check: referenced sheet names must exist
    foreach ($spreadsheet->getSheetNames() as $name) { /* ... */ }
}
$value = $cell->getCalculatedValue();

null

try {
    $value = $cell->getCalculatedValue();
} catch (CalculationException $e) {
    // message is 'Sheet1!B2 -> root cause'; getPrevious() holds the engine error
    $this->logger->warning('calc failure: ' . $e->getMessage(), [
        'root' => $e->getPrevious()?->getMessage(),
    ]);
    $value = null; // continue batch instead of aborting
}

When it happens

Trigger: Formulas referencing sheet titles or defined names that do not exist at calculation time (renamed/removed after load); links to external workbooks that cannot be accessed; corrupt or missing cached values after programmatic sheet manipulation; exotic formulas hitting engine edge cases.

Common situations: Loading an xlsx that links other workbooks and recalculating without those files present; renaming/removing sheets while stale formulas still point at old names; batch recalculation over thousands of rows where one broken formula aborts the run.

Related errors

• Invalid R1C1-format Cell Reference
• Invalid R1C1-format Cell Reference, Value out of range
• Invalid A1-format Cell Reference
• Invalid value $calculateDateTimeType for calculated date tim
• Unrecognized space type in tAttrSpace token

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/54212d68c3b5fafc. Report an issue: GitHub.