iOfficeAI/OfficeCLI · error · ArgumentException

Formula contains an R1C1-style reference (e.g. R2C3, RC[1]).

Error message

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).

What it means

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).

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:185

    /// <summary>
    /// Reject R1C1-style references (R2C3, RC[1], R[-1]C) in a formula string.
    /// The OOXML &lt;f&gt; element is A1-only; writing R1C1 verbatim makes real
    /// Excel refuse the file (0x800A03EC) while schema validation stays green.
    /// Shared by cell formulas and conditional-formatting formulas. Does NOT
    /// do grid-bounds checking (out-of-range A1 refs are tolerated by Excel in
    /// CF formulas — only cell-formula validation adds the bounds check).
    /// </summary>
    internal static void ValidateNoR1C1Reference(string formula)
    {
        if (string.IsNullOrEmpty(formula)) return;
        var stripped = StripFormulaStringLiterals(formula.TrimStart('='));
        // Only unambiguous forms are rejected: bracketed offsets, or
        // R<digits>C<digits> (never a legal A1 token or name). "RC1"/"RC"
        // stay accepted — RC is a real A1 column / legal name.
        if (System.Text.RegularExpressions.Regex.IsMatch(stripped,
                @"(?<![A-Za-z0-9_$])(R\[-?\d+\]C(\[-?\d+\]|\d+)?|R\d*C\[-?\d+\]|R\d+C\d+)(?![A-Za-z0-9_])"))
            throw new ArgumentException(
                "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).");
    }

    // Blank out "..." string literals so cell-like substrings inside them
    // don't trigger reference validation.
    private static string StripFormulaStringLiterals(string trimmed)
    {
        var sb = new System.Text.StringBuilder(trimmed.Length);
        bool inStr = false;
        for (int i = 0; i < trimmed.Length; i++)
        {
            char c = trimmed[i];
            if (c == '"')
            {
                inStr = !inStr;
                sb.Append(' ');
                continue;
            }

View on GitHub (pinned to 1ced45e900)

Solutions

1. Rewrite R1C1 references in A1 form: R2C3 → C2 (row 2, column 3 = C2), RC[1] → the cell one column right.
2. If you have R1C1 from a macro, convert with an R1C1→A1 helper before sending.
3. Use the validationCode to scan formulas before submission.

Example fix

// before
string formula = "=SUM(R[-1]C:R[-1]C[2])"; // R1C1
// after
string formula = "=SUM(B5:D5)"; // A1 equivalent (example row)

// Detect R1C1 before the API call
static bool HasR1C1Ref(string formula)
{
    if (string.IsNullOrEmpty(formula)) return false;
    return System.Text.RegularExpressions.Regex.IsMatch(formula.TrimStart('='),
        @"(?<![A-Za-z0-9_$])(R\[-?\d+\]C(\[-?\d+\]|\d+)?|R\d*C\[-?\d+\]|R\d+C\d+)(?![A-Za-z0-9_])");
}

null

try { /* set formula */ }
catch (ArgumentException ex) when (ex.Message.Contains("R1C1-style reference"))
{ /* convert R1C1 → A1 then retry; do not pass the formula through unchanged */ }

When it happens

Trigger: 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.

Common situations: Copying formulas from VBA macros recorded in R1C1 mode; translating references programmatically and forgetting to convert notation; formulas from tools that emit R1C1.

Related errors

• Formula has a function call with {commas + 1} arguments; Exc
• {context} is {content.Length} characters; Excel's limit is {
• Invalid '{propertyName}' value '{value}'. Expected a non-neg
• Invalid color value: '{value}'. Expected 6-digit hex RGB (e.
• Invalid source range: {sourceRef}

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/759077e58985c79d. Report an issue: GitHub.