Exemple #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
            }
        }
        /// <summary>
        /// Creates all the pivot tables, slicers, and row fields, filters and Description, based on values from
        /// their hashTables.
        /// </summary>
        /// <param name="inputCSVFile"></param>
        /// <param name="PivotOutputReportFullPath"></param>
        /// <param name="newSheetName"></param>
        /// <param name="inputDataSheetName"></param>
        /// <param name="PivottableName"></param>
        /// <param name="htRowPivotFields"></param>
        /// <param name="htPgaefilterFields"></param>
        /// <param name="htSlicers"></param>
        /// <param name="pivotCountField"></param>
        /// <param name="component"></param>
        /// <param name="numberOfFilesCount"></param>
        /// <param name="otherNodes"></param>
        public static void GeneratePivotAndSlicersView(string inputCSVFile, string PivotOutputReportFullPath, ref string newSheetName, string inputDataSheetName, string PivottableName, Hashtable htRowPivotFields, Hashtable htPgaefilterFields, Hashtable htSlicers, string pivotCountField, XmlNode component, int numberOfFilesCount, List <XmlNode> otherNodes)
        {
            Excel.Application oApp;
            Excel.Worksheet   oSheet;
            Excel.Workbook    oBook   = null;
            Excel.Worksheet   oSheet1 = null;
            Excel.Worksheet   oSheet2 = null;
            oApp = new Excel.Application();
            var     workbooks = oApp.Workbooks;
            string  sheetName = newSheetName;
            XmlNode style     = otherNodes.Find(item => item.Name == "Style");

            Excel.Range rangeToChange = null;

            string exceptionComment = "Processing for CSV :" + inputCSVFile;

            Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Started for (" + inputCSVFile + ")"), false);
            Excel.Workbook oDiscoveryViewook = null;
            try
            {
                oBook = oApp.Workbooks.Open(inputCSVFile);
                //Excel.Workbook oDiscoveryViewook = null;

                if (System.IO.File.Exists(PivotOutputReportFullPath))
                {
                    oDiscoveryViewook = workbooks.Open(PivotOutputReportFullPath);
                    // create multiple sheets
                    if (oApp.Application.Application.Sheets.Count >= 1)
                    {
                        oSheet2 = oDiscoveryViewook.Worksheets.OfType <Excel.Worksheet>().FirstOrDefault(ws => ws.Name == sheetName);
                        oSheet1 = (Excel.Worksheet)oDiscoveryViewook.Worksheets.Add(After: oDiscoveryViewook.Sheets[oDiscoveryViewook.Sheets.Count]);
                        if (oSheet2 == null)
                        {
                            oSheet1.Name = newSheetName;
                        }
                        else
                        {
                            if (newSheetName.Length >= Constants.SheetNameMaxLength)
                            {
                                newSheetName = newSheetName.Remove(newSheetName.Length - 3);
                            }

                            newSheetName = newSheetName + "_" + numberOfFilesCount;
                            oSheet1.Name = newSheetName;
                        }
                    }
                    else
                    {
                        oSheet1 = oApp.Worksheets[2];
                    }
                }

                if (inputDataSheetName.Length >= Constants.SheetNameMaxLength)
                {
                    oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(1);
                }
                else
                {
                    oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(inputDataSheetName);
                }


                // now capture range of the first sheet
                Excel.Range oRange = oSheet.UsedRange;
                // specify first cell for pivot table
                Excel.Range oRange2 = (Excel.Range)oSheet1.Cells[3, 1];
                //Create Pivot Cache

                if (oRange.Rows.Count > 1)
                {
                    PivotCache oPivotCache = oDiscoveryViewook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, oRange, XlPivotTableVersionList.xlPivotTableVersion14);
                    PivotTable oPivotTable = oPivotCache.CreatePivotTable(TableDestination: oRange2, TableName: PivottableName);

                    //Creating row pivot fields
                    foreach (DictionaryEntry rowField in htRowPivotFields)
                    {
                        string rowFieldContent = rowField.Value.ToString();
                        string rowFieldValue   = rowFieldContent.Substring(0, rowFieldContent.IndexOf("~"));
                        string rowFieldLabel   = rowFieldContent.Substring(rowFieldContent.IndexOf("~") + 1);
                        if ((Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue)) != null)
                        {
                            Excel.PivotField oPivotFieldPivotFieldName = (Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue));
                            oPivotFieldPivotFieldName.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                            oPivotTable.CompactLayoutRowHeader    = rowFieldLabel;
                        }
                        else
                        {
                            Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Error: No data available"), true);
                        }
                    }

                    //page filters
                    foreach (DictionaryEntry rowPgaeField in htPgaefilterFields)
                    {
                        Excel.PivotField scPageFilterFiled = oPivotTable.PivotFields(Convert.ToString(rowPgaeField.Value));
                        scPageFilterFiled.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                    }

                    //Count Field
                    Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields(pivotCountField);
                    oPivotTable.AddDataField(oPivotField2, "Count of " + pivotCountField + "", Excel.XlConsolidationFunction.xlCount);

                    rangeToChange = oPivotTable.TableRange2;

                    oSheet1.Activate();
                    rangeToChange.Select();
                    //excelApp.Selection.Font;
                    oSheet1.Application.Selection.Font.Name = CommonHelper.CheckAttributes("FontFamily", null, style);
                    oSheet1.Application.Selection.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", null, style));

                    //Create Slicer Cache Object
                    int Slicerpos = 0, slicersCount = 0;
                    foreach (DictionaryEntry rowSlicer in htSlicers)
                    {
                        slicersCount++;
                        string rowSlicerValue = rowSlicer.Value.ToString();
                        if (rowSlicerValue.Contains("~"))
                        {
                            string            sliceName    = rowSlicerValue.Substring(0, rowSlicerValue.IndexOf("~"));
                            string            sliceStyle   = rowSlicerValue.Substring(rowSlicerValue.IndexOf("~") + 1);
                            Excel.SlicerCache oSlicerCache = (Excel.SlicerCache)oDiscoveryViewook.SlicerCaches.Add2(oPivotTable, sliceName);
                            Excel.Slicer      oSlicer      = (Excel.Slicer)oSlicerCache.Slicers.Add(oSheet1, Type.Missing, sliceName + "_" + newSheetName, sliceName, Top: 30, Left: 400 + Slicerpos, Width: 144, Height: 200);
                            oSlicer.Style = sliceStyle;

                            //To Move Left Position of next slicers(2,3...)
                            Slicerpos += 190;
                        }
                    }
                }
                Range Line = (Range)oSheet1.Rows[1];
                Line.Insert();

                XmlNode descriptionTitle = component.SelectSingleNode("DescriptionTitle");
                XmlNode description      = component.SelectSingleNode("Description");

                //Get the range of sheet to fill count
                XmlNode styleOfDescription = style.SelectSingleNode("ComponentStyle").SelectSingleNode("Description");

                Excel.Range descriptionRange = oSheet1.get_Range("B1", "O1");
                descriptionRange.Merge();

                string descText = description.InnerText.Trim();
                descriptionRange.Value = descText.Replace("\r", "").Replace("\n", "");
                descriptionRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                descriptionRange.Columns.AutoFit();
                descriptionRange.RowHeight      = Convert.ToDouble(CommonHelper.CheckAttributes("RowHeight", styleOfDescription, style));
                descriptionRange.WrapText       = true;
                descriptionRange.Interior.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescription, style));
                descriptionRange.Font.Color     = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescription, style));
                descriptionRange.Font.Size      = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescription, style));
                descriptionRange.Font.Name      = CommonHelper.CheckAttributes("FontFamily", styleOfDescription, style);
                descriptionRange.Borders.Color  = XlRgbColor.rgbSlateGray;

                //Get the range of sheet to fill count

                XmlNode styleOfDescTitle = style.SelectSingleNode("ComponentStyle").SelectSingleNode("DescriptionTitle");

                Excel.Range descriptionTitleRange = oSheet1.get_Range("A1", "A1");
                descriptionTitleRange.Value = descriptionTitle.InnerText.Trim();
                //(styleOfDescription.Attributes["FontFamily"] == null || (styleOfDescription.Attributes["FontFamily"].InnerText == null) ? fontFamily : styleOfDescription.Attributes["FontFamily"].InnerText;
                descriptionTitleRange.ColumnWidth             = Convert.ToDouble(CommonHelper.CheckAttributes("ColumnWidth", styleOfDescTitle, style));
                descriptionTitleRange.Interior.Color          = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescTitle, style));
                descriptionTitleRange.Font.Color              = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescTitle, style));
                descriptionTitleRange.Borders.Color           = XlRgbColor.rgbSlateGray;
                descriptionTitleRange.Font.Size               = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescTitle, style));
                descriptionTitleRange.Font.Name               = CommonHelper.CheckAttributes("FontFamily", styleOfDescTitle, style);
                descriptionTitleRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                descriptionTitleRange.Font.Bold               = true;

                //descriptionTitleRange.Columns.AutoFit();

                oDiscoveryViewook.Save();
                oDiscoveryViewook.Close();

                object misValue = System.Reflection.Missing.Value;
                oBook.Close(false, misValue, misValue);

                oApp.Quit();
                oApp.Application.Quit();

                Marshal.ReleaseComObject(oBook);
                Marshal.ReleaseComObject(workbooks);
                Marshal.ReleaseComObject(oDiscoveryViewook);

                Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Completed for (" + inputCSVFile + ") and sheet " + newSheetName + " is created in Pivot Output file: " + PivotOutputReportFullPath), true);
            }
            catch (Exception ex)
            {
                if (oDiscoveryViewook != null)
                {
                    oDiscoveryViewook.Save();
                    oDiscoveryViewook.Close();
                }

                object misValue = System.Reflection.Missing.Value;
                if (oBook != null)
                {
                    oBook.Close(false, misValue, misValue);
                }
                if (oApp != null)
                {
                    oApp.Quit();
                    oApp.Application.Quit();
                }

                Marshal.ReleaseComObject(oBook);
                Marshal.ReleaseComObject(workbooks);
                Marshal.ReleaseComObject(oDiscoveryViewook);

                Logger.LogErrorMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView][Exception]: " + ex.Message + ", " + exceptionComment), true);
                ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
                                            "[GeneratePivotReports]: GeneratePivotAndSlicersView", ex.GetType().ToString(), exceptionComment);
            }
            finally
            {
                Marshal.ReleaseComObject(oApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }