iOfficeAI/OfficeCLI · error · InvalidOperationException

pivotTableDefinition is missing <pivotFields>

Error message

pivotTableDefinition is missing <pivotFields>

What it means

InvalidOperationException thrown by ApplyCalculatedFields when the pivotTableDefinition has no <pivotFields> child. Calculated fields add a <dataField> referencing a field, and the definition must already carry its <pivotFields> collection; a missing <pivotFields> root indicates the pivot definition was not fully built.

Source

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

    // under pivotCacheDefinition, and Excel rejects the file as schema-
    // invalid). The cacheField/@formula attribute alone fully expresses
    // the calculated field; Excel rebuilds the column live on open.
    //
    // No records are written for calculated fields (databaseField="0"),
    // matching the date-group-derived pattern — Excel computes the column
    // live from the formula when the workbook opens.
    internal static void ApplyCalculatedFields(
        PivotCacheDefinition cacheDef,
        PivotTableDefinition pivotDef,
        Dictionary<string, string> properties)
    {
        var specs = ParseCalculatedFieldSpecs(properties);
        if (specs.Count == 0) return;

        var cacheFields = cacheDef.GetFirstChild<CacheFields>()
            ?? throw new InvalidOperationException("pivotCacheDefinition is missing <cacheFields>");
        var pivotFields = pivotDef.PivotFields
            ?? throw new InvalidOperationException("pivotTableDefinition is missing <pivotFields>");

        // Collect existing names (in both cacheFields and calculated specs)
        // so we can reject duplicates cleanly.
        var existingNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var cf in cacheFields.Elements<CacheField>())
            if (!string.IsNullOrEmpty(cf.Name?.Value))
                existingNames.Add(cf.Name!.Value!);

        // Ensure <dataFields> exists so we can append to it.
        var dataFields = pivotDef.DataFields;
        if (dataFields == null)
        {
            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

View on GitHub (pinned to 1ced45e900)

Solutions

1. Ensure the pivot table definition is fully built (with <pivotFields> from the source columns) before adding a calculated field.
2. Create/refresh the pivot table layout against a populated source first.
3. If the file is corrupted, rebuild the pivot table definition from valid source data.

var pivotFields = pivotDef.PivotFields;
if (pivotFields == null) { /* do not add calculated fields; finalize the pivot definition layout first */ }

static bool PivotHasFields(PivotTableDefinition pd) => pd?.PivotFields != null;

When it happens

Trigger: Adding a calculated field to a pivot table whose PivotTableDefinition lacks a PivotFields element — an incompletely-constructed or corrupted pivot definition.

Common situations: Operating on a pivot definition that was never finalized (no field layout generated); a corrupted file missing <pivotFields>; running the calculated-field step before the definition was initialized from the cache.

Related errors

• pivotCacheDefinition is missing <cacheFields>
• Worksheet missing
• Workbook is missing
• Invalid source range: {sourceRef}
• Column {startCol} out of range (max: XFD)

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