iOfficeAI/OfficeCLI · error · ArgumentException

Invalid sparkline range '{range}'. Expected an A1 range like

Error message

Invalid sparkline range '{range}'. Expected an A1 range like A1:E1 (optionally sheet-qualified).

What it means

The sparkline range string is not a valid A1 range. After stripping an optional sheet qualifier and $ signs, each colon-separated token must match ^([A-Za-z]{1,3}\d+|[A-Za-z]{1,3}|\d+)$ — a cell (col+row), a column-only, or a row-only token. Arbitrary strings written into <xne:f> make real Excel refuse the file while schema validation stays green.

Source

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

    private static readonly System.Text.RegularExpressions.Regex CanonicalNumericLiteral =
        new(@"^-?(\d+\.?\d*|\.\d+)([eE][+-]?\d+)?$", System.Text.RegularExpressions.RegexOptions.Compiled);

    internal static bool IsCanonicalNumericText(string text) => CanonicalNumericLiteral.IsMatch(text);

    /// <summary>Shape-check a sparkline data range ("A1:E1" or
    /// "Sheet1!A1:E1", whole rows/cols allowed). Arbitrary strings written
    /// into &lt;xne:f&gt; make real Excel refuse the file while schema
    /// validation stays green.</summary>
    internal static void ValidateSparklineRange(string range)
    {
        var r = (range ?? "").Trim();
        var bang = r.LastIndexOf('!');
        if (bang >= 0) r = r[(bang + 1)..];
        r = r.Replace("$", "");
        var ok = r.Length > 0 && r.Split(':').All(tok =>
            System.Text.RegularExpressions.Regex.IsMatch(tok.Trim(), @"^([A-Za-z]{1,3}\d+|[A-Za-z]{1,3}|\d+)$"));
        if (!ok)
            throw new ArgumentException(
                $"Invalid sparkline range '{range}'. Expected an A1 range like A1:E1 (optionally sheet-qualified).");
        // Grid-bounds check, same family as ValidateSqref: shape-valid B0 or
        // A99999999 landed verbatim in <xne:f>. Excel tolerates rather than
        // rejects these, but the sparkline is silently broken — tighten to
        // the severity every other cell-ref entry point now enforces.
        foreach (var tok in r.Split(':'))
        {
            var tm = System.Text.RegularExpressions.Regex.Match(tok.Trim(),
                @"^([A-Za-z]{1,3})?(\d+)?$");
            if (tm.Groups[1].Success && tm.Groups[1].Value.Length > 0)
            {
                var colIdx = ColumnNameToIndex(tm.Groups[1].Value.ToUpperInvariant());
                if (colIdx < 1 || colIdx > 16384)
                    throw new ArgumentException(
                        $"Invalid sparkline range '{range}': column '{tm.Groups[1].Value}' is outside Excel's grid (A..XFD).");
            }
            if (tm.Groups[2].Success && tm.Groups[2].Value.Length > 0
                && (!long.TryParse(tm.Groups[2].Value, out var rowN) || rowN < 1 || rowN > 1048576))

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Pass a valid A1 range such as 'A1:E1' (optionally sheet-qualified like 'Sheet1!A1:E1').
  2. Use whole-row ('1:5') or whole-column ('A:E') forms if appropriate.
  3. Build the range from cell indices to avoid malformed tokens.

Example fix

// before
sheet.SetSparkline("F1", location: "F1", data: "A1 to E1");

// after
sheet.SetSparkline("F1", location: "F1", data: "A1:E1");
Defensive patterns

Strategy: validation

Validate before calling

static readonly Regex SparklineTok =
    new(@"^([A-Za-z]{1,3}\d+|[A-Za-z]{1,3}|\d+)$");
static bool IsValidSparklineRange(string range) {
    var r = (range ?? "").Trim();
    var bang = r.LastIndexOf('!');
    if (bang >= 0) r = r[(bang + 1)..];
    r = r.Replace("$", "");
    return r.Length > 0 && r.Split(':').All(t => SparklineTok.IsMatch(t.Trim()));
}

Try / catch

try { sheet.SetSparkline(loc, data); }
catch (ArgumentException ex) when (ex.Message.Contains("Expected an A1 range")) {
    // re-derive the range from cell indices
}

Prevention

When it happens

Trigger: Passing a sparkline range like 'foo', 'A1:B', '' (empty), 'A1:B2:C3', or '1:2:3'. The All(...) check over split tokens fails.

Common situations: Wrong input format from a UI; missing range; passing a named range where an A1 range is expected; localized separators.

Related errors

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