Pivot Table Style

GcExcel .NET allows users to apply built-in and custom styles to the pivot table.

With the help of this feature, users will be able to save pivot tables with different styles (with respect to the pivot table layout and pivot table fields). Users can customize how their pivot table is displayed including the pivot table’s orientation, page size, pivot table fields and many other characteristics as per their custom display preferences. Further, users can also refer to the topic Export Pivot Table Styles and Format in order export spreadsheets with different pivot table styles in PDF format.

Usually, when users add a pivot table to the worksheet, a default pivot table style is applied automatically. Users can modify the default style of the pivot table added to the worksheet by either copying an existing style (also called built-in style) or creating a custom pivot table style right from the scratch.

In order to apply style to the pivot table, you can refer to the following sections:

Apply Built-In Pivot Table Style

You can change the default appearance of the pivot table by applying any of the built-in styles. In order to apply built-in style to the pivot table, users can either use the Style property or use the TableStyle property of the IPivotTable interface.

The image shared below depicts a pivot table with built-in style.

Pivot table with built-in style

Refer to the following example code in order to apply built-in style to the pivot table.

 // Initialize workbook
 Workbook workbook = new Workbook();
 
 // Fetch default worksheet 
 IWorksheet worksheet = workbook.Worksheets[0];

 // Create PivotTable
 object[,] sourceData = new object[,] {
{ "Order ID", "Product",  "Category",   "Amount", "Date", "Country" },
{ 1,  "Carrots",  "Vegetables",  4270,    new DateTime(2018, 1, 6),  "United States" },
{ 2,  "Broccoli", "Vegetables",  8239,    new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,  "Banana",   "Fruit",       617,     new DateTime(2018, 1, 8),  "United States" },
{ 4,  "Banana",   "Fruit",       8384,    new DateTime(2018, 1, 10), "Canada" },
{ 5,  "Beans",    "Vegetables",  2626,    new DateTime(2018, 1, 10), "Germany" },
{ 6,  "Orange",   "Fruit",       3610,    new DateTime(2018, 1, 11), "United States" },
{ 7,  "Broccoli", "Vegetables",  9062,    new DateTime(2018, 1, 11), "Australia" },
{ 8,  "Banana",   "Fruit",       6906,    new DateTime(2018, 1, 16), "New Zealand" },
{ 9,  "Apple",    "Fruit",       2417,    new DateTime(2018, 1, 16), "France" },
{ 10, "Apple",    "Fruit",       7431,    new DateTime(2018, 1, 16), "Canada" },
{ 11, "Banana",   "Fruit",       8250,    new DateTime(2018, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables",  7012,    new DateTime(2018, 1, 18), "United States" },
{ 13, "Carrots",  "Vegetables",  1903,    new DateTime(2018, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables",  2824,    new DateTime(2018, 1, 22), "Canada" },
{ 15, "Apple",    "Fruit",       6946,    new DateTime(2018, 1, 24), "France" },
};
   
 worksheet.Range["A20:F33"].Value = sourceData;
 worksheet.Range["A:F"].ColumnWidth = 10;
 
 // Add pivot table
 var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A20:F33"]);
 var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");
 
 // Setting number format for a field
 worksheet.Range["D21:D35"].NumberFormat = "$#,##0.00";

 // Configure pivot table's fields
 var field_Date = pivottable.PivotFields["Date"];
 field_Date.Orientation = PivotFieldOrientation.PageField;

 var field_Category = pivottable.PivotFields["Category"];
 field_Category.Orientation = PivotFieldOrientation.RowField;

 var field_Product = pivottable.PivotFields["Product"];
 field_Product.Orientation = PivotFieldOrientation.ColumnField;
 
 var field_Amount = pivottable.PivotFields["Amount"];
 field_Amount.Orientation = PivotFieldOrientation.DataField;
 
 field_Amount.NumberFormat = "$#,##0.00";

 var field_Country = pivottable.PivotFields["Country"];
 field_Country.Orientation = PivotFieldOrientation.RowField;

 // Set pivot style
 pivottable.TableStyle = "PivotStyleMedium20";

 worksheet.PageSetup.TopMargin = 30;
 worksheet.PageSetup.LeftMargin = 30;

 worksheet.Range["A1:H16"].Columns.AutoFit();
 
 // Saving workbook to PDF
 workbook.Save(@"81-PivotTableBuiltInStyle.pdf", SaveFileFormat.Pdf);

Note: While applying built-in styles to the pivot table, it is important to note that if users apply a TableStyle whose ShowAsAvailableTableStyle property is set to true, then the InvalidOperationException is thrown.

Apply Custom Style

If you don't want to apply any of the built-in styles, you can also create and apply your own custom style to the pivot table. This can be done using the Style property of the IPivotTable interface.

The image shared below depicts a pivot table with custom style.

Pivot table with custom style

Refer to the following example code in order to apply custom style to the pivot table.

 // Initialize workbook
 Workbook workbook = new Workbook();
 
 // Fetch default worksheet 
 IWorksheet worksheet = workbook.Worksheets[0];

 object[,] sourceData = new object[,] {
{ "Order ID", "Product",  "Category",   "Amount", "Date", "Country" },
{ 1,  "Carrots",  "Vegetables",  4270,    new DateTime(2018, 1, 6),  "United States" },
{ 2,  "Broccoli", "Vegetables",  8239,    new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,  "Banana",   "Fruit",       617,     new DateTime(2018, 1, 8),  "United States" },
{ 4,  "Banana",   "Fruit",       8384,    new DateTime(2018, 1, 10), "Canada" },
{ 5,  "Beans",    "Vegetables",  2626,    new DateTime(2018, 1, 10), "Germany" },
{ 6,  "Orange",   "Fruit",       3610,    new DateTime(2018, 1, 11), "United States" },
{ 7,  "Broccoli", "Vegetables",  9062,    new DateTime(2018, 1, 11), "Australia" },
{ 8,  "Banana",   "Fruit",       6906,    new DateTime(2018, 1, 16), "New Zealand" },
{ 9,  "Apple",    "Fruit",       2417,    new DateTime(2018, 1, 16), "France" },
{ 10, "Apple",    "Fruit",       7431,    new DateTime(2018, 1, 16), "Canada" },
{ 11, "Banana",   "Fruit",       8250,    new DateTime(2018, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables",  7012,    new DateTime(2018, 1, 18), "United States" },
{ 13, "Carrots",  "Vegetables",  1903,    new DateTime(2018, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables",  2824,    new DateTime(2018, 1, 22), "Canada" },
{ 15, "Apple",    "Fruit",       6946,    new DateTime(2018, 1, 24), "France" },
};
 
 // Set source data
 worksheet.Range["A20:F33"].Value = sourceData;
 worksheet.Range["A:F"].ColumnWidth = 10;
 
 // Add pivot table
 var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A20:F33"]);
 var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");
 
 // Setting number format for a field
 worksheet.Range["D21:D35"].NumberFormat = "$#,##0.00";

 // Configure pivot table's fields
 var field_Date = pivottable.PivotFields["Date"];
 field_Date.Orientation = PivotFieldOrientation.PageField;

 var field_Category = pivottable.PivotFields["Category"];
 field_Category.Orientation = PivotFieldOrientation.RowField;

 var field_Product = pivottable.PivotFields["Product"];
 field_Product.Orientation = PivotFieldOrientation.ColumnField;

 var field_Amount = pivottable.PivotFields["Amount"];
 field_Amount.Orientation = PivotFieldOrientation.DataField;
 field_Amount.NumberFormat = "$#,##0.00";

 var field_Country = pivottable.PivotFields["Country"];
 field_Country.Orientation = PivotFieldOrientation.RowField;

 // Create pivot style with name "CustomPivotstyle"
 ITableStyle pivotStyle = workbook.TableStyles.Add("CustomPivotstyle");

 // Set table style as pivot table style
 pivotStyle.ShowAsAvailablePivotStyle = true;

 pivotStyle.TableStyleElements[TableStyleElementType.PageFieldLabels].Interior.Color = System.Drawing.Color.LightGreen;
 pivotStyle.TableStyleElements[TableStyleElementType.PageFieldValues].Interior.Color = System.Drawing.Color.LightGreen;

 pivotStyle.TableStyleElements[TableStyleElementType.GrandTotalColumn].Interior.Color = System.Drawing.Color.PowderBlue;
 pivotStyle.TableStyleElements[TableStyleElementType.GrandTotalRow].Interior.Color = System.Drawing.Color.PowderBlue;

 pivotStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = System.Drawing.Color.MistyRose;
 pivotStyle.TableStyleElements[TableStyleElementType.FirstColumn].Interior.Color = System.Drawing.Color.LightPink;

 pivotStyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.Color = System.Drawing.Color.SteelBlue;
 pivotStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].Interior.Color = System.Drawing.Color.NavajoWhite;

 // Set ShowTableStyleRowStripes as true
 pivottable.ShowTableStyleRowStripes = true;

 // Set pivot table style
 pivottable.Style = pivotStyle;
 worksheet.Range["A1:H16"].Columns.AutoFit();
 worksheet.PageSetup.TopMargin = 30;
 worksheet.PageSetup.LeftMargin = 30;

 // Saving workbook to PDF
 workbook.Save(@"82-PivotTableCustomStyle.pdf", SaveFileFormat.Pdf);

Note: While applying custom styles to the pivot table, it is important to note that if users apply a TableStyle whose ShowAsAvailablePivotStyle property is set to false, then the InvalidOperationException is thrown.