OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools

Versions Compared

Old Version 4

changes.mady.by.user Alison Bird [softartisans]

Saved on

compared with

New Version Current

changes.mady.by.user Gabe Goodwin [softartisans]

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Note

The instructions on this page apply for using pivot tables PivotTables with *ExcelWriter 7.6 and above*. If you are using ExcelWriter 7.5 or earlier please use the instructions on Templates and Pivot Tables in Older Versions

Excerpt

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.

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).

Anchordatamarkerdatamarker 

Table of Contents
maxLevel2

What is a data marker?

An ExcelWriter template is  template is a file created in Microsoft Excel that contains data markers. A *data marker* is a cell value beginning with %%= or  or %%=$ that  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. Anchorscenarioscenario

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. Anchorhowtohowto

How to

...

create and 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!
    #

    Image Added

  2. Create a pivot table

    In Excel 2003:

    Use

     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!

    Image Added

    *

    In Excel 2007 or above:

    * Use

     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!
    #

    Image Added 

     

  3. Specify the initial layout and field formatting for the

    pivot table

    PivotTable. Place the

    pivot table

    PivotTable in any worksheet you desire. (See the Excel documentation for more details about creating your

    pivot table

    PivotTable.)

    #

  4. In Excel 2003:

    Before

     Before clicking "Finish" in the Wizard, click the "Options" button and make sure that "Refresh on open" is checked.

    !RefreshOnOpen2003.png!

    Image Added

    *

    In Excel 2007 or above:

    *

    After creating the

    Pivot Table

    PivotTable, 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

    None from the Number of items to retain per field


    !PT_DataOptions_New

    .

    png!
    #


    Image Added 

     

  5. In your newly created

    pivot table

    PivotTable, 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

    Image Added

  6.  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