Page History

A pivot table PivotTable report is an interactive table which allows the user to have multiple views of data. By changing the view, the pivot table PivotTable report can show different summaries of the data or drill down to display more details for particular areas. ExcelWriter allows you to include pivot tables PivotTables in an ExcelWriter template file. If there are pivot tables PivotTables in the original template file, they will be preserved when the template is processed using ExcelWriter.

...

• What is a data marker?
• Data markers and pivot tablesPivotTables
• How to Create an ExcelWriter Template with pivot tablesPivotTables

...

For detailed information on data markers, see Creating Data Markers.

Data markers and

...

PivotTables

ExcelTemplate data markers can be used to bind data to a template for use with a pivot tablePivotTable. When ExcelTemplate binds the data to the data markers, it also updates the range that the pivot table PivotTable uses as a pivot table PivotTable cache. By setting the pivot tables PivotTables to refresh on opening, they will automatically bring in the new values from the pivot table PivotTable cache.

How to Create an ExcelWriter Template with

...

PivotTables

1. Create a template file containing data markers, each with their own column heading (see How To Use Templates). Do not create any of the pivot tables PivotTables yet.
   !Data Markers.png!
   # In Excel 2003: Use Microsoft Excel's pivot table PivotTable and PivotChart Report Wizard to create a pivot table PivotTable referencing the range of cells containing the column headings and data markers.
   !PivotTableWizard.png!
   *In Excel 2007 or above:* Use the Ribbon controls (in Excel 2007 and beyond) to create a pivot table PivotTable referencing the range of cells containing the column headings and data markers.
   !PivotTableRibbon.png!
   # Specify the initial layout and field formatting for the pivot tablePivotTable. Place the pivot table PivotTable in any worksheet you desire. (See the Excel documentation for more details about creating your pivot tablePivotTable.)
   # In Excel 2003: Before clicking "Finish" in the Wizard, click the "Options" button and make sure that "Refresh on open" is checked.
   !RefreshOnOpen2003.png!
   *In Excel 2007 or above:* After creating the Pivot TablePivotTable, right-click on the table and select "pivot table PivotTable Options". Click on the Data tab and make sure that "Refresh data on opening the file" is checked. Also, make sure to select None from the Number of items to retain per field
   !PT_DataOptions_New.png!
   # In your newly created pivot tablePivotTable, you will now see the data markers. Data markers in the pivot table PivotTable will be ignored by ExcelWriter, which will instead update the data source.
   !Finished Table.png!
   # Repeat steps 2 through 5 for every pivot table PivotTable you wish to create. Finish creating your template file and save it in the location on your server which will be accessed by your ExcelWriter script.

   Scrollbar