OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools
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 17 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 BasicExample.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. See Adding OfficeWriter to your .NET Application.

Icon

This example places the PivotTable on a separate sheet. It is recommended that each PivotTable be placed on a separate sheet.

This is because Excel does not allow PivotTables to overlap. When building PivotTables in Excel, an error message will be thrown if performing an action would cause two PivotTables to overlap. ExcelWriter does not have the ability to render PivotTables so it cannot calculate which cells a PivotTable occupies. Therefore, it cannot detect when PivotTables might overlap and will not throw an exception even if the code generates overlapping PivotTables.

To avoid this, we encourage you to keep your PivotTables on separate worksheets.

Writing the code

Step 1. 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, which can also be found in BasicExample.xlsx:

There are 9 columns and 244 rows in the data set, including the row with the header values.

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.

Step 2. Create the PivotTable

To create a PivotTable, call CreatePivotTable on the PivotTables collection. Specify the 0-indexed row and column values for the PivotTable location:

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.

Step 3. 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.

All PivotTableFields are created from SourceFields. A SourceField is a read-only field that is automatically generated from the data source of the PivotTable. Each column in the data source corresponds with a SourceField in the PivotTable and the name of the SourceField comes from the column header name.

Icon

A SourceField can be used to create one PageField, ColumnLabel, or RowLabel, in addition to an unlimited number of DataFields.

PageFields

To add a PageField, call CreateField on the PivotTable.PageFields collection. You will need to specify the SourceField that will be used to create the PageField.

Icon

PageFields are always placed two rows above the upper left corner of the PivotTable, so if a PivotTable is placed in cell B10, then page fields will be in B8. Any additional PageFields are added to B8 and existing PageFields are moved up a row (B7, B6 and so on).

If there is no space to add the PageFields above the PivotTable (i.e. the PivotTable is located in cell A1), then ExcelWriter will automatically move the PivotTable down to accommodate the PageFields.

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

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