PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Writer\Exception

Cannot yet write formulae with defined names to Xls

Error message

Cannot yet write formulae with defined names to Xls

What it means

The Xls writer historically cannot serialize formulas that reference defined names (named ranges/named formulas). convertDefinedName() contains an experimental serialization path guarded by $tryDefinedName, but that flag is protected, defaults to false, and has no public setter - so by default ANY formula referencing a defined name hits the unconditional throw. This is a documented limitation of the BIFF8 writer, not a data problem.

Source

Thrown at src/PhpSpreadsheet/Writer/Xls/Parser.php:798

        }

        if ($this->tryDefinedName) {
            // @codeCoverageIgnoreStart
            $nameReference = 1;
            foreach ($this->spreadsheet->getDefinedNames() as $definedName) {
                if ($name === $definedName->getName()) {
                    break;
                }
                ++$nameReference;
            }

            $ptgRef = pack('Cvxx', $this->ptg['ptgName'], $nameReference);

            return $ptgRef;
            // @codeCoverageIgnoreEnd
        }

        throw new WriterException('Cannot yet write formulae with defined names to Xls');
    }

    /**
     * Look up the REF index that corresponds to an external sheet name
     * (or range). If it doesn't exist yet add it to the workbook's references
     * array. It assumes all sheet names given must exist.
     *
     * @param string $ext_ref The name of the external reference
     *
     * @return string The reference index in packed() format on success
     */
    private function getRefIndex(string $ext_ref): string
    {
        $ext_ref = Preg::replace(["/^'/", "/'$/"], ['', ''], $ext_ref); // Remove leading and trailing ' if any.
        $ext_ref = str_replace('\'\'', '\'', $ext_ref); // Replace escaped '' with '

        // Check if there is a sheet range eg., Sheet1:Sheet2.
        if (Preg::isMatch('/:/', $ext_ref)) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Rewrite affected formulas to reference explicit ranges before saving: replace the name with the range string from the DefinedName object.
2. Save as Xlsx instead if the consuming system can be upgraded - Xlsx fully supports named references.
3. Automate the rewrite: iterate defined names and str_replace() them in cell values before invoking the Xls writer.

Example fix

// before
$sheet->getCell('D2')->setValue('=SUM(TaxTable)');
(new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet))->save('out.xls');
// WriterException: Cannot yet write formulae with defined names to Xls

// after: expand names to their ranges first
foreach ($spreadsheet->getDefinedNames() as $name => $dn) {
    $value = (string) $dn->getValue(); // e.g. 'Sheet1!$A$2:$A$50'
    foreach ($sheet->getCoordinates() as $coord) {
        $cell = $sheet->getCell($coord);
        if (is_string($cell->getValue()) && str_contains($cell->getValue(), $name)) {
            $cell->setValue(str_replace($name, $value, (string) $cell->getValue()));
        }
    }
}
(new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet))->save('out.xls');

/** Expand defined-name references to explicit ranges before Xls save. */
function expandDefinedNamesForXls(Spreadsheet $spreadsheet): void
{
    $names = array_keys($spreadsheet->getDefinedNames());
    if ($names === []) {
        return;
    }
    usort($names, fn ($a, $b) => strlen($b) <=> strlen($a)); // longest first
    foreach ($spreadsheet->getAllSheets() as $sheet) {
        foreach ($sheet->getCoordinates() as $coord) {
            $cell = $sheet->getCell($coord);
            $v = $cell->getValue();
            if (!is_string($v) || !str_contains($v, '=')) {
                continue;
            }
            $expanded = $v;
            foreach ($names as $n) {
                $expanded = preg_replace('/\b' . preg_quote($n, '/') . '\b/', (string) $spreadsheet->getDefinedName($n)->getValue(), $expanded);
            }
            if ($expanded !== $v) {
                $cell->setValue($expanded);
            }
        }
    }
}

When it happens

Trigger: A cell containing something like =SUM(TaxTable) or =Revenue*VATRate where TaxTable/Revenue/VATRate are defined names on the workbook, saved with (new Xls($spreadsheet))->save(). It fires during save() while converting that cell's formula to ptg tokens.

Common situations: Loading an Xlsx template that uses named ranges (common in finance templates) and converting it to legacy Xls for a downstream system; testing with plain formulas passes, then the first named-range template breaks production conversion.

Related errors

• Defined Named {$definedName} is a formula, not a range or ce
• Unknown token $token
• Unknown range separator
• Unknown sheet name $ext_ref in formula
• ')' token expected.

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