iOfficeAI/OfficeCLI · error · ArgumentException

Property 'ref' or 'range' is required for table

Error message

Property 'ref' or 'range' is required for table

What it means

Thrown by AddTable when neither a 'ref' nor a 'range' property is supplied. The table's cell range is mandatory because it defines the <table reference> attribute Excel requires; without it the part would be invalid. The handler accepts either key ('ref' or 'range') for convenience but throws if both are absent.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:1067

                        : fop == FilterOperatorValues.LessThan ? "lt"
                        : fop == FilterOperatorValues.LessThanOrEqual ? "lte"
                        : "equals";
                    node.Format[prefix + op] = val;
                }
            }
        }
    }

    private string AddTable(string parentPath, string type, InsertPosition? position, Dictionary<string, string> properties)
    {
        var index = position?.Index;
        var tblSegments = parentPath.TrimStart('/').Split('/', 2);
        var tblSheetName = tblSegments[0];
        var tblWorksheet = FindWorksheet(tblSheetName)
            ?? throw new ArgumentException($"Sheet not found: {tblSheetName}");

        var rangeRef = (properties.GetValueOrDefault("ref") ?? properties.GetValueOrDefault("range")
            ?? throw new ArgumentException("Property 'ref' or 'range' is required for table")).ToUpperInvariant();

        // T4 — reject a new table whose ref overlaps any existing table on
        // the same sheet. Excel silently corrupts the file otherwise.
        foreach (var existingTdp in tblWorksheet.TableDefinitionParts)
        {
            var existing = existingTdp.Table;
            if (existing?.Reference?.Value is not string existingRef) continue;
            if (RangesOverlap(rangeRef, existingRef))
                throw new ArgumentException(
                    $"Table ref overlaps existing table '{existing.Name?.Value ?? existing.DisplayName?.Value}' ({existingRef})");
        }


        var existingTableIds = _doc.WorkbookPart!.WorksheetParts
            .SelectMany(wp => wp.TableDefinitionParts)
            .Select(tdp => tdp.Table?.Id?.Value ?? 0);
        var tableId = existingTableIds.Any() ? existingTableIds.Max() + 1 : 1;

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add a 'ref' (or 'range') property with an A1-style range, e.g. ref=A1:D10.
2. Ensure the key is spelled exactly 'ref' or 'range' (case-insensitive lookup is used).
3. Verify the range sits inside the target sheet's bounds.

Example fix

// before
add /Sheet1/table --prop name=Sales
// after
add /Sheet1/table --prop name=Sales --prop ref=A1:D10

// Ensure a table ref/range is present before Add.
if (!props.ContainsKey("ref") && !props.ContainsKey("range"))
    throw new InvalidOperationException("Table requires 'ref' or 'range' property.");

try { handler.Add(parentPath, "table", null, props); }
catch (ArgumentException ex) when (ex.Message.Contains("'ref' or 'range' is required"))
{ /* prompt user for the table range and retry */ }

When it happens

Trigger: Calling Add('/Sheet1/table', ...) with properties that omit both ref and range, e.g. only supplying name/style, or misspelling the key as 'refs' or 'rng'.

Common situations: Forgetting the range argument, typoing the property name, or assuming the handler auto-detects the used range.

Related errors

• Defined name '{nrName}' collides with the table name '{exist
• Property 'sqref' (or 'range'/'ref') is required for validati
• Sheet not found: {tblSheetName}
• Table ref overlaps existing table '{existing.Name?.Value ??
• Table name '{tableName}' already exists in workbook; choose

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