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.

...

ExcelApplication xla = new ExcelApplication();

//Example.xlsx has a worksheet 'Data' with 9 columns and 244 rows of data
Workbook wb = xla.Open("\\BasicExample.xlsx");

Worksheet data_ws = wb.Worksheets["Data"];
Worksheet pivot_ws = wb.Worksheets.CreateWorksheet("Pivot");

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

Excel does not allow PivoTables to overlap and will use error messages to prevent users from creating overlapping PivotTables. ExcelWriter does not have the ability to render PivotTables, so it cannot detect if two PivotTables will overlap when they are rendered. To avoid this, we encourage you to keep your PivotTables on separate worksheets.

Writing the code

Step 1. Set up the data source

...

Step 4. Add PivotTableFields

Next, add PivotTableFields PivotTable fields to the PivotTable.

In Excel, there are four types of PivotTable fields: data (or value) fields, column labels, row labels, and page fields (or report filters), which are generated from source fields.

There are four types of PivotTableField: DataFields, RowLabels, ColumnLabels, and PageFields (or also called report filters). The Pivot field PivotTableField properties are available for all four types of PivotTableField, however some properties will not have an affect in the output file, depending on the type of field.

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.

...