iOfficeAI/OfficeCLI · error · ArgumentException

Property 'formula1' is empty for validation type=list; suppl

Error message

Property 'formula1' is empty for validation type=list; supply options like formula1="1,2,3" or a range reference.

What it means

Thrown by AddValidation when type is list and a formula1 property is supplied but, after trimming surrounding double quotes, is empty/whitespace. An empty list formula renders an empty dropdown or makes Excel refuse the file, so it is rejected up front.

Source

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

                "notbetween" => DataValidationOperatorValues.NotBetween,
                "equal" => DataValidationOperatorValues.Equal,
                "notequal" => DataValidationOperatorValues.NotEqual,
                "greaterthan" => DataValidationOperatorValues.GreaterThan,
                "lessthan" => DataValidationOperatorValues.LessThan,
                "greaterthanorequal" => DataValidationOperatorValues.GreaterThanOrEqual,
                "lessthanorequal" => DataValidationOperatorValues.LessThanOrEqual,
                _ => throw new ArgumentException($"Unknown operator: {dvOp}")
            };
        }

        if (properties.TryGetValue("formula1", out var dvFormula1))
        {
            // R28-A1 — reject empty formula1 for type=list. Excel renders an empty
            // dropdown (or rejects the file outright depending on form), and the
            // user almost certainly meant to provide options like "1,2,3".
            if (dv.Type?.Value == DataValidationValues.List
                && string.IsNullOrWhiteSpace(dvFormula1.Trim('"')))
                throw new ArgumentException(
                    "Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
            // Excel caps data-validation formulas at 255 chars; longer ones
            // pass schema validation but the file is refused (0x800A03EC).
            if (dvFormula1.Length > 255)
                throw new ArgumentException(
                    $"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.");
            // Embedded double quotes inside a literal list (not a range ref)
            // produce list literals Excel refuses to open — empirically
            // verified; reject rather than write a corrupt file.
            if (dv.Type?.Value == DataValidationValues.List)
            {
                var inner = dvFormula1.Trim();
                if (inner.StartsWith('"') && inner.EndsWith('"') && inner.Length >= 2)
                    inner = inner[1..^1];
                if (inner.Contains('"'))
                    throw new ArgumentException(
                        "validation list options must not contain double quotes; Excel refuses files with quoted-literal escapes inside a list formula. Put the options in cells and reference the range instead.");
            }

View on GitHub (pinned to 1ced45e900)

Solutions

1. Supply non-empty options: formula1="1,2,3".
2. Or supply a range reference the list should pull from.
3. Guard against empty inputs before calling and skip / error with your own message.

Example fix

// before
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "" });
// after
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "Yes,No" });

if (properties.GetValueOrDefault("type").Equals("list", StringComparison.OrdinalIgnoreCase)
    && properties.TryGetValue("formula1", out var f1)
    && string.IsNullOrWhiteSpace(f1.Trim('"')))
    throw new InvalidOperationException("formula1 is empty for type=list");

When it happens

Trigger: Call Add type "validation" with properties["type"]="list" and formula1="", formula1="\"\"", or formula1=" ".

Common situations: Building formula1 from an empty source list; passing a placeholder; stripping quotes earlier in a pipeline and ending with an empty string.

Related errors

• Property 'sqref' (or 'range'/'ref') is required for validati
• Property 'formula1' is required for validation type=list; su
• Chart requires a 'data' property. Use: data="Series1:1,2,3;S
• 'src' property is required for picture type
• Property 'ref' is required for comment

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