.
...
| Code Block |
|---|
PivotTable pt = pivot_ws.PivotTables.CreatePivotTable(data_area, 0, 0); |
Step3. 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 PivotTableSettings.
There are a couple properties that you should always consider when working with PivotTables with ExcelWriter.
Refresh On Open
After creating the PivotTable, always set RefreshOnOpen to true.
...
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.
...
ItemsToRetain
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 ItemToRetain to None to make sure the original values are cleared out when the PivotTable is refreshed.
| Code Block |
|---|
pt.PivotTableSettings.ItemsToRetain.Value = ItemsToRetain.None;
|
| Note |
|---|
This is especially important when working with PivotTables that have data markers in the data source. Set |
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 in the output file, depending on the type of field.
...