EPPlus DataField with PercentOfTotal

Question

I'm usingg EPPlus to create a pivot table in excel but I wish show data as percent of the total in one my DataFields, how can I do that?

public static void createTableMotivo(Worksheet ws, ExcelRangeBase range)
{
    const string FORMATCURRENCY = "#,###;[Red](#,###)";

    ExcelWorksheet worksheet = ws.EPPlusSheet;

    //The pivot table
    ExcelPivotTable pivotTable = worksheet.PivotTables.Add(worksheet.Cells["B12"], range, "pivot_table1");

    //The label row field
    pivotTable.RowFields.Add(pivotTable.Fields["FIELD1"]);
    pivotTable.DataOnRows     = false;
    pivotTable.ShowCalcMember = true;

    //The data fields
    ExcelPivotTableDataField fieldSum = pivotTable.DataFields.Add(pivotTable.Fields["FIELD2"]);
    fieldSum.Name     = "Quantidade de Faturas";
    fieldSum.Function = DataFieldFunctions.Sum;
    fieldSum.Format   = FORMATCURRENCY;

    ExcelPivotTableDataField fieldPercent = pivotTable.DataFields.Add(pivotTable.Fields["FIELD2"]);
    fieldPercent.Name      = "%";
    fieldPercent.Function  = DataFieldFunctions.None;
    fieldPercent.Format    = "0.00%";


    pivotTable.PageFields.Add(pivotTable.Fields["FIELD3"]);
    pivotTable.PageFields.Add(pivotTable.Fields["FIELD4"]);
}

Alex C Alex C · Accepted Answer · 2018-03-30T23:27:20

I am trying to do something similar. The only way I can get it this to work is by manipulating the xml of the Excel file.

This was interesting in itself - I can't remember where I saw that you can rename .xlsx to .zip and then just view all of the xml files inside that zip.

In my case, I first set "Show Values As" to "% of Grand Total" on the pivot table of the actual Excel file. Then after changing the file extention from .xlsx to .zip and extracting, there was a pivotTable3.xml file that had:

<dataFields count="2">
    <dataField name="Number of Orders" fld="10" subtotal="count"/>
    <dataField name="Percent of Total" fld="10" subtotal="count" showDataAs="percentOfTotal" numFmtId="10"/>
</dataFields>

The goal is to get showDataAs="percentOfTotal" in the dataField element whose name is "Percent of Total".

I tried to use an xpath expression to get the dataField element, but it returns null:

pivotTable.PivotTableXml.SelectSingleNode("//dataField[name='Percent of Total']")

So I had to fall back to walking down the xml:

pivotTable.DataFields[1].Format = "#0.00%";
pivotTable.DataFields[1].Function = DataFieldFunctions.Count;
pivotTable.DataFields[1].Name = "Percent of Total";

foreach (XmlElement documentElementChild in pivotTable.PivotTableXml.DocumentElement.ChildNodes)
{
    if (documentElementChild.Name.Equals("dataFields"))
    {
        foreach (XmlElement dataFieldChild in documentElementChild.ChildNodes)
        {
            foreach (XmlAttribute attribute in dataFieldChild.Attributes)
            {
                if (attribute.Value.Equals("Percent of Total"))
                {
                    // found our dataField element; add the attribute
                    dataFieldChild.SetAttribute("showDataAs", "percentOfTotal");
                    break;
                }
            }
        }
    }
}

EPPlus DataField with PercentOfTotal

1 Answers