iOfficeAI/OfficeCLI · error · System.ArgumentException

Formula-based conditional formatting requires 'formula' prop

Error message

Formula-based conditional formatting requires 'formula' property (e.g. formula=$A1>100)

What it means

Thrown by AddFormulaCf when none of the accepted property keys supply the formula expression. The builder checks 'formula', then 'formula1', then 'value' (aliases documented in the help schema so the formula branch matches the cellIs branch vocabulary). A formula-based (expression) cfRule requires an <x:formula> child element; without it the rule is meaningless and OOXML-invalid.

Source

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

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

        // CONSISTENCY(cf-sqref): three-level fallback matches dataBar/colorScale branches.
        // R22-2: path-tail range is the fallback before the hardcoded default.
        var fcfPathRange = fcfSegments.Length > 1 && !string.IsNullOrEmpty(fcfSegments[1]) ? fcfSegments[1] : "A1:A10";
        var fcfSqref = ValidateSqref(properties.GetValueOrDefault("sqref") ?? properties.GetValueOrDefault("range") ?? properties.GetValueOrDefault("ref", fcfPathRange), "ref");
        // CONSISTENCY(cf-value-alias): the help schema documents value/
        // formula1 as aliases of formula, and the cellIs branch already
        // accepts them — the formula branch alone rejected the alias.
        var fcfFormula = properties.GetValueOrDefault("formula")
            ?? properties.GetValueOrDefault("formula1")
            ?? properties.GetValueOrDefault("value")
            ?? throw new ArgumentException("Formula-based conditional formatting requires 'formula' property (e.g. formula=$A1>100)");
        // The <x:formula> element is A1-only — an R1C1-style reference makes
        // real Excel refuse the file (0x800A03EC) while schema validation
        // stays green. Same guard cell formulas already get.
        ValidateNoR1C1Reference(fcfFormula);
        ValidateFormulaLength(fcfFormula, "conditional-format formula");

        // Build DifferentialFormat (dxf) for the formatting.
        // A dxf Font may carry: b, i, u, strike, sz, rFont, color.
        // All sub-props are threaded together so users can combine
        // (e.g. bold + italic + underline + custom size + name).
        var dxf = new DifferentialFormat();
        var dxfFont = BuildFormulaCfFont(properties);
        if (dxfFont != null) dxf.Append(dxfFont);

        if (properties.TryGetValue("fill", out var fillColor))
        {
            var normalizedFillColor = ParseHelpers.NormalizeArgbColor(fillColor);
            dxf.Append(new Fill(new PatternFill(

View on GitHub (pinned to 1ced45e900)

Solutions

1. Supply the formula via the 'formula' property: formula=$A1>100.
2. The aliases 'formula1' and 'value' are also accepted if your tooling prefers them.
3. Ensure the value is an A1-style expression (R1C1 refs are separately rejected by ValidateNoR1C1Reference).

Example fix

// before: missing formula property
add /Sheet1/A1:A10 formulacf fill=FFFF00
// after
add /Sheet1/A1:A10 formulacf formula=$A1>100 fill=FFFF00

if (!properties.ContainsKey("formula") && !properties.ContainsKey("formula1") && !properties.ContainsKey("value"))
    throw new ArgumentException("Formula CF requires 'formula' (aliases: formula1, value).");

static bool HasFormulaCfExpression(IReadOnlyDictionary<string,string> p)
    => p.ContainsKey("formula") || p.ContainsKey("formula1") || p.ContainsKey("value");

try { return Add(path, "formulacf", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("requires 'formula'"))
{ /* prompt for formula, then retry */ throw; }

When it happens

Trigger: Calling Add with type=formula/formulacf (or cf type=formula/expression) but omitting all of formula, formula1, and value properties. Also triggered when the key is misspelled (e.g. formulas=, expr=).

Common situations: User expects the formula to be inferred from a cell reference; copy-paste from a sample that used a different property name; assuming 'expression=' is accepted (it is a type alias, not a property key).

Related errors

• cellIs conditional formatting requires 'value' property (e.g
• Unknown dataBar axisPosition '{dbAxisPos}'. Valid: automatic
• Unknown dataBar direction '{dbDir}'. Valid: leftToRight, rig
• Unknown midPoint kind '{badKind}'. Valid: percentile:<n>, pe
• Invalid midPoint '{midPointStr}': expected a number, optiona

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