iOfficeAI/OfficeCLI · error · ArgumentException

Invalid {field} '{value}': empty A1 range.

Error message

Invalid {field} '{value}': empty A1 range.

What it means

Thrown by ValidateSqref when the sqref value is null, empty, or whitespace. sqref (sequence-of-references) is the OOXML attribute holding A1 ranges for conditional formatting, data validation, etc. An empty value is invalid — at least one A1 token is required.

Source

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

    // rule cannot land an `INVALID!REF` literal in the sheet — Excel
    // refuses to open the file in that state.
    private static readonly System.Text.RegularExpressions.Regex SqrefShape =
        new(@"^\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?(\s+\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?)*$",
            System.Text.RegularExpressions.RegexOptions.Compiled
            | System.Text.RegularExpressions.RegexOptions.IgnoreCase);

    // 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;

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Provide at least one A1 token (e.g. 'A1' or 'A1:B10').
  2. Skip the CF/DV rule entirely if there is no range to apply it to.
  3. Default the parameter to a meaningful range rather than ''.

Example fix

// before
string sqref = ""; // empty
// after
string sqref = "A1"; // at least one cell
// or skip the rule if no range applies:
if (string.IsNullOrWhiteSpace(sqref)) return;
Defensive patterns

Strategy: validation

Validate before calling

static bool HasSqrefValue(string s) => !string.IsNullOrWhiteSpace(s);

Type guard

null

Try / catch

try { ValidateSqref(value, field); }
catch (ArgumentException ex) when (ex.Message.Contains("empty A1 range"))
{ /* skip the CF/DV rule or default to a sane range */ }

Prevention

When it happens

Trigger: Calling any CF/DV API with sqref='', sqref=null (when the API passes it through), or sqref=' ' (only whitespace). ValidateSqref is the shared entry point for all sqref-bearing fields.

Common situations: Optional parameters defaulting to empty string; form inputs left blank; conditional logic that builds sqref conditionally and ends up empty.

Related errors

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