{"record":{"id":"850c5768249e9b50","repo":"iOfficeAI/OfficeCLI","slug":"defined-name-ref-reftext-contains-outside","errorCode":null,"errorMessage":"Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.","messagePattern":"Defined name ref '(.+?)' contains '#' outside a known error literal — not valid formula text\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":641,"sourceCode":"    {\n        // Defined-name bodies are full formulas — validating them properly\n        // is out of scope (functions, unions, cross-part brackets, escaped\n        // apostrophes are all legal). Reject only the empirically fatal\n        // patterns that pass schema validation but make real Excel refuse\n        // the file: doubled/trailing '!' (\"乱码!!!\") and stray '#' outside\n        // the known error literals (\"乱码###\").\n        // Formula-length ceiling (8192) applies to defined-name bodies too.\n        ValidateFormulaLength(refText, \"defined-name ref\");\n        var body = (refText ?? \"\").TrimStart('=').Trim();\n        if (body.Length == 0) return;\n        if (body.Contains('\"')) return; // string literals — leave to Excel\n        // Strip the known error literals first: \"#REF!\" legitimately ends\n        // with '!' and must not trip the dangling-bang check below.\n        var probe = System.Text.RegularExpressions.Regex.Replace(body,\n            @\"#(REF!|N/A|NAME\\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)\",\n            \"\", System.Text.RegularExpressions.RegexOptions.IgnoreCase);\n        if (probe.Contains('#'))\n            throw new ArgumentException(\n                $\"Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.\");\n        if (probe.Contains(\"!!\") || probe.EndsWith(\"!\", StringComparison.Ordinal))\n            throw new ArgumentException(\n                $\"Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).\");\n    }\n\n    /// <summary>Text to store in a numeric cell's &lt;v&gt;: the literal digits\n    /// when already canonical (preserves >15-significant-digit values that\n    /// double cannot represent), else the parsed double re-serialized.</summary>\n    internal static string NormalizeNumericCellText(string text, double parsed)\n        => CanonicalNumericLiteral.IsMatch(text)\n            ? text\n            : parsed.ToString(System.Globalization.CultureInfo.InvariantCulture);\n}\n","sourceCodeStart":623,"sourceCodeEnd":656,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L623-L656","documentation":"A defined-name body contains a '#' that is not part of a recognized Excel error literal (#REF!, #N/A, #NAME?, #DIV/0!, #VALUE!, #NULL!, #NUM!, #SPILL!, #CALC!, #GETTING_DATA). Such text is not valid formula syntax. String literals (bodies containing a double-quote) bypass this check entirely, and known error literals are stripped from the probe first.","triggerScenarios":"Setting a defined-name refersTo whose body, after stripping known error literals and ignoring quoted strings, still contains a '#'. Examples: 'Sheet1!A1#B2', 'Total#2026', a half-typed '#REF'.","commonSituations":"Copy-paste from web text with broken refs; manually typed error literals that miss the trailing '!'; stray hash from a hashtag or anchor; truncated #REF! during edit.","solutions":["Remove stray '#' characters from the body.","Use a complete, recognized error literal (e.g. '#REF!') if an error value is intended.","If the body legitimately contains text with '#', wrap it in a double-quoted string literal to bypass the check."],"exampleFix":"// before\nwb.AddDefinedName(\"MyName\", \"Sheet1!A1#B2\");\n\n// after\nwb.AddDefinedName(\"MyName\", \"Sheet1!A1\");\n// or, if a broken ref is intentional:\nwb.AddDefinedName(\"MyName\", \"#REF!\");","handlingStrategy":"validation","validationCode":"static readonly Regex ErrorLiteral =\n    new(@\"#(REF!|N/A|NAME\\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)\",\n        RegexOptions.IgnoreCase);\nstatic bool DefinedNameHasStrayHash(string body) {\n    if (body.Contains('\"')) return false;\n    var probe = ErrorLiteral.Replace(body, \"\");\n    return probe.Contains('#');\n}","typeGuard":null,"tryCatchPattern":"try { wb.AddDefinedName(name, refersTo); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"contains '#'\")) {\n    wb.AddDefinedName(name, refersTo.Replace(\"#\", string.Empty));\n}","preventionTips":["Build defined-name refs from validated sheet + range parts, not pasted text.","If a literal text value is intended, wrap it in double quotes to bypass the check."],"tags":["excel","defined-name","formula","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}