iOfficeAI/OfficeCLI · error · ArgumentException

Invalid cell reference: '{cellRef}'

Error message

Invalid cell reference: '{cellRef}'

What it means

When --prop ref= is supplied together with arrayformula=, the ref may be a spill range (e.g. A1:C3). AddCell resolves the cell to the range's top-left for FindOrCreateCell, but first validates that the top-left token matches the `^[A-Z]+\d+$` cell-reference shape. If the colon-split first half is not a valid cell reference (malformed range), this throws rather than writing a corrupt anchor.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs:400

                $"Expected a cell reference (e.g. A2), cell[A2], or row[N].");
        }

        string cellRef;
        // BUG-R36-B1: when --prop arrayformula= is supplied with --prop ref=A1:C3,
        // the range is the spill region, not a single cell address. Detect it and
        // resolve cellRef to the top-left so FindOrCreateCell doesn't reject the
        // colon. The full range is still passed through to arrayformula below via
        // properties["ref"].
        string? arrayFormulaRefRange = null;
        if (properties.ContainsKey("ref"))
        {
            cellRef = properties["ref"];
            if (cellRef.Contains(':') && properties.ContainsKey("arrayformula"))
            {
                arrayFormulaRefRange = cellRef;
                var topLeft = cellRef.Split(':', 2)[0];
                if (!Regex.IsMatch(topLeft, @"^[A-Z]+\d+$", RegexOptions.IgnoreCase))
                    throw new ArgumentException($"Invalid cell reference: '{cellRef}'");
                cellRef = topLeft.ToUpperInvariant();
            }
            if (cellRefFromPath != null && !cellRefFromPath.Equals(cellRef, StringComparison.OrdinalIgnoreCase))
                Console.Error.WriteLine($"warning: path tail '{cellRefFromPath}' does not match --prop ref='{properties["ref"]}'; using ref='{properties["ref"]}'.");
        }
        else if (properties.ContainsKey("address"))
        {
            cellRef = properties["address"];
            if (cellRefFromPath != null && !cellRefFromPath.Equals(cellRef, StringComparison.OrdinalIgnoreCase))
                Console.Error.WriteLine($"warning: path tail '{cellRefFromPath}' does not match --prop address='{cellRef}'; using address='{cellRef}'.");
        }
        else if (cellRefFromPath != null)
        {
            cellRef = cellRefFromPath;
        }
        else
        {
            // BUG-R41-B6: if the parent path supplies a row index (/Sheet1/row[5]),

View on GitHub (pinned to 1ced45e900)

Solutions

1. Provide a well-formed range where both halves are valid A1 cell references, e.g. ref="A1:C3".
2. Validate the range string with the regex ^[A-Z]+\d+:[A-Z]+\d+$ before passing it as ref.
3. If you only need a single-cell array formula, omit the colon and pass a single cell ref.

Example fix

// before
handler.Add("/Sheet1/A1", "cell", null, new() { ["ref"] = left + ":" + right, ["arrayformula"] = "=B1:B3*C1:C3" });
// after
if (Regex.IsMatch(left + ":" + right, @"^[A-Z]+\d+:[A-Z]+\d+$"))
    handler.Add("/Sheet1/A1", "cell", null, new() { ["ref"] = left + ":" + right, ["arrayformula"] = "=B1:B3*C1:C3" });

if (props.TryGetValue("ref", out var r) && r.Contains(':') && props.ContainsKey("arrayformula"))
{
    var topLeft = r.Split(':', 2)[0];
    if (!Regex.IsMatch(topLeft, @"^[A-Z]+\d+$", RegexOptions.IgnoreCase))
        throw new ArgumentException($"Bad arrayformula range: {r}");
}
h.Add(parentPath, "cell", pos, props);

static bool IsValidCellRange(string r) =>
    Regex.IsMatch(r, @"^[A-Z]+\d+:[A-Z]+\d+$", RegexOptions.IgnoreCase);

try { h.Add(parentPath, "cell", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("Invalid cell reference"))
{ /* rebuild the ref range correctly */ }

When it happens

Trigger: Add("/Sheet1/A1","cell",pos,{["ref"]="A1:bad",["arrayformula"]="..."}); ref=":C3" (empty left side); ref="foo:B2"; ref="1A:2B"; any range whose left half fails the cell-ref regex.

Common situations: Building the spill range dynamically and producing a malformed range string; swapping the order of tokens in a range expression; an empty or whitespace left operand after splitting on ':'.

Related errors

• Literal braces '{...}' around a formula create an Excel-reje
• arrayformula=true requires a formula: pass the text directly
• Unrecognized cell parent path segment '{cellSegments[1]}'. E
• --prop shift={shiftVal} not valid for add cell. Use 'right'
• Cannot store '{properties.GetValueOrDefault("value") ?? prop

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