.
Table of Contents | |
|---|---|
|
Intro
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.
...
| Note |
|---|
This example places the PivotTable on a separate sheet. It is recommended that each PivotTable be placed on a separate sheet. This is because Excel does not allow PivotTables to overlap. When building PivotTables in Excel, an error message will be thrown if performing an action would cause two PivotTables to overlap. ExcelWriter does not have the ability to render PivotTables so it cannot calculate which cells a PivotTable occupies. Therefore, it cannot detect when PivotTables might overlap and will not throw an exception even if the code generates overlapping PivotTables. 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 can be defined as an Area or a NamedRange.
...
| Code Block |
|---|
Area data_area = data_ws.CreateArea(0, 0, 9, 244); |
Step 2. Create the PivotTable
To create a PivotTable, call CreatePivotTable on the PivotTables collection. Specify the 0-indexed row and column values for the PivotTable location:
...
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 RefreshOnOpen is true, Excel will refresh the PivotTable when the workbook opens, which will re-render the PivotTable.
Step 4. Add PivotTableFields
Next, add PivotTableFields to the PivotTable. There are four types of PivotTableField: DataFields, RowLabels, ColumnLabels, and PageFields (or report filters). The Pivot field properties are available for all four types of PivotTableField, however some properties will not have an affect, depending on the type of field. These properties can be read and written to, but they will be ignored by Excel.
PageFields
Adding a page field - behavior
RowLabels and ColumnLabels
Adding a pivot table field - row labels / column labels, source fields, naming convention, changing the display name
DataFields
Adding data fields - calculated fields, naming conventions, changing the display name
DataField specific properties
Summarize By
Show Value As
Additional Resources
- Answers post on CopySheet
- Documentation on creating an ExcelTemplate PivotTable
- PivotTable Glossary
- Answers post on how to set properties that aren't available