Table of Contents
This example takes an existing workbook that contains some data and creates a PivotTable. The workbook used in this example is available for download: Download BasicExample.xlsx.
Before writing any PivotTable code, make sure to open the workbook with
ExcelApplication and get references to the data worksheet and a worksheet for the PivotTable. See Adding OfficeWriter to your .NET Application.
This example places the PivotTable on a separate sheet. It is recommended that each PivotTable be placed on a separate sheet.
Excel does not allow PivotTables to overlap and will use error messages to prevent users from creating overlapping PivotTables. ExcelWriter does not have the ability to render PivotTables, so it cannot detect if two PivotTables will overlap when they are rendered. To avoid this, we encourage you to keep your PivotTables on separate worksheets.
Writing the code
Step 1. Set up the data source
The data source needs to be a continuous block of cells with a header row with column names. The data source is defined as an
Here is a snapshot of the data for this tutorial, which can also be found in BasicExample.xlsx:
There are 9 columns and 244 rows in the data set, including the row with the header values.
In this case, the data source for the PivotTable will be a dynamically defined area on the data worksheet. Note that the row of column names is included in the area.
Step 2. Create the PivotTable
To create a
CreatePivotTable on the
PivotTables collection. Specify the 0-indexed row and column values for the PivotTable location:
Step 3. Set PivotTable Options
In Excel, there are a number of options that can be set by going to PivotTable Options. These properties are available through
There are a couple of properties that you should always consider when working with PivotTables with ExcelWriter.
Refresh On Open
After creating the PivotTable, always set
ExcelWriter does not have the ability to render a PivotTable, so any modifications made to a PivotTable will not take affect until the output file is opened in Excel and the PivotTable is refreshed. If
true, Excel will refresh the PivotTable when the workbook opens, which will re-render the PivotTable.
The other important property to set is
ItemsToRetain. When the PivotTable is created, the values that are available through the row label, column label, or page field drop-down filters are based on the values in the data source of the PivotTable at the time the PivotTable was created.
By default, the PivotTable will retain all the original values in those filters, even if those values are no longer in the data source. Set
None to make sure the original values are cleared out when the PivotTable is refreshed.
Step 4. Add PivotTableFields
There are four types of
PageFields (also called report filters).
PivotTableFields are created from read-only
SourceFields, which are generated based on the
PivotTable data source.
Get a handle on the
SourceFields to use for building
SourceFields can be referenced by the column header name or by column index.
To add a
CreateField on the
PivotTable.PageFields collection. You will need to specify the
SourceField that will be used to create the
The layout of the page fields depends on whether the page layout is set to 'Down, then Over' or 'Over, then Down' and the number of fields allowed per row/column. These properties are not exposed in the current API, so the defaults apply when creating a PivotTable from scratch. The defaults are 'Down, then Over' and an unlimited number of fields per column.
This means that in PivotTables created by ExcelWriter, page fields are always placed starting two rows above the upper left corner of the PivotTable, so if a PivotTable is placed in cell B10, then page fields will start in B8. Any additional page fields are added to B8 and existing page fields are moved up, or the new page field is added to a column to the right, depending on the page layout properties in the PivotTable.
By default, all the page fields appear in one column.
If there is no space to add the page fields above the PivotTable (i.e. the PivotTable is located in cell A1), then ExcelWriter will automatically move the PivotTable down to accommodate the page fields.
RowLabels and ColumnLabels
Similarly to page fields,
ColumnLabels are created on the
PivotTable.ColumnLabels collections. As mentioned earlier, only one
PageField can be created from a particular
Row labels and column labels display the subtotals for each group of values. The subtotal can be displayed at the top or bottom of the group.
Excel automatically sorts and re-renders a PivotTable any time a change is made. By default, the row label or column label values are sorted alpha-numerically in ascending order.
Since ExcelWriter does not have the ability to render PivotTables or sort the values for a field, the only way to guarantee that the data will be sorted is to set
true and set
SortOptions.Ordering on a
PivotTableField to be
This property only affects row and column labels.
When Excel refreshes the PivotTable, it will observe the
SortOptions setting for a particular field.
To create a data field, call
CreateField on the
DataFields collection. Unlike row labels, column labels, and page fields, multiple data fields can be created from the same source field.
In Excel, a unique name is given to the data field depending on the type of data in the source field (numerical or mixed), whether or not any other data fields were already created from the same source field, and whether the source field name ends in a number or alphabetical character (e.g. Case1 vs. CaseOne).
ExcelWriter uses a consistent naming convention when creating data fields: all data fields follow the format SOURCEFIELDNAME#, where _# is an incremental number starting at 1. To change the name of a data field, use the
The function used to aggregate the data can be specified through the summarize by property. If a column data contains just numbers, the function defaults to SUM. If the column contains mixed data, the function defaults to COUNT. There are other functions available.
A number format can be specified for all PivotTable fields, but it will only take affect on data fields and row labels/column labels/page fields that have numeric data only.
And that concludes how to create a basic PivotTable. Here is the full sample code below:
- Templates and PivotTables - using PivotTables with ExcelTemplate
- Intro to PivotTables - Terminology from Excel