Style Pivot Tables

Oct 10, 2013 at 4:00 PM
First off, thanks for this incredible code. It has helped tremendously with my requirement to provide a pivot table tab in a spreadsheet. The example code details how to style cells and I've got that figured out. Is there an easy way to apply a style to a pivot table? I'd like to be able to apply a defined style to the heading columns in the pivot table.
Oct 17, 2013 at 2:37 PM
I figured this one out in case anybody is interested. I created a custom pivot table style in a spreadsheet, saved the spreadsheet, opened the styles.xml in the spreadsheet document and cut and paste that into the CreateDefaultStyles function in WorksheetAccessor. That gives me the default styles plus any other styles I want to set in code. Then I add the style record to pivotTable1.xml with the following code so the style is used for the pivot table.
    var ps = new PivotTableStyle()
        Name = "MyCustomStyle",
        ShowRowHeaders = true,
        ShowColumnHeaders = true,
        ShowRowStripes = true,
        ShowColumnStripes = true,
        ShowLastColumn = true
    var theSheet2 = WorksheetAccessor.GetWorksheet(xlsDocument, "Pivot Table");
    var pivotTablePart = theSheet2.GetPartsOfType<PivotTablePart>().First();
    var dataFields = pivotTablePart.PivotTableDefinition.Descendants<DataFields>().FirstOrDefault();
    pivotTablePart.PivotTableDefinition.InsertAfter<PivotTableStyle>(ps, dataFields);     

The code above will insert the pivot table style info node into the pivot table xml file right below the data fields node. If you open pivotTable1.xml in the spreadsheet your code created you should then see the entry for your custom style.

<pivotTableStyleInfo name="MyCustomStyle" showRowHeaders="1" showColHeaders="1" showRowStripes="0" showColStripes="0" showLastColumn="1"/>