EPPlus - Pivot Table how to show values as % of Grand Total

1
votes

When creating a Pivot table using EPPlus library, we can add the row fields using PivotTable.RowFields.Add(...) function, similarly the data fields can be added using the PivotTable.DataFields.Add(...) function.

The issue is when I want to show the values of data field as percentage (%) of the grand total (see below image, setting used in actual Excel pivot table). The data field only gives DataFieldFunctions.Sum, Count, Average etc. But, there is no setting which allows the field value to be show as % of the grand total.

1
c#pivot-tableepplus

1 Answers

2
votes

EPPlus doesn't support "Show Value As" feature for pivot data fields, but this page describes a nice trick modifying the XML data behind the pivot table.

The main idea behind this, is that you set your data field's function to sum, and it's display format to percentage with EPPlus, and the rest with OpenXML.

If pivotTable is your Pivot and dataField is the Data Field you are working with than the code example on the mentioned page gives the following solution:

var xdoc = pivotTable.PivotTableXml;
var nsm = new XmlNamespaceManager(xdoc.NameTable);

var schemaMain = xdoc.DocumentElement.NamespaceURI;
if (nsm.HasNamespace("x") == false)
    nsm.AddNamespace("x", schemaMain);

var dataFieldNode = xdoc.SelectSingleNode(
    "/x:pivotTableDefinition/x:dataFields/x:dataField[@name='" + dataField.Name + "']",
    nsm
);

dataFieldNode.AppendAttribute("showDataAs", "percentOfTotal");