OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools

Versions Compared

Old Version 30

changes.mady.by.user Alison Bird [softartisans]

Saved on

compared with

New Version 31

changes.mady.by.user Alison Bird [softartisans]

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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;
  pivot.PageFields.CreateField(dateStart);
     pivot.PageFields.CreateField(dateEnd)salesFld.DisplaySubtotalsAtTop = false;

Excel automatically sorts and re-renders a PivotTable any time a change is made. By default, the row label or column label values are sorted alpha-numerically in ascending order.

...

This property only affects row and column labels.

Code Block
Insert code to sort the row label and column label values here
prodSubCategoryFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
prodNameFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;
salesFld.SortOptions.Ordering = SortOptions.OrderingType.Ascending;

When Excel refreshes the PivotTable, it will observe the SortOptions setting for a particular field.

...

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
CODEPivotTableField TOlp CREATE A DATA FIELD
= pt.DataFields.CreateField(listPrice);
PivotTableField dct = pt.DataFields.CreateField(discount);
PivotTableField udct = pt.DataFields.CreateField(unitDiscountPrice);

In Excel, a unique name is given to the data field depending on the type of data in the source field (numerical or mixed), whether or not any other data fields were already created from the same source field, and whether the source field name ends in a number or alphabetical character (e.g. Case1 vs. CaseOne).

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
CODElp.DisplayName FOR= CHANGING"Avg THEList DISPLAY NAME
Note

In Excel, a calculated field is a type of row or column label that is automatically generated when two or more data fields exist in the PivotTable. Calculated fields cannot be modified except to change whether the calculated field is a row label or a column label. Excel handles this by disabling access to the calculate fields' properties.

ExcelWriter will return null or default values for each PivotTableField property. Trying to set these properties will result in an exception. For more information on the values returned by calculated fields, please refer to THIS ANSWERS POST. 
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";

Completed Code

And that concludes how to create a basic PivotTable. Here is the full sample code below:

Code Block
INSERTExcelApplication COMPLETEDxla CODE= HERE
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, 9, 244);

//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;

SourceField prodSubCategory = pt.SourceFields[0], prodName = pt.SourceFields[1], sales = pt.SourceFields[2],
	 dateStart = pt.SourceFields[3], dateEnd = pt.SourceFields[4], prodStdCost = pt.SourceFields[5],
		listPrice = pt.SourceFields[6], discount = pt.SourceFields[7], unitDiscountPrice = pt.SourceFields[8];

//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");

Additional Resources

  • Documentation on creating an ExcelTemplate PivotTable
  • Documentation on modifying an existing PivotTable
  • PivotTable Settings Glossary
  • PivotTablEField Settings Glossary
  • Answers post on how to set properties that aren't available
  • Using CopySheet with PivotTables