{"record":{"id":"393a7ef8b1419af7","repo":"iOfficeAI/OfficeCLI","slug":"invalid-field-value-expected-an-a1-referenc","errorCode":null,"errorMessage":"Invalid {field} '{value}': expected an A1 reference (e.g. 'A1', 'A1:D10', 'A:A', '1:3', 'A1 B2:C5').","messagePattern":"Invalid (.+?) '(.+?)': expected an A1 reference \\(e\\.g\\. 'A1', 'A1:D10', 'A:A', '1:3', 'A1 B2:C5'\\)\\.","errorType":"exception","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":45,"sourceCode":"\n    // Whole-column (A:A, B:XFD) and whole-row (1:1, 2:10) tokens are legal\n    // sqref members — dump reads them from real files, so add/replay must\n    // accept them too (a column-wide CF rule could not be round-tripped).\n    private static readonly System.Text.RegularExpressions.Regex SqrefWholeToken =\n        new(@\"^(\\$?[A-Z]+:\\$?[A-Z]+|\\$?[0-9]+:\\$?[0-9]+)$\",\n            System.Text.RegularExpressions.RegexOptions.Compiled\n            | System.Text.RegularExpressions.RegexOptions.IgnoreCase);\n\n    internal static string ValidateSqref(string value, string field)\n    {\n        if (string.IsNullOrWhiteSpace(value))\n            throw new ArgumentException($\"Invalid {field} '{value}': empty A1 range.\");\n        var trimmed = value.Trim();\n        var ok = trimmed\n            .Split(' ', StringSplitOptions.RemoveEmptyEntries)\n            .All(tok => SqrefShape.IsMatch(tok) || SqrefWholeToken.IsMatch(tok));\n        if (!ok)\n            throw new ArgumentException(\n                $\"Invalid {field} '{value}': expected an A1 reference (e.g. 'A1', 'A1:D10', 'A:A', '1:3', 'A1 B2:C5').\");\n        // Shape-valid tokens can still point outside Excel's grid: sqref=\"A0\"\n        // passed here, saved fine, and real Excel refused the whole file\n        // (0x800A03EC) — the same out-of-grid family the drawing-anchor parser\n        // rejects. Bounds-check every cell/row/column component.\n        foreach (var tok in trimmed.Split(' ', StringSplitOptions.RemoveEmptyEntries))\n        {\n            foreach (System.Text.RegularExpressions.Match cm in\n                System.Text.RegularExpressions.Regex.Matches(tok, @\"\\$?([A-Z]+)?\\$?([0-9]+)?\",\n                    System.Text.RegularExpressions.RegexOptions.IgnoreCase))\n            {\n                if (cm.Length == 0) continue;\n                if (cm.Groups[1].Success && cm.Groups[1].Value.Length > 0)\n                {\n                    var colIdx = ColumnNameToIndex(cm.Groups[1].Value.ToUpperInvariant());\n                    if (colIdx < 1 || colIdx > 16384)\n                        throw new ArgumentException(\n                            $\"Invalid {field} '{value}': column '{cm.Groups[1].Value}' is outside Excel's grid (A..XFD).\");","sourceCodeStart":27,"sourceCodeEnd":63,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L27-L63","documentation":"Thrown by ValidateSqref when at least one whitespace-separated token does not match the A1 shape regex (SqrefShape: A1, A1:B10 forms with optional $) or the whole-column/whole-row regex (SqrefWholeToken: A:A, 1:1 forms). The whole value is rejected if any token is malformed.","triggerScenarios":"Calling a CF/DV API with sqref='A1,B2' (comma instead of space), sqref='A1 B2 C' (incomplete token), sqref='Range1' (a named range, not an A1 ref), sqref='A1-B2' (dash instead of colon).","commonSituations":"Using commas to separate ranges (Excel UI often shows them, but OOXML sqref uses spaces); pasting named-range names into an A1-only field; mixing notations.","solutions":["Use SPACES to separate multiple ranges in sqref: 'A1 B2:C5'.","Use ':' for ranges, not '-' or ','.","Keep sqref strictly A1 notation — named ranges belong elsewhere."],"exampleFix":"// before\nstring sqref = \"A1,B2,C3\"; // commas\n// after\nstring sqref = \"A1 B2 C3\"; // spaces\n// ranges:\nstring sqref = \"A1:B10 C1:D10\";","handlingStrategy":"validation","validationCode":"static readonly Regex Shape = new(@\"^\\$?[A-Z]+\\$?[0-9]+(:\\$?[A-Z]+\\$?[0-9]+)?(\\s+\\$?[A-Z]+\\$?[0-9]+(:\\$?[A-Z]+\\$?[0-9]+)?)*$\",\n    RegexOptions.Compiled | RegexOptions.IgnoreCase);\nstatic bool IsValidSqref(string s)\n    => !string.IsNullOrWhiteSpace(s) && s.Trim().Split(' ').All(t => Shape.IsMatch(t));","typeGuard":"null","tryCatchPattern":"try { ValidateSqref(value, field); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"expected an A1 reference\"))\n{ value = value.Replace(',', ' '); /* retry after comma→space */ }","preventionTips":["sqref tokens are space-separated, never comma-separated.","Use ':' for ranges; whole-column/whole-row forms (A:A, 1:1) are accepted.","Named ranges are not valid sqref tokens."],"tags":["excel","conditional-formatting","data-validation","input-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}