iOfficeAI/OfficeCLI · error · ArgumentException

Property 'ref' is required for comment

Error message

Property 'ref' is required for comment

What it means

Thrown by AddComment when no cell reference can be resolved. cmtRef is chosen from properties["ref"], else a cell-like second path segment (matching ^[A-Z]+\d+$), else this error. So it fires only when neither a ref property nor a /Sheet/Cell path is supplied.

Source

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

        var nrIdx = PathIndex.FromArrayIndex(definedNames.Elements<DefinedName>().ToList().IndexOf(dn));
        return $"/namedrange[{nrIdx}]";
    }

    private string AddComment(string parentPath, string type, InsertPosition? position, Dictionary<string, string> properties)
    {
        var index = position?.Index;
        var cmtSegments = parentPath.TrimStart('/').Split('/', 2);
        var cmtSheetName = cmtSegments[0];
        // Extract cell reference from path if present (e.g., /Sheet1/A1 -> A1)
        string? cmtRefFromPath = null;
        if (cmtSegments.Length > 1 && Regex.IsMatch(cmtSegments[1], @"^[A-Z]+\d+$", RegexOptions.IgnoreCase))
            cmtRefFromPath = cmtSegments[1];
        var cmtWorksheet = FindWorksheet(cmtSheetName)
            ?? throw new ArgumentException($"Sheet not found: {cmtSheetName}");

        var cmtRef = properties.GetValueOrDefault("ref") ?? cmtRefFromPath
            ?? throw new ArgumentException("Property 'ref' is required for comment");
        // Validate cell reference up-front; ParseCellReference rejects bad
        // syntax, out-of-range rows (>1048576), and out-of-range columns (>XFD)
        // with a clear ArgumentException — matches the validation surface
        // already enforced for cells/ranges elsewhere.
        ParseCellReference(cmtRef);
        var cmtText = properties.GetValueOrDefault("text", "");
        var cmtAuthor = properties.GetValueOrDefault("author", "Author");
        OfficeCli.Core.ParseHelpers.ValidateXmlText(cmtText, "comment text");
        OfficeCli.Core.ParseHelpers.ValidateXmlText(cmtAuthor, "comment author");

        var commentsPart = cmtWorksheet.WorksheetCommentsPart
            ?? cmtWorksheet.AddNewPart<WorksheetCommentsPart>();

        if (commentsPart.Comments == null)
        {
            commentsPart.Comments = new Comments(
                new Authors(new Author(cmtAuthor)),
                new CommentList()

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add properties["ref"] = "A1" (any valid A1 cell).
2. Or include the cell in the path as "/Sheet1/A1".
3. Validate ParseCellReference on the chosen ref before calling, to also catch out-of-range columns/rows.

Example fix

// before
handler.Add("/Sheet1", "comment", null, new() { ["text"] = "note" });
// after
handler.Add("/Sheet1", "comment", null,
    new() { ["ref"] = "B2", ["text"] = "note" });

var seg = parentPath.TrimStart('/').Split('/', 2);
string? refFromPath = (seg.Length > 1 && Regex.IsMatch(seg[1], @"^[A-Z]+\d+$", RegexOptions.IgnoreCase)) ? seg[1] : null;
string cmtRef = properties.GetValueOrDefault("ref") ?? refFromPath
    ?? throw new InvalidOperationException("comment needs ref= or /Sheet/Cell path");

static bool HasCommentTarget(string parentPath, Dictionary<string,string> p)
{
    if (p.ContainsKey("ref")) return true;
    var seg = parentPath.TrimStart('/').Split('/', 2);
    return seg.Length > 1 && Regex.IsMatch(seg[1], @"^[A-Z]+\d+$", RegexOptions.IgnoreCase);
}

When it happens

Trigger: Call Add type "comment" with parentPath "/Sheet1" (no cell segment) and a properties dictionary that has no "ref" key.

Common situations: Forgetting the --prop ref=A1 argument; passing only the sheet path expecting the API to pick a cell; building the properties dict programmatically and skipping the ref key.

Related errors

• Property 'sqref' (or 'range'/'ref') is required for validati
• Chart requires a 'data' property. Use: data="Series1:1,2,3;S
• 'src' property is required for picture type
• comment already exists on {cmtRefUpper}. Remove it first bef
• Property 'formula1' is empty for validation type=list; suppl

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