...
When Excel refreshes the PivotTable, it will observe the SortOptions
setting for a particular field.
DataFields
Adding data fields - calculated fields, naming conventions, changing the display name
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 |
---|
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, 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
- Templates and PivotTables - using PivotTables with ExcelTemplate
- Intro to PivotTables - Terminology from Excel
- Answers post on how to set properties that aren't available
- Using CopySheet with PivotTables