iOfficeAI/OfficeCLI · error · InvalidOperationException

Pivot cache source is not a worksheet source

Error message

Pivot cache source is not a worksheet source

What it means

The cache definition has a CacheSource element but no WorksheetSource child, meaning the pivot's source is not a worksheet range. RefreshPivotCacheFromSource only supports the worksheet-source flavour because it re-reads cells from a worksheet part; consolidation sources and external sources are structurally different and unsupported. The message states this precisely.

Source

Thrown at src/officecli/Core/PivotTableHelper.Readback.cs:384

    /// longer line up — RebuildFieldAreas reapplies them after this returns.
    /// </summary>
    private static void RefreshPivotCacheFromSource(PivotTablePart pivotPart, string newSourceSpec,
        Dictionary<string, string>? pendingFieldAreaProps = null)
    {
        if (string.IsNullOrWhiteSpace(newSourceSpec))
            throw new ArgumentException("source must not be empty");
        newSourceSpec = newSourceSpec.Trim();
        if (newSourceSpec.StartsWith("["))
            throw new ArgumentException(
                "External workbook references are not supported in pivot source. "
                + "Use a local sheet name (e.g. Sheet1!A1:D10)");

        var cachePart = pivotPart.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()
            ?? throw new InvalidOperationException("Pivot table has no cache definition part");
        var cacheDef = cachePart.PivotCacheDefinition
            ?? throw new InvalidOperationException("Pivot cache definition is missing");
        var existingWsSource = cacheDef.CacheSource?.WorksheetSource
            ?? throw new InvalidOperationException("Pivot cache source is not a worksheet source");

        // Parse the new source spec.
        string newSheetName;
        string newRef;
        if (newSourceSpec.Contains('!'))
        {
            var parts = newSourceSpec.Split('!', 2);
            newSheetName = parts[0].Trim().Trim('\'', '"').Trim();
            newRef = parts[1].Trim();
        }
        else
        {
            newSheetName = existingWsSource.Sheet?.Value ?? "";
            newRef = newSourceSpec;
        }

        // Locate the source worksheet via the workbook part.
        var workbookPart = pivotPart.GetParentParts().OfType<WorksheetPart>().FirstOrDefault()

View on GitHub (pinned to 1ced45e900)

Solutions

1. Rebuild the pivot with a worksheet range source so RefreshPivotCacheFromSource can process it
2. If you need consolidation/external sources, refresh them in Excel rather than via this helper
3. Check the Get readback — the source type is usually discoverable there before attempting a refresh

var cacheDef = cachePart.PivotCacheDefinition;
if (cacheDef?.CacheSource?.WorksheetSource == null)
    throw new InvalidOperationException("Pivot cache source is not a worksheet range; refresh unsupported");

static bool IsWorksheetSourced(PivotTablePart p)
{
    var cd = p.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()?.PivotCacheDefinition;
    return cd?.CacheSource?.WorksheetSource != null;
}

try { RefreshPivotCacheFromSource(pivotPart, sourceSpec); }
catch (InvalidOperationException ex) when (ex.Message.Contains("not a worksheet source"))
{ /* rebuild the pivot with a worksheet range source */ }

When it happens

Trigger: Refreshing a pivot built from a Consolidation (multiple ranges) cache source; refreshing a pivot whose source is an external data connection; a cache source with neither worksheet nor consolidation children (malformed).

Common situations: User opened a pivot created via Excel's 'Multiple consolidation ranges' wizard; pivots sourced from Power Query / data model connections; corporate templates that wire pivots to Analysis Services.

Related errors

• Pivot table has no cache definition part
• Pivot cache definition is missing
• Workbook part not reachable from pivot table part
• source must not be empty
• External workbook references are not supported in pivot sour

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