Page History

Area data_area = data_ws.CreateArea(0, 0, 9244, 2449); 

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, 9244, 2449);

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