Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0

Table of Contents

Table of Contents

Intro

A PivotTable is an interactive table which allows the user to transpose, filter, sort, and aggregate large amounts of data into a single table format. ExcelWriter works with PivotTables in two ways: include a PivotTable in an ExcelTemplate template file or manipulate PivotTables programmatically with ExcelApplication. This article provides a brief overview on the functionality of PivotTables.

Note

The ability to programmatically manipulate PivotTables was introduced in OfficeWriter 8.4.0.

Data

Data Source

A PivotTable uses a two-dimensional area of cells for a data source. The first row in the data source must contain names for each column of data and the data source must contain at least one row of data. The data can include regular values or formulas, but it must be a continuous area of cells.

Here is an example of a data source for a PivotTable:

Pivot Cache

When a PivotTable is created using the data source, a copy of this data is stored in the pivot cache or PivotTable cache. Multiple PivotTables can use data from the same pivot cache.

PivotTable

After a PivotTable has been created, it is given a default area that is 3 columns wide and 18 rows tall until the source fields are used to create additional PivotTable fields.

Source Fields

A list of source fields is automatically generated based on the column names from the data source. These fields are used for building other types of PivotTable fields, such as report filters, column labels, row labels, or data fields.

PivotTable Options

There are numerous settings that can be applied to a PivotTable. These are available by right-clicking on the PivotTable and going to PivotTable Options in Excel. The settings are organized into groups, based on how they affect the PivotTable.

Info

In ExcelTemplate, just set these properties on the PivotTable in Excel. In ExcelApplication, these properties are available through PivotTableSettings.

Note

Take special note of the data options, particularly Refresh data when opening the file and Number of items to retain per field, as they are important for ensuring properly rendered PivotTables in ExcelWriter output.

More details are available in our ExcelTemplate Templates and PivotTables and ExcelApplication Creating a Basic PivotTable tutorials.

PivotTable Field

A PivotTable is made up of different PivotTable fields, which can perform different actions on data. PivotTable fields are created from source fields. In Excel this is done by dragging a source field into one of the pivot field areas.

Row and Column Label

Row labels and Column Labels group the same values together to nest data and display subtotals for different groups. You can use row and column labels to transpose data.

You will note that there is a values label listed under the column labels. This is a row or column label that is automatically generated whenever there are two or more data fields. These represent the labels for the data fields in the table. This label can be either a row label or a column label and cannot be modified.

Report Filters (Page Fields)

Report Filters, or page fields, provide filtering across the entire PivotTable.

Data fields

Data fields, or value fields, are used to summarize and aggregate data values with formulas. The values computed from the data fields make up the majority of the PivotTable.

Data fields have different settings for how to aggregate and compute the data. The first of these is Summarize Values By, which determines the function used to aggregate the data, for example, Sum, Count, Average, Min, Max etc. The second is Show Values As, which allows you to change how the aggregation is display. For example, you can specify that you want to do a sum, but show the values as a percentage of the column total.

Next Steps

Those are the basics of PivotTables. To learn how to use PivotTables with ExcelTemplate, see Templates and PivotTables. To learn how to create or manipulate PivotTables with ExcelTemplate, see Creating a Basic PivotTable or our PivotTable API reference.