private bool FieldExistsInPivotTable(string field, MSExcel.PivotCell cell) { MSExcel.PivotFields pFields = cell.PivotTable.PivotFields(); foreach (MSExcel.PivotField pField in pFields) { if (pField.Name.Equals(field)) { return(true); } } return(false); }
private bool IsPivotTableDataField(MSExcel.PivotCell cell, string fieldName) { MSExcel.PivotFields dataFields = cell.PivotTable.DataFields; foreach (MSExcel.PivotField dataField in dataFields) { if (dataField.SourceName == fieldName) { return(true); } } return(false); }
public static PivotCellDictionary GetPivotCellQuery(Excel.Range rngCell) { Excel.PivotTable pt = rngCell.PivotTable; Excel.PivotCell pc = rngCell.PivotCell; //Field values Excel.PivotFields pgfs = (Excel.PivotFields)(pt.PageFields); var pivotCellDic = new PivotCellDictionary(); AddSingleAxisFiltersToDic(pc, pivotCellDic); AddSinglePageFieldFiltersToDic(pgfs, pivotCellDic); AddMultiplePageFieldFilterToDic(pt, pivotCellDic); return(pivotCellDic); }
private void PivotGroupingWindowOkButton_Click(object sender, EventArgs e) { if (groups.Count <= 1) // groups are empty { return; } groups.ForEach(groupItem => { if (groupItem.Item2.Count <= 1) { return; } Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable; Excel.PivotFields pfs = pt.RowFields; string selectedFields = pfs.Item(0).Name + "["; groupItem.Item2.ForEach(item => selectedFields += item + ","); selectedFields = selectedFields.Substring(0, selectedFields.Length - 1); selectedFields += "]"; pt.PivotSelect(selectedFields, Excel.XlPTSelectionMode.xlLabelOnly); var selection = Globals.PivotGroupingAddIn.Application.Selection; selection.Group(); foreach (Excel.PivotField pf in pt.PivotFields()) { foreach (Excel.PivotItem rowItem in pf.PivotItems()) { if (rowItem.Value.StartsWith("Group")) { rowItem.Value = groupItem.Item1; } } } }); this.Close(); }
private static void AddSinglePageFieldFiltersToDic(Excel.PivotFields pfs, PivotCellDictionary pivotCellDic) { //Filter by page field if not all items are selected foreach (Excel.PivotField pf in pfs) { var dicCell = pivotCellDic.SingleSelectDictionary; if (DaxFilterCreator.PivotFieldIsHierarchy(pf.SourceName)) { continue; } string pageName = pf.DataRange.Value2; if (pageName == "All" || pageName == "(Multiple Items)") { continue; } var cubeField = pf.CubeField; dicCell.Add(pf.Name, cubeField.Name + ".&[" + pageName + "]"); } }
public void CreatePivot(string sheetName, int colCount, int rowCount, string pivotTableLocation, string pivotTableName, string rowField, string rowHeader, string columnField, string columnHeader) { #region Initialization 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 rowPivotField = null; Excel.PivotField columnPivotField = null; Excel.PivotField countPivotField = null; Excel.PivotItems pivotItems = null; Excel.Range pivotRange = null; Excel.PivotField hiddenPivotField = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; string rangeName = "'" + sheetName + "'!$A$4:$" + ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); pivotTable = pivotCache.CreatePivotTable(pivotTableLocation, pivotTableName); pivotTable.NullString = "0"; pivotTable.HasAutoFormat = false; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Row Pivot Field rowPivotField = (Excel.PivotField)pivotFields.Item(rowField); rowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; rowPivotField.Position = 1; pivotTable.CompactLayoutRowHeader = rowHeader; // Column Pivot Field columnPivotField = (Excel.PivotField)pivotFields.Item(columnField); // Causes error and not needed // columnPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Count Field countPivotField = pivotTable.AddDataField(columnPivotField, columnHeader, Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields(rowField).AutoSort(Excel.XlSortOrder.xlDescending, columnHeader); // Show only Top 10 results for the pivot table int top10 = 10; // pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, // DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } else { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } string cellValue = ""; pivotRange = pivotTable.RowRange; int itemCount = pivotRange.Count - 2; // Minus 2 because of title and total rows if (itemCount > top10) { pivotItems = pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotItems; for (int i = itemCount - 1; i >= top10; i--) { cellValue = (string)(pivotWorkSheet.Cells[pivotRange.Row + i + 1, pivotRange.Column] as Excel.Range).Value.ToString(); pivotItems.Item(cellValue).Visible = false; } } if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUser = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroup = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoint = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } string reportTable = ""; string reportLabel = "\""; string reportValue = ""; reportTable = "<table id=\"newspaper-a\" class=\"sortable\">"; string head1 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column] as Excel.Range).Value.ToString(); string head2 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<thead><tr><th scope=\"col\">" + head1 + "</th><th style=\"text-align:right;\" scope=\"col\" nowrap>" + head2 + "</th></tr></thead><tbody>"; string col1 = ""; string col2 = ""; string labelShorted = ""; int tableItems = itemCount > 10 ? 10 : itemCount; for (int i = 1; i <= tableItems; i++) { col1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column] as Excel.Range).Value.ToString(); col2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<tr><td>" + col1 + "</td><td style=\"text-align:right;\">" + col2 + "</td></tr>"; if (tableItems > 5 && col1.Length > 10) { labelShorted = col1.Substring(0, 10) + ".."; } else if (col1.Length > 20) { labelShorted = col1.Substring(0, 20) + ".."; } else { labelShorted = col1; } reportLabel = reportLabel + labelShorted + "\",\""; reportValue = reportValue + col2 + ","; } string foot1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column] as Excel.Range).Value.ToString(); string foot2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "</tbody><tfoot><tr><td>" + foot1 + "</td><td style=\"text-align:right;\">" + foot2 + "</td></tr></tfoot></table>"; reportValue = reportValue.TrimEnd(','); reportLabel = reportLabel.TrimEnd('\"'); reportLabel = reportLabel.TrimEnd(','); if (pivotTableName == "PivotTableClassifier") { Globals.DetectionEngine = reportTable; Globals.DetectionEnginesLabel = reportLabel; Globals.DetectionEnginesValue = reportValue; } else if (pivotTableName == "PivotTableFileDisplayName") { Globals.InfectedFiles = reportTable; Globals.InfectedFilesLabel = reportLabel; Globals.InfectedFilesValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroups = reportTable; Globals.MostAtRiskGroupsLabel = reportLabel; Globals.MostAtRiskGroupsValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUsers = reportTable; Globals.MostAtRiskUsersLabel = reportLabel; Globals.MostAtRiskUsersValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoints = reportTable; Globals.MostAtRiskEndpointsLabel = reportLabel; Globals.MostAtRiskEndpointsValue = reportValue; } else if (pivotTableName == "PivotTableIsActive") { Globals.NetworkStatus = reportTable; Globals.NetworkStatusLabel = reportLabel; Globals.NetworkStatusValue = reportValue; } else if (pivotTableName == "PivotTableOs") { Globals.EndpointOS = reportTable; Globals.EndpointOSLabel = reportLabel; Globals.EndpointOSValue = reportValue; } else if (pivotTableName == "PivotTableAgent") { Globals.EndpointVersion = reportTable; Globals.EndpointVersionLabel = reportLabel; Globals.EndpointVersionValue = reportValue; } else if (pivotTableName == "PivotTableApplicationName") { Globals.TopApplications = reportTable; Globals.TopApplicationsLabel = reportLabel; Globals.TopApplicationsValue = reportValue; } Excel.Range colToFormat = pivotWorkSheet.get_Range("K:Q", System.Type.Missing); colToFormat.EntireColumn.AutoFit(); Excel.Range colToEdit = pivotWorkSheet.get_Range("K:K", System.Type.Missing); // Was 28, changed 11/9/2017 if (pivotTableName == "PivotTableApplicationName") { colToEdit.EntireColumn.ColumnWidth = 70; } else if (colToEdit.EntireColumn.ColumnWidth > 35) { colToEdit.EntireColumn.ColumnWidth = 35; } // Customizing the pivot table style pivotWorkSheet.PivotTables(pivotTableName).TableStyle2 = "PivotStyleMedium9"; // Remembers the bottom of the pivot table so that the next one will not overlap Globals.PivotBottom = pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Row + pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count; Excel.Range rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + Globals.PivotBottom.ToString(), "A" + Globals.PivotBottom.ToString()); // Globals.ChartBottom = (int)rng.Top + (int)rng.Height; } catch (Exception ex) { (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 3] = ex.Message; /* * Excel.PivotTable pt = pivotWorkSheet.PivotTables(pivotTableName); * Excel.Range pr = pt.TableRange2; * pr.Clear(); */ } finally { #region Finally if (countPivotField != null) { Marshal.ReleaseComObject(countPivotField); } if (columnPivotField != null) { Marshal.ReleaseComObject(columnPivotField); } if (rowPivotField != null) { Marshal.ReleaseComObject(rowPivotField); } 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 (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } if (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } #endregion } }
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 } }