Tabular pivot table in excel how to#

TableStyleElement totalRowStyle = customStyle.TableStyleElements Specify formatting characteristics for the grand total row. TableStyleElement wholeTable = customStyle.TableStyleElements Specify formatting characteristics for the whole table. TableStyleElement header = customStyle.TableStyleElements Specify formatting characteristics for the column headers. Modify the required formatting characteristics of the created pivot table style. TableStyle customStyle = sourceStyle.Duplicate() TableStyle sourceStyle = workbook.TableStyles Get the pivot table style to be duplicated. PivotTable pivotTable = worksheet.PivotTables Access the pivot table by its name in the collection The example below duplicates the built-in pivot table style and modifies the new style by changing formatting characteristics for the entire table, column headers and the grand total row.

Apply the created style to the pivot table by using the PivotTable.Style property.

Repeat this step for all table style elements you wish to modify. If you need to remove existing formatting from the element, use its TableStyleElement.Clear method. Use the TableStyleElement properties to specify the required formatting for the element.

Call the TableStyle.BeginUpdate method.Īccess the table style element to be modified from the TableStyle.TableStyleElements collection by the corresponding TableStyleElementType enumeration member.

For the custom styles that are based on the built-in pivot table styles, this step can be skipped since such styles copy appropriate values of these properties from the built-in styles. Set the TableStyle.IsPivotStyle property to true and the TableStyle.IsTableStyle property to false to indicate that the created style should be applied only to pivot tables.This method creates a copy of the specified style and returns the TableStyle object representing the created style. To do this, use the TableStyle.Duplicate method. Note that you can also create a custom pivot table style based on the existing pivot table style (e.g., a built-in pivot table style). This method returns the TableStyle object that represents the newly created pivot table style. To create a custom pivot table style, do the following.Īdd a new pivot table style to the IWorkbook.TableStyles collection by calling the TableStyleCollection.Add method.

Use properties of the TableStyleElement object to customize borders ( TableStyleElement.Borders), fill ( TableStyleElement.Fill) and font ( TableStyleElement.Font) for the corresponding element of a pivot table.

The TableStyleElementType enumerator lists the supported table style elements. Each table style element ( TableStyleElement) specifies formatting for a particular element of a pivot table. By default, the workbook’s collection of pivot table styles ( TableStyleCollection) contains built-in styles similar to Microsoft® Excel® and the None style, which specifies that no formatting should be applied to the pivot table.Ī pivot table style is defined by the TableStyle object, which consists of a collection of table style elements ( TableStyle.TableStyleElements).

Tabular pivot table in excel how to#

This example demonstrates how to create a custom style and apply it to a pivot table. How to: Apply a Custom Style to a Pivot Table