ExcelApplication xla = new ExcelApplication(); //Example.xlsx has a worksheet 'Data' with 9 columns and 244 rows of data Workbook wb = xla.Open("\\Example.xlsx"); Worksheet data_ws = wb.Worksheets["Data"]; Worksheet pivot_ws = wb.Worksheets.CreateWorksheet("Pivot"); |
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>
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.
Area data_area = data_ws.CreateArea(0, 0, 9, 244); |
CREATING A PIVOT TABLE
After creating the PivotTable, always set RefreshOnOpen
to true
.
pt.PivotTableSettings.RefreshOnOpen = 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.
Other PivotTable properties - friendly error on value, empty value
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.
Adding a page field - behavior
Adding a pivot table field - row labels / column labels, source fields, naming convention, changing the display name
Adding data fields - calculated fields, naming conventions, changing the display name
Summarize By
Show Value As