.
...
In this case, the data source for the PivotTable will be a dynamically defined Area area on the data worksheet. Note that the row of column names is included in the area.
...
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 in the output file, depending on the type of field. These properties can be read and written to, but they will be ignored by Excel.
All PivotTableFields are created from SourceFields. A SourceField is a read-only field that is automatically generated from the data source of the PivotTable. Each column in the data source corresponds with a SourceField in the PivotTable and the name of the SourceField comes from the column header name.
| Note |
|---|
A |
PageFields
To add a PageField, call CreateField on the PivotTable.PageFields collection. You will need to specify the SourceField that will be used to create the PageField.
| Code Block |
|---|
INSERT PageField CODE HERE |
| Info |
|---|
If there is no space to add the |
RowLabels and ColumnLabels
...
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 RefreshOnOpen to true and set SortOptions.Ordering on a PivotTablefield to be Ascending, or Descending.
This property only affects row and column labels.
| Code Block |
|---|
Insert code to sort the row label and column label values here |
When Excel refreshes the PivotTable, it will observe the SortOptions setting for a particular field.
DataFields
Adding data fields - calculated fields, naming conventions, changing the display name
Additional Resources
...
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.
| Code Block |
|---|
CODE TO CREATE A DATA 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 DisplayName property.
| Code Block |
|---|
CODE FOR CHANGING THE DISPLAY NAME
|
| Note |
|---|
In Excel, a calculated field is a type of row or column label that is automatically generated when two or more data fields exist in the PivotTable. Calculated fields cannot be modified except to change whether the calculated field is a row label or a column label. Excel handles this by disabling access to the calculate fields' properties. ExcelWriter will return |
Completed Code
And that concludes how to create a basic PivotTable. Here is the full sample code below:
| Code Block |
|---|
INSERT COMPLETED CODE HERE
|
Additional Resources
- Documentation on creating an ExcelTemplate PivotTable
- Documentation on modifying an existing PivotTable
- PivotTable Settings Glossary
- PivotTablEField Settings Glossary
- Answers post on how to set properties that aren't available