Search code examples
pivot-tablems-officeopenxmlepplus

EPPlus 6.x - Show/Hide Pivot Table Field List (hidePivotFieldList="1")

EPPlus version: 6.1.0, .NET Core 7, C#

Is there a way to hide the Pivot Table Field List? I couldn't find any existing methods to accomplish this.

Pivot Table Context Menu PivotTable Analyze Menu

I have added a Macro to accomplish this behaviour, however the user experience is poor as they have to enable macros.

I have examined the generated workbook.xml file before and after hiding the Field List, it appears to add the following xml element / attribute :

<workbookPr hidePivotFieldList="1" defaultThemeVersion="166925"/>

I was expecting a method similar to WorkBook.hidePivotFieldList()

It looks like XmlDocument.WorkbookXml is read only, perhaps there are methods to manipulate the generated workbook.xml file, so I can add the element.

Solution

  • I had to prepend the missing XML Element to the ExcelPackage.Workbook.WorkbookXml

    XmlDocument xdoc = package.Workbook.WorkbookXml;
XmlNode newStateNode = xdoc.GetElementsByTagName("workbook")[0];         
XmlElement XEle = xdoc.CreateElement("workbookPr", newStateNode.NamespaceURI);
XEle.SetAttribute("hidePivotFieldList", "1");
xdoc.DocumentElement.PrependChild(XEle.Clone());