iOfficeAI/OfficeCLI · error · ArgumentException

calculatedField '{name}' collides with an existing field nam

Error message

calculatedField '{name}' collides with an existing field name

What it means

Each calculated field must have a unique name across both the existing source cacheFields and any prior calculated specs in the same call. The check is case-insensitive (existingNames uses StringComparer.OrdinalIgnoreCase) and runs against names collected from cacheFields plus names already added earlier in the loop. Excel itself would otherwise demote the duplicate, rename with a numeric suffix, or reject the file on refresh, so the helper fails fast with a precise message.

Source

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

            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);
            cacheFields.Count = (uint)cacheFields.Elements<CacheField>().Count();

View on GitHub (pinned to 1ced45e900)

Solutions

1. Choose a distinct name for the calc field, e.g. calculatedField=SalesAdj:=Sales*1.1
2. If you intend to replace an existing calc field, delete the old one first so its name leaves existingNames
3. Check the Get readback for current field names before constructing the new spec

Example fix

// before
calculatedField="Sales:=Sales*1.1"
// after
calculatedField="SalesAdjusted:=Sales*1.1"

// existingNames would come from the readback of current cache fields
var incoming = specs.Select(s => s.Name).Distinct(StringComparer.OrdinalIgnoreCase);
var dup = incoming.FirstOrDefault(n => existingNames.Contains(n));
if (dup != null) throw new InvalidOperationException($"Duplicate calc field name: {dup}");

static bool IsUniqueCalcName(string name, IEnumerable<string> existing) =>
    !existing.Contains(name, StringComparer.OrdinalIgnoreCase);

try { AddCalculatedFields(props); }
catch (ArgumentException ex) when (ex.Message.Contains("collides"))
{ /* offer to delete the existing field or pick a new name */ }

When it happens

Trigger: Naming a calc field identically to an existing source column (calculatedField=Sales:=Sales*1.1 when 'Sales' is already a header); listing the same name twice in one call (calculatedFields=[{"name":"X",...},{"name":"x",...}]); re-adding a calc field that already exists in the pivot.

Common situations: User wants to 'override' a column by reusing its name; case variation between Excel-displayed and stored names; automation that regenerates a full spec list and forgets to clear the existing pivot first.

Related errors

• calculatedField requires a non-empty name
• calculatedField '{name}' requires a non-empty formula
• 'calculatedFields' must be a JSON array
• each calculatedFields entry must be a JSON object
• invalid JSON for calculatedFields: {ex.Message}

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