How to find the data source of a Pivot Table using OpenXML

Question

I am using EPP to open and edit an existing excel document.

The document contains 2 sheets - one with a pivot table (named Pivot) and one with the data (Data!$A$1:$L$9899).

I have a reference to the ExcelPivotTable with the code below, but can't find any properties that relate to the data source.

ExcelPackage package = new ExcelPackage(pivotSpreadsheet);

        foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
        {
            if (worksheet.PivotTables.Count > 0)
            {
                pivotWorkSheetName = worksheet.Name;
                pivotTable = worksheet.PivotTables[0];
            }
        }

How do I get the name and range of the source data? Is there an obvious property that I'm missing or do I have to go hunting through some xml?

Answer 1

PivotTables use a data cache for the data store for performance & abstraction reasons. Remember, you can have a pivot that points to a web service call. The cache itself is what stores that reference. For pivots that refer to data elsewhere in a workbook, you can access it in EPPlus like this:

worksheet.PivotTables[0].CacheDefinition.SourceRange.FullAddress;