OpenOfficeXml Repeat Labels in Pivot Table

Question

I m using Epplus and trying to "repeat all item labels" in tabular type pivot table. I tried lots of things but it looks no way with EPPlus library. I decided to manulplate pivot table xml and i need to add fillDownLabels attribute on pivotTableFields but i m not sure how to do this.

private void ManuplateXml(OfficeOpenXml.Table.PivotTable.ExcelPivotTable pivotTable)
{

    var xdPivotTable = pivotTable.PivotTableXml;
    var xdPivotFields = xdPivotTable.FirstChild["pivotFields"];
    if (xdPivotFields == null)
        return;

    foreach (XmlElement pField in xdPivotFields)
    {
        pField.SetAttribute("fillDownLabels", "1");
    }
}

I write this method it added the attribute but my pivot table still doesnt repeat item labels. How should xml format be ? How can i use fillDownLabels attribute ?

score 4 · Answer 1 · answered Mar 24 '17 at 14:08

Construction pField.SetAttribute("fillDownLabels", "true"); doesn't work. Attribute fillDownLabels should be used on extension (<ext>) belonging to ExtensionList Class (<extLst>). Below my solution:

    private void ManipulateXml(OfficeOpenXml.Table.PivotTable.ExcelPivotTable pivotTable)
    {
        var pivotTableXml = pivotTable.PivotTableXml;
        var nsManager = new XmlNamespaceManager(pivotTableXml.NameTable);
        nsManager.AddNamespace("d", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        nsManager.AddNamespace("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
        var topNode = pivotTableXml.DocumentElement;
        var nodes = topNode.SelectNodes("d:pivotFields/d:pivotField[@axis=\"axisRow\"]", nsManager);

        if (nodes == null) return;

        topNode.SetAttribute("updatedVersion", "6");//this line is important!
        foreach (XmlElement node in nodes)
        {
            var element = pivotTableXml.CreateElement("extLst", nsManager.LookupNamespace("d"));
            var ext = pivotTableXml.CreateElement("ext", nsManager.LookupNamespace("d"));
            ext.SetAttribute("uri", "{2946ED86-A175-432a-8AC1-64E0C546D7DE}");
            ext.SetAttribute("xmlns:x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            var fdLabels = pivotTableXml.CreateElement("x14:pivotField", nsManager.LookupNamespace("x14"));
            fdLabels.SetAttribute("fillDownLabels", "1");
            ext.AppendChild(fdLabels);
            element.AppendChild(ext);
            node.AppendChild(element);
        }
    }

score 0 · Answer 2 · answered Jan 25 '17 at 22:50

You need to set the fillDownLabels to true. This is per the OpenXML specification for the PivotField element which the fillDownLabels is an attribute of.

To set it to true, use the following convention:

pField.SetAttribute("fillDownLabels", "true");

Also note from the spec that this attribute can be ignored in some situations:

This attribute is ignored when the compact attribute and the outline attribute of the PivotTable ([ISO/IEC-29500-1] section 18.10) field (1) are "true". This attribute is ignored if the PivotTable ([ISO/IEC-29500-1] section 18.10) field (1) is not on the PivotTable ([ISO/IEC-29500-1] section 18.10) row (2) axis or the PivotTable ([ISO/IEC-29500-1] section 18.10) column (2) axis.

To summarize - to get your fillDownLabels to work correctly:

set the fillDownLabels to true - the default is false
make sure the compact attribute of the outline attribute of this pField is set or false (likely the default - check the spec). If this is true, the fillDownLabels attribute is ignored.
Ensure the Pfield is on the row axis or column axis, otherwise fillDownLabels is ignored.

one last note for folks using OpenXMLSDK - to set a BooleanValue attribute you can use the convention:

fillDownLabels = BooleanValue.FromBoolean(true)

OpenOfficeXml Repeat Labels in Pivot Table

2 Answers2