iOfficeAI/OfficeCLI · error · ArgumentException

Invalid dataRange: '{dataRange}'. Expected format: 'Sheet1!A

Error message

Invalid dataRange: '{dataRange}'. Expected format: 'Sheet1!A1:D5', 'A1:B3', or a defined-name

What it means

After optional defined-name resolution, ParseDataRangeForChart strips '$' and splits the range on ':'; if it is not exactly two parts it throws. This catches ranges with no colon (single cell) or extra colons, and also covers a resolved defined-name whose referent is a single cell or malformed. A well-formed 'Sheet1!$A$1:$B$2' is fine because '$' is removed first.

Source

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

                throw new ArgumentException($"DefinedName '{trimmedInput}' not found");
            dataRange = match.Text!;
        }

        // Parse sheet name and range
        string rangeSheetName = defaultSheetName;
        string rangePart = dataRange.Trim();
        var bangIdx = rangePart.IndexOf('!');
        if (bangIdx >= 0)
        {
            rangeSheetName = rangePart[..bangIdx].Trim('\'');
            rangePart = rangePart[(bangIdx + 1)..];
        }

        // Strip any $ signs for parsing
        var cleanRange = rangePart.Replace("$", "");
        var rangeParts = cleanRange.Split(':');
        if (rangeParts.Length != 2)
            throw new ArgumentException($"Invalid dataRange: '{dataRange}'. Expected format: 'Sheet1!A1:D5', 'A1:B3', or a defined-name");

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

        // Find the worksheet and read cells
        var ws = FindWorksheet(rangeSheetName)
            ?? throw new ArgumentException($"Sheet not found: {rangeSheetName}");
        var sheetData = GetSheet(ws).GetFirstChild<SheetData>();
        if (sheetData == null)
            throw new ArgumentException($"Sheet '{rangeSheetName}' has no data");

        // Build cell lookup. Track value, the originating Cell (for DataType),
        // and a "is blank" flag for cells that exist but carry no value.
        // R20-03: blank-vs-zero distinction is needed for dispBlanksAs=gap.
        // R20-04: DataType drives header detection — only string-typed
        // first-row cells are treated as series names.

View on GitHub (pinned to 1ced45e900)

Solutions

1. Use a real two-dimensional range 'A1:D5'.
2. Fix the defined name so its referent is a proper range.
3. Ensure the range string has exactly one ':' after '$' stripping.
4. Validate the shape before calling AddChart.

Example fix

// before
props["dataRange"] = "Sheet1!A1";  // single cell -> throw

// after
props["dataRange"] = "Sheet1!A1:D5";

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

if (!IsTwoAnchorRange(dataRange))
    throw new ArgumentException($"dataRange must be a two-anchor range: {dataRange}");

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

When it happens

Trigger: dataRange=A1 (single cell, cannot form series); dataRange=A1:B2:C3; a defined name pointing at a single cell like A1.

Common situations: Passing a single cell as chart data; copy-paste introducing an extra colon; defined-name referent collapsed to one cell.

Related errors

• Invalid categories range: '{explicitValue}'. Expected format
• 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/74cd191b9b5760b9. Report an issue: GitHub.