Example #1
0
        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
            }
        }
Example #2
0
        /// <summary>
        /// Returns Query object to be passed to SqlGenerator, which transforms the Query object into SQL string.
        /// This is meant to be used with a Range that is a pivot cell.
        /// </summary>
        /// <param name="cell"></param>
        /// <returns>Query</returns>
        private Query?CreateQueryFromPivotCell(MSExcel.Range cell)
        {
            // First check if pivot cell is a type that can be drilled into (custom subtotal, subtotal, grand total, or cell value.
            // If not, then throw exception.
            if (!PivotCellTypeIsAmount(cell.PivotCell))
            {
                throw new Exception("Cannot drill into this cell");
            }

            var query = new Query("drilldown");

            query.SetColumns(_workbookPropertiesConfig.DrilldownSql.Split(',').ToList());
            var table = _workbookPropertiesConfig.SelectedTable + "_detail";

            query.SetTable(table);
            query.SetSuppressNulls(false);

            try
            {
                var tableSchema = _dbConnection.GetColumns(table);
                query.SetTableSchema(tableSchema);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            // Add row fields and items.
            Criteria criteria;

            foreach (MSExcel.PivotItem item in cell.PivotCell.RowItems)
            {
                criteria = new Criteria();

                var columnName = item.Parent.Name;

                criteria.AndOr = Conjunction.And;
                //criteria.FrontParenthesis = null;
                criteria.Column   = columnName;
                criteria.Operator = Operator.EqualTo;
                //criteria.EndParenthesis = null;

                if (NULL_EQUIVALENTS.Contains(item.Name))
                {
                    criteria.OrIsNull = true;
                }
                else
                {
                    criteria.Filter = item.Name;
                }

                query.AddSingleCriteria(criteria);
            }

            // Add column fields and items.
            foreach (MSExcel.PivotItem item in cell.PivotCell.ColumnItems)
            {
                criteria = new Criteria();

                var columnName = item.Parent.Name;

                criteria.AndOr = Conjunction.And;
                //criteria.FrontParenthesis = null;
                criteria.Column   = columnName;
                criteria.Operator = Operator.EqualTo;
                //criteria.EndParenthesis = null;

                if (NULL_EQUIVALENTS.Contains(item.Name))
                {
                    criteria.OrIsNull = true;
                }
                else
                {
                    criteria.Filter = item.Name;
                }

                query.AddSingleCriteria(criteria);
            }

            // Add all other pivot fields and items that are visible.
            try
            {
                // Loop thru each drilldown table field
                foreach (DataRow row in query.TableSchema.Rows)
                {
                    var field = row["COLUMN_NAME"].ToString();

                    // If a criteria does not already exist for this field/column
                    if (!query.CriteriaExistsForColumn(field))
                    {
                        // Only attempt to add fields that exist in the pivot table (ie: fields that have items in the pivot table).
                        if (FieldExistsInPivotTable(field, cell.PivotCell))
                        {
                            // And the field if is NOT a data field.
                            if (!IsPivotTableDataField(cell.PivotCell, field))
                            {
                                var fieldHasNullItems         = false;
                                MSExcel.PivotItems fieldItems = cell.PivotCell.PivotTable.PivotFields(field).PivotItems();
                                var filter = "";

                                foreach (MSExcel.PivotItem item in fieldItems)
                                {
                                    if (item.Visible)
                                    {
                                        if (NULL_EQUIVALENTS.Contains(item.Name))
                                        {
                                            fieldHasNullItems = true;
                                        }
                                        else
                                        {
                                            filter += $"{item.Name},";
                                        }
                                    }
                                }

                                if (filter.Length > 0)
                                {
                                    filter = filter.Substring(0, filter.Length - 1);

                                    criteria = new Criteria();

                                    filter.Substring(0, filter.Length - 1);

                                    criteria.AndOr = Conjunction.And;
                                    //criteria.FrontParenthesis = (fieldHasNullItems) ? "(" : null;
                                    criteria.Column   = field;
                                    criteria.Operator = Operator.In;
                                    criteria.Filter   = filter;
                                    criteria.OrIsNull = (fieldHasNullItems) ? true : false;
                                    //criteria.Filter = (fieldHasNullItems) ? $"{filter} OR {field} IS NULL" : filter;
                                    //criteria.EndParenthesis = (fieldHasNullItems) ? ")" : null;

                                    query.ReplaceAllMatchingCriteria(criteria);
                                }

                                //if (filter.Length > 0)
                                //{
                                //    criteria = new Criteria();

                                //    filter.Substring(0, filter.Length - 1);

                                //    criteria.AndOr = "AND";
                                //    criteria.FrontParenthesis = (fieldHasNullItems) ? "(" : null;
                                //    criteria.Column = field;
                                //    criteria.Operator = "In";
                                //    criteria.Filter = (fieldHasNullItems) ? $"{filter} OR {field} IS NULL" : filter;
                                //    criteria.EndParenthesis = (fieldHasNullItems) ? ")" : null;

                                //    query.ReplaceAllMatchingCriteria(criteria);
                                //}
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Drilldown", MessageBoxButtons.OK);
                return(null);
            }

            return(query);
        }