iOfficeAI/OfficeCLI · error · ArgumentException

Invalid {field} '{value}': expected an A1 reference (e.g. 'A

Error message

Invalid {field} '{value}': expected an A1 reference (e.g. 'A1', 'A1:D10', 'A:A', '1:3', 'A1 B2:C5').

What it means

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.

Source

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

    // Whole-column (A:A, B:XFD) and whole-row (1:1, 2:10) tokens are legal
    // sqref members — dump reads them from real files, so add/replay must
    // accept them too (a column-wide CF rule could not be round-tripped).
    private static readonly System.Text.RegularExpressions.Regex SqrefWholeToken =
        new(@"^(\$?[A-Z]+:\$?[A-Z]+|\$?[0-9]+:\$?[0-9]+)$",
            System.Text.RegularExpressions.RegexOptions.Compiled
            | System.Text.RegularExpressions.RegexOptions.IgnoreCase);

    internal static string ValidateSqref(string value, string field)
    {
        if (string.IsNullOrWhiteSpace(value))
            throw new ArgumentException($"Invalid {field} '{value}': empty A1 range.");
        var trimmed = value.Trim();
        var ok = trimmed
            .Split(' ', StringSplitOptions.RemoveEmptyEntries)
            .All(tok => SqrefShape.IsMatch(tok) || SqrefWholeToken.IsMatch(tok));
        if (!ok)
            throw new ArgumentException(
                $"Invalid {field} '{value}': expected an A1 reference (e.g. 'A1', 'A1:D10', 'A:A', '1:3', 'A1 B2:C5').");
        // Shape-valid tokens can still point outside Excel's grid: sqref="A0"
        // passed here, saved fine, and real Excel refused the whole file
        // (0x800A03EC) — the same out-of-grid family the drawing-anchor parser
        // rejects. Bounds-check every cell/row/column component.
        foreach (var tok in trimmed.Split(' ', StringSplitOptions.RemoveEmptyEntries))
        {
            foreach (System.Text.RegularExpressions.Match cm in
                System.Text.RegularExpressions.Regex.Matches(tok, @"\$?([A-Z]+)?\$?([0-9]+)?",
                    System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            {
                if (cm.Length == 0) continue;
                if (cm.Groups[1].Success && cm.Groups[1].Value.Length > 0)
                {
                    var colIdx = ColumnNameToIndex(cm.Groups[1].Value.ToUpperInvariant());
                    if (colIdx < 1 || colIdx > 16384)
                        throw new ArgumentException(
                            $"Invalid {field} '{value}': column '{cm.Groups[1].Value}' is outside Excel's grid (A..XFD).");

View on GitHub (pinned to 1ced45e900)

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.

Example fix

// before
string sqref = "A1,B2,C3"; // commas
// after
string sqref = "A1 B2 C3"; // spaces
// ranges:
string sqref = "A1:B10 C1:D10";

Defensive patterns

Strategy: validation

Validate before calling

static readonly Regex Shape = new(@"^\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?(\s+\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?)*$",
    RegexOptions.Compiled | RegexOptions.IgnoreCase);
static bool IsValidSqref(string s)
    => !string.IsNullOrWhiteSpace(s) && s.Trim().Split(' ').All(t => Shape.IsMatch(t));

Type guard

null

Try / catch

try { ValidateSqref(value, field); }
catch (ArgumentException ex) when (ex.Message.Contains("expected an A1 reference"))
{ value = value.Replace(',', ' '); /* retry after comma→space */ }

Prevention

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.

When it happens

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

Common situations: 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.

Related errors

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