{"record":{"id":"6abdd0f875fff057","repo":"iOfficeAI/OfficeCLI","slug":"pivot-cache-source-is-not-a-worksheet-source","errorCode":null,"errorMessage":"Pivot cache source is not a worksheet source","messagePattern":"Pivot cache source is not a worksheet source","errorType":"exception","errorClass":"InvalidOperationException","httpStatus":null,"severity":"error","filePath":"src/officecli/Core/PivotTableHelper.Readback.cs","lineNumber":384,"sourceCode":"    /// longer line up — RebuildFieldAreas reapplies them after this returns.\n    /// </summary>\n    private static void RefreshPivotCacheFromSource(PivotTablePart pivotPart, string newSourceSpec,\n        Dictionary<string, string>? pendingFieldAreaProps = null)\n    {\n        if (string.IsNullOrWhiteSpace(newSourceSpec))\n            throw new ArgumentException(\"source must not be empty\");\n        newSourceSpec = newSourceSpec.Trim();\n        if (newSourceSpec.StartsWith(\"[\"))\n            throw new ArgumentException(\n                \"External workbook references are not supported in pivot source. \"\n                + \"Use a local sheet name (e.g. Sheet1!A1:D10)\");\n\n        var cachePart = pivotPart.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()\n            ?? throw new InvalidOperationException(\"Pivot table has no cache definition part\");\n        var cacheDef = cachePart.PivotCacheDefinition\n            ?? throw new InvalidOperationException(\"Pivot cache definition is missing\");\n        var existingWsSource = cacheDef.CacheSource?.WorksheetSource\n            ?? throw new InvalidOperationException(\"Pivot cache source is not a worksheet source\");\n\n        // Parse the new source spec.\n        string newSheetName;\n        string newRef;\n        if (newSourceSpec.Contains('!'))\n        {\n            var parts = newSourceSpec.Split('!', 2);\n            newSheetName = parts[0].Trim().Trim('\\'', '\"').Trim();\n            newRef = parts[1].Trim();\n        }\n        else\n        {\n            newSheetName = existingWsSource.Sheet?.Value ?? \"\";\n            newRef = newSourceSpec;\n        }\n\n        // Locate the source worksheet via the workbook part.\n        var workbookPart = pivotPart.GetParentParts().OfType<WorksheetPart>().FirstOrDefault()","sourceCodeStart":366,"sourceCodeEnd":402,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Core/PivotTableHelper.Readback.cs#L366-L402","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","solutions":["Rebuild the pivot with a worksheet range source so RefreshPivotCacheFromSource can process it","If you need consolidation/external sources, refresh them in Excel rather than via this helper","Check the Get readback — the source type is usually discoverable there before attempting a refresh"],"exampleFix":null,"handlingStrategy":"try-catch","validationCode":"var cacheDef = cachePart.PivotCacheDefinition;\nif (cacheDef?.CacheSource?.WorksheetSource == null)\n    throw new InvalidOperationException(\"Pivot cache source is not a worksheet range; refresh unsupported\");","typeGuard":"static bool IsWorksheetSourced(PivotTablePart p)\n{\n    var cd = p.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()?.PivotCacheDefinition;\n    return cd?.CacheSource?.WorksheetSource != null;\n}","tryCatchPattern":"try { RefreshPivotCacheFromSource(pivotPart, sourceSpec); }\ncatch (InvalidOperationException ex) when (ex.Message.Contains(\"not a worksheet source\"))\n{ /* rebuild the pivot with a worksheet range source */ }","preventionTips":["Build pivots from worksheet ranges if you intend to refresh them programmatically","Refresh consolidation/external-source pivots in Excel instead","Check the Get readback to learn the source type before refreshing"],"tags":["pivottable","cache-refresh","source-type","invalid-operation","unsupported-feature"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}