.
A PivotTable is an interactive table which allows the user to transpose, filter, sort, and aggregate large amounts of data into a single table format. ExcelWriter works with PivotTables in two ways: include a PivotTable in an ExcelTemplate template file or manipulate PivotTables programmatically with ExcelApplication. This article provides a brief overview on the functionality of PivotTables.
...
After a PivotTable has been created, it is given a default area that is 3 columns wide and 18 rows tall until the source fields are used to create additional PivotTable fields.
Source Fields
A list of source fields is automatically generated based on the column names from the data source. These fields are used for building other types of PivotTable fields, such as report filters, column labels, row labels, or data fields.
PivotTable Options
There are numerous settings that can be applied to a PivotTable. These are available by right-clicking on the PivotTable and going to PivotTable Options in Excel. The settings are organized into groups, based on how they affect the PivotTable.
| Info |
|---|
In ExcelTemplate, just set these properties on the PivotTable in Excel. In ExcelApplication, these properties are available through |
| Note |
|---|
Take special note of the data options, particularly Refresh data when opening the file and Number of items to retain per field. Since ExcelWriter cannot render PivotTables on the server, if a PivotTable or PivotTable data source is modified with ExcelWriter, those changes will not be reflected until the PivotTable is refreshed. Therefore, we always encourage you to turn on Refresh data when opening the file or PivotTables by default retain all values that are included in the data source, starting from the original values, and makes them available in various drop-downs and selections. In the case of ExcelTemplate, this means that data markers will appear in parts of the PivotTable, even after it has refreshed. To make sure that old values are cleared out when the PivotTable is refreshed, make sure to set Number of items to retain per field to None, or |
PivotTable Field
PivotTable Field Settings
...