.
Table of Contents | |
|---|---|
|
Intro
| Note | ||
|---|---|---|
|
| Note |
|---|
This example assumes a basic level of understanding the different parts of a PivotTable and how they work. If you are not familiar with PivotTable terminology, we highly recommend that you read this Special Topics article before continuing. |
...
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.
| Code Block |
|---|
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["Pivot"];
|
...
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.
| Code Block |
|---|
Area data_area = data_ws.CreateArea(0, 0, 244, 9);
|
...
To create a PivotTable, call CreatePivotTable on the PivotTables collection. Specify the 0-indexed row and column values for the PivotTable location:
| Code Block |
|---|
PivotTable pt = pivot_ws.PivotTables.CreatePivotTable(data_area, 0, 0);
|
...
After creating the PivotTable, always set RefreshOnOpen to true.
| Code Block |
|---|
pt.PivotTableSettings.RefreshOnOpen = true;
|
...
By default, the PivotTable will retain all the original values in those filters, even if those values are no longer in the data source. Set ItemsToRetain to None to make sure the original values are cleared out when the PivotTable is refreshed.
| Code Block |
|---|
pt.PivotTableSettings.ItemsToRetain.Value = ItemsToRetain.None;
|
...
Get a handle on the SourceFields to use for building PivotTableFields later. SourceFields can be referenced by the column header name or by column index.
| Code Block |
|---|
SourceField prodSubCategory = pt.SourceFields["ProductSubCategoryName"];
SourceField prodName = pt.SourceFields["Product Name"];
SourceField sales = pt.SourceFields["Sales"];
SourceField dateStart = pt.SourceFields["Special Offer Start Date"];
SourceField dateEnd = pt.SourceFields["Special Offer End Date"];
SourceField prodStdCost = pt.SourceFields["Product Standard Cost"];
SourceField listPrice = pt.SourceFields["Product List Price"];
SourceField discount = pt.SourceFields["Special Offer Dicount"];
SourceField unitDiscountPrice = pt.SourceFields["Unit Price with Discount"];
|
...
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.
| Code Block |
|---|
pivot.PageFields.CreateField(dateStart);
pivot.PageFields.CreateField(dateEnd);
|
...
Similarly to page fields, RowLabels and ColumnLabels are created on the PivotTable.RowLabels and PivotTable.ColumnLabels collections. As mentioned earlier, only one RowLabel, ColumnLabel, or PageField can be created from a particular SourceField.
| Code Block |
|---|
PivotTableField prodSubCategoryFld = pt.RowLabels.CreateField(prodSubCategory);
PivotTableField prodNameFld = pt.RowLabels.CreateField(prodName);
PivotTableField salesFld = pt.RowLabels.CreateField(sales);
|
Row labels and column labels display the subtotals for each group of values. The subtotal can be displayed at the top or bottom of the group.
| Code Block |
|---|
prodNameFld.DisplaySubtotalsAtTop = true;
salesFld.DisplaySubtotalsAtTop = false;
|
...
Since ExcelWriter does not have the ability to render PivotTables or sort the values for a field, the only way to guarantee that the data will be sorted is to set RefreshOnOpen to true and set SortOptions.Ordering on a PivotTablefield PivotTableField to be Ascending or Descending.
This property only affects row and column labels.
| Code Block |
|---|
prodSubCategoryFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
prodNameFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
salesFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
|
...
To create a data field, call CreateField on the DataFields collection. Unlike row labels, column labels, and page fields, multiple data fields can be created from the same source field.
| Code Block |
|---|
PivotTableField lp = pt.DataFields.CreateField(listPrice);
PivotTableField dct = pt.DataFields.CreateField(discount);
PivotTableField udct = pt.DataFields.CreateField(unitDiscountPrice);
|
...
ExcelWriter uses a consistent naming convention when creating data fields: all data fields follow the format SOURCEFIELDNAME#, where _# is an incremental number starting at 1. To change the name of a data field, use the DisplayName property.
| Code Block |
|---|
lp.DisplayName = "Avg List Price";
dct.DisplayName = "Avg Discount";
udct.DisplayName = "Avg Discount Price";
|
The function used to aggregate the data can be specified through the summarize by property. If a column data contains just numbers, the function defaults to SUM. If the column contains mixed data, the function defaults to COUNT. There are other functions available.
| Code Block |
|---|
lp.SummarizeBy = PivotTableField.SummarizeByType.Average;
dct.SummarizeBy = PivotTableField.SummarizeByType.Average;
udct.SummarizeBy = PivotTableField.SummarizeByType.Average;
|
A number format can be specified for all PivotTable fields, but it will only take affect on data fields and row labels/column labels/page fields that have numeric data only.
| Code Block |
|---|
lp.NumberFormat = "$#,##0.00";
dct.NumberFormat = "0.00%";
udct.NumberFormat = "$#,##0.00";
|
...
And that concludes how to create a basic PivotTable. Here is the full sample code below:
| Code Block |
|---|
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open("\\BasicExample.xlsx"); //Template already contains the data
Worksheet data_ws = wb.Worksheets["Data"];
Worksheet pivot_ws = wb.Worksheets["Pivot"];
Area data_area = data_ws.CreateArea(0, 0, 244, 9);
//Create a PivotTable in cell A1 on worksheet 'Pivot'
PivotTable pt = pivot_ws.PivotTables.CreatePivotTable(data_area, 0, 0);
//Set some of the PivotTable options
pt.PivotTableSettings.RefreshOnOpen = true;
pt.PivotTableSettings.ItemsToRetain.Value = ItemsToRetain.None;
//Get a handle on the source fields
SourceField prodSubCategory = pt.SourceFields["ProductSubCategoryName"];
SourceField prodName = pt.SourceFields["Product Name"];
SourceField sales = pt.SourceFields["Sales"];
SourceField dateStart = pt.SourceFields["Special Offer Start Date"];
SourceField dateEnd = pt.SourceFields["Special Offer End Date"];
SourceField prodStdCost = pt.SourceFields["Product Standard Cost"];
SourceField listPrice = pt.SourceFields["Product List Price"];
SourceField discount = pt.SourceFields["Special Offer Dicount"];
SourceField unitDiscountPrice = pt.SourceFields["Unit Price with Discount"];
//Create some page fields
pt.PageFields.CreateField(dateStart);
pt.PageFields.CreateField(dateEnd);
//Create some row labels
PivotTableField prodSubCategoryFld = pt.RowLabels.CreateField(prodSubCategory);
PivotTableField prodNameFld = pt.RowLabels.CreateField(prodName);
PivotTableField salesFld = pt.RowLabels.CreateField(sales);
//Set where the subtotals for the groups are displayed
prodNameFld.DisplaySubtotalsAtTop = true;
salesFld.DisplaySubtotalsAtTop = false;
//Set the sort type to be ascending
prodSubCategoryFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
prodNameFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
salesFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
//Create some data fields
PivotTableField lp = pt.DataFields.CreateField(listPrice);
PivotTableField dct = pt.DataFields.CreateField(discount);
PivotTableField udct = pt.DataFields.CreateField(unitDiscountPrice);
//Set the display name
lp.DisplayName = "Avg List Price";
dct.DisplayName = "Avg Discount";
udct.DisplayName = "Avg Discount Price";
//Set the summarize by values
lp.SummarizeBy = PivotTableField.SummarizeByType.Average;
dct.SummarizeBy = PivotTableField.SummarizeByType.Average;
udct.SummarizeBy = PivotTableField.SummarizeByType.Average;
//Set the number format
lp.NumberFormat = "$#,##0.00";
dct.NumberFormat = "0.00%";
udct.NumberFormat = "$#,##0.00";
xla.Save(wb, "\\BasicExampleOut.xlsx");
|
...