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