.
...
| Code Block |
|---|
Area data_area = data_ws.CreateArea(0, 0, 244, 9);
|
Step 2. Create the PivotTable
...
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 of properties that you should always consider when working with PivotTables with ExcelWriter.
...
After creating the PivotTable, always set RefreshOnOpen to true.
| Code Block |
|---|
pt.PivotTableSettings.RefreshOnOpen = true;
|
...
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 ItemsToRetain to None to make sure the original values are cleared out when the PivotTable is refreshed.
| Code Block |
|---|
pt.PivotTableSettings.ItemsToRetain.Value = ItemsToRetain.None;
|
...
Get a handle on the SourceFields to use for building PivotTableFields later.
| Code Block |
|---|
SourceField prodSubCategory = pivot.SourceFields[0], prodName = pivot.SourceFields[1], sales = pivot.SourceFields[2], dateStart = pivot.SourceFields[3], dateEnd = pivot.SourceFields[4], prodStdCost = pivot.SourceFields[5], listPrice = pivot.SourceFields[6], discount = pivot.SourceFields[7], unitDiscountPrice = pivot.SourceFields[8]; |
...
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.
...