iOfficeAI/OfficeCLI · error · System.ArgumentException

Unsupported cellIs operator '{opStr}'. Valid: greaterThan, l

Error message

Unsupported cellIs operator '{opStr}'. Valid: greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, equal, notEqual, between, notBetween.

What it means

Thrown by AddCellIs when the 'operator' property (default 'greaterThan') lowercases to a value not in the supported operator set. The operator drives the OOXML ConditionalFormattingOperatorValues enum and determines how many formula operands are required. Rejecting an unknown operator prevents emitting an invalid cfRule and avoids silent miscategorization.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Cf.cs:539

        // CONSISTENCY(cf-sqref): three-level fallback matches dataBar/colorScale branches.
        // R22-2: path-tail range is the fallback before the hardcoded default.
        var cisPathRange = cisSegments.Length > 1 && !string.IsNullOrEmpty(cisSegments[1]) ? cisSegments[1] : "A1:A10";
        var cisSqref = ValidateSqref(properties.GetValueOrDefault("sqref")
            ?? properties.GetValueOrDefault("range")
            ?? properties.GetValueOrDefault("ref", cisPathRange), "ref");
        var opStr = (properties.GetValueOrDefault("operator") ?? "greaterThan").Trim();
        var opVal = opStr.ToLowerInvariant() switch
        {
            "greaterthan" or "gt" or ">" => ConditionalFormattingOperatorValues.GreaterThan,
            "lessthan" or "lt" or "<" => ConditionalFormattingOperatorValues.LessThan,
            "greaterthanorequal" or "gte" or ">=" => ConditionalFormattingOperatorValues.GreaterThanOrEqual,
            "lessthanorequal" or "lte" or "<=" => ConditionalFormattingOperatorValues.LessThanOrEqual,
            "equal" or "eq" or "=" or "==" => ConditionalFormattingOperatorValues.Equal,
            "notequal" or "ne" or "!=" or "<>" => ConditionalFormattingOperatorValues.NotEqual,
            "between" => ConditionalFormattingOperatorValues.Between,
            "notbetween" => ConditionalFormattingOperatorValues.NotBetween,
            _ => throw new ArgumentException(
                $"Unsupported cellIs operator '{opStr}'. Valid: greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, equal, notEqual, between, notBetween.")
        };

        var primary = properties.GetValueOrDefault("value")
            ?? properties.GetValueOrDefault("formula")
            ?? properties.GetValueOrDefault("value1")
            ?? throw new ArgumentException("cellIs conditional formatting requires 'value' property (e.g. value=50).");
        var secondary = properties.GetValueOrDefault("value2")
            ?? properties.GetValueOrDefault("formula2")
            ?? properties.GetValueOrDefault("maxvalue");

        if ((opVal == ConditionalFormattingOperatorValues.Between
             || opVal == ConditionalFormattingOperatorValues.NotBetween)
            && secondary == null)
        {
            throw new ArgumentException(
                $"cellIs operator '{opStr}' requires 'value2' property (e.g. value=10 value2=50).");
        }

View on GitHub (pinned to 1ced45e900)

Solutions

Use a supported operator name or symbol alias (see message for the full list).
For text-matching rules (contains/beginsWith/endsWith), use the cfextended path (type=containsText/beginsWith/endsWith), not cellIs.
Omit operator to accept the default 'greaterThan'.

Example fix

// before: operator=contains (wrong for cellIs)
add /Sheet1/A1:A10 cellis operator=contains value=50
// after: comparison operator
add /Sheet1/A1:A10 cellis operator=equal value=50

Defensive patterns

Strategy: validation

Validate before calling

var ops = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{ "greaterThan","gt",">","lessThan","lt","<","greaterThanOrEqual","gte",">=",
  "lessThanOrEqual","lte","<=","equal","eq","=","==","notEqual","ne","!=","<>","between","notBetween" };
var op = (properties.GetValueOrDefault("operator") ?? "greaterThan").Trim();
if (!ops.Contains(op)) throw new ArgumentException($"operator '{op}' invalid.");

Type guard

static readonly HashSet<string> CellIsOperators = new(StringComparer.OrdinalIgnoreCase)
{ "greaterThan","gt",">","lessThan","lt","<","greaterThanOrEqual","gte",">=",
  "lessThanOrEqual","lte","<=","equal","eq","=","==","notEqual","ne","!=","<>","between","notBetween" };
static bool IsValidCellIsOperator(string? s) => s is null || CellIsOperators.Contains(s);

Try / catch

try { return Add(path, "cellis", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("cellIs operator"))
{ props["operator"] = "equal"; return Add(path, "cellis", pos, props); }

Prevention

Whitelist the operator against the comparison set before calling Add.
Use text-match types (containsText/beginsWith) for text rules, not cellIs.
Omit operator to accept the default 'greaterThan'.

When it happens

Trigger: Calling Add with type=cellis and operator=<x> where x is not one of: greaterThan/gt/>, lessThan/lt/<, greaterThanOrEqual/gte/>=, lessThanOrEqual/lte/<=, equal/eq/=/==, notEqual/ne/!=/<>, between, notBetween. Example: operator=contains, operator=>>.

Common situations: Typing a text-operator name (contains/beginsWith) into a cellIs rule instead of a comparison; using a symbol form not in the alias list; locale capitalization (handled, but a genuinely wrong word is not).

Related errors

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