|
ExcelTemplate in Depth
> Templates and PivotTables
Templates and 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
an ExcelWriter template file.
If there are PivotTables in the original template file, they will be preserved in
the ExcelWriter-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).
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 column containing the marker. At runtime, ExcelWriter
gets values from a data source, and imports them to spreadsheet data markers.
For detailed information on data markers, see
Creating Template Data Markers.
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
- In Microsoft Excel, create a template file containing data markers (see
How To Use Templates).
- Open the Data menu and select PivotTable and PivotChart Report.
- 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 in the
workbook. (See the Excel documentation for more details about
creating your PivotTable.)
- Before clicking Finish, click the Options button and
make sure that Refresh on open is not checked.
- In your new 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 the steps above for each 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".
- Save another copy of the template file for production, for example
"template_production.xls"
- In the production copy, right-click a PivotTable and select Table
Options. Check Refresh on open. Repeat this for every
PivotTable in your workbook. You must enable refreshing in the production
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, save a second copy of the file as the
production version.
Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.
|
