iOfficeAI/OfficeCLI · error · InvalidOperationException

pivotCacheDefinition is missing <cacheFields>

Error message

pivotCacheDefinition is missing <cacheFields>

What it means

InvalidOperationException thrown by ApplyCalculatedFields when the pivotCacheDefinition has no <cacheFields> child. Calculated fields are added both as a <cacheField> (for the formula/field metadata) and a <dataField>, so a missing <cacheFields> root means the cache definition is malformed/incomplete and the operation cannot proceed.

Source

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

    // Do NOT emit a <x:calculatedFields> block on pivotTableDefinition —
    // that element is NOT a valid child there (ECMA-376 places it only
    // 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;
        }

View on GitHub (pinned to 1ced45e900)

Solutions

1. Ensure the pivot table's source cache has real <cacheFields> (i.e. the source range has headers/columns) before adding a calculated field.
2. Create/refresh the pivot cache against a populated source range first.
3. If the file is corrupted, rebuild the pivot cache (or the workbook) from valid source data.

var cacheFields = cacheDef.GetFirstChild<CacheFields>();
if (cacheFields == null) { /* do not add calculated fields; rebuild/refresh the cache from a populated source first */ }

static bool CacheHasFields(PivotCacheDefinition cd) => cd?.GetFirstChild<CacheFields>() != null;

When it happens

Trigger: Calling pivot-table calculated-field creation (ApplyCalculatedFields via the pivot create/Set path with a calculatedFields spec) on a pivot cache whose PivotCacheDefinition lacks a CacheFields element — e.g. a cache built from an empty/degenerate source or a corrupted definition.

Common situations: Adding a calculated field to a pivot whose cache was never populated (empty source range / no headers); a hand-built or corrupted cache definition missing <cacheFields>; operating before the cache was fully initialized.

Related errors

• pivotTableDefinition is missing <pivotFields>
• 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/6fa3322c803cc367. Report an issue: GitHub.