Pivot table styling via Pivot Areas

Pivot tables can by styled by using special conditions rather than styling via the A1C1 notation. As an option to custom styling for individual pivot tables you can create your own custom named table styles as shown here EPPlus from version 5.6 supports this custom styling.

Samples

The samples below are taken from our sample project, Pivot Tables/PivotTablesStylingSample.cs or Pivot Tables/PivotTablesStylingSample.vb

Styling the entire pivot table

A pivot area defines an area within the pivot table. This example adds a style for the whole table and sets the font to Times New Roman and the font color to Accent2.

var styleWholeTable = pivotTable1.Styles.AddWholeTable();
styleWholeTable.Style.Font.Name = "Times New Roman";
styleWholeTable.Style.Font.Color.SetColor(eThemeSchemeColor.Accent2);

You can also specify a style for all Labels...

var styleLabels = pivot1.Styles.AddAllLabels();
styleLabels.Style.Font.Color.SetColor(eThemeSchemeColor.Accent4);
styleLabels.Style.Font.Italic = true;

...or all data

var styleData = pivot1.Styles.AddAllData();
styleData.Style.Font.Name = "Arial";
styleLabels.Style.Font.Bold = true;

Styling other parts of the pivot table.

You can also add styles for other parts of the pivot table like the button fields, top left or right area.

//Styles the area to the left of the column axis button field.
var topLeft = pivot2.Styles.AddTopStart();
topLeft.Style.Fill.BackgroundColor.SetColor(Color.Green);

//Styles the area to the right of the the column axis button field label. 
var topRight = pivot2.Styles.AddTopEnd();
topRight.Style.Fill.BackgroundColor.SetColor(Color.Red);

//Set the style for the column axis button field label
var columnStyle = pivot2.Styles.AddButtonField(ePivotTableAxis.ColumnAxis);
columnStyle.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
columnStyle.Style.Font.Color.SetColor(eThemeSchemeColor.Text1);

//Here we only want to style a part of the top right area. 
//We use the Offset property that uses the A1C1 address notation starting from the cell to the right as A1. 
//B1 here will be the second cell to the right. You can also use multiple cells references, like A1:B1
var topRightOffset1 = pivot2.Styles.AddTopEnd("B1");
topRightOffset1.Style.Border.BorderAround(ExcelBorderStyle.Dotted, eThemeSchemeColor.Text1);

Styling areas using fields and conditions.

Pivot areas can also be used to style individual parts of a pivot table. Conditions can be set for column and row fields and their values and data fields.

This pivot area add the row fields Name, Years and Quarters and will style the "Q4" label with Name: Christina Parker and Year: 2017

//Here we style a label for a single row item. 
//We add all the row fields to the pivot area and then add the values we want to style. 
//Note that the value and data type must match the value in the pivot field.
 var labelItem1 = pivot2.Styles.AddLabel(pivot2.Fields["Name"], pivot2.Fields["Years"], pivot2.Fields["Quarters"]);
 labelItem1.Conditions.Fields[0].Items.AddByValue("Christina Parker");
 labelItem1.Conditions.Fields[1].Items.AddByValue(2017D);    //Double here to match the value in the pivot table
 labelItem1.Conditions.Fields[2].Items.AddByValue("Q4");
 labelItem1.Style.Font.Color.SetColor(Color.DarkRed);

You can also style data items. Here we add a pivot area for field Name, Years and Quarters with the condition Name: Hellen Kuhlman, Year: 2017 and Quarters: Q3 or Q4. As we have multiple data field we restrict this format to the data fields OrderValue and Freight. Note that this style will apply to multiple cells.

//Here we style a data cell for a single row item. 
//We add all the row fields and the data fields we want to the pivot area and then add the values of the row fields. 
var dataItem1 = pivot2.Styles.AddData(pivot2.Fields["Name"], pivot2.Fields["Years"], pivot2.Fields["Quarters"]);
dataItem1.Conditions.Fields[0].Items.AddByValue("Hellen Kuhlman");
dataItem1.Conditions.Fields[1].Items.AddByValue(2017D);    //We use the double data type here to match the value in the pivot table
dataItem1.Conditions.Fields[2].Items.AddByValue("Q3");
dataItem1.Conditions.Fields[2].Items.AddByValue("Q4");
dataItem1.Conditions.DataFields.Add(pivot2.DataFields[0]);  //OrderValue
dataItem1.Conditions.DataFields.Add(pivot2.DataFields[2]);  //Freight
dataItem1.Style.Font.Color.SetColor(Color.DarkMagenta);

You also have properties on the pivot area to apply the style to a totals or grand rows/columns.

//Here we mark the grand total cell for the last data column.
var style2 = pivot3.Styles.AddData();
style2.Conditions.DataFields.Add(pivot3.DataFields[2]);
style2.GrandRow = true; //The pivot area will apply to the Grand Row only.
style2.Style.Font.Color.SetColor(Color.Red);

//Here we set the number format of the total cell only.
var style4 = pivot3.Styles.AddData(pivot3.Fields["Name"]);
style4.Conditions.Fields[0].Items.AddByValue("Jason Zemlak");
style4.Conditions.DataFields.Add(pivot3.DataFields[2]);
style4.Style.NumberFormat.Format = "#,##0.00000";            
style4.CollapsedLevelsAreSubtotals = true; //Only for the total only. Setting this to false will set the format for the sub items as well

This is the output of the first worksheet in sample 18.

See also

• Pivot Table Sample 7.2 or Pivot Table Sample 7.2-VB
• Pivot tables