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(); } }