{"record":{"id":"d9be199608087613","repo":"PHPOffice/PhpSpreadsheet","slug":"cannot-yet-write-formulae-with-defined-names-to-xl","errorCode":null,"errorMessage":"Cannot yet write formulae with defined names to Xls","messagePattern":"Cannot yet write formulae with defined names to Xls","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Writer\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Writer/Xls/Parser.php","lineNumber":798,"sourceCode":"        }\n\n        if ($this->tryDefinedName) {\n            // @codeCoverageIgnoreStart\n            $nameReference = 1;\n            foreach ($this->spreadsheet->getDefinedNames() as $definedName) {\n                if ($name === $definedName->getName()) {\n                    break;\n                }\n                ++$nameReference;\n            }\n\n            $ptgRef = pack('Cvxx', $this->ptg['ptgName'], $nameReference);\n\n            return $ptgRef;\n            // @codeCoverageIgnoreEnd\n        }\n\n        throw new WriterException('Cannot yet write formulae with defined names to Xls');\n    }\n\n    /**\n     * Look up the REF index that corresponds to an external sheet name\n     * (or range). If it doesn't exist yet add it to the workbook's references\n     * array. It assumes all sheet names given must exist.\n     *\n     * @param string $ext_ref The name of the external reference\n     *\n     * @return string The reference index in packed() format on success\n     */\n    private function getRefIndex(string $ext_ref): string\n    {\n        $ext_ref = Preg::replace([\"/^'/\", \"/'$/\"], ['', ''], $ext_ref); // Remove leading and trailing ' if any.\n        $ext_ref = str_replace('\\'\\'', '\\'', $ext_ref); // Replace escaped '' with '\n\n        // Check if there is a sheet range eg., Sheet1:Sheet2.\n        if (Preg::isMatch('/:/', $ext_ref)) {","sourceCodeStart":780,"sourceCodeEnd":816,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Writer/Xls/Parser.php#L780-L816","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Rewrite affected formulas to reference explicit ranges before saving: replace the name with the range string from the DefinedName object.","Save as Xlsx instead if the consuming system can be upgraded - Xlsx fully supports named references.","Automate the rewrite: iterate defined names and str_replace() them in cell values before invoking the Xls writer."],"exampleFix":"// before\n$sheet->getCell('D2')->setValue('=SUM(TaxTable)');\n(new \\PhpOffice\\PhpSpreadsheet\\Writer\\Xls($spreadsheet))->save('out.xls');\n// WriterException: Cannot yet write formulae with defined names to Xls\n\n// after: expand names to their ranges first\nforeach ($spreadsheet->getDefinedNames() as $name => $dn) {\n    $value = (string) $dn->getValue(); // e.g. 'Sheet1!$A$2:$A$50'\n    foreach ($sheet->getCoordinates() as $coord) {\n        $cell = $sheet->getCell($coord);\n        if (is_string($cell->getValue()) && str_contains($cell->getValue(), $name)) {\n            $cell->setValue(str_replace($name, $value, (string) $cell->getValue()));\n        }\n    }\n}\n(new \\PhpOffice\\PhpSpreadsheet\\Writer\\Xls($spreadsheet))->save('out.xls');","handlingStrategy":"validation","validationCode":"/** Expand defined-name references to explicit ranges before Xls save. */\nfunction expandDefinedNamesForXls(Spreadsheet $spreadsheet): void\n{\n    $names = array_keys($spreadsheet->getDefinedNames());\n    if ($names === []) {\n        return;\n    }\n    usort($names, fn ($a, $b) => strlen($b) <=> strlen($a)); // longest first\n    foreach ($spreadsheet->getAllSheets() as $sheet) {\n        foreach ($sheet->getCoordinates() as $coord) {\n            $cell = $sheet->getCell($coord);\n            $v = $cell->getValue();\n            if (!is_string($v) || !str_contains($v, '=')) {\n                continue;\n            }\n            $expanded = $v;\n            foreach ($names as $n) {\n                $expanded = preg_replace('/\\b' . preg_quote($n, '/') . '\\b/', (string) $spreadsheet->getDefinedName($n)->getValue(), $expanded);\n            }\n            if ($expanded !== $v) {\n                $cell->setValue($expanded);\n            }\n        }\n    }\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Treat 'Xls output' as a formula subset: no defined names, document it for template authors.","Pre-process imported Xlsx templates to expand named references before converting to Xls.","Offer Xlsx output so named ranges survive round-trips unmodified."],"tags":["xls","defined-name","named-range","formula","unsupported","biff8"],"backgroundTag":"unsupported-formula-feature","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}