public static void CreatePivot(string tableSource, int[] pageFields, int[] rowFields, int[] dataFields, string pivotTableName = "Pivot Table", string[] slicerColumns = null)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
worksheet = workbook.Worksheets.Add(After: workbook.ActiveSheet);
worksheet.Name = "Pivot";
ListObject table = GetTable(tableSource);
Range rng = table.Range;
worksheet.PivotTableWizard(
XlPivotTableSourceType.xlDatabase,
rng,
worksheet.Range["A1"],
pivotTableName
);
PivotTable pivot = (PivotTable)worksheet.PivotTables(pivotTableName);
//pivot.HasAutoFormat = true;
pivot.ColumnGrand = true;
pivot.RowGrand = true;
for (int i = 0; i < pageFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(pageFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlPageField;
field1.Position = i + 1;
field1.CurrentPage = "(All)";
}
for (int i = 0; i < rowFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(rowFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlRowField;
field1.Position = i + 1;
}
PivotField columnField = pivot.PivotFields();
columnField.Orientation = XlPivotFieldOrientation.xlColumnField;
columnField.Position = 1;
for (int i = 0; i < dataFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(dataFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlDataField;
field1.Position = 1 + i;
field1.Function = XlConsolidationFunction.xlSum;
}
//Add Slicers
SlicerCaches caches = workbook.SlicerCaches;
int counter = 1;
if (slicerColumns != null)
{
foreach (string s in slicerColumns)
{
SlicerCache cache = caches.Add(pivot, s, s);
Slicers slicers = cache.Slicers;
Slicer slicer = slicers.Add(worksheet, Type.Missing, s, s, 160 * counter, 10, 144, 200);
counter++;
}
}
}
private void Awake()
{
slicers = GetComponentInParent <Slicers>();
}
