{"record":{"id":"759077e58985c79d","repo":"iOfficeAI/OfficeCLI","slug":"formula-contains-an-r1c1-style-reference-e-g-r2c","errorCode":null,"errorMessage":"Formula contains an R1C1-style reference (e.g. R2C3, RC[1]). OOXML stores formulas in A1 notation only — rewrite the reference in A1 form (e.g. C2, $B$3).","messagePattern":"Formula contains an R1C1-style reference \\(e\\.g\\. R2C3, RC\\[1\\]\\)\\. OOXML stores formulas in A1 notation only — rewrite the reference in A1 form \\(e\\.g\\. C2, \\$B\\$3\\)\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":185,"sourceCode":"\n    /// <summary>\n    /// Reject R1C1-style references (R2C3, RC[1], R[-1]C) in a formula string.\n    /// The OOXML &lt;f&gt; element is A1-only; writing R1C1 verbatim makes real\n    /// Excel refuse the file (0x800A03EC) while schema validation stays green.\n    /// Shared by cell formulas and conditional-formatting formulas. Does NOT\n    /// do grid-bounds checking (out-of-range A1 refs are tolerated by Excel in\n    /// CF formulas — only cell-formula validation adds the bounds check).\n    /// </summary>\n    internal static void ValidateNoR1C1Reference(string formula)\n    {\n        if (string.IsNullOrEmpty(formula)) return;\n        var stripped = StripFormulaStringLiterals(formula.TrimStart('='));\n        // Only unambiguous forms are rejected: bracketed offsets, or\n        // R<digits>C<digits> (never a legal A1 token or name). \"RC1\"/\"RC\"\n        // stay accepted — RC is a real A1 column / legal name.\n        if (System.Text.RegularExpressions.Regex.IsMatch(stripped,\n                @\"(?<![A-Za-z0-9_$])(R\\[-?\\d+\\]C(\\[-?\\d+\\]|\\d+)?|R\\d*C\\[-?\\d+\\]|R\\d+C\\d+)(?![A-Za-z0-9_])\"))\n            throw new ArgumentException(\n                \"Formula contains an R1C1-style reference (e.g. R2C3, RC[1]). OOXML stores formulas in A1 notation only — rewrite the reference in A1 form (e.g. C2, $B$3).\");\n    }\n\n    // Blank out \"...\" string literals so cell-like substrings inside them\n    // don't trigger reference validation.\n    private static string StripFormulaStringLiterals(string trimmed)\n    {\n        var sb = new System.Text.StringBuilder(trimmed.Length);\n        bool inStr = false;\n        for (int i = 0; i < trimmed.Length; i++)\n        {\n            char c = trimmed[i];\n            if (c == '\"')\n            {\n                inStr = !inStr;\n                sb.Append(' ');\n                continue;\n            }","sourceCodeStart":167,"sourceCodeEnd":203,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L167-L203","documentation":"Thrown by ValidateNoR1C1Reference when a formula contains an unambiguous R1C1-style reference: bracketed offsets (R[-2]C[1], RC[-1]) or R<digits>C<digits> (R2C3). OOXML stores formulas in A1 notation only — Excel refuses files with R1C1 references. The validator rejects only unambiguous forms; 'RC'/'RC1' stay accepted (RC is a legal A1 column/name).","triggerScenarios":"Writing a cell formula, defined-name refersTo, or CF expression with R1C1 notation: '=R[-1]C', '=SUM(R2C3:R5C3)', '=RC[1]'. The check runs on all formula-bearing inputs.","commonSituations":"Copying formulas from VBA macros recorded in R1C1 mode; translating references programmatically and forgetting to convert notation; formulas from tools that emit R1C1.","solutions":["Rewrite R1C1 references in A1 form: R2C3 → C2 (row 2, column 3 = C2), RC[1] → the cell one column right.","If you have R1C1 from a macro, convert with an R1C1→A1 helper before sending.","Use the validationCode to scan formulas before submission."],"exampleFix":"// before\nstring formula = \"=SUM(R[-1]C:R[-1]C[2])\"; // R1C1\n// after\nstring formula = \"=SUM(B5:D5)\"; // A1 equivalent (example row)","handlingStrategy":"validation","validationCode":"// Detect R1C1 before the API call\nstatic bool HasR1C1Ref(string formula)\n{\n    if (string.IsNullOrEmpty(formula)) return false;\n    return System.Text.RegularExpressions.Regex.IsMatch(formula.TrimStart('='),\n        @\"(?<![A-Za-z0-9_$])(R\\[-?\\d+\\]C(\\[-?\\d+\\]|\\d+)?|R\\d*C\\[-?\\d+\\]|R\\d+C\\d+)(?![A-Za-z0-9_])\");\n}","typeGuard":"null","tryCatchPattern":"try { /* set formula */ }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"R1C1-style reference\"))\n{ /* convert R1C1 → A1 then retry; do not pass the formula through unchanged */ }","preventionTips":["OOXML is A1-only — always store formulas in A1 notation.","R1C1 from VBA must be converted before persistence.","'RC' alone is a legal A1 column; only bracketed/R\\d+C\\d+ forms are rejected."],"tags":["excel","formulas","r1c1","input-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}