iOfficeAI/OfficeCLI · error · ArgumentException

Invalid categories range: '{explicitValue}'. Expected format

Error message

Invalid categories range: '{explicitValue}'. Expected format: 'Sheet1!A2:A3' or 'A2:A3'.

What it means

Same colon-split validation as the data range, but applied to the explicit categories reference. After '$' stripping the categories ref must split into exactly two parts, else it throws. Fires from the categories-parsing branch when an explicit categories ref is supplied.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Chart.cs:653

        {
            var inline = ChartHelper.ParseCategories(properties) ?? Array.Empty<string>();
            return (inline, null);
        }

        // Cell range form. Strip a leading 'Sheet'! prefix (same approach as dataRange).
        string catSheetName = defaultSheetName;
        string rangePart = trimmed;
        var bangIdx = rangePart.IndexOf('!');
        if (bangIdx >= 0)
        {
            catSheetName = rangePart[..bangIdx].Trim('\'');
            rangePart = rangePart[(bangIdx + 1)..];
        }

        var cleanRange = rangePart.Replace("$", "");
        var rangeParts = cleanRange.Split(':');
        if (rangeParts.Length != 2)
            throw new ArgumentException(
                $"Invalid categories range: '{explicitValue}'. Expected format: 'Sheet1!A2:A3' or 'A2:A3'.");

        var (startCol, startRow) = ParseCellReference(rangeParts[0]);
        var (endCol, endRow) = ParseCellReference(rangeParts[1]);
        var startColIdx = ColumnNameToIndex(startCol);
        var endColIdx = ColumnNameToIndex(endCol);

        var ws = FindWorksheet(catSheetName)
            ?? throw new ArgumentException($"Sheet not found: {catSheetName}");
        var sheetData = GetSheet(ws).GetFirstChild<SheetData>()
            ?? throw new ArgumentException($"Sheet '{catSheetName}' has no data");

        // Read the explicit-range cells directly (rows may differ from dataRange).
        var lookup = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
        foreach (var row in sheetData.Elements<Row>())
        {
            var rowIdx = (int)(row.RowIndex?.Value ?? 0);
            if (rowIdx < startRow || rowIdx > endRow) continue;

View on GitHub (pinned to 1ced45e900)

Solutions

1. Pass a two-cell range 'A2:A3'.
2. Omit the categories property to let the chart use default series-axis categories.
3. Validate the string has exactly one ':' before calling.
4. Trim whitespace from the categories ref.

Example fix

// before
props["categories"] = "Sheet1!A2";  // single cell -> throw

// after
props["categories"] = "Sheet1!A2:A10";
// or omit to use defaults

static bool IsTwoAnchorCats(string r)
{
    var bang = r.IndexOf('!');
    var body = bang >= 0 ? r[(bang+1)..] : r;
    return body.Replace("$","").Split(':') is { Length: 2 };
}

if (!string.IsNullOrEmpty(cats) && !IsTwoAnchorCats(cats))
    throw new ArgumentException($"categories must be a two-anchor range: {cats}");

static bool IsTwoAnchorCats(string r)
{
    var bang = r.IndexOf('!');
    var body = bang >= 0 ? r[(bang+1)..] : r;
    return body.Replace("$","").Split(':').Length == 2;
}

When it happens

Trigger: categories=A2 (single cell); categories=Sheet1!A2:A3:A4 (extra colon); categories built by concatenation that lost an anchor.

Common situations: Categories passed as a single cell ref where a range is required; overflow range from a selection; stray colon from manual editing.

Related errors

• Invalid dataRange: '{dataRange}'. Expected format: 'Sheet1!A
• Invalid range: {range}
• DefinedName '{trimmedInput}' not found
• Sheet not found: {rangeSheetName}
• Sheet '{rangeSheetName}' has no data

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