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++; } } }
static void Main(string[] args) { // Declare variables that hold references to excel objects Application eApplication = null; Workbook eWorkbook = null; Worksheet sheet = null; PivotTable pivotTable = null; Range pivotData = null; Range pivotDestination = null; PivotField salesRegion = null; PivotField salesAmount = null; ChartObjects chartObjects = null; ChartObject pivotChart = null; SlicerCache salesTypeSlicer = null; SlicerCache salesRegionSlicer = null; SlicerCache salesPersonSlicer = null; // Declare helper variables string workBookName = @"C:\temp\Excel2010Slicers.xlsx"; string pivotTableName = @"Sales By Type"; string workSheetName = @"Quarterly Sales"; try { // Create an instance of Excel eApplication = new Application(); //Create a workbook and add a worksheet. eWorkbook = eApplication.Workbooks.Add( XlWBATemplate.xlWBATWorksheet); sheet = (Worksheet)(eWorkbook.Worksheets[1]); sheet.Name = workSheetName; //Add Data to the worksheet. //The custom SetRow helper function located at the end of this // source file is used to add a row of values to the worksheet. SetRow(sheet, 1, "Sales Region", "Sales Person", "Sales Type", "Sales Amount"); SetRow(sheet, 2, "West", "Joe", "Wholesale", "123"); SetRow(sheet, 3, "West", "Joe", "Retail", "432"); SetRow(sheet, 4, "West", "Joe", "Government", "111"); SetRow(sheet, 5, "East", "Robert", "Wholesale", "564"); SetRow(sheet, 6, "East", "Robert", "Retail", "234"); SetRow(sheet, 7, "East", "Robert", "Government", "321"); SetRow(sheet, 8, "East", "Michelle", "Wholesale", "940"); SetRow(sheet, 9, "East", "Michelle", "Retail", "892"); SetRow(sheet, 10, "East", "Michelle", "Government", "10"); SetRow(sheet, 11, "West", "Erich", "Wholesale", "120"); SetRow(sheet, 12, "West", "Erich", "Retail", "45"); SetRow(sheet, 13, "West", "Erich", "Government", "410"); SetRow(sheet, 14, "West", "Dafna", "Wholesale", "800"); SetRow(sheet, 15, "West", "Dafna", "Retail", "3409"); SetRow(sheet, 16, "West", "Dafna", "Government", "123"); SetRow(sheet, 17, "East", "Rob", "Wholesale", "777"); SetRow(sheet, 18, "East", "Rob", "Retail", "450"); SetRow(sheet, 19, "East", "Rob", "Government", "900"); sheet.Columns.AutoFit(); // Select a range of data for the Pivot Table. pivotData = sheet.get_Range("A1", "D19"); // Select location of the Pivot Table. pivotDestination = sheet.get_Range("F2"); // Add a pivot table to the worksheet. sheet.PivotTableWizard( XlPivotTableSourceType.xlDatabase, pivotData, pivotDestination, pivotTableName ); // Set variables used to manipulate the Pivot Table. pivotTable = (PivotTable)sheet.PivotTables(pivotTableName); salesRegion = ((PivotField)pivotTable.PivotFields(3)); salesAmount = ((PivotField)pivotTable.PivotFields(4)); // Format the Pivot Table. pivotTable.TableStyle2 = "PivotStyleLight16"; pivotTable.InGridDropZones = false; // Set Sales Region as a Row Field. salesRegion.Orientation = XlPivotFieldOrientation.xlRowField; // Set Sum of Sales Amount as a Value Field. salesAmount.Orientation = XlPivotFieldOrientation.xlDataField; salesAmount.Function = XlConsolidationFunction.xlSum; //Add a pivot chart to the work sheet. chartObjects = (ChartObjects)sheet.ChartObjects(); pivotChart = chartObjects.Add(310, 100, 225, 175); //Format the pivot chart. pivotChart.Chart.ChartWizard(pivotData, XlChartType.xlColumnClustered, Title: "Sales", HasLegend: false, CategoryLabels: 3, SeriesLabels: 0); //Add slicers to the pivot table. salesTypeSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Type"); salesTypeSlicer.Slicers.Add(sheet, Top: 10, Left: 540, Width: 100, Height: 100); salesRegionSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Region"); salesRegionSlicer.Slicers.Add(sheet, Top: 120, Left: 540, Width: 100, Height: 100); salesPersonSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Person"); salesPersonSlicer.Slicers.Add(sheet, Top: 10, Left: 645, Width: 100, Height: 200); // Save the workbook. sheet.get_Range("A1").Activate(); eWorkbook.SaveAs(workBookName, AccessMode: XlSaveAsAccessMode.xlNoChange); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { //Release the references to the Excel objects. salesAmount = null; salesRegion = null; pivotDestination = null; pivotData = null; pivotChart = null; chartObjects = null; pivotTable = null; salesTypeSlicer = null; salesRegionSlicer = null; salesPersonSlicer = null; sheet = null; //Release the Workbook object. if (eWorkbook != null) { eWorkbook = null; } //Release the ApplicationClass object. if (eApplication != null) { eApplication.Quit(); eApplication = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }