Example #1
0
        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
            }
        }