.
A PivotTable report is an interactive table which allows the user to have multiple views of data. By changing the view, the PivotTable report can show different summaries of the data or drill down to display more details for particular areas. ExcelWriter allows you to include PivotTables in an ExcelWriter template file. If there are PivotTables in the original template file, they will be preserved when the template is processed using ExcelWriter.
There are specific guidelines you must follow when creating a template file for bringing data into PivotTables using ExcelTemplate.
What is a data marker?
An ExcelWriter template is a file created in Microsoft Excel that contains data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, variable, or array to insert in the spreadsheet cell or column containing the marker. ExcelWriter gets values at runtime from a data source and imports them to spreadsheet data markers.
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 PivotTable. However, if you add a data marker row as part of a PivotTable data source without taking any further steps, the data markers will be pulled into the PivotTable cells. When ExcelTemplate binds data to the template, it will see these data markers (as well as subtotal cells) as regular data markers and attempt to process them, resulting in an error.
ExcelTemplate data markers can be used to bind data to a template for use with a PivotTable. However, if you add a data marker row as part of a PivotTable data source without taking any further steps, the data markers will be pulled into the PivotTable cells. When ExcelTemplate binds data to the template, it will see these data markers (as well as subtotal cells) as regular data markers and attempt to process them, resulting in an error.
To solve this problem, you will need to do two things:
- Replace the data markers in the PivotTables with temporary data.
- Keep two copies of your template file, one for editing and one for production. In the editable version the PivotTables will contain temporary data and should never be refreshed. In the production version, the PivotTables must be set to "Refresh on Open" so that the newly-loaded data will populate the PivotTables of the ExcelWriter generated spreadsheet,
How to create and ExcelWriter Template with PivotTables
Create a template file containing data markers, each with their own column heading (see How to Use Templates). Do not create any of the PivotTables yet.
- Copy the data markers row to another location on the worksheet. Replace the original data markers with arbitrary values of the same data type, which we call "dummy data".
In Excel 2003: Use Microsoft Excel's _PivotTable and PivotChart Report Wizard_ to create a PivotTable referencing the range of cells containing the column headings and dummy data.
In Excel 2007 or above: Use the Ribbon controls (in Excel 2007 and beyond) to create a PivotTable referencing the range of cells containing the column headings and dummy data.
Specify the initial layout and field formatting for the PivotTable. Place the PivotTable in any worksheet you desire. (See the Excel documentation for more details about creating your PivotTable.)
In Excel 2003: Before clicking "Finish" in the Wizard, click the "Options" button and make sure that "Refresh on open" is *not checked*.
In Excel 2007 or above: After creating the PivotTable, right-click on the table and select "PivotTable Options". Click on the Data tab and make sure that "Refresh on open" is not checked.
- In your newly created PivotTable, you will now see the dummy data. *Replace* the dummy data on the data sheet with the original data marker.
- Repeat steps 2 through 6 for every PivotTable you wish to create. Finish creating your template file and save it as a version which you may edit in the future, for example "template_edit.xls"
- Now, save another copy of the template file for production, for example "template_production.xls"
Set the PivotTable to refresh on open using the following methods,
In Excel 2003: Right-click on a PivotTable and select Table Options. *Check* "Refresh on open". Repeat this for every PivotTable in your workbook. You need to enable refreshing in the production copy in order for the newly-loaded data to automatically fill your PivotTables when the ExcelWriter-generated workbook is opened on the client.
In Excel 2007 or above: Right-click on a PivotTable and select PivotTable Options. Select the "Data" tab and Check "Refresh on open". Repeat this for every PivotTable in your workbook. You need to enable refreshing in the production copy in order for the newly-loaded data to automatically fill your PivotTables when the ExcelWriter-generated workbook is opened on the client.Save and close the production copy of your workbook and place it in the location on your server which will be accessed by your ExcelWriter script. Never open this file in Excel. If you open the production copy of the template in Excel, the tables will refresh, pulling in the data markers.
If you wish to make changes in your template file, open your editable version. Again, do not refresh the tables in the editable version. After your changes are complete, repeat steps 6 through 9 and overwrite the existing production version.