Using ExcelTemplate with PivotTables |
 |
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 a template* file created with ExcelTemplate. If there are PivotTables in the original template file, they will be preserved in the newly generated spreadsheet.
There are specific guidelines you must follow when creating an ExcelWriter template file with PivotTables. Note: 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).
| * | In the ExcelWriter documentation, the term "template" refers to an ExcelWriter template, not a Microsoft Excel template (.xlt). However, ExcelWriter can open and generate both .xls and .xlt files. Use an .xlt file the same way you would an .xls file. |
- What is a data marker?
- Data markers and PivotTables
- How to Create an ExcelWriter Template with PivotTables
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 "%%=" that specifies a database column, variable, or array to insert in the spreadsheet column containing the marker.
| Data marker formats | |
|---|---|
| Recordset data marker | %%=[RecordsetMarkerName.]ColumnNameOrNumber |
| Variable data marker | %%=$VariableMarkerName |
| Array data marker | %%=$ArrayMarkerName[.#FieldNumber] The optional field number may be included for two dimensional arrays. |
Data markers and PivotTables
If you place data markers in the worksheet which will contain the imported data, and then create a PivotTable referencing these cells, the data markers will be pulled into the PivotTable. If the PivotTable remains in this state when ExcelWriter's ExcelTemplate object opens the template file, ExcelTemplate will detect these data markers and attempt to load them into the PivotTable itself.
To ensure that ExcelTemplate supplies the PivotTable with real values rather than the literal data marker strings, you must,
- 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.
How to Create an ExcelWriter Template with PivotTables
- Create a template file containing data markers (see Using Template Spreadsheets). Do not create any of the PivotTables yet.
- Use Microsoft Excel's PivotTable and PivotChart Report Wizard to create a
PivotTable referencing the range of cells containing the column headings and
data markers.
- 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.)
- Before clicking "Finish" in the Wizard, click the "Options" button and
make sure that "Refresh on open" is not checked.
- In your newly created PivotTable, you will now see the data markers. Replace the data marker in each cell of the table with temporary data of the appropriate type and format. For example, if the data will be a phone number, write something like "111-111-1111". From this point on, do not refresh your PivotTables.
- Repeat steps 2 through 5 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"
- In this production copy, 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.
- 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.
Copyright © 2003, SoftArtisans, Inc.