iOfficeAI/OfficeCLI · error · ArgumentException

calculatedField '{name}' requires a non-empty formula

Error message

calculatedField '{name}' requires a non-empty formula

What it means

Thrown in the same calculated-field loop when the name is valid but the formula is null, empty, or whitespace. A calculated field with no formula is meaningless to Excel and would produce a corrupt cacheField, so the helper rejects it before appending anything to the package. Note the cleaner does TrimStart('=') later — that step does not excuse an empty input, it only normalizes a leading '='.

Source

Thrown at src/officecli/Core/PivotTableHelper.Definition.cs:1536

        {
            dataFields = new DataFields { Count = 0u };
            pivotDef.DataFields = dataFields;
        }

        // Mirror layout-dependent attributes (compact/outline) from an existing
        // source pivotField so the calc fields stay attribute-consistent with
        // the rest of the table. Excel rejects a pivotTable where some
        // pivotFields declare compact="0" outline="0" but later ones omit them.
        var templatePf = pivotFields.Elements<PivotField>().FirstOrDefault();
        bool templateCompactFalse = templatePf?.Compact?.Value == false;
        bool templateOutlineFalse = templatePf?.Outline?.Value == false;

        foreach (var (name, formula) in specs)
        {
            if (string.IsNullOrWhiteSpace(name))
                throw new ArgumentException("calculatedField requires a non-empty name");
            if (string.IsNullOrWhiteSpace(formula))
                throw new ArgumentException($"calculatedField '{name}' requires a non-empty formula");
            if (existingNames.Contains(name))
                throw new ArgumentException(
                    $"calculatedField '{name}' collides with an existing field name");
            existingNames.Add(name);

            // 1. cacheField
            var cleanFormula = formula.TrimStart('=').Trim();
            var cacheField = new CacheField
            {
                Name = name,
                Formula = cleanFormula,
                DatabaseField = false,
                NumberFormatId = 0u
            };
            cacheFields.AppendChild(cacheField);

            // New field index = position of the freshly-appended cacheField.
            var newFieldIdx = (uint)(cacheFields.Elements<CacheField>().Count() - 1);

View on GitHub (pinned to 1ced45e900)

Solutions

1. Provide the formula after the colon: calculatedField=Margin:=Sales-Cost
2. In JSON, ensure 'formula' is a non-empty expression: [{"name":"Margin","formula":"=Sales-Cost"}]
3. Validate that formula has real content (not just '=' or spaces) before calling the helper

Example fix

// before
calculatedField="Margin:"
// after
calculatedField="Margin:=Sales-Cost"

var specs = rawSpecs.Where(s => !string.IsNullOrWhiteSpace(s.Formula)).ToList();
if (rawSpecs.Count != specs.Count)
    throw new InvalidOperationException("One or more calculated field formulas are empty");

static bool IsValidCalcFormula(string? formula) =>
    !string.IsNullOrWhiteSpace(formula);

try { AddCalculatedFields(props); }
catch (ArgumentException ex) when (ex.Message.Contains("non-empty formula"))
{ /* prompt user for the formula body */ }

When it happens

Trigger: calculatedField="Margin:" (bare prop with nothing after colon), calculatedFields=[{"name":"Margin","formula":""}], or a formula that is only whitespace like calculatedField="Margin: ".

Common situations: Formula string built from a missing config value; user copy-paste that dropped the formula half; a generated spec where the formula variable was never assigned.

Related errors

• calculatedField requires a non-empty name
• calculatedField '{raw}' must be 'Name:=Formula' (colon-separ
• calculatedField '{name}' collides with an existing field nam
• 'calculatedFields' must be a JSON array
• each calculatedFields entry must be a JSON object

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