How can I get a reference to the output range of a pivot table that I just created?

Question

I am using EPPlus to create an Excel workbook, and I have created a pivot table.

var pivotTable1 = sheet.PivotTables.Add(sheet.Cells["M1"], sourcePivotData, "MyPivotTable");

pivotTable1.ColumnFields.Add("col4");
pivotTable1.RowFields.Add(pivotTable1.Fields["Col1"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["Col2"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["Col3"]);

var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields["Amount"]);

I now want to get a reference to the pivot table OUTPUT data as a Range. I know the upper left cell, but given the size of the pivot table in terms of rows and column is dynamic based on the underlying data source, I can't figure out a way to dynamically figure out the last row and last column in the pivot table output range and I can't find any property of the pivot table object that gives me this.

In addition, it doesn't look like pivot table output data shows up in the

worksheet.Cells[]

property of the sheet, so I can't figure it out through that mechanism.

Is there a way to figure out the range of a pivot table output data using EPPlus?

Answer 1

I do see a GitHub issue matching this. So until they fix this / release a new feature, you can't do anything without a hacky solution.

It looks like with EPPlus (at least till 6.1), you can only create the schema definition of "how" the pivot table should be generated, and not the actual table itself. At least this is how the default config / setting is. I am not some EPPlus expert. But I don't see any settings / config to instruct EPPlus to generate the data alongside schema.

That's the reason you can't see any data with worksheet.Cells, and also the reason why the underlying XML file doesn't have rowItems and colItems node.

The actual table is generated only when you open it in some .xlsx reader (like Libre or Excel). It then evaluates / generates the Pivot table based on the definition generated by EPPlus (your code). And if at this point if you save the file, it will then copy the data to the XML files as well.

Once you reach this stage, you should be able to access the data using worksheet.Cells as usual, and even get the range using pivotTable.Address property ( or maybe the below function if Address property doesn't work).

This might not sound like a solution, but, as a hacky solution, you can do the following steps to generate, and read the underlying data.

1. Create the pivot table as usual
2. Open the file using some Excel application (from code)
3. Send Ctrl + S command to the process. (This will save the generated pivot table to the underlying XML file.)
4. Read the Excel sheet from your code again.

You can refer to this for the hacky part.

Or maybe use another library which actually generates the data, and not just the definition.

using OfficeOpenXml;
using OfficeOpenXml.Table.PivotTable;
using System.Text;

public static class ExcelExtensions
{
    // https://stackoverflow.com/a/182924/15066006
    static int ToColumnNumber(this string colAdress)
    {
        int[] digits = new int[colAdress.Length];
        for (int i = 0; i < colAdress.Length; ++i)
        {
            digits[i] = Convert.ToInt32(colAdress[i]) - 64;
        }
        int mul = 1; int res = 0;
        for (int pos = digits.Length - 1; pos >= 0; --pos)
        {
            res += digits[pos] * mul;
            mul *= 26;
        }
        return res;
    }

    // https://stackoverflow.com/a/2652855/15066006
    static string ToColumnAdress(this int col)
    {
        if (col <= 26)
        {
            return Convert.ToChar(col + 64).ToString();
        }
        int div = col / 26;
        int mod = col % 26;
        if (mod == 0) { mod = 26; div--; }
        return ToColumnAdress(div) + ToColumnAdress(mod);
    }

    public static ExcelRange? GetExcelRange(this ExcelPivotTable pivotTable)
    {
        var locationTag = pivotTable.PivotTableXml.GetElementsByTagName("location")[0];
        var baseLoc = locationTag?.Attributes?.GetNamedItem("ref")?.Value ?? string.Empty;

        if (baseLoc.Contains(':'))
            return pivotTable.WorkSheet.Cells[baseLoc];

        var firstHeaderValue = locationTag?.Attributes?.GetNamedItem("firstHeaderRow")?.Value ?? string.Empty;
        var firstDataRowValue = locationTag?.Attributes?.GetNamedItem("firstDataRow")?.Value ?? string.Empty;
        var firstDataColValue = locationTag?.Attributes?.GetNamedItem("firstDataCol")?.Value ?? string.Empty;

        var firstHeaderRow = firstHeaderValue is null ? 1 : Int32.Parse(firstHeaderValue);
        var firstDataRow = firstDataRowValue is null ? 1 : Int32.Parse(firstDataRowValue);
        var firstDataCol = firstDataColValue is null ? 1 : Int32.Parse(firstDataColValue);

        var headers = firstHeaderRow - 1;
        var colItems = pivotTable.GetColumnItems();
        var rowItems = pivotTable.GetRowItems();
        var totalRows = firstDataRow + headers + rowItems;
        var totalColumns = firstDataCol + colItems;

        string columnLetter; int currentRowNumber; int i = 0;
        var columnLetterBuffer = new StringBuilder();
        while (Char.IsLetter(baseLoc[i]))
        {
            columnLetterBuffer.Append(baseLoc[i]);
            ++i;
        }
        columnLetter = columnLetterBuffer.ToString();
        currentRowNumber = int.Parse(baseLoc[i..]);

        var currentColNumber = columnLetter.ToColumnNumber();
        var lastColNumber = currentColNumber + totalColumns - 1;
        var lastRowNumber = currentRowNumber + totalRows;
        var bottomRightCell = $"{lastColNumber.ToColumnAdress()}{lastRowNumber}";
        return pivotTable.WorkSheet.Cells[$"{baseLoc}:{bottomRightCell}"];
    }

        public static int GetRowItems(this ExcelPivotTable pivotTable)
    {
        var rowItems = pivotTable.PivotTableXml.GetElementsByTagName("rowItems")[0];

        if (rowItems is null)
        {
            Console.WriteLine("Data hasn't been generated yet. Open it in some excel application to actually generate the pivot table first");
            return 0;
        }
        else
        {
            var attrValue = rowItems?.Attributes?.GetNamedItem("count")?.Value;
            return attrValue is null ? 0 : Int32.Parse(attrValue);
        }
    }

    public static int GetColumnItems(this ExcelPivotTable pivotTable)
    {
        var colItems = pivotTable.PivotTableXml.GetElementsByTagName("colItems")[0];

        if (colItems is null)
        {
            Console.WriteLine("Data hasn't been generated yet. Open it in some excel application to actually generate the pivot table first");
            return 0;
        }
        else
        {
            var attrValue = colItems?.Attributes?.GetNamedItem("count")?.Value;
            return attrValue is null ? 0 : Int32.Parse(attrValue);
        }
    }
}

Answer 2

using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo("Workbook.xlsx")))
{
    // Get the worksheet that contains the pivot table
    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet1"];

    // Get the pivot table
    ExcelPivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

    // Get the range of the pivot table
    ExcelRange pivotTableRange = pivotTable.CacheDefinition.SourceRange;

    // Get the last row of the pivot table range
    int lastRow = pivotTableRange.End.Row;

    // Get the last column of the pivot table range
    int lastColumn = pivotTableRange.End.Column;
}

By using the End property of the ExcelRange object and the Row and Column properties of the resulting ExcelRange, you can dynamically figure out the last row and last column of the range of the pivot table.

Answer 3

Use something like this:

var range = pivotTable1.CacheDefinition.SourceRange;