Page History

SourceField prodSubCategory = pivotpt.SourceFields["ProductSubCategoryName"]; 
SourceField prodName = pivotpt.SourceFields["Product Name"];
SourceField sales = pivotpt.SourceFields["Sales"];
SourceField dateStart = pivotpt.SourceFields["Special Offer Start Date"];
SourceField dateEnd = pivotpt.SourceFields["Special Offer End Date"];
SourceField prodStdCost = pivotpt.SourceFields["Product Standard Cost"];
SourceField listPrice = pivotpt.SourceFields["Product List Price"];
SourceField discount = pivotpt.SourceFields["Special Offer Dicount"];
SourceField unitDiscountPrice = pivotpt.SourceFields["Unit Price with Discount"];

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 = pivotpt.SourceFields["ProductSubCategoryName"]; 
SourceField prodName = pivotpt.SourceFields["Product Name"];
SourceField sales = pivotpt.SourceFields["Sales"];
SourceField dateStart = pivotpt.SourceFields["Special Offer Start Date"];
SourceField dateEnd = pivotpt.SourceFields["Special Offer End Date"];
SourceField prodStdCost = pivotpt.SourceFields["Product Standard Cost"];
SourceField listPrice = pivotpt.SourceFields["Product List Price"];
SourceField discount = pivotpt.SourceFields["Special Offer Dicount"];
SourceField unitDiscountPrice = pivotpt.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");