Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 6 Next »

Table of Contents

Intro

This example takes an existing workbook that contains some data and creates a PivotTable. The workbook used in this example is available for download: Download Example.xlsx.

Before writing any PivotTable code, make sure to open the workbook with ExcelApplication and get references to the data worksheet and a worksheet for the PivotTable.

Icon

Excel does not encourage placing more than one PivotTable on a worksheet because PivotTables are not allowed to overlap. ExcelWriter cannot render PivotTables, so it does not have the ability to calculate which cells the PivotTable occupies and cannot detect when PivotTables might overlap. To avoid this, we encourage you to keep each PivotTable on separate worksheets.

Writing the code

Set up the data source

The data source needs to be a continuous block of cells with a header row with column names. The data source can be defined as an Area or a NamedRange.

Here is a snapshot of the data for this tutorial:

<IMAGE>

Icon

Download Example.xlsx to see the full sample data set.

In this case, the data source for the PivotTable will be a dynamically defined Area on the data worksheet. Note that the row of column names is included in the area.

Create the PivotTable

CREATING A PIVOT TABLE

After creating the PivotTable, always set RefreshOnOpen to true.

ExcelWriter does not have the ability to render a PivotTable, so any modifications made to a PivotTable will not take affect until the output file is opened in Excel and the PivotTable is refreshed. If RefreshOnOpen is true, Excel will refresh the PivotTable when the workbook opens, which will re-render the PivotTable.

Set PivotTableSettings

Other PivotTable properties - friendly error on value, empty value

Add PivotTableFields

Next, add PivotTableFields to the PivotTable. There are four types of PivotTableField: DataFields, RowLabels, ColumnLabels, and PageFields (or report filters). The Pivot field properties are available for all four types of PivotTableField, however some properties will not have an affect, depending on the type of field. These properties can be read and written to, but they will be ignored by Excel.

PageFields

Adding a page field - behavior

RowLabels and ColumnLabels

Adding a pivot table field - row labels / column labels, source fields, naming convention, changing the display name

DataFields

Adding data fields - calculated fields, naming conventions, changing the display name

DataField specific properties

Summarize By
Show Value As

Additional Resources

  • Answers post on CopySheet
  • Documentation on creating an ExcelTemplate PivotTable
  • PivotTable Glossary
  • Answers post on how to set properties that aren't available
  • No labels