How can I create a DataField that calculates values in EPPlus?

Question

I am porting Excel Interop PivotTable code to EPPlus. I'm at a sticking point in generating a calculated field value. In Excel Interop I can do it this way: ``` pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages", XlConsolidationFunction.xlSum).NumberFormat = "###,##0"; pvt.AddDataField(pvt.PivotFields("TotalPrice"), "Total Purchases", XlConsolidationFunction.xlSum).NumberFormat = "$#,##0.00"; PivotField avg = pvt.CalculatedFields().Add("Average Price", "=TotalPrice/TotalQty", true); avg.Orientation = XlPivotFieldOrientation.xlDataField; avg.NumberFormat = "$###0.00"; ``` With this the "Average Price" value on the PivotTable displays the value for TotalPrice divided by TotalQty. But how to do it in EPPlus? I can create the "plain vanilla" data fields like so: ``` var totQtyField = pivotTable.Fields["TotalQty"]; pivotTable.DataFields.Add(totQtyField); var totPriceField = pivotTable.Fields["TotalPrice"]; pivotTable.DataFields.Add(totPriceField); ``` ...but when it comes to ca…

Accepted Answer

EPPlus does not currently support calculated fields in PivotTables. I used the following workaround:

```
using System.Linq;
using System.Xml;
using OfficeOpenXml.Table.PivotTable;
using ReflectionMagic;

public static ExcelPivotTableField AddCalculatedField(this ExcelPivotTable pivotTable, string calcFieldName, string formula)
{
    var dynamicPivotTable = pivotTable.AsDynamic();
    var maxIndex = pivotTable.Fields.Max(f => f.Index);
    const string schemaMain = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    var cacheTopNode = dynamicPivotTable.CacheDefinition.CacheDefinitionXml.SelectSingleNode("//d:cacheFields", dynamicPivotTable.NameSpaceManager);
    cacheTopNode.SetAttribute("count", (int.Parse(cacheTopNode.GetAttribute("count")) + 1).ToString());
    var cacheFieldNode = dynamicPivotTable.CacheDefinition.CacheDefinitionXml.CreateElement("cacheField", schemaMain);
    cacheFieldNode.SetAttribute("name", calcFieldName);
    cacheFieldNode.SetAttribute("databaseField", "0");
    cacheFieldNode.SetAttribute("formula", formula);
    cacheFieldNode.SetAttribute("numFmtId", "0");
    cacheTopNode.AppendChild(cacheFieldNode);

var topNode = dynamicPivotTable.PivotTableXml.SelectSingleNode("//d:pivotFields", dynamicPivotTable.NameSpaceManager);
    topNode.SetAttribute("count", (int.Parse(topNode.GetAttribute("count")) + 1).ToString());
    XmlElement fieldNode = dynamicPivotTable.PivotTableXml.CreateElement("pivotField", schemaMain);
    fieldNode.SetAttribute("compact", "0");
    fieldNode.SetAttribute("outline", "0");
    fieldNode.SetAttribute("showAll", "0");
    fieldNode.SetAttribute("defaultSubtotal", "0");
    topNode.AppendChild(fieldNode);

var excelPivotTableFieldType = typeof(ExcelPivotTableField).AsDynamicType();
    var excelPivotTableField = excelPivotTableFieldType.New((XmlNamespaceManager)dynamicPivotTable.NameSpaceManager, fieldNode, (ExcelPivotTable)dynamicPivotTable, maxIndex + 1, maxIndex + 1);
    excelPivotTableField.SetCacheFieldNode(cacheFieldNode);
    dynamicPivotTable.Fields.AddInternal(excelPivotTableField);

return pivotTable.Fields.First(f => f.Name == calcFieldName);
}
```

Example usage:

```
var calc1 = pivotTable.AddCalculatedField("Calc1", "BCol*BCol");
var dataField = pivotTable.DataFields.Add(calc1);
dataField.Function = DataFieldFunctions.Sum;
dataField.Name = "Override Name";
```

How can I create a DataField that calculates values in EPPlus?

Solution

Problem