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