private void PivotData() { index++; Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches(); Excel.Range pivotData = (Excel.Range)osheet.UsedRange; Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData); Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index); Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW"); pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status"); rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status"); valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches; //Month Slicer string nameDateTime = "Slicer_DATETIME" + index.ToString(); string nameMonth = "Slicer_Month" + index.ToString(); Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline); Excel.Slicers monthSlicers = monthSlicerCache.Slicers; Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing, nameMonth, "Date Range", 160, 10, 250, 150); }
private void GenerateReport() { #region Initialize Excel.Workbook activeWorkBook = null; Excel.Worksheet pivotWorkSheet = null; Excel.PivotCaches pivotCaches = null; Excel.PivotCache pivotCache = null; Excel.PivotTable pivotTable = null; Excel.PivotFields pivotFields = null; Excel.PivotField monthPivotField = null; Excel.PivotField statusPivotField = null; Excel.PivotField resolvedPivotField = null; Excel.PivotField threatIdPivotField = null; Excel.PivotField threatIdCountPivotField = null; Excel.SlicerCaches slicerCaches = null; Excel.SlicerCache monthSlicerCache = null; Excel.Slicers monthSlicers = null; Excel.Slicer monthSlicer = null; Excel.SlicerCache statusSlicerCache = null; Excel.Slicers statusSlicers = null; Excel.Slicer statusSlicer = null; Excel.SlicerCache resolvedSlicerCache = null; Excel.Slicers resolvedSlicers = null; Excel.Slicer resolvedSlicer = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; try { pivotWorkSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Threat Reports"); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet.Application.DisplayAlerts = false; pivotWorkSheet.Delete(); pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet; pivotWorkSheet.Name = "Agent Reports"; pivotWorkSheet.Activate(); } catch { pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet; pivotWorkSheet.Name = "Agent Reports"; pivotWorkSheet.Activate(); } (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWindow.DisplayGridlines = false; #region Create Headings // Create headings // ================================================================================================================= Excel.Range title = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "A1"); title.ClearFormats(); title.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); title.Font.Color = System.Drawing.Color.White; title.InsertIndent(1); title.Font.Size = 18; title.VerticalAlignment = -4108; // xlCenter Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "CA1"); titleRow.Select(); titleRow.RowHeight = 33; titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "Threat Reports"; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 1] = "Generated by: " + userName; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 1] = DateTime.Now.ToString("f"); Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", "CA3"); rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); // rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick #endregion // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; // pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, "Threats!$A$4:$" + ExcelColumnLetter(colCount) + "$" + rowCount); // string rangeName = "Threats!$A$4:$T$100"; string rangeName = "'Agent Data'!$A$4:$" + eHelper.ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); // pivotTable = pivotCache.CreatePivotTable("Reports!R3C1"); pivotTable = pivotCache.CreatePivotTable("'Agent Reports'!R7C1"); pivotTable.NullString = "0"; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Month Pivot Field monthPivotField = (Excel.PivotField)pivotFields.Item("Created Date"); monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; monthPivotField.Position = 1; monthPivotField.DataRange.Cells[1].Group(true, true, Type.Missing, new bool[] { false, false, false, false, true, true, true }); // Mitigation Status Pivot Field statusPivotField = (Excel.PivotField)pivotFields.Item("Mitigation Status"); statusPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Resolved Pivot Field resolvedPivotField = (Excel.PivotField)pivotFields.Item("Resolved"); resolvedPivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; // Threat ID Pivot Field threatIdPivotField = (Excel.PivotField)pivotFields.Item("ID"); // Count of Threat ID Field threatIdCountPivotField = pivotTable.AddDataField(threatIdPivotField, "# of Threats", Excel.XlConsolidationFunction.xlCount); slicerCaches = activeWorkBook.SlicerCaches; // Month Slicer monthSlicerCache = slicerCaches.Add(pivotTable, "Created Date", "CreatedDate"); monthSlicers = monthSlicerCache.Slicers; monthSlicer = monthSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Created Date", "Created Date", 80, 480, 144, 100); // Mitigation Status Slicer statusSlicerCache = slicerCaches.Add(pivotTable, "Mitigation Status", "MitigationStatus"); statusSlicers = statusSlicerCache.Slicers; statusSlicer = statusSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Mitigation Status", "Mitigation Status", 80, 634, 144, 100); // Resolved Slicer resolvedSlicerCache = slicerCaches.Add(pivotTable, "Resolved", "Resolved"); resolvedSlicers = resolvedSlicerCache.Slicers; resolvedSlicer = resolvedSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Resolved", "Resolved", 80, 788, 144, 100); // Slicer original sizes top 15, width 144, height 200 } catch (Exception ex) { MessageBox.Show(ex.Message, "Error generating report", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { #region Finally if (resolvedSlicer != null) { Marshal.ReleaseComObject(resolvedSlicer); } if (resolvedSlicers != null) { Marshal.ReleaseComObject(resolvedSlicers); } if (resolvedSlicerCache != null) { Marshal.ReleaseComObject(resolvedSlicerCache); } if (statusSlicer != null) { Marshal.ReleaseComObject(statusSlicer); } if (statusSlicers != null) { Marshal.ReleaseComObject(statusSlicers); } if (statusSlicerCache != null) { Marshal.ReleaseComObject(statusSlicerCache); } if (monthSlicer != null) { Marshal.ReleaseComObject(monthSlicer); } if (monthSlicers != null) { Marshal.ReleaseComObject(monthSlicers); } if (monthSlicerCache != null) { Marshal.ReleaseComObject(monthSlicerCache); } if (slicerCaches != null) { Marshal.ReleaseComObject(slicerCaches); } if (threatIdCountPivotField != null) { Marshal.ReleaseComObject(threatIdCountPivotField); } if (threatIdPivotField != null) { Marshal.ReleaseComObject(threatIdPivotField); } if (resolvedPivotField != null) { Marshal.ReleaseComObject(resolvedPivotField); } if (statusPivotField != null) { Marshal.ReleaseComObject(statusPivotField); } if (monthPivotField != null) { Marshal.ReleaseComObject(monthPivotField); } if (pivotFields != null) { Marshal.ReleaseComObject(pivotFields); } if (pivotTable != null) { Marshal.ReleaseComObject(pivotTable); } if (pivotCache != null) { Marshal.ReleaseComObject(pivotCache); } if (pivotCaches != null) { Marshal.ReleaseComObject(pivotCaches); } if (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } if (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } #endregion } }