iOfficeAI/OfficeCLI · error · ArgumentException

Sheet '{catSheetName}' has no data

Error message

Sheet '{catSheetName}' has no data

What it means

Same as error 672 but on the categories branch: after resolving the categories sheet, GetSheet(ws).GetFirstChild<SheetData>() is null, so the categories range cannot be read. Means the categories worksheet has no <sheetData> element at all.

Source

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

            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;
            foreach (var cell in row.Elements<Cell>())
                if (cell.CellReference?.Value != null)
                    lookup[cell.CellReference.Value] = GetCellDisplayValue(cell);
        }

        var labels = new List<string>();
        for (int r = startRow; r <= endRow; r++)
            for (int c = startColIdx; c <= endColIdx; c++)
            {
                var cellRef = $"{IndexToColumnName(c)}{r}";
                lookup.TryGetValue(cellRef, out var v);

View on GitHub (pinned to 1ced45e900)

Solutions

1. Populate the categories sheet before charting.
2. Omit explicit categories to use defaults.
3. Point categories at a sheet known to contain data.

Example fix

// before
handler.AddSheet("Labels");
props["categories"] = "Labels!A2:A10";  // empty sheet -> throw

// after
handler.Set("/Labels/A2", "Jan");
handler.Set("/Labels/A3", "Feb");
props["categories"] = "Labels!A2:A10";

if (!handler.SheetHasData(catsSheetName)) // your helper
    throw new InvalidOperationException($"Populate '{catsSheetName}' before using it for categories.");

When it happens

Trigger: Pointing categories at a freshly added sheet that was never written to; categories ref on a stripped template sheet.

Common situations: Categories helper sheet created but not populated; wrong sheet chosen for categories.

Related errors

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

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