|The instructions on this page apply for using pivot tables with ExcelWriter 7.5 and earlier. If you are using ExcelWriter 7.6 or later please use the instructions on Templates and Pivot Tables|
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.
|When using the ExcelApplication object to modify an existing spreadsheet, the following steps are not required; these steps are necessary only when opening a file containing data markers (i.e. an ExcelWriter template).|
- What is a data marker?
- Data markers and PivotTables
- How to Create an ExcelWriter Template with PivotTables
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.
ExcelTemplate data markers can be used to bind data to a template for use with a Pivot Table. However, if you add a data marker row as part of a pivot table data source without taking any further steps, the data markers will be pulled into the Pivot Table 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 2 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.
- 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 Pivot Table, 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 markers.
- 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 pivot table 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.