OpenXml pivot table: how do you read the total and subtotals in order to display them?

Question

I'm trying to create an Excel pivot table entirely from scratch, using OpenXml.

I've successfully created the pivot table itself (created a PivotTable definition, a cache definition, all the cache records, pivotFields, RowItems, etc. etc.).

But how do I display any of the data? How do I read the PivotTable calculations in order to write these values to cells?

For example:

The "grand total" is $86,631.62.
Two subtotals are $61,631,12 and $25,000.50

When I look at the XML in xl\worksheets\sheet2.xml, these values are all "hard coded" into the cell.

If I'm creating the cell myself (using OpenXml), then how do I "query" these values, letting the Pivot Table calculate them for me?

PS: I've been using the OpenXml Productivity Tool extensively ... but it, too, just "hard codes" the totals and subtotals ... without giving any clue how/where the values were actually calculated.

Barr J Barr J · Accepted Answer · 2020-06-01T13:06:27

You can use cell formula if you do not want to use EPPlus:

cell.DataType = new EnumValue<CellValue.Number);
cell.CellFormula = new CellFormula($"=SUBTOTAL{"109"}{rowName}");

//force full recalculation of the cells
workbookPart.workbook.CalculationProperties.ForceFullCalculation = true;
workbookPart.workbook.CalculationProperties.FullCalculationLoad = true;

This way you can use every formula through OpenXml to calculate whatever yoe need.

In order to load into DataTable:

DataTable dt = new DataTable();

using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"..\..\example.xlsx", false))
{

    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
    IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
    string relationshipId = sheets.First().Id.Value;
    WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
    Worksheet workSheet = worksheetPart.Worksheet;
    SheetData sheetData = workSheet.GetFirstChild<SheetData>();
    IEnumerable<Row> rows = sheetData.Descendants<Row>();

    foreach (Cell cell in rows.ElementAt(0))
    {
        dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
    }

    foreach (Row row in rows) //this will also include your header row...
    {
        DataRow tempRow = dt.NewRow();

        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
        {
            tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
        }

        dt.Rows.Add(tempRow);
    }

}

OpenXml pivot table: how do you read the total and subtotals in order to display them?

1 Answers