internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable) :
base(ns, null)
{
foreach (var r in pivotTable.Part.GetRelationshipsByType(ExcelPackage.schemaRelationships + "/pivotCacheDefinition"))
{
Relationship = r;
}
CacheDefinitionUri = PackUriHelper.ResolvePartUri(Relationship.SourceUri, Relationship.TargetUri);
var pck = pivotTable.WorkSheet._package.Package;
Part = pck.GetPart(CacheDefinitionUri);
CacheDefinitionXml = new XmlDocument();
LoadXmlSafe(CacheDefinitionXml, Part.GetStream());
TopNode = CacheDefinitionXml.DocumentElement;
PivotTable = pivotTable;
if (CacheSource == eSourceType.Worksheet)
{
var worksheetName = GetXmlNodeString(_sourceWorksheetPath);
if (pivotTable.WorkSheet.Workbook.Worksheets.Any(t => t.Name == worksheetName))
{
_sourceRange = pivotTable.WorkSheet.Workbook.Worksheets[worksheetName].Cells[GetXmlNodeString(_sourceAddressPath)];
}
}
}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node) :
// base(drawings, node/*, 1*/)
//{
// SetChartNodeText("");
//}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type) :
// base(drawings, node, type)
//{
// SetChartNodeText("");
// SetTypeProperties(drawings, type);
//}
internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
SetChartNodeText("");
SetTypeProperties(drawings, type);
}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node) :
// base(drawings, node/*, 1*/)
//{
// SetChartNodeText("");
//}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type) :
// base(drawings, node, type)
//{
// SetChartNodeText("");
// SetTypeProperties(drawings, type);
//}
internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
SetChartNodeText("");
SetTypeProperties(drawings, type);
}
internal void SetPivotSource(ExcelPivotTable pivotTableSource)
{
PivotTableSource = pivotTableSource;
XmlElement chart = ChartXml.SelectSingleNode("c:chartSpace/c:chart", NameSpaceManager) as XmlElement;
var pivotSource = ChartXml.CreateElement("pivotSource", ExcelPackage.schemaChart);
chart.ParentNode.InsertBefore(pivotSource, chart);
pivotSource.InnerXml = string.Format("<c:name>[]{0}!{1}</c:name><c:fmtId val=\"0\"/>", PivotTableSource.WorkSheet.Name, pivotTableSource.Name);
var fmts = ChartXml.CreateElement("pivotFmts", ExcelPackage.schemaChart);
chart.PrependChild(fmts);
fmts.InnerXml = "<c:pivotFmt><c:idx val=\"0\"/><c:marker><c:symbol val=\"none\"/></c:marker></c:pivotFmt>";
Series.AddPivotSerie(pivotTableSource);
}
internal ExcelBubbleChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
ShowNegativeBubbles = false;
BubbleScale = 100;
_chartSeries = new ExcelBubbleChartSeries(this, drawings.NameSpaceManager, _chartNode, PivotTableSource != null);
//SetTypeProperties();
}
internal ExcelDoughnutChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
}
/// <summary>
/// Adds a new surface chart to the worksheet.
/// </summary>
/// <param name="Name"></param>
/// <param name="ChartType">Type of chart</param>
/// <param name="PivotTableSource">The pivottable source for a pivotchart</param>
/// <returns>The chart</returns>
public ExcelSurfaceChart AddSurfaceChart(string Name, eSurfaceChartType ChartType, ExcelPivotTable PivotTableSource)
{
return((ExcelSurfaceChart)AddChart(Name, (eChartType)ChartType, PivotTableSource));
}
internal ExcelLineChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
Smooth = false;
}
/// <summary>
/// Adds a new doughnut chart to the worksheet.
/// </summary>
/// <param name="Name"></param>
/// <param name="ChartType">Type of chart</param>
/// <param name="PivotTableSource">The pivottable source for a pivotchart</param>
/// <returns>The chart</returns>
public ExcelDoughnutChart AddDoughnutChart(string Name, eDoughnutChartType ChartType, ExcelPivotTable PivotTableSource)
{
return((ExcelDoughnutChart)AddChart(Name, (eChartType)ChartType, PivotTableSource));
}
/// <summary>
/// Adds a new radar chart to the worksheet.
/// </summary>
/// <param name="Name"></param>
/// <param name="ChartType">Type of chart</param>
/// <param name="PivotTableSource">The pivottable source for a pivotchart</param>
/// <returns>The chart</returns>
public ExcelRadarChart AddRadarChart(string Name, eRadarChartType ChartType, ExcelPivotTable PivotTableSource)
{
return((ExcelRadarChart)AddChart(Name, (eChartType)ChartType, PivotTableSource));
}
/// <summary>
/// Constructs the calculator.
/// </summary>
/// <param name="pivotTable">The pivot table to calculate against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
/// <param name="totalsCalculator">A <see cref="TotalsFunctionHelper"/> to calculate values with.</param>
public PercentOfCalculator(ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, dataFieldCollectionIndex, totalsCalculator)
{
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Snapshots Method Calls Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Snapshots Method Call Lines Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Snapshots Method Call Lines Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivot
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Type";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Location";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.Cells[4, 1].Value = "See Timeline";
sheet.Cells[4, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[4, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 5, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 9, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Snapshots Method Call Lines Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationSnapshotsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Method Call Lines
loggerConsole.Info("List of Method Call Lines");
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.SnapshotsMethodCallLinesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
loggerConsole.Info("Finalize Snapshots Method Call Lines Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshots"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshots"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsNormal"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsNormal"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsVerySlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsVerySlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsStall"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsStall"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsSlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsSlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Method Call Lines
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT + 1)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT + 1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_METHOD_CALL_LINES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
try
{
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["SnapshotUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestID"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Type"].Position + 1).Width = 10;
sheet.Column(table.Columns["Framework"].Position + 1).Width = 15;
sheet.Column(table.Columns["FullNameIndent"].Position + 1).Width = 45;
sheet.Column(table.Columns["ExitCalls"].Position + 1).Width = 15;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
}
catch (OutOfMemoryException ex)
{
// Do nothing, we must have a lot of cells
logger.Warn("Ran out of memory due to too many rows/cells");
logger.Warn(ex);
}
ExcelAddress cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SegmentUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SegmentUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SnapshotUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SnapshotUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "FullName");
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTYPE_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "Framework");
addRowFieldToPivot(pivot, "FullName");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addColumnFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESLOCATION_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 6, 1], range, PIVOT_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Class", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Method", eSortType.Ascending);
addFilterFieldToPivot(pivot, "FullName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTIMELINE_EXEC_AVERAGE, eChartType.Line, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.SnapshotMethodCallsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
internal static ExcelChart GetNewChart(ExcelDrawings drawings, XmlNode drawNode, eChartType? chartType, ExcelChart topChart, ExcelPivotTable PivotTableSource, XmlDocument chartXml = null)
{
switch (chartType)
{
case eChartType.Area:
case eChartType.Area3D:
case eChartType.AreaStacked:
case eChartType.AreaStacked100:
case eChartType.AreaStacked1003D:
case eChartType.AreaStacked3D:
return new ExcelAreaChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Pie:
case eChartType.PieExploded:
case eChartType.Pie3D:
case eChartType.PieExploded3D:
return new ExcelPieChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.BarOfPie:
case eChartType.PieOfPie:
return new ExcelOfPieChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Doughnut:
case eChartType.DoughnutExploded:
return new ExcelDoughnutChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.BarClustered:
case eChartType.BarStacked:
case eChartType.BarStacked100:
case eChartType.BarClustered3D:
case eChartType.BarStacked3D:
case eChartType.BarStacked1003D:
case eChartType.ConeBarClustered:
case eChartType.ConeBarStacked:
case eChartType.ConeBarStacked100:
case eChartType.CylinderBarClustered:
case eChartType.CylinderBarStacked:
case eChartType.CylinderBarStacked100:
case eChartType.PyramidBarClustered:
case eChartType.PyramidBarStacked:
case eChartType.PyramidBarStacked100:
case eChartType.ColumnClustered:
case eChartType.ColumnStacked:
case eChartType.ColumnStacked100:
case eChartType.Column3D:
case eChartType.ColumnClustered3D:
case eChartType.ColumnStacked3D:
case eChartType.ColumnStacked1003D:
case eChartType.ConeCol:
case eChartType.ConeColClustered:
case eChartType.ConeColStacked:
case eChartType.ConeColStacked100:
case eChartType.CylinderCol:
case eChartType.CylinderColClustered:
case eChartType.CylinderColStacked:
case eChartType.CylinderColStacked100:
case eChartType.PyramidCol:
case eChartType.PyramidColClustered:
case eChartType.PyramidColStacked:
case eChartType.PyramidColStacked100:
return new ExcelBarChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.XYScatter:
case eChartType.XYScatterLines:
case eChartType.XYScatterLinesNoMarkers:
case eChartType.XYScatterSmooth:
case eChartType.XYScatterSmoothNoMarkers:
return new ExcelScatterChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Line:
case eChartType.Line3D:
case eChartType.LineMarkers:
case eChartType.LineMarkersStacked:
case eChartType.LineMarkersStacked100:
case eChartType.LineStacked:
case eChartType.LineStacked100:
return new ExcelLineChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Bubble:
case eChartType.Bubble3DEffect:
return new ExcelBubbleChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Radar:
case eChartType.RadarFilled:
case eChartType.RadarMarkers:
return new ExcelRadarChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Surface:
case eChartType.SurfaceTopView:
case eChartType.SurfaceTopViewWireframe:
case eChartType.SurfaceWireframe:
return new ExcelSurfaceChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.StockHLC:
case eChartType.StockOHLC:
case eChartType.StockVHLC:
case eChartType.StockVOHLC:
return new ExcelStockChart(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
case eChartType.Sunburst:
return new ExcelSunburstChart(drawings, drawNode, chartType, chartXml);
case eChartType.Treemap:
return new ExcelTreemapChart(drawings, drawNode, chartType, chartXml);
case eChartType.BoxWhisker:
return new ExcelBoxWhiskerChart(drawings, drawNode, chartType, chartXml);
case eChartType.Histogram:
case eChartType.Pareto:
return new ExcelHistogramChart(drawings, drawNode, chartType, chartXml);
case eChartType.Waterfall:
return new ExcelWaterfallChart(drawings, drawNode, chartType, chartXml);
case eChartType.Funnel:
return new ExcelFunnelChart(drawings, drawNode, chartType, chartXml);
case eChartType.RegionMap:
return new ExcelRegionMapChart(drawings, drawNode, chartType, chartXml);
default:
return new ExcelChartStandard(drawings, drawNode, chartType, topChart, PivotTableSource, chartXml);
}
}
internal void AddPivotSerie(ExcelPivotTable pivotTableSource)
{
var r=pivotTableSource.WorkSheet.Cells[pivotTableSource.Address.Address];
_isPivot = true;
AddSeries(r.Offset(0, 1, r._toRow - r._fromRow + 1, 1).FullAddressAbsolute, r.Offset(0, 0, r._toRow - r._fromRow + 1, 1).FullAddressAbsolute);
}
private int Sort()
{
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;//EEplus license
var file = new FileInfo(Application.StartupPath + $"\\總表{DateTime.Now.ToString("yyyy-MM-dd")}.xlsx");
using (var excel = new ExcelPackage())
{
var ws = excel.Workbook.Worksheets.Add("Sheet1");
ws.Cells["B2"].LoadFromDataTable(ds.Tables[0], true);
/*
* ExcelWorksheet worksheetPivot =excel.Workbook.Worksheets.Add("Pivot");
* var worksheetData = excel.Workbook.Worksheets.Add("Sheet2");
*
* worksheetData.Cells["A1"].Value = "Column A";
* worksheetData.Cells["A2"].Value = "Group A";
* worksheetData.Cells["A3"].Value = "Group B";
* worksheetData.Cells["A4"].Value = "Group C";
* worksheetData.Cells["A5"].Value = "Group A";
* worksheetData.Cells["A6"].Value = "Group B";
* worksheetData.Cells["A7"].Value = "Group C";
* worksheetData.Cells["A8"].Value = "Group A";
* worksheetData.Cells["A9"].Value = "Group B";
* worksheetData.Cells["A10"].Value = "Group C";
* worksheetData.Cells["A11"].Value = "Group D";
*
* worksheetData.Cells["B1"].Value = "Column B";
* worksheetData.Cells["B2"].Value = "emc";
* worksheetData.Cells["B3"].Value = "fma";
* worksheetData.Cells["B4"].Value = "h2o";
* worksheetData.Cells["B5"].Value = "emc";
* worksheetData.Cells["B6"].Value = "fma";
* worksheetData.Cells["B7"].Value = "h2o";
* worksheetData.Cells["B8"].Value = "emc";
* worksheetData.Cells["B9"].Value = "fma";
* worksheetData.Cells["B10"].Value = "h2o";
* worksheetData.Cells["B11"].Value = "emc";
*
* worksheetData.Cells["C1"].Value = "Column C";
* worksheetData.Cells["C2"].Value = 299;
* worksheetData.Cells["C3"].Value = 792;
* worksheetData.Cells["C4"].Value = 458;
* worksheetData.Cells["C5"].Value = 299;
* worksheetData.Cells["C6"].Value = 792;
* worksheetData.Cells["C7"].Value = 458;
* worksheetData.Cells["C8"].Value = 299;
* worksheetData.Cells["C9"].Value = 792;
* worksheetData.Cells["C10"].Value = 458;
* worksheetData.Cells["C11"].Value = 299;
*
* worksheetData.Cells["D1"].Value = "Column D";
* worksheetData.Cells["D2"].Value = 40075;
* worksheetData.Cells["D3"].Value = 31415;
* worksheetData.Cells["D4"].Value = 384400;
* worksheetData.Cells["D5"].Value = 40075;
* worksheetData.Cells["D6"].Value = 31415;
* worksheetData.Cells["D7"].Value = 384400;
* worksheetData.Cells["D8"].Value = 40075;
* worksheetData.Cells["D9"].Value = 31415;
* worksheetData.Cells["D10"].Value = 384400;
* worksheetData.Cells["D11"].Value = 40075;
* //---------------------------------------------------------------------------------------------------------------------------------
* var dataRange = worksheetData.Cells[worksheetData.Dimension.Address];
*
*
* var pivotTable = worksheetPivot.PivotTables.Add(worksheetPivot.Cells["B2"], dataRange, "PivotTable");
*
* //label field
* pivotTable.RowFields.Add(pivotTable.Fields["Column A"]);
* pivotTable.DataOnRows = false;
*
* //data fields
* var field = pivotTable.DataFields.Add(pivotTable.Fields["Column B"]);
* field.Name = "Count of Column B";
* field.Function = DataFieldFunctions.Count;
*
*
* field = pivotTable.DataFields.Add(pivotTable.Fields["Column C"]);
* field.Name = "Sum of Column C";
* field.Function = DataFieldFunctions.Sum;
* field.Format = "0.00";
*
* field = pivotTable.DataFields.Add(pivotTable.Fields["Column D"]);
* field.Name = "Sum of Column D";
* field.Function = DataFieldFunctions.Sum;
* field.Format = "€#,##0.00";
*
*/
//------------------------------------------------------------------------------------------------------------------------------
var dataRange1 = ws.Cells[ws.Dimension.Address];
var pivotTable1 = ws.PivotTables.Add(ws.Cells["X2"], dataRange1, "PivotTable1");
//label field
pivotTable1.RowFields.Add(pivotTable1.Fields["ERROR_DESC"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["ERROR_CODE"]);
pivotTable1.DataOnRows = false;
//data fields
var field1 = pivotTable1.DataFields.Add(pivotTable1.Fields["SERIAL_NUMBER"]);
field1.Name = "Count of SERIAL_NUMBER";
field1.Function = DataFieldFunctions.Count;
//field1 = pivotTable1.DataFields.Add(pivotTable.Fields["Column C"]);
//field1.Name = "Sum of Column C";
//field1.Function = DataFieldFunctions.Sum;
//field1.Format = "0.00";
//field1 = pivotTable.DataFields.Add(pivotTable.Fields["Column D"]);
// field1.Name = "Sum of Column D";
// field1.Function = DataFieldFunctions.Sum;
// field1.Format = "€#,##0.00";
//worksheetPivot.Cells["A1:Z50"].AutoFitColumns
// ws.Cells[ws.Dimension.Address].Sort(6);
//----------------------------------------------------------------------------------------------------
//data Cells
//var wsData = excel.Workbook.Worksheets["Dataa"];
//wsData.Cells.LoadFromDataTable(ds.Tables[0],true);
ExcelRange dataCells = ws.Cells[ws.Dimension.Address];
//pivot table
//ExcelRange pvtLocation = new ExcelPackage(n).Workbook.Worksheets.cell["B2"];
//string pvtName = "Name";
ExcelPivotTable pivotTable = ws.PivotTables.Add(ws.Cells["AB4"], dataCells, "myPvt");
//header
pivotTable.ShowHeaders = true;
pivotTable.RowHeaderCaption = "ERROR";
//grand total
pivotTable.ColumnGrandTotals = true;
pivotTable.GrandTotalCaption = "Total";
//data field are plcae in columns
pivotTable.DataOnRows = false;
//style
pivotTable.TableStyle = OfficeOpenXml.Table.TableStyles.Medium9;
//filter
ExcelPivotTableField TitleField = pivotTable.PageFields.Add(pivotTable.Fields["TEST_GROUP"]);
TitleField.Sort = eSortType.Ascending;
//row:Error
ExcelPivotTableField ErroeCodefiled = pivotTable.RowFields.Add(pivotTable.Fields["ERROR_CODE"]);
ExcelPivotTableField ErroeCodefiled2 = pivotTable.RowFields.Add(pivotTable.Fields["ERROR_DESC"]);
//valure serial number
ExcelPivotTableDataField SerialnumberField = pivotTable.DataFields.Add(pivotTable.Fields["SERIAL_NUMBER"]);
//column
ExcelPivotTableField Dutyfield = pivotTable.ColumnFields.Add(pivotTable.Fields["DUTY_TYPE"]);
SerialnumberField.Function = DataFieldFunctions.Count;
//SerialnumberField.Format = "#,##0_);(#,##0)";
SerialnumberField.Name = "EEEE";
pivotTable.SortOnDataField(ErroeCodefiled, SerialnumberField, true);
/*
* ExcelPivotTableField fields = ErroeCodefiled;
* ExcelPivotTableDataField dataField = SerialnumberField;
*
* bool descending = true;
*
* var xdoc = pivotTable.PivotTableXml;
* var nsm = new XmlNamespaceManager(xdoc.NameTable);
*
* var schemaMain = xdoc.DocumentElement.NamespaceURI;
* if (nsm.HasNamespace("x") == false)
* nsm.AddNamespace("x", schemaMain);
*
* // <x:pivotField sortType="descending">
* var pivotField = xdoc.SelectSingleNode(
* "/x:pivotTableDefinition/x:pivotFields/x:pivotField[position()=" + (fields.Index + 1) + "]",
* nsm
* );
*
*
*
* pivotField.("sortType", (descending ? "descending" : "ascending"));
*
* // <x:autoSortScope>
* var autoSortScope = pivotField.AppendElement(schemaMain, "x:autoSortScope");
*
* // <x:pivotArea>
* var pivotArea = autoSortScope.AppendElement(schemaMain, "x:pivotArea");
*
* // <x:references count="1">
* var references = pivotArea.AppendElement(schemaMain, "x:references");
* references.AppendAttribute("count", "1");
*
* // <x:reference field="4294967294">
* var reference = references.AppendElement(schemaMain, "x:reference");
* // Specifies the index of the field to which this filter refers.
* // A value of -2 indicates the 'data' field.
* // int -> uint: -2 -> ((2^32)-2) = 4294967294
* reference.AppendAttribute("field", "4294967294");
*
* // <x:x v="0">
* var x = reference.AppendElement(schemaMain, "x:x");
* int v = 0;
* foreach (ExcelPivotTableDataField pivotDataField in pivotTable.DataFields)
* {
* if (pivotDataField == dataField)
* {
* x.AppendAttribute("v", v.ToString());
* break;
* }
* v++;
* }
*/
ws.Cells[ws.Dimension.Start.Row, ws.Dimension.Start.Column, ws.Dimension.End.Row, ws.Dimension.End.Column].AutoFitColumns();//寬度自動調整
try
{
excel.SaveAs(file);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Pivot Sort ERROR! || Save File ERROR");
return(1);
}
}
return(0);
}
internal ExcelLineChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
Smooth = false;
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_MOBILE) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Detected MOBILE Entities Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Detected MOBILE Entities Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Detected MOBILE Entities Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_MOBILE_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
#endregion
loggerConsole.Info("Fill Detected MOBILE Entities Report File");
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications - All
loggerConsole.Info("List of Applications - All");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_MOBILE_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILEApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Network Requests
loggerConsole.Info("List of Network Requests");
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILENetworkRequestsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Network Request Business Transactions
loggerConsole.Info("List of Network Request Business Transactions");
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILENetworkRequestsBusinessTransactionsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Detected MOBILE Entities Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications - All
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Description"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_MOBILE_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_MOBILE);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNetworkRequests"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNetworkRequests"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNoActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNoActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Network Requests
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_NETWORK_REQUESTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["RequestName"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestNameInternal"].Position + 1).Width = 20;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_NETWORK_REQUESTS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "RequestName");
addRowFieldToPivot(pivot, "RequestNameInternal");
addDataFieldToPivot(pivot, "RequestID", DataFieldFunctions.Count, "NumReqs");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "TimeTotal", DataFieldFunctions.Sum, "Time");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_NETWORK_REQUESTS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
}
#endregion
#region Network Request Business Transactions
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["RequestName"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestNameInternal"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTType"].Position + 1).Width = 15;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "RequestName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addDataFieldToPivot(pivot, "BTID", DataFieldFunctions.Count, "NumBTs");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.MOBILEEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
internal ExcelSurfaceChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
Init();
}
internal ExcelRadarChart(ExcelDrawings drawings, XmlNode node, eChartType?type, ExcelChart topChart, ExcelPivotTable PivotTableSource, XmlDocument chartXml, ExcelGroupShape parent = null) :
base(drawings, node, type, topChart, PivotTableSource, chartXml, parent)
{
SetTypeProperties();
}
internal ExcelScatterChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
SetTypeProperties();
}
internal static void addFilterFieldToPivot(ExcelPivotTable pivot, string fieldName, eSortType sort)
{
ExcelPivotTableField fieldF = pivot.PageFields.Add(pivot.Fields[fieldName]);
fieldF.Sort = sort;
}
/// <summary>
/// Create a new <see cref="ColumnGrandTotalHelper"/> object.
/// </summary>
/// <param name="pivotTable">The <see cref="ExcelPivotTable"/>.</param>
/// <param name="backingData">The data backing the pivot table.</param>
/// <param name="totalsCalculator">The calculation helper.</param>
internal ColumnGrandTotalHelper(ExcelPivotTable pivotTable, PivotCellBackingData[,] backingData, TotalsFunctionHelper totalsCalculator)
: base(pivotTable, backingData, totalsCalculator)
{
this.MajorHeaderCollection = this.PivotTable.RowHeaders;
this.MinorHeaderCollection = this.PivotTable.ColumnHeaders;
}
internal static void addDataFieldToPivot(ExcelPivotTable pivot, string fieldName, DataFieldFunctions function)
{
addDataFieldToPivot(pivot, fieldName, function, String.Empty);
}
internal static ExcelChart GetNewChart(ExcelDrawings drawings, XmlNode drawNode, eChartType chartType, ExcelChart topChart, ExcelPivotTable PivotTableSource)
{
switch(chartType)
{
case eChartType.Pie:
case eChartType.PieExploded:
case eChartType.Pie3D:
case eChartType.PieExploded3D:
return new ExcelPieChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.BarOfPie:
case eChartType.PieOfPie:
return new ExcelOfPieChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Doughnut:
case eChartType.DoughnutExploded:
return new ExcelDoughnutChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.BarClustered:
case eChartType.BarStacked:
case eChartType.BarStacked100:
case eChartType.BarClustered3D:
case eChartType.BarStacked3D:
case eChartType.BarStacked1003D:
case eChartType.ConeBarClustered:
case eChartType.ConeBarStacked:
case eChartType.ConeBarStacked100:
case eChartType.CylinderBarClustered:
case eChartType.CylinderBarStacked:
case eChartType.CylinderBarStacked100:
case eChartType.PyramidBarClustered:
case eChartType.PyramidBarStacked:
case eChartType.PyramidBarStacked100:
case eChartType.ColumnClustered:
case eChartType.ColumnStacked:
case eChartType.ColumnStacked100:
case eChartType.Column3D:
case eChartType.ColumnClustered3D:
case eChartType.ColumnStacked3D:
case eChartType.ColumnStacked1003D:
case eChartType.ConeCol:
case eChartType.ConeColClustered:
case eChartType.ConeColStacked:
case eChartType.ConeColStacked100:
case eChartType.CylinderCol:
case eChartType.CylinderColClustered:
case eChartType.CylinderColStacked:
case eChartType.CylinderColStacked100:
case eChartType.PyramidCol:
case eChartType.PyramidColClustered:
case eChartType.PyramidColStacked:
case eChartType.PyramidColStacked100:
return new ExcelBarChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.XYScatter:
case eChartType.XYScatterLines:
case eChartType.XYScatterLinesNoMarkers:
case eChartType.XYScatterSmooth:
case eChartType.XYScatterSmoothNoMarkers:
return new ExcelScatterChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Line:
case eChartType.Line3D:
case eChartType.LineMarkers:
case eChartType.LineMarkersStacked:
case eChartType.LineMarkersStacked100:
case eChartType.LineStacked:
case eChartType.LineStacked100:
return new ExcelLineChart(drawings, drawNode, chartType, topChart, PivotTableSource);
default:
return new ExcelChart(drawings, drawNode, chartType, topChart, PivotTableSource);
}
}
internal static void setDefaultPivotTableSettings(ExcelPivotTable pivot)
{
pivot.ApplyWidthHeightFormats = false;
pivot.DataOnRows = false;
}
internal ExcelChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, "xdr:graphicFrame/xdr:nvGraphicFramePr/xdr:cNvPr/@name")
{
ChartType = type;
CreateNewChart(drawings, type, topChart);
Init(drawings, _chartNode);
_chartSeries = new ExcelChartSeries(this, drawings.NameSpaceManager, _chartNode, PivotTableSource!=null);
if (PivotTableSource != null) SetPivotSource(PivotTableSource);
SetTypeProperties(drawings);
if (topChart == null)
LoadAxis();
else
{
_axis = topChart.Axis;
if (_axis.Length > 0)
{
XAxis = _axis[0];
YAxis = _axis[1];
}
}
}
internal static void addFilterFieldToPivot(ExcelPivotTable pivot, string fieldName)
{
addFilterFieldToPivot(pivot, fieldName, eSortType.None);
}
internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable, ExcelRangeBase sourceAddress, int tblId) :
base(ns, null)
{
PivotTable = pivotTable;
var pck = pivotTable.WorkSheet._package.Package;
//CacheDefinition
CacheDefinitionXml = new XmlDocument();
LoadXmlSafe(CacheDefinitionXml, GetStartXml(sourceAddress));
CacheDefinitionUri = GetNewUri(pck, "/xl/pivotCache/pivotCacheDefinition{0}.xml", tblId);
Part = pck.CreatePart(CacheDefinitionUri, ExcelPackage.schemaPivotCacheDefinition);
TopNode = CacheDefinitionXml.DocumentElement;
//CacheRecord. Create an empty one.
CacheRecordUri = GetNewUri(pck, "/xl/pivotCache/pivotCacheRecords{0}.xml", tblId);
var cacheRecord = new XmlDocument();
cacheRecord.LoadXml("<pivotCacheRecords xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" count=\"0\" />");
var recPart = pck.CreatePart(CacheRecordUri, ExcelPackage.schemaPivotCacheRecords);
cacheRecord.Save(recPart.GetStream());
RecordRelationship = Part.CreateRelationship(PackUriHelper.ResolvePartUri(CacheDefinitionUri, CacheRecordUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheRecords");
RecordRelationshipID = RecordRelationship.Id;
CacheDefinitionXml.Save(Part.GetStream());
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Events and Health Rule Violations Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Events and Health Rule Violations Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Events and Health Rule Violations Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 7, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 4, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 3, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Events and Health Rule Violations Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllersReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Events
loggerConsole.Info("List of Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.EventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Health Rule Violation Events
loggerConsole.Info("List of Health Rule Violation Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.HealthRuleViolationsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Audit Events
loggerConsole.Info("List of Audit Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.AuditEventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Events and Health Rule Violations Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["UserName"].Position + 1).Width = 25;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEvents"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEvents"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsInfo"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsInfo"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolations"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolations"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsCritical"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsCritical"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["Summary"].Position + 1).Width = 35;
sheet.Column(table.Columns["Type"].Position + 1).Width = 20;
sheet.Column(table.Columns["SubType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "SubType");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "NodeName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 3, 1], range, REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ApplicationName");
addFilterFieldToPivot(pivot, "TierName");
addFilterFieldToPivot(pivot, "BTName");
addFilterFieldToPivot(pivot, "TriggeredEntityName");
addFilterFieldToPivot(pivot, "ApplicationName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "SubType", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TIMELINE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Health Rule Violation Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_HEALTH_RULE_VIOLATION_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["From"].Position + 1).Width = 25;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 25;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["HealthRuleName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Status");
addRowFieldToPivot(pivot, "HealthRuleName");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
}
#endregion
#region Audit Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_AUDIT_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["Username"].Position + 1).Width = 20;
sheet.Column(table.Columns["LoginType"].Position + 1).Width = 15;
sheet.Column(table.Columns["Action"].Position + 1).Width = 15;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 30;
sheet.Column(table.Columns["EntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "Action");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "LoginType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "UserName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 30;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "UserName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Action", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TIMELINE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[REPORT_SHEET_TOC];
sheet.Cells[1, 1].Value = "Sheet Name";
sheet.Cells[1, 2].Value = "# Entities";
sheet.Cells[1, 3].Value = "Link";
int rowNum = 1;
foreach (ExcelWorksheet s in excelReport.Workbook.Worksheets)
{
rowNum++;
sheet.Cells[rowNum, 1].Value = s.Name;
sheet.Cells[rowNum, 3].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", s.Name);
sheet.Cells[rowNum, 3].StyleName = "HyperLinkStyle";
if (s.Tables.Count > 0)
{
table = s.Tables[0];
sheet.Cells[rowNum, 2].Value = table.Address.Rows - 1;
}
}
range = sheet.Cells[1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_TOC);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Sheet Name"].Position + 1).Width = 25;
sheet.Column(table.Columns["# Entities"].Position + 1).Width = 25;
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.EventsAndHealthRuleViolationsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
internal ExcelScatterChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
SetTypeProperties();
}
/// <summary>
/// Resolve the name references and other formulas contained in a formula.
/// </summary>
/// <param name="calculatedFields">The list of calculated fields in the pivot table.</param>
/// <param name="totalsCalculator">The function helper calculator.</param>
/// <param name="pivotTable">The pivot table the fields are on.</param>
public static void ConfigureCalculatedFields(IEnumerable <CacheFieldNode> calculatedFields, TotalsFunctionHelper totalsCalculator, ExcelPivotTable pivotTable)
{
// Add all of the cache field names to the calculation helper.
var cacheFieldNames = new HashSet <string>(pivotTable.CacheDefinition.CacheFields.Select(c => c.Name));
totalsCalculator.AddNames(cacheFieldNames);
// Resolve any calclulated fields that may be referencing each other to forumlas composed of regular ol' cache fields.
foreach (var calculatedField in calculatedFields)
{
var resolvedFormulaTokens = PivotTableDataManager.ResolveFormulaReferences(calculatedField.Formula, totalsCalculator, calculatedFields);
foreach (var token in resolvedFormulaTokens.Where(t => t.TokenType == TokenType.NameValue))
{
if (!calculatedField.ReferencedCacheFieldsToIndex.ContainsKey(token.Value))
{
var referencedFieldIndex = pivotTable.CacheDefinition.GetCacheFieldIndex(token.Value);
calculatedField.ReferencedCacheFieldsToIndex.Add(token.Value, referencedFieldIndex);
}
}
// Reconstruct the formula and wrap all field names in single ticks.
string resolvedFormula = string.Empty;
foreach (var token in resolvedFormulaTokens)
{
string tokenValue = token.Value;
if (token.TokenType == TokenType.NameValue)
{
tokenValue = $"'{tokenValue}'";
}
resolvedFormula += tokenValue;
}
calculatedField.ResolvedFormula = resolvedFormula;
}
}
internal ExcelPivotTableField(XmlNamespaceManager ns, XmlNode topNode,ExcelPivotTable table, int index, int baseIndex) :
base(ns, topNode)
{
Index = index;
BaseIndex = baseIndex;
_table = table;
}
/// <summary>
/// Constructor.
/// </summary>
/// <param name="pivotTable">The pivot table.</param>
public PivotTableDataManager(ExcelPivotTable pivotTable)
{
this.PivotTable = pivotTable;
}
internal ExcelDoughnutChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
//SetPaths();
}
/// <summary>
/// Base calculator constructor.
/// </summary>
/// <param name="pivotTable">The pivot table to calculate against.</param>
/// <param name="dataFieldCollectionIndex">The index of the data field that the calculator is calculating.</param>
public ShowDataAsCalculatorBase(ExcelPivotTable pivotTable, int dataFieldCollectionIndex, TotalsFunctionHelper totalsCalculator)
{
this.PivotTable = pivotTable;
this.DataFieldCollectionIndex = dataFieldCollectionIndex;
this.TotalsCalculator = totalsCalculator;
}