public static ChartSerie CreateChartSerie(ExcelChart chart, bool isPivot, Range seriesAddress, Range xSeriesAddress) { ChartSerie result = null ; switch (chart.ChartType) { case ExcelChartType.XYScatter: case ExcelChartType.XYScatterLines: case ExcelChartType.XYScatterLinesNoMarkers: case ExcelChartType.XYScatterSmooth: case ExcelChartType.XYScatterSmoothNoMarkers: result = new ScatterChartSerie(chart, isPivot); break; case ExcelChartType.Pie: case ExcelChartType.Pie3D: case ExcelChartType.PieExploded: case ExcelChartType.PieExploded3D: case ExcelChartType.PieOfPie: case ExcelChartType.Doughnut: case ExcelChartType.DoughnutExploded: case ExcelChartType.BarOfPie: result = new PieChartSerie(chart, isPivot); break; case ExcelChartType.Line: case ExcelChartType.LineMarkers: case ExcelChartType.LineMarkersStacked: case ExcelChartType.LineMarkersStacked100: case ExcelChartType.LineStacked: case ExcelChartType.LineStacked100: result = new LineChartSerie(chart, isPivot); if (chart.ChartType == ExcelChartType.LineMarkers || chart.ChartType == ExcelChartType.LineMarkersStacked || chart.ChartType == ExcelChartType.LineMarkersStacked100) { ((LineChartSerie)result).Marker = ExcelMarkerStyle.Square; } ((LineChartSerie)result).Smooth = ((ExcelLineChart)chart).Smooth; break; default: result = new ChartSerie(chart, isPivot); break; } result.Series = seriesAddress; result.XSeries = xSeriesAddress; return result; }
public ChartDataLabel(ExcelChart chart, bool isPivot) { this._Chart = chart; this.IsPivot = isPivot; }
private void WriteWorkSheetChart_char_legend(ExcelChart excelChart, XElement chartElement) { if (excelChart._Legend != null) { XElement legendElement = new XElement(XName.Get("legend", ExcelCommon.Schema_Chart)); XElement legendPosElement = new XElement(XName.Get("legendPos", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "l")); legendElement.Add(legendPosElement); XElement layoutElement = new XElement(XName.Get("layout", ExcelCommon.Schema_Chart)); legendElement.Add(layoutElement); XElement overlayElement = new XElement(XName.Get("overlay", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "0")); legendElement.Add(overlayElement); XElement txPrElement = new XElement(XName.Get("txPr", ExcelCommon.Schema_Chart)); XElement bodyPrElement = new XElement(XName.Get("bodyPr", ExcelCommon.Schema_Drawings)); txPrElement.Add(bodyPrElement); XElement lstStyleElement = new XElement(XName.Get("lstStyle", ExcelCommon.Schema_Drawings)); txPrElement.Add(lstStyleElement); WriteWorkSheetChart_chart_title_P(excelChart.Legend.Font, txPrElement); legendElement.Add(txPrElement); chartElement.Add(legendElement); } }
private void WriteWorkSheetChart_chart(ExcelChart excelChart, XElement root) { XElement chartElement = new XElement(XName.Get("chart", ExcelCommon.Schema_Chart)); if (excelChart._Title != null) chartElement.Add(WriteWorkSheetChart_chart_title(excelChart._Title)); chartElement.Add(new XElement(XName.Get("autoTitleDeleted", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelChart.AutoTitleDeleted ? "1" : "0"))); WriteWorkSheetChart_chart_plotArea(excelChart, chartElement); WriteWorkSheetChart_char_legend(excelChart, chartElement); WriteWorkSheetChart_char_plotVisOnly(excelChart, chartElement); WriteWorkSheetChart_char_dispBlanksAs(excelChart, chartElement); WriteWorkSheetChart_char_showDLblsOverMax(excelChart, chartElement); root.Add(chartElement); }
private void WriteWorkSheetChart_chart_plotArea_varyColors(ExcelChart excelChart, XElement chartTypeElement) { XElement varyColorsElement = new XElement(XName.Get("grouping", ExcelCommon.Schema_Chart)); switch (excelChart.ChartType) { case ExcelChartType.Pie: case ExcelChartType.PieExploded: case ExcelChartType.PieOfPie: case ExcelChartType.Pie3D: case ExcelChartType.PieExploded3D: case ExcelChartType.BarOfPie: case ExcelChartType.Doughnut: case ExcelChartType.DoughnutExploded: varyColorsElement.Add(new XAttribute(XName.Get("val"), 1)); break; default: varyColorsElement.Add(new XAttribute(XName.Get("val"), 0)); break; } chartTypeElement.Add(varyColorsElement); }
private void WriteWorkSheetChart_chart_plotArea_axId(ExcelChart excelChart, XElement chartTypeElement) { switch (excelChart.ChartType) { case ExcelChartType.Pie: case ExcelChartType.PieExploded: case ExcelChartType.PieOfPie: case ExcelChartType.Pie3D: case ExcelChartType.PieExploded3D: case ExcelChartType.BarOfPie: case ExcelChartType.Doughnut: case ExcelChartType.DoughnutExploded: break; default: chartTypeElement.Add(new XElement(XName.Get("axId", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 1))); chartTypeElement.Add(new XElement(XName.Get("axId", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 2))); break; } }
private void WriteWorkSheetChart_chart_Ax_Content(ExcelChart excelChart, XElement parentElement) { XElement axIdElement = new XElement(XName.Get("axId", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 1)); parentElement.Add(axIdElement); XElement scalingElement = new XElement(XName.Get("scaling", ExcelCommon.Schema_Chart), new XElement(XName.Get("orientation", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "minMax"))); parentElement.Add(scalingElement); XElement deleteElement = new XElement(XName.Get("delete", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 0)); parentElement.Add(deleteElement); XElement axPosElement = new XElement(XName.Get("axPos", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "b")); parentElement.Add(axPosElement); XElement tickLblPosElement = new XElement(XName.Get("tickLblPos", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "nextTo")); parentElement.Add(tickLblPosElement); XElement crossAxElement = new XElement(XName.Get("crossAx", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 2)); parentElement.Add(crossAxElement); XElement crossesElement = new XElement(XName.Get("crosses", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "autoZero")); parentElement.Add(crossesElement); }
private void WriteWorkSheetChart_printSettings(ExcelChart excelChart, XElement root) { XElement printSettingsElement = new XElement(XName.Get("printSettings", ExcelCommon.Schema_Chart)); XElement headerFooterElement = new XElement(XName.Get("headerFooter", ExcelCommon.Schema_Chart)); printSettingsElement.Add(headerFooterElement); XElement pageMarginsElement = new XElement(XName.Get("pageMargins", ExcelCommon.Schema_Chart)); pageMarginsElement.Add(new XAttribute(XName.Get("b"), "0.75")); pageMarginsElement.Add(new XAttribute(XName.Get("l"), "0.7")); pageMarginsElement.Add(new XAttribute(XName.Get("r"), "0.7")); pageMarginsElement.Add(new XAttribute(XName.Get("t"), "0.75")); pageMarginsElement.Add(new XAttribute(XName.Get("header"), "0.3")); pageMarginsElement.Add(new XAttribute(XName.Get("footer"), "0.3")); printSettingsElement.Add(pageMarginsElement); XElement pageSetupElement = new XElement(XName.Get("pageSetup", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("orientation"), "portrait")); printSettingsElement.Add(pageSetupElement); root.Add(printSettingsElement); }
/// <inheritdoc /> /// <summary> /// Generates output in Office Open Xml Excel format. /// </summary> protected override void Execute() { MemoryStream stream = null; try { stream = new MemoryStream(); using (var excel = new ExcelPackage(stream)) { #region destroy stream stream = null; #endregion #region initialize var items = Table.Fields; var location = TableLocation; var x = location.X; var y = location.Y; #endregion #region get target data var rows = Service.RawDataFiltered; #endregion #region add worksheet var worksheet = excel.Workbook.Worksheets.Add(WorkSheetName); worksheet.View.ShowGridLines = Table.ShowGridLines == YesNo.Yes; #endregion #region add styles excel.Workbook.Styles.CreateFromModel(Resources.Styles); #endregion #region has logo? worksheet.AppendLogo(Table.Logo); #endregion #region add top aggregates var rowsCount = rows.Length; var columnHeaders = Table.Headers; var hasColumnheaders = columnHeaders.Any(); var topAggregates = items.GetRange(KnownAggregateLocation.Top).ToList(); var hasTopAggregates = topAggregates.Any(); if (hasTopAggregates) { foreach (var field in topAggregates) { var aggregate = field.Aggregate; var formula = new ExcelFormulaResolver(aggregate) { StartRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes ? 3 : 2, EndRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes ? rowsCount + 2 : rowsCount + 1, HasAutoFilter = Table.AutoFilter, }; var column = items.IndexOf(field); var cell = worksheet.Cells[y, x + column]; cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle; var type = aggregate.AggregateType; if (type == KnownAggregateType.Text) { cell.Value = formula.Resolve(); } else { cell.FormulaR1C1 = formula.Resolve(); } } } #endregion #region add column headers if (Table.ShowColumnHeaders == YesNo.Yes) { if (hasTopAggregates) { y++; } foreach (var columnHeader in columnHeaders) { var cell = worksheet.GetRangeFromModel(y, columnHeader); cell.Merge = true; cell.StyleName = columnHeader.Style ?? StyleModel.NameOfDefaultStyle; cell.Merge = columnHeader.Show == YesNo.Yes; cell.Value = columnHeader.Show == YesNo.Yes ? columnHeader.Text : string.Empty; worksheet.AddColumnGroupFromModel(columnHeader); } } #endregion #region add headers if (hasColumnheaders) { y++; } var fieldHeaders = items.GetRange(YesNo.Yes).ToList(); var hasFieldHeaders = fieldHeaders.Any(); foreach (var field in fieldHeaders) { var header = field.Header; var column = items.IndexOf(field); var cell = worksheet.Cells[y, x + column]; cell.Value = field.Alias; cell.StyleName = header.Style ?? StyleModel.NameOfDefaultStyle; var showHyperLink = header.HyperLink.Show == YesNo.Yes; if (!showHyperLink) { continue; } string tooltip; if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldName.ToString())) { tooltip = BaseDataFieldModel.GetFieldNameFrom(header.Parent); } else if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldAlias.ToString())) { tooltip = header.Parent.Alias; } else { tooltip = header.HyperLink.Tooltip; } var hyperLinkStyle = header.Style ?? StyleModel.NameOfDefaultStyle; if (!header.HyperLink.Style.Equals("Current")) { hyperLinkStyle = header.HyperLink.Style; } var hyperLinkType = header.HyperLink.Current.GetType().Name; switch (hyperLinkType) { case "WebHyperLink": var webHyperLink = (WebHyperLink)header.HyperLink.Current; cell.Hyperlink = new ExcelHyperLink(webHyperLink.Address); //, tooltip); //cell.StyleName = hyperLinkStyle; break; } } #endregion #region add data if (hasFieldHeaders) { y++; } var conditions = Table.Conditions.Items.ToList(); var hasConditions = conditions.Any(); var fieldDictionary = new Dictionary <BaseDataFieldModel, int>(); for (var row = 0; row < rowsCount; row++) { var rowData = rows[row]; Service.SetCurrentRow(row); for (var col = 0; col < items.Count; col++) { Service.SetCurrentCol(col); var field = items[col]; field.DataSource = rowData; Service.SetCurrentField(field); var value = field.Value.GetValue(Provider.SpecialChars); var valueLenght = value.FormattedValue.Length; var cell = worksheet.Cells[y + row, x + col]; cell.Value = value.Value; cell.AddErrorComment(value); if (hasConditions) { foreach (var condition in conditions) { if (condition.Active == YesNo.No) { continue; } var styleToApply = condition.Apply(); if (styleToApply == null) { continue; } cell.StyleName = styleToApply; } } else { cell.StyleName = row.IsOdd() ? $"{value.Style.Name}_Alternate" : value.Style.Name ?? StyleModel.NameOfDefaultStyle; } cell.Style.WrapText = field.FieldType == KnownFieldType.Group; if (!fieldDictionary.ContainsKey(field)) { fieldDictionary.Add(field, valueLenght); } else { var entry = fieldDictionary[field]; if (valueLenght > entry) { fieldDictionary[field] = valueLenght; } } } } #endregion #region add bottom aggregates var fieldsWithBottomAggregates = items.GetRange(KnownAggregateLocation.Bottom).ToList(); var hasBottomAggregates = fieldsWithBottomAggregates.Any(); if (hasBottomAggregates) { foreach (var field in fieldsWithBottomAggregates) { var aggregate = field.Aggregate; var formula = new ExcelFormulaResolver(aggregate) { EndRow = -1, StartRow = -rowsCount, HasAutoFilter = Table.AutoFilter, }; var column = items.IndexOf(field); var cell = worksheet.Cells[y + rowsCount, x + column]; cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle; var type = aggregate.AggregateType; if (type == KnownAggregateType.Text) { cell.Value = formula.Resolve(); } else { cell.FormulaR1C1 = formula.Resolve(); } } } #endregion #region add blocklines var blocklines = Provider.Input.Model.BlockLines; var hasBlockLines = blocklines.Any(); if (hasBlockLines) { foreach (var blockline in blocklines) { if (blockline.Show == YesNo.No) { continue; } var blocklineLocation = blockline.Location.LocationType.Equals(KnownElementLocation.ByAlignment) ? new Point(1, 1) : ((CoordenatesModel)blockline.Location.Mode).TableCoordenates; var dy = 0; var blocklineY = blocklineLocation.Y; var blocklineX = blocklineLocation.X; var keyLines = blockline.Items.Keys; foreach (var keyLine in keyLines) { var line = Resources.Lines.GetBy(keyLine); if (line.Show == YesNo.No) { continue; } var times = line.Repeat == 0 ? 1 : line.Repeat; for (var i = 1; i <= times; i++) { int merge; var dx = 0; ExcelRange cell; var lineType = line.LineType; switch (lineType) { case KnownLineType.EmptyLine: var emptyLine = (EmptyLineModel)line; merge = emptyLine.Merge; if (merge > 0) { var range = ExcelCellBase.GetAddress(blocklineY + dy, blocklineX, blocklineY + dy, blocklineX + merge - 1); cell = worksheet.Cells[range]; cell.Merge = true; } else { cell = worksheet.Cells[blocklineY + dy, blocklineX]; } cell.StyleName = line.Style; break; case KnownLineType.TextLine: var textLine = (TextLineModel)line; var itemss = textLine.Items; foreach (var item in itemss) { if (item.Show == YesNo.No) { continue; } merge = item.Merge; if (merge > 0) { var range = ExcelCellBase .GetAddress( blocklineY + dy, blocklineX + dx, blocklineY + dy, blocklineX + dx + merge - 1); cell = worksheet.Cells[range]; cell.Merge = true; } else { cell = worksheet.Cells[blocklineY + dy, blocklineX + dx]; } cell.StyleName = string.IsNullOrEmpty(item.Style) ? (item.Owner.Style ?? StyleModel.Default.Name) : item.Style; var text = item.Value; cell.Value = text; dx++; if (merge > 0) { dx += merge - 1; } } break; } dy++; } } } } #endregion #region add autofilter if (Table.AutoFilter == YesNo.Yes) { if (hasFieldHeaders) { worksheet.Cells[y - 1, x, y - 1, x + items.Count - 1].AutoFilter = true; } else { worksheet.Cells[worksheet.Dimension.Address].AutoFilter = true; } } #endregion #region autofitcolumns? if (Table.AutoFitColumns == YesNo.Yes) { try { worksheet.Cells.AutoFitColumns(0); } catch (Exception) { // ignored } worksheet.AutoFitGroupColumns(fieldDictionary, this); } #endregion #region add charts var genericCharts = Table.Charts; foreach (var genericChart in genericCharts) { if (genericChart.Show == YesNo.No) { continue; } if (genericChart.ChartType == KnownChartTypes.ChartType) { var chart = (ChartModel)genericChart; ExcelChart mainchart = null; var plots = chart.Plots; foreach (var plot in plots) { // Calculate y-coordenates var tableLocation = TableLocation; tableLocation.Offset(-1, -1); var dataSerieY = tableLocation.Y; if (hasTopAggregates) { dataSerieY++; } if (hasColumnheaders) { dataSerieY++; } if (hasFieldHeaders) { dataSerieY++; } // Series data, name var series = plot.Series; foreach (var serie in series) { var item = items[serie.Field]; if (item == null) { continue; } var axis = items[serie.Axis]; if (axis == null) { continue; } // Create chart ExcelChart workchart; if (plot.UseSecondaryAxis.Equals(YesNo.No)) { if (mainchart == null) { mainchart = worksheet.Drawings.AddChart(serie.Field, serie.ChartType.ToEppChartType()); mainchart.Name = plot.Name; } workchart = mainchart; } else { workchart = mainchart.PlotArea.ChartTypes.Add(serie.ChartType.ToEppChartType()); workchart.UseSecondaryAxis = true; workchart.XAxis.Deleted = false; } var axisColumnIndex = tableLocation.X + items.IndexOf(axis) + 1; var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1; var sr = workchart.Series.Add( ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex), ExcelCellBase.GetAddress(dataSerieY + 1, axisColumnIndex, rowsCount + dataSerieY, axisColumnIndex)); sr.Header = serie.Name; } } mainchart.FormatFromModel(chart); } else { var miniChart = (MiniChartModel)genericChart; // Calculate y-coordenates var tableLocation = TableLocation; tableLocation.Offset(-1, -1); var dataSerieY = tableLocation.Y; if (hasTopAggregates) { dataSerieY++; } if (hasColumnheaders) { dataSerieY++; } if (hasFieldHeaders) { dataSerieY++; } var item = items[miniChart.Field]; if (item == null) { continue; } var locationRange = ExcelCellBase.GetAddress(18, 2); var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1; var dataRange = ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex); var sparkline = worksheet.SparklineGroups.Add( miniChart.Type.Active.ToEppeSparklineType(), worksheet.Cells[locationRange], worksheet.Cells[dataRange]); sparkline.DisplayHidden = miniChart.DisplayHidden == YesNo.Yes; sparkline.ColorSeries.SetColor(miniChart.Type.Column.Serie.GetColor()); sparkline.DisplayEmptyCellsAs = miniChart.EmptyValueAs.ToEppeDisplayBlanksAs(); // Axes // Horizontal axis sparkline.RightToLeft = miniChart.Axes.Horizontal.RightToLeft == YesNo.Yes; if (miniChart.Axes.Horizontal.Show == YesNo.Yes) { sparkline.DisplayXAxis = true; sparkline.ColorAxis.SetColor(miniChart.Axes.Horizontal.GetColor()); } sparkline.DateAxisRange = null; var isHorizontalDateAxis = miniChart.Axes.Horizontal.IsDateAxis; if (isHorizontalDateAxis) { var horizontalAxisField = miniChart.Axes.Horizontal.GetAxisField(); if (horizontalAxisField != null) { var dateFielColumnIndex = tableLocation.X + items.IndexOf(horizontalAxisField) + 1; var dateFieldRange = ExcelCellBase.GetAddress(dataSerieY + 1, dateFielColumnIndex, rowsCount + dataSerieY, dateFielColumnIndex); sparkline.DateAxisRange = worksheet.Cells[dateFieldRange]; } } // Vertical axis var maxVerticalAxisIsAuto = miniChart.Axes.Vertical.Max.Equals("Automatic"); sparkline.MaxAxisType = maxVerticalAxisIsAuto ? eSparklineAxisMinMax.Individual : eSparklineAxisMinMax.Custom; var minVerticalAxisIsAuto = miniChart.Axes.Vertical.Min.Equals("Automatic"); sparkline.MinAxisType = minVerticalAxisIsAuto ? eSparklineAxisMinMax.Individual : eSparklineAxisMinMax.Custom; if (!maxVerticalAxisIsAuto) { sparkline.ManualMax = double.Parse(miniChart.Axes.Vertical.Max); } if (!minVerticalAxisIsAuto) { sparkline.ManualMin = double.Parse(miniChart.Axes.Vertical.Min); } // Points switch (miniChart.Type.Active) { case KnownMiniChartType.Column: if (!miniChart.Type.Column.Points.Low.IsDefault) { sparkline.Low = true; sparkline.ColorLow.SetColor(miniChart.Type.Column.Points.Low.GetColor()); } if (!miniChart.Type.Column.Points.First.IsDefault) { sparkline.First = true; sparkline.ColorFirst.SetColor(miniChart.Type.Column.Points.First.GetColor()); } if (!miniChart.Type.Column.Points.High.IsDefault) { sparkline.High = true; sparkline.ColorHigh.SetColor(miniChart.Type.Column.Points.High.GetColor()); } if (!miniChart.Type.Column.Points.Last.IsDefault) { sparkline.Last = true; sparkline.ColorLast.SetColor(miniChart.Type.Column.Points.Last.GetColor()); } if (!miniChart.Type.Column.Points.Negative.IsDefault) { sparkline.Negative = true; sparkline.ColorNegative.SetColor(miniChart.Type.Column.Points.Negative.GetColor()); } break; case KnownMiniChartType.Line: sparkline.LineWidth = double.Parse(miniChart.Type.Line.Serie.Width); if (!miniChart.Type.Line.Points.Low.IsDefault) { sparkline.Low = true; sparkline.ColorLow.SetColor(miniChart.Type.Line.Points.Low.GetColor()); } if (!miniChart.Type.Line.Points.First.IsDefault) { sparkline.First = true; sparkline.ColorFirst.SetColor(miniChart.Type.Line.Points.First.GetColor()); } if (!miniChart.Type.Line.Points.High.IsDefault) { sparkline.High = true; sparkline.ColorHigh.SetColor(miniChart.Type.Line.Points.High.GetColor()); } if (!miniChart.Type.Line.Points.Last.IsDefault) { sparkline.Last = true; sparkline.ColorLast.SetColor(miniChart.Type.Line.Points.Last.GetColor()); } if (!miniChart.Type.Line.Points.Negative.IsDefault) { sparkline.Negative = true; sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Negative.GetColor()); } if (!miniChart.Type.Line.Points.Markers.IsDefault) { sparkline.Markers = true; sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Markers.GetColor()); } break; case KnownMiniChartType.WinLoss: if (!miniChart.Type.WinLoss.Points.Low.IsDefault) { sparkline.Low = true; sparkline.ColorLow.SetColor(miniChart.Type.WinLoss.Points.Low.GetColor()); } if (!miniChart.Type.WinLoss.Points.First.IsDefault) { sparkline.First = true; sparkline.ColorFirst.SetColor(miniChart.Type.WinLoss.Points.First.GetColor()); } if (!miniChart.Type.WinLoss.Points.High.IsDefault) { sparkline.High = true; sparkline.ColorHigh.SetColor(miniChart.Type.WinLoss.Points.High.GetColor()); } if (!miniChart.Type.WinLoss.Points.Last.IsDefault) { sparkline.Last = true; sparkline.ColorLast.SetColor(miniChart.Type.WinLoss.Points.Last.GetColor()); } if (!miniChart.Type.WinLoss.Points.Negative.IsDefault) { sparkline.Negative = true; sparkline.ColorNegative.SetColor(miniChart.Type.WinLoss.Points.Negative.GetColor()); } break; } } } #endregion #region add document information var document = Host.Document; #region sets document view worksheet.View.SetDocumentViewFromModel(document); #endregion #region sets document metadata worksheet.Workbook.Properties.SetDocumentMetadataFromModel(document.Metadata); #endregion #region sets document header/footer worksheet.HeaderFooter.SetDocumentHeaderFromModel(document.Header); worksheet.HeaderFooter.SetDocumentFooterFromModel(document.Footer); #endregion #region sets page orientation, margins and size var printAreaRangeY2 = location.Y; var repeatRowsRangeY2 = printAreaRangeY2; if (hasFieldHeaders) { printAreaRangeY2++; } if (hasTopAggregates) { printAreaRangeY2++; repeatRowsRangeY2++; } if (hasBottomAggregates) { printAreaRangeY2++; } var repeatRowsRange = $"{1}:{repeatRowsRangeY2}"; var printAreaRange = ExcelCellBase.GetAddress( 1, 1, printAreaRangeY2 + rowsCount - 1, location.X + items.Count - 1, true); worksheet.PrinterSettings.PaperSize = document.Size.ToEppPaperSize(); worksheet.PrinterSettings.Orientation = document.Orientation.ToEppOrientation(); worksheet.PrinterSettings.SetMarginsFromModel(document.Margins); worksheet.PrinterSettings.PrintArea = worksheet.Cells[printAreaRange]; worksheet.PrinterSettings.RepeatRows = worksheet.Cells[repeatRowsRange]; #endregion #endregion #region add freeze panes if (Host.Document.View == KnownDocumentView.Normal) { if (Table.FreezePanesPoint.X != 1 && Table.FreezePanesPoint.Y != 1) { worksheet.View.FreezePanes(Table.FreezePanesPoint.Y, Table.FreezePanesPoint.X); } } #endregion var range1 = worksheet.Cells["A1:H501"]; var wsPivot = worksheet.Workbook.Worksheets.Add("PivotSimple"); var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], range1, "PerEmploee"); pivotTable1.RowFields.Add(pivotTable1.Fields[3]); var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[5]); dataField.Format = "#,##0"; pivotTable1.DataOnRows = true; var chartp = wsPivot.Drawings.AddChart("PivotChart", eChartType.Pie, pivotTable1); chartp.SetPosition(1, 0, 4, 0); chartp.SetSize(600, 400); //var hl = new ExcelHyperLink("Statistics!A1", "Statistics"); //hl. //chartp. //var wsPivot2 = pck.Workbook.Worksheets.Add("PivotDateGrp"); //var pivotTable2 = wsPivot2.PivotTables.Add(wsPivot2.Cells["A3"], dataRange, "PerEmploeeAndQuarter"); //pivotTable2.RowFields.Add(pivotTable2.Fields["Name"]); ////Add a rowfield //var rowField = pivotTable2.RowFields.Add(pivotTable2.Fields["OrderDate"]); ////This is a date field so we want to group by Years and quaters. This will create one additional field for years. //rowField.AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Quarters); ////Get the Quaters field and change the texts //var quaterField = pivotTable2.Fields.GetDateGroupField(eDateGroupBy.Quarters); //quaterField.Items[0].Text = "<"; //Values below min date, but we use auto so its not used //quaterField.Items[1].Text = "Q1"; //quaterField.Items[2].Text = "Q2"; //quaterField.Items[3].Text = "Q3"; //quaterField.Items[4].Text = "Q4"; //quaterField.Items[5].Text = ">"; //Values above max date, but we use auto so its not used ////Add a pagefield //var pageField = pivotTable2.PageFields.Add(pivotTable2.Fields["Title"]); ////Add the data fields and format them //dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["SubTotal"]); //dataField.Format = "#,##0"; //dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]); //dataField.Format = "#,##0"; //dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]); //dataField.Format = "#,##0"; ////We want the datafields to appear in columns //pivotTable2.DataOnRows = false; #region save Result.Add(excel.GetAsByteArray()); #endregion } } finally { stream?.Dispose(); } }
internal ExcelThemeOverride(ExcelChart chart, ZipPackageRelationship rel) : base(chart._drawings._package, chart.NameSpaceManager, rel, "") { _chart = chart; }
private void SaveXlsx(string spath) { FileInfo newFile = new FileInfo(spath + ".xlsx"); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(spath + ".xlsx"); } using (ExcelPackage package = new ExcelPackage(newFile)) { ExcelWorksheet ws = package.Workbook.Worksheets.Add(zg[0].GraphPane.Title.Text); ExcelChart chart = ws.Drawings.AddChart(zg[0].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers); int b = 1; string a, c; for (int i = 0; i < zg[0].GraphPane.CurveList[0].Points.Count; i++) { ws.Cells[i + 2, b].Value = i; } foreach (var _c in zg[0].GraphPane.CurveList) { b++; for (int i = 0; i < _c.Points.Count; i++) { ws.Cells[(int)_c.Points[i].Y + 2, b].Value = _c.Points[i].X; } a = ExcelRange.GetAddress((int)_c.Points[0].Y + 2, 1, (int)_c.Points[_c.Points.Count - 1].Y + 2, 1); c = ExcelRange.GetAddress((int)_c.Points[0].Y + 2, b, (int)_c.Points[_c.Points.Count - 1].Y + 2, b); chart.Series.Add(a, c); chart.Legend.Remove(); } ws = package.Workbook.Worksheets.Add(zg[1].GraphPane.Title.Text); chart = ws.Drawings.AddChart(zg[1].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers); for (int i = 0; i < zg[1].GraphPane.CurveList[1].Points.Count; i++) { ws.Cells[i + 2, 1].Value = zg[1].GraphPane.CurveList[1].Points[i].X; ws.Cells[i + 2, 2].Value = zg[1].GraphPane.CurveList[1].Points[i].Y; } a = ExcelRange.GetAddress(2, 1, zg[1].GraphPane.CurveList[1].Points.Count + 1, 1); c = ExcelRange.GetAddress(2, 2, zg[1].GraphPane.CurveList[1].Points.Count + 1, 2); chart.Series.Add(c, a); chart.Legend.Remove(); ws.Cells[2, 3].Value = zg[1].GraphPane.CurveList[0].Points[0].X; ws.Cells[2, 4].Value = zg[1].GraphPane.CurveList[0].Points[0].Y; ws.Cells[3, 3].Value = zg[1].GraphPane.CurveList[0].Points[1].X; ws.Cells[3, 4].Value = zg[1].GraphPane.CurveList[0].Points[1].Y; a = ExcelRange.GetAddress(2, 3, 3, 3); c = ExcelRange.GetAddress(2, 4, 3, 4); chart.Series.Add(c, a); chart.Legend.Remove(); ws = package.Workbook.Worksheets.Add(zg[2].GraphPane.Title.Text); chart = ws.Drawings.AddChart(zg[2].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers); for (int i = 0; i < zg[2].GraphPane.CurveList[0].Points.Count; i++) { ws.Cells[i + 2, 1].Value = zg[2].GraphPane.CurveList[0].Points[i].X; ws.Cells[i + 2, 2].Value = zg[2].GraphPane.CurveList[0].Points[i].Y; } a = ExcelRange.GetAddress(2, 1, zg[2].GraphPane.CurveList[0].Points.Count + 1, 1); c = ExcelRange.GetAddress(2, 2, zg[2].GraphPane.CurveList[0].Points.Count + 1, 2); chart.Series.Add(c, a); chart.Legend.Remove(); package.Workbook.Properties.Title = textBox4.Text; package.Workbook.Properties.Author = Environment.UserName; package.Workbook.Properties.Comments = "Расчёт выполнен при помощи POSProgram"; package.Save(); } }
private void AddSeries(ExcelWorksheet bridgeWorkSummaryWorkSheet, int totalPoorDeckAreaByBPNYearsRow, int count, ExcelChart chart) { CreateSeries(bridgeWorkSummaryWorkSheet, totalPoorDeckAreaByBPNYearsRow, count, chart, totalPoorDeckAreaByBPNYearsRow + 1, Properties.Resources.BPN1, Color.FromArgb(0, 176, 80)); CreateSeries(bridgeWorkSummaryWorkSheet, totalPoorDeckAreaByBPNYearsRow, count, chart, totalPoorDeckAreaByBPNYearsRow + 2, Properties.Resources.BPN2, Color.FromArgb(255, 192, 0)); CreateSeries(bridgeWorkSummaryWorkSheet, totalPoorDeckAreaByBPNYearsRow, count, chart, totalPoorDeckAreaByBPNYearsRow + 3, Properties.Resources.BPN3, Color.FromArgb(0, 32, 96)); CreateSeries(bridgeWorkSummaryWorkSheet, totalPoorDeckAreaByBPNYearsRow, count, chart, totalPoorDeckAreaByBPNYearsRow + 4, Properties.Resources.BPN4, Color.FromArgb(143, 170, 220)); }
public void RunSample4() { using (ExcelPackage p = new ExcelPackage(new FileInfo("../../../SampleApp/GraphTemplate.xlsx"), true)) { //Set up the headers ExcelWorksheet ws = p.Workbook.Worksheets[1]; ws.Cells["A20"].Value = "Date"; ws.Cells["B20"].Value = "EOD Rate"; ws.Cells["B20:D20"].Merge = true; ws.Cells["E20"].Value = "Change"; ws.Cells["E20:G20"].Merge = true; ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous; using (ExcelRange r = ws.Cells["A20:G20"]) { r.Style.Fill.PatternType = ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93)); r.Style.Font.Color.SetColor(Color.White); r.Style.Font.Bold = true; } ws.Cells["B21"].Value = "USD/JPY"; ws.Cells["C21"].Value = "USD/EUR"; ws.Cells["D21"].Value = "USD/GBP"; ws.Cells["E21"].Value = "USD/JPY"; ws.Cells["F21"].Value = "USD/EUR"; ws.Cells["G21"].Value = "USD/GBP"; using (ExcelRange r = ws.Cells["A21:G21"]) { r.Style.Fill.PatternType = ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); r.Style.Font.Color.SetColor(Color.Black); r.Style.Font.Bold = true; } int startRow = 22; //Connect to the database and fill the data int row = startRow; // get the data and fill rows 22 onwards var rnd = new Random(); while (row < 50) { ws.Cells[row, 1].Value = rnd.Next(); ws.Cells[row, 2].Value = rnd.Next(); ws.Cells[row, 3].Value = rnd.Next(); ws.Cells[row, 4].Value = rnd.Next(); row++; } //Set the numberformat ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd"; ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000"; //Set the Formulas ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1); ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%"; //Set the series for the chart. The series must exist in the template or the program will crash. ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]); chart.Title.Text = "Exchange rate %"; chart.Series[0].Header = "USD/JPY"; chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5); chart.Series[1].Header = "USD/EUR"; chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6); chart.Series[2].Header = "USD/GBP"; chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7); //Get the documet as a byte array from the stream and save it to disk. (This is usefull in a webapplication) ... Byte[] bin = p.GetAsByteArray(); string file = Path.Combine("bin", "sample4.xlsx"); File.WriteAllBytes(file, bin); } }
private void AddChart(ExcelFile xls) { //This code is adapted from APIMate. //Objects TShapeProperties ChartOptions1 = new TShapeProperties(); ChartOptions1.Anchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, 1, 215, 1, 608, 30, 228, 17, 736); ChartOptions1.ShapeName = "Lines of code"; ChartOptions1.Print = true; ChartOptions1.Visible = true; ChartOptions1.ShapeOptions.SetValue(TShapeOption.fLockText, true); ChartOptions1.ShapeOptions.SetValue(TShapeOption.LockRotation, true); ChartOptions1.ShapeOptions.SetValue(TShapeOption.fAutoTextMargin, true); ChartOptions1.ShapeOptions.SetValue(TShapeOption.fillColor, 134217806); ChartOptions1.ShapeOptions.SetValue(TShapeOption.wzName, "Lines of code"); ExcelChart Chart1 = xls.AddChart(ChartOptions1, TChartType.Area, new ChartStyle(102), false); TDataLabel Title = new TDataLabel(); Title.PositionZeroBased = null; ChartFillOptions TextFillOptions = new ChartFillOptions(new TShapeFill(new TSolidFill(TDrawingColor.FromRgb(0x80, 0x80, 0x80)), true, TFormattingType.Subtle, TDrawingColor.FromRgb(0x00, 0x00, 0x00, new TColorTransform(TColorTransformType.Alpha, 0)), false)); TChartTextOptions LabelTextOptions = new TChartTextOptions(new TFlxChartFont("Calibri Light", 320, TExcelColor.FromArgb(0x80, 0x80, 0x80), TFlxFontStyles.Bold, TFlxUnderline.None, TFontScheme.Major), THFlxAlignment.center, TVFlxAlignment.center, TBackgroundMode.Transparent, TextFillOptions); Title.TextOptions = LabelTextOptions; TDataLabelOptions LabelOptions = new TDataLabelOptions(); Title.LabelOptions = LabelOptions; ChartLineOptions ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle)); ChartFillOptions ChartFillOptions = new ChartFillOptions(new TShapeFill(new TNoFill(), false, TFormattingType.Subtle, null, false)); Title.Frame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false); TRTFRun[] Runs; Runs = new TRTFRun[1]; Runs[0].FirstChar = 0; TFlxFont fnt; fnt = xls.GetDefaultFont; fnt.Name = "Calibri Light"; fnt.Size20 = 320; fnt.Color = TExcelColor.FromArgb(0x80, 0x80, 0x80); fnt.Style = TFlxFontStyles.Bold; fnt.Family = 0; fnt.CharSet = 1; fnt.Scheme = TFontScheme.Major; Runs[0].FontIndex = xls.AddFont(fnt); TRichString LabelValue1 = new TRichString("FlexCel: Lines of code over time", Runs, xls); Title.LabelValues = new Object[] { LabelValue1 }; Chart1.SetTitle(Title); Chart1.Background = new TChartFrameOptions(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), 9525, TDrawingColor.FromTheme(TThemeColor.Light1), false); TChartFrameOptions PlotAreaFrame; ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle)); ChartFillOptions = new ChartFillOptions(new TShapeFill(new TPatternFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), TDrawingColor.FromTheme(TThemeColor.Light1), TDrawingPattern.ltDnDiag), true, TFormattingType.Subtle, null, false)); PlotAreaFrame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false); TChartPlotAreaPosition PlotAreaPos = new TChartPlotAreaPosition(true, TChartRelativeRectangle.Automatic, TChartLayoutTarget.Inner, true); Chart1.PlotArea = new TChartPlotArea(PlotAreaFrame, PlotAreaPos, false); Chart1.SetChartOptions(1, new TAreaChartOptions(false, TStackedMode.Stacked, null)); int LastYear = 0; double shade = 1; for (int i = 2; i < 190; i++) { ChartSeries Series = new ChartSeries( "=" + new TCellAddress("Data", 1, i, true, true).CellRef, "=" + new TCellAddress("Data", 2, i, true, true).CellRef + ":" + new TCellAddress("Data", 189, i, true, true).CellRef, "=Data!$A$2:$A$189"); //We will display every year in a single color. Each month gets its own shade. int xf = -1; int Year = FlxDateTime.FromOADate(((double)xls.GetCellValue(2, 1, i, ref xf)), false).Year; if (LastYear != Year) { shade = 1; } else if (shade > 0.3) { shade -= 0.05; } LastYear = Year; TDrawingColor SeriesColor = TDrawingColor.FromTheme(TThemeColor.Accent1 + Year % 6, new TColorTransform(TColorTransformType.Shade, shade)); ChartSeriesFillOptions SeriesFill = new ChartSeriesFillOptions(new TShapeFill(new TSolidFill(SeriesColor), true, TFormattingType.Subtle, null, false), null, false, false); ChartSeriesLineOptions SeriesLine = new ChartSeriesLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle), false); Series.Options.Add(new ChartSeriesOptions(-1, SeriesFill, SeriesLine, null, null, null, true)); Chart1.AddSeries(Series); } Chart1.PlotEmptyCells = TPlotEmptyCells.Zero; Chart1.ShowDataInHiddenRowsAndCols = false; TFlxChartFont AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor); TAxisLineOptions AxisLine = new TAxisLineOptions(); AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle)); AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false; TAxisTickOptions AxisTicks = new TAxisTickOptions(TTickType.Outside, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0); TAxisRangeOptions AxisRangeOptions = new TAxisRangeOptions(12, 1, false, false, false); TBaseAxis CatAxis = new TCategoryAxis(0, 0, 12, TDateUnits.Days, 12, TDateUnits.Days, TDateUnits.Months, 0, TCategoryAxisOptions.AutoMin | TCategoryAxisOptions.AutoMax | TCategoryAxisOptions.DateAxis | TCategoryAxisOptions.AutoCrossDate | TCategoryAxisOptions.AutoDate, AxisFont, "yyyy\\-mm\\-dd;@", true, AxisLine, AxisTicks, AxisRangeOptions, null, TChartAxisPos.Bottom, 1); AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor); AxisLine = new TAxisLineOptions(); AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle)); AxisLine.MajorGridLines = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle)); AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false; AxisTicks = new TAxisTickOptions(TTickType.None, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0); CatAxis.NumberFormat = "yyyy-mm"; CatAxis.NumberFormatLinkedToSource = false; TBaseAxis ValAxis = new TValueAxis(0, 0, 0, 0, 0, TValueAxisOptions.AutoMin | TValueAxisOptions.AutoMax | TValueAxisOptions.AutoMajor | TValueAxisOptions.AutoMinor | TValueAxisOptions.AutoCross, AxisFont, "General", true, AxisLine, AxisTicks, null, TChartAxisPos.Left); Chart1.SetChartAxis(new TChartAxis(0, CatAxis, ValAxis)); }
internal ChartPlotArea(ExcelChart firstChart) { this._FirstChart = firstChart; }
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 Users, Groups, Roles and Permissions 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 RBAC 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 RBAC 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_RBAC_SHEET_CONTROLLERS_LIST); 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_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_LIST); 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 = "Types of Users"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USERS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_TYPE_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_RBAC_SHEET_USERS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_LIST); 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 = "Types of Groups"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_TYPE_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_RBAC_SHEET_GROUPS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_LIST); 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 = "Types of Roles"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_TYPE_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_RBAC_SHEET_ROLES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_LIST); 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 = "Types of Permissions"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_TYPE_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_RBAC_SHEET_PERMISSIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST); 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 = "Types of User Permissions"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST); 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_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST); 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_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); #endregion loggerConsole.Info("Fill Users, Groups, Roles and Permissions Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RBACControllerSummaryReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Users loggerConsole.Info("List of Users"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UsersReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Groups loggerConsole.Info("List of Groups"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Roles loggerConsole.Info("List of Roles"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RolesReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Permissions loggerConsole.Info("List of Permissions"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.PermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region User Permissions loggerConsole.Info("List of User Permissions"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UserPermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Group Memberships loggerConsole.Info("List of Group Memberships"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Role Memberships loggerConsole.Info("List of Role Memberships"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RoleMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Users, Groups, Roles and Permissions Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_CONTROLLERS); 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["SecurityProvider"].Position + 1).Width = 20; } #endregion #region Users // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USERS); 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["DisplayName"].Position + 1).Width = 20; sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USERS); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "UserName"); addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending); addDataFieldToPivot(pivot, "UserID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USERS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Groups // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUPS); 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["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 20; sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_GROUPS); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "GroupName"); addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending); addDataFieldToPivot(pivot, "GroupID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_GROUPS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Roles // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLES); 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["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["Description"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_ROLES); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "RoleName"); addDataFieldToPivot(pivot, "RoleID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_ROLES_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Permissions // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_PERMISSIONS); 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["RoleName"].Position + 1).Width = 20; sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_PERMISSIONS); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "RoleName"); addRowFieldToPivot(pivot, "EntityName"); addRowFieldToPivot(pivot, "PermissionName"); addColumnFieldToPivot(pivot, "Allowed"); addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_PERMISSIONS_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; } #endregion #region User Permissions // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USER_PERMISSIONS); 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["UserSecurityProvider"].Position + 1).Width = 15; sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["GroupSecurityProvider"].Position + 1).Width = 15; sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USER_PERMISSIONS); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "UserName"); addRowFieldToPivot(pivot, "GroupName"); addRowFieldToPivot(pivot, "RoleName"); addRowFieldToPivot(pivot, "EntityName"); addRowFieldToPivot(pivot, "PermissionName"); addColumnFieldToPivot(pivot, "Allowed"); addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USER_PERMISSIONS_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 Group Memberships // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUP_MEMBERSHIPS); 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["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["UserName"].Position + 1).Width = 20; } #endregion #region Role Memberships // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_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 > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLE_MEMBERSHIPS); 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["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["EntityName"].Position + 1).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) { sheet.Cells[rowNum, 2].Value = s.Tables[0].Address.Rows - 1; } } range = sheet.Cells[1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_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.RBACExcelReportFilePath(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); } }
private string CreateChartPackage(ExcelChart excelchart, PackagePart wrokSheetDrawingPart, Uri drawingUri) { Uri uriChart = this.Context.Package.GetNewUri("/xl/charts/chart{0}.xml"); PackageRelationship chartRelation = wrokSheetDrawingPart.CreateRelationship(PackUriHelper.GetRelativeUri(drawingUri, uriChart), TargetMode.Internal, ExcelCommon.Schema_Relationships + "/chart"); PackagePart chartPackagePart = this.Context.Package.CreatePart(uriChart, ExcelCommon.ContentType_sheetChart, CompressionOption.NotCompressed); XDocument chartDoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes")); WriteWrokSheetChart(excelchart, chartDoc); using (Stream chartStream = chartPackagePart.GetStream(FileMode.Create, FileAccess.Write)) { chartDoc.Save(chartStream); chartStream.Flush(); } return chartRelation.Id; }
//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); }
private void WriteWorkSheetChart_chart_valAx(ExcelChart excelChart, XElement plotAreaElement) { XElement valAxElement = new XElement(XName.Get("valAx", ExcelCommon.Schema_Chart)); WriteWorkSheetChart_chart_valAx_Content(excelChart, valAxElement); plotAreaElement.Add(valAxElement); }
internal ExcelBarChart(ExcelChart topChart, XmlNode chartNode) : base(topChart, chartNode) { SetChartNodeText(chartNode.Name); }
private void WriteWorkSheetChart_chart_chartType(ExcelChart excelChart, XElement plotAreaElement) { XElement chartTypeElement = new XElement(XName.Get(GetChartNodeTextByChartType(excelChart.ChartType), ExcelCommon.Schema_Chart)); WriteWorkSheetChart_chart_plotArea_scatterStyle(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_barDir(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_grouping(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_marker(excelChart, chartTypeElement); WriteWorkSheetChart_chart_varyColors(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_shape(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_firstSliceAng(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_holeSize(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_overlap(excelChart, chartTypeElement); WriteWorkSheetChart_chart_plotArea_axId(excelChart, chartTypeElement); switch (excelChart.ChartType) { case ExcelChartType.PieOfPie: case ExcelChartType.BarOfPie: case ExcelChartType.Pie: case ExcelChartType.Pie3D: WriteWorkSheetChart_chart_dLbls((ExcelPieChart)excelChart, chartTypeElement); WriteWorkSheetChart_chart_ser((ExcelPieChart)excelChart, chartTypeElement); break; } plotAreaElement.Add(chartTypeElement); }
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(programOptions, jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Dashboards 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 Dashboards 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 Dashboards 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_DASHBOARDS_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_DASHBOARDS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DASHBOARDS_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_DASHBOARDS_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_WIDGETS_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_WIDGETS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_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_WIDGETS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_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 Type"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DATA_SERIES_TYPE_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_DATA_SERIES_LOCATION_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_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_DATA_SERIES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_LOCATION_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_DATA_SERIES_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 Dashboards 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, typeof(ControllerSummary), 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, typeof(ControllerApplication), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Dashboards loggerConsole.Info("List of Dashboards"); sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardsReportFilePath(), 0, typeof(Dashboard), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Widgets loggerConsole.Info("List of Widgets"); sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardWidgetsReportFilePath(), 0, typeof(DashboardWidget), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Widget Data Series loggerConsole.Info("List of Widget Data Series"); sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardMetricSeriesReportFilePath(), 0, typeof(DashboardMetricSeries), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Dashboards 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 Dashboards // Make table sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_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_DASHBOARDS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumWidgets"].Position + 1, sheet.Dimension.Rows, table.Columns["NumWidgets"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["StartTime"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["StartTimeUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_DASHBOARDS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy", eSortType.Ascending); addFilterFieldToPivot(pivot, "UpdatedBy", eSortType.Ascending); addFilterFieldToPivot(pivot, "IsShared"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addDataFieldToPivot(pivot, "DashboardID", DataFieldFunctions.Count, "Dashboards"); addDataFieldToPivot(pivot, "NumWidgets", DataFieldFunctions.Sum, "Widgets"); addDataFieldToPivot(pivot, "NumAnalyticsWidgets", DataFieldFunctions.Sum, "Analytics"); addDataFieldToPivot(pivot, "NumEventListWidgets", DataFieldFunctions.Sum, "Events"); addDataFieldToPivot(pivot, "NumGaugeWidgets", DataFieldFunctions.Sum, "Gauges"); addDataFieldToPivot(pivot, "NumGraphWidgets", DataFieldFunctions.Sum, "Graphs"); addDataFieldToPivot(pivot, "NumIFrameWidgets", DataFieldFunctions.Sum, "IFrames"); addDataFieldToPivot(pivot, "NumImageWidgets", DataFieldFunctions.Sum, "Images"); addDataFieldToPivot(pivot, "NumMetricLabelWidgets", DataFieldFunctions.Sum, "Labels"); addDataFieldToPivot(pivot, "NumPieWidgets", DataFieldFunctions.Sum, "Pies"); addDataFieldToPivot(pivot, "NumTextWidgets", DataFieldFunctions.Sum, "Texts"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DASHBOARDS_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; } #endregion #region Widgets // Make table sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_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_WIDGETS); 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["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 15; sheet.Column(table.Columns["Title"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_WIDGETS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "EntityType", eSortType.Ascending); addFilterFieldToPivot(pivot, "EntitySelectionType"); addFilterFieldToPivot(pivot, "NumSelectedEntities", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumDataSeries", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addColumnFieldToPivot(pivot, "WidgetType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WIDGETS_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; } #endregion #region Data Series // Make table sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_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_DATA_SERIES); 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["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 15; sheet.Column(table.Columns["SeriesName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricType"].Position + 1).Width = 15; sheet.Column(table.Columns["MetricPath"].Position + 1).Width = 25; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DATA_SERIES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "WidgetType"); addFilterFieldToPivot(pivot, "ApplicationName", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addRowFieldToPivot(pivot, "MetricType"); addRowFieldToPivot(pivot, "MetricPath"); addColumnFieldToPivot(pivot, "SeriesType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DATA_SERIES_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; sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_LOCATION_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DATA_SERIES_LOCATION); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ApplicationName", eSortType.Ascending); addFilterFieldToPivot(pivot, "EntityType", eSortType.Ascending); addRowFieldToPivot(pivot, "WidgetType"); addRowFieldToPivot(pivot, "MetricType"); addRowFieldToPivot(pivot, "MetricPath"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addColumnFieldToPivot(pivot, "SeriesType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); chart = sheet.Drawings.AddChart(GRAPH_DATA_SERIES_LOCATION, 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 TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.DashboardsExcelReportFilePath(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); } }
private void WriteWorkSheetChart_chart_plotArea_firstSliceAng(ExcelChart excelChart, XElement chartTypeElement) { if (excelChart.ChartType == ExcelChartType.Doughnut || excelChart.ChartType == ExcelChartType.DoughnutExploded) { chartTypeElement.Add(new XElement(XName.Get("firstSliceAng", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 0))); } }
static void Main(string[] args) { try { string temp = args[0]; } catch { Console.WriteLine("This program was not meant to be run in standalone mode."); System.Threading.Thread.Sleep(3000); System.Environment.Exit(0); } string uut = args[0]; string csvMetCalFileName = args[1]; int maxFreq = Convert.ToInt32(args[2]); bool isFirstTest = Convert.ToBoolean(args[3]); string pattern = "M:/UserPrograms/CMW500/"; string replacement = ""; Regex rgx = new Regex(pattern); string csvFileName = rgx.Replace(csvMetCalFileName, replacement); DirectoryInfo csvTempFolder = new DirectoryInfo(@"M:\UserPrograms\CMW500\"); FileInfo csvFullFileName = new FileInfo(csvTempFolder + csvFileName); string userDesktop = Environment.GetEnvironmentVariable("USERPROFILE") + @"\Desktop\"; string bookName = userDesktop + uut + ".xlsx"; FileInfo book = new FileInfo(bookName); if (IsFileinUse(book)) { Console.WriteLine("Close the Workbook!"); while (IsFileinUse(book)) { System.Threading.Thread.Sleep(500); } } if (book.Exists && isFirstTest) { book.Delete(); book = new FileInfo(bookName); } ExcelPackage package = new ExcelPackage(book); ExcelWorksheet sheet = package.Workbook.Worksheets.Add(csvFileName); var csvText = sheet.Cells.LoadFromText(csvFullFileName); sheet.Cells["B1:E1"].Clear(); sheet.Cells["B2"].Clear(); sheet.Cells[maxFreq + 3, 2].Clear(); sheet.Cells[maxFreq + 4, 2, maxFreq + 4, 5].Clear(); sheet.Cells["A1"].Style.Font.Size = 22; sheet.Row(1).Merged = true; ExcelChart chart = (ExcelLineChart)sheet.Drawings.AddChart("chart1", eChartType.Line); for (int col = 1; col <= 6; col++) { chart.Series.Add(csvText.Offset(1, col, maxFreq + 2, 1), csvText.Offset(1, 0, maxFreq + 2, 1)); } chart.Title.Text = sheet.Cells[maxFreq + 4, 1].Value.ToString(); chart.SetPosition(42, 350); chart.SetSize(800, 300); chart.DisplayBlanksAs = eDisplayBlanksAs.Gap; chart.Legend.Remove(); RemoveGridlines(chart); double yMax = Math.Ceiling((double)sheet.Cells["G2"].Value / 0.075) / 10; //double yMax = (double)sheet.Cells["G2"].Value / 0.075; //Console.WriteLine(yMax); //Console.ReadLine(); chart.XAxis.CrossesAt = -yMax; chart.XAxis.MajorTickMark = eAxisTickMark.In; chart.XAxis.MinorTickMark = eAxisTickMark.None; chart.XAxis.MinValue = 0; chart.XAxis.MaxValue = maxFreq + 2; chart.XAxis.MajorUnit = 2; chart.XAxis.Title.Text = "Frequency (MHz)"; chart.XAxis.Title.Font.Size = 12; // chart.YAxis.MinValue = -yMax;// - 0.01; // chart.YAxis.MaxValue = yMax + 0.05; // chart.YAxis.MajorUnit = Math.Ceiling(yMax / 5) * 5; chart.YAxis.MinorTickMark = eAxisTickMark.None; chart.YAxis.Format = "0.0"; chart.YAxis.Title.Text = "Error (dB)"; chart.YAxis.Title.Font.Size = 12; chart.YAxis.CrossBetween = eCrossBetween.MidCat; var data = (ExcelLineChartSerie)chart.Series[0]; data.Smooth = true; data.LineColor = Color.CornflowerBlue; var loLimit24 = (ExcelLineChartSerie)chart.Series[1]; loLimit24.LineWidth = 1; loLimit24.LineColor = Color.Red; var loLimit12 = (ExcelLineChartSerie)chart.Series[2]; loLimit12.LineWidth = 1; loLimit12.LineColor = Color.PaleVioletRed; var zero = (ExcelLineChartSerie)chart.Series[3]; zero.LineWidth = 1; zero.LineColor = Color.Silver; var hiLimit12 = (ExcelLineChartSerie)chart.Series[4]; hiLimit12.LineWidth = 1; hiLimit12.LineColor = Color.PaleVioletRed; var hiLimit24 = (ExcelLineChartSerie)chart.Series[5]; hiLimit24.LineWidth = 1; hiLimit24.LineColor = Color.Red; package.Save(); #if !DEBUG csvFullFileName.Delete(); #endif }
private void WriteWorkSheetChart_chart_plotArea_barDir(ExcelChart excelChart, XElement chartTypeElement) { switch (excelChart.ChartType) { case ExcelChartType.BarClustered3D: case ExcelChartType.BarStacked3D: case ExcelChartType.BarStacked1003D: case ExcelChartType.Column3D: case ExcelChartType.ColumnClustered3D: case ExcelChartType.ColumnStacked3D: case ExcelChartType.ColumnStacked1003D: case ExcelChartType.Bubble3DEffect: case ExcelChartType.ConeBarClustered: case ExcelChartType.ConeBarStacked: case ExcelChartType.ConeBarStacked100: case ExcelChartType.ConeCol: case ExcelChartType.ConeColClustered: case ExcelChartType.ConeColStacked: case ExcelChartType.ConeColStacked100: case ExcelChartType.CylinderBarClustered: case ExcelChartType.CylinderBarStacked: case ExcelChartType.CylinderBarStacked100: case ExcelChartType.CylinderCol: case ExcelChartType.CylinderColClustered: case ExcelChartType.CylinderColStacked: case ExcelChartType.CylinderColStacked100: case ExcelChartType.PyramidBarClustered: case ExcelChartType.PyramidBarStacked: case ExcelChartType.PyramidBarStacked100: case ExcelChartType.PyramidCol: case ExcelChartType.PyramidColClustered: case ExcelChartType.PyramidColStacked: case ExcelChartType.PyramidColStacked100: chartTypeElement.Add(new XElement(XName.Get("barDir", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "col"))); break; } }
private void AddSeries(ExcelWorksheet bridgeWorkSummaryWorkSheet, int nonNHSDeckAreaPercentRow, int count, ExcelChart chart) { CreateSeries(bridgeWorkSummaryWorkSheet, nonNHSDeckAreaPercentRow, count, chart, nonNHSDeckAreaPercentRow + 3, Properties.Resources.Poor, Color.Red); CreateSeries(bridgeWorkSummaryWorkSheet, nonNHSDeckAreaPercentRow, count, chart, nonNHSDeckAreaPercentRow + 2, Properties.Resources.Fair, Color.Yellow); CreateSeries(bridgeWorkSummaryWorkSheet, nonNHSDeckAreaPercentRow, count, chart, nonNHSDeckAreaPercentRow + 1, Properties.Resources.Good, Color.FromArgb(0, 176, 80)); }
private void WriteWorkSheetChart_char_dispBlanksAs(ExcelChart excelChart, XElement chartElement) { if (excelChart.DisplayBlanksAs != ExcelDisplayBlanksAs.Gap) { XElement dispBlanksAsElement = new XElement(XName.Get("dispBlanksAs", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelChart.DisplayBlanksAs.ToString().ToLower())); chartElement.Add(dispBlanksAsElement); } }
private void CreateSeries(ExcelWorksheet bridgeWorkSummaryWorkSheet, int nonNHSDeckAreaPercentRow, int count, ExcelChart chart, int fromRow, string header, Color color) { var serie = bridgeWorkSummaryWorkSheet.Cells[fromRow, 2, fromRow, count + 2]; var xSerie = bridgeWorkSummaryWorkSheet.Cells[nonNHSDeckAreaPercentRow, 2, nonNHSDeckAreaPercentRow, count + 2]; var excelChartSerie = chart.Series.Add(serie, xSerie); excelChartSerie.Header = header; excelChartSerie.Fill.Color = color; }
private static void AddTestSerie(ExcelWorksheet ws, ExcelChart chrt) { ws.Cells["U19"].Value = new DateTime(2009, 12, 31); ws.Cells["U20"].Value = new DateTime(2010, 1, 1); ws.Cells["U21"].Value = new DateTime(2010, 1, 2); ws.Cells["U22"].Value = new DateTime(2010, 1, 3); ws.Cells["U23"].Value = new DateTime(2010, 1, 4); ws.Cells["U24"].Value = new DateTime(2010, 1, 5); ws.Cells["U19:U24"].Style.Numberformat.Format = "yyyy-mm-dd"; ws.Cells["V19"].Value = 100; ws.Cells["V20"].Value = 102; ws.Cells["V21"].Value = 101; ws.Cells["V22"].Value = 103; ws.Cells["V23"].Value = 105; ws.Cells["V24"].Value = 104; chrt.Series.Add("V19:V24", "U19:U24"); }
private void SetChartAxes(ExcelChart chart) { stackedColumnChartCommon.SetChartAxes(chart); chart.YAxis.Format = "#0%"; chart.YAxis.MaxValue = 1; }
/// <summary> /// This sample creates a new workbook from a template file containing a chart and populates it with Exchangrates from /// the Adventureworks database and set the three series on the chart. /// </summary> /// <param name="connectionString">Connectionstring to the Adventureworks db</param> /// <param name="template">the template</param> /// <param name="outputdir">output dir</param> /// <returns></returns> public static string RunSample4(string connectionString, FileInfo template) { using (ExcelPackage p = new ExcelPackage(template, true)) { //Set up the headers ExcelWorksheet ws = p.Workbook.Worksheets[0]; ws.Cells["A20"].Value = "Date"; ws.Cells["B20"].Value = "EOD Rate"; ws.Cells["B20:D20"].Merge = true; ws.Cells["E20"].Value = "Change"; ws.Cells["E20:G20"].Merge = true; ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous; using (ExcelRange row = ws.Cells["A20:G20"]) { row.Style.Fill.PatternType = ExcelFillStyle.Solid; row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93)); row.Style.Font.Color.SetColor(Color.White); row.Style.Font.Bold = true; } ws.Cells["B21"].Value = "USD/JPY"; ws.Cells["C21"].Value = "USD/EUR"; ws.Cells["D21"].Value = "USD/GBP"; ws.Cells["E21"].Value = "USD/JPY"; ws.Cells["F21"].Value = "USD/EUR"; ws.Cells["G21"].Value = "USD/GBP"; using (ExcelRange row = ws.Cells["A21:G21"]) { row.Style.Fill.PatternType = ExcelFillStyle.Solid; row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); row.Style.Font.Color.SetColor(Color.Black); row.Style.Font.Bold = true; } int startRow = 22; //Connect to the database and fill the data using (SqlConnection sqlConn = new SqlConnection(connectionString)) { int row = startRow; sqlConn.Open(); using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [Sales].[CurrencyRate] where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate ORDER BY CurrencyRateDate", sqlConn)) { using (SqlDataReader sqlReader = sqlCmd.ExecuteReader()) { // get the data and fill rows 22 onwards while (sqlReader.Read()) { ws.Cells[row, 1].Value = sqlReader[0]; ws.Cells[row, 2].Value = sqlReader[1]; ws.Cells[row, 3].Value = sqlReader[2]; ws.Cells[row, 4].Value = sqlReader[3]; row++; } } //Set the numberformat ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd"; ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000"; //Set the Formulas ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1); ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%"; } //Set the series for the chart. The series must exist in the template or the program will crash. ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]); chart.Title.Text = "Exchange rate %"; chart.Series[0].Header = "USD/JPY"; chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5); chart.Series[1].Header = "USD/EUR"; chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6); chart.Series[2].Header = "USD/GBP"; chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1); chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7); } //Get the documet as a byte array from the stream and save it to disk. (This is useful in a webapplication) ... Byte[] bin = p.GetAsByteArray(); FileInfo file = Utils.GetFileInfo("sample4.xlsx"); File.WriteAllBytes(file.FullName, bin); return(file.FullName); } }
internal ExcelDoughnutChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) : base(drawings, node, type, topChart, PivotTableSource) { //SetPaths(); }
private XElement WriteWorkSheetDrawing_Content_graphicFrame(ExcelChart excelchart, DrawingCollection drawingCollection, PackagePart wrokSheetDrawingPart, int index) { XElement graphicFrameNode = new XElement(XName.Get("graphicFrame", ExcelCommon.Schema_SheetDrawings), new XAttribute(XName.Get("macro"), "")); XElement nvGraphicFramePrNode = new XElement(XName.Get("nvGraphicFramePr", ExcelCommon.Schema_SheetDrawings)); XElement cNvPrNode = new XElement(XName.Get("cNvPr", ExcelCommon.Schema_SheetDrawings), new XAttribute(XName.Get("id"), index + 2), new XAttribute(XName.Get("name"), excelchart.Name)); nvGraphicFramePrNode.Add(cNvPrNode); nvGraphicFramePrNode.Add(new XElement(XName.Get("cNvGraphicFramePr", ExcelCommon.Schema_SheetDrawings))); graphicFrameNode.Add(nvGraphicFramePrNode); XElement xfrmElement = new XElement(XName.Get("xfrm", ExcelCommon.Schema_SheetDrawings), new XElement(XName.Get("off", ExcelCommon.Schema_Drawings), new XAttribute(XName.Get("x"), 0), new XAttribute(XName.Get("y"), 0)), new XElement(XName.Get("ext", ExcelCommon.Schema_Drawings), new XAttribute(XName.Get("cx"), 0), new XAttribute(XName.Get("cy"), 0))); graphicFrameNode.Add(xfrmElement); XElement graphicElement = new XElement(XName.Get("graphic", ExcelCommon.Schema_Drawings)); XElement graphicDataElement = new XElement(XName.Get("graphicData", ExcelCommon.Schema_Drawings), new XAttribute(XName.Get("uri"), ExcelCommon.Schema_Chart)); XElement chartElement = new XElement(XName.Get("chart", ExcelCommon.Schema_Chart), new XAttribute(XNamespace.Xmlns + "c", ExcelCommon.Schema_Chart), new XAttribute(XNamespace.Xmlns + "r", ExcelCommon.Schema_Relationships)); string chartRelationID = CreateChartPackage(excelchart, wrokSheetDrawingPart, drawingCollection.DrawingUri); chartElement.Add(new XAttribute(XName.Get("id", ExcelCommon.Schema_Relationships), chartRelationID)); graphicDataElement.Add(chartElement); graphicElement.Add(graphicDataElement); //graphicElement.Add(new XElement(XName.Get("clientData", ExcelCommon.Schema_SheetDrawings))); graphicFrameNode.Add(graphicElement); return graphicFrameNode; }
internal ExcelDoughnutChart(ExcelChart topChart, XmlNode chartNode) : base(topChart, chartNode) { //SetPaths(); }
public void WriteWrokSheetChart(ExcelChart excelChart, XDocument chartDoc) { XElement root = new XElement(XName.Get("chartSpace", ExcelCommon.Schema_Chart), new XAttribute(XNamespace.Xmlns + "c", ExcelCommon.Schema_Chart), new XAttribute(XNamespace.Xmlns + "a", ExcelCommon.Schema_Drawings), new XAttribute(XNamespace.Xmlns + "r", ExcelCommon.Schema_Relationships)); XElement dateElement = new XElement(XName.Get("date1904", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 0)); root.Add(dateElement); XElement langElement = new XElement(XName.Get("lang", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "zh-CN")); root.Add(langElement); XElement roundedCornersElement = new XElement(XName.Get("roundedCorners", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelChart.RoundedCorners ? "1" : "0")); root.Add(roundedCornersElement); if (excelChart.Style != ExcelChartStyle.None) { XElement styleElement = new XElement(XName.Get("style", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), (int)excelChart.Style)); root.Add(styleElement); } WriteWorkSheetChart_chart(excelChart, root); WriteWorkSheetChart_printSettings(excelChart, root); chartDoc.Add(root); /* <c:plotArea> <c:layout /> <c:pieChart> <c:varyColors val="1" /> <c:ser> <c:idx val="0" /> <c:order val="0" /> <c:tx> <c:strRef> <c:f></c:f> <c:strCache> <c:ptCount val="1" /> </c:strCache> </c:strRef> </c:tx> <c:cat> <c:numRef> <c:f>PieChart!$U$19:$U$24</c:f> <c:numCache> <c:formatCode>yyyy\-mm\-dd</c:formatCode> <c:ptCount val="6" /> <c:pt idx="0"> <c:v>40178</c:v> </c:pt> <c:pt idx="1"> <c:v>40179</c:v> </c:pt> <c:pt idx="2"> <c:v>40180</c:v> </c:pt> <c:pt idx="3"> <c:v>40181</c:v> </c:pt> <c:pt idx="4"> <c:v>40182</c:v> </c:pt> <c:pt idx="5"> <c:v>40183</c:v> </c:pt> </c:numCache> </c:numRef> </c:cat> <c:val> <c:numRef> <c:f>PieChart!$V$19:$V$24</c:f> <c:numCache> <c:formatCode>General</c:formatCode> <c:ptCount val="6" /> <c:pt idx="0"> <c:v>100</c:v> </c:pt> <c:pt idx="1"> <c:v>102</c:v> </c:pt> <c:pt idx="2"> <c:v>101</c:v> </c:pt> <c:pt idx="3"> <c:v>103</c:v> </c:pt> <c:pt idx="4"> <c:v>105</c:v> </c:pt> <c:pt idx="5"> <c:v>104</c:v> </c:pt> </c:numCache> </c:numRef> </c:val> </c:ser> <c:dLbls> <c:showLegendKey val="0" /> <c:showVal val="0" /> <c:showCatName val="0" /> <c:showSerName val="0" /> <c:showPercent val="1" /> <c:showBubbleSize val="0" /> <c:separator></c:separator> <c:showLeaderLines val="0" /> </c:dLbls> <c:firstSliceAng val="0" /> </c:pieChart> </c:plotArea> <c:legend> <c:legendPos val="l" /> <c:layout /> <c:overlay val="0" /> <c:txPr> <a:bodyPr /> <a:lstStyle /> <a:p> <a:pPr> <a:defRPr> <a:solidFill> <a:srgbClr val="4682B4" /> </a:solidFill> </a:defRPr> </a:pPr> <a:endParaRPr lang="zh-CN" /> </a:p> </c:txPr> </c:legend> <c:plotVisOnly val="1" /> <c:dispBlanksAs val="zero" /> <c:showDLblsOverMax val="1" /> </c:chart> </c:chartSpace> */ }
internal ExcelChartExLegend(ExcelChart chart, XmlNamespaceManager nsm, XmlNode node) : base(nsm, node, chart, "cx") { SchemaNodeOrder = new string[] { "spPr", "txPr" }; }
private void WriteWorkSheetChart_chart_Ax(ExcelChart excelChart, XElement plotAreaElement) { bool isTypePieDoughnut = false, isTypeScatter = false; switch (excelChart.ChartType) { case ExcelChartType.Pie: case ExcelChartType.PieExploded: case ExcelChartType.PieOfPie: case ExcelChartType.Pie3D: case ExcelChartType.PieExploded3D: case ExcelChartType.BarOfPie: case ExcelChartType.Doughnut: case ExcelChartType.DoughnutExploded: isTypePieDoughnut = true; break; case ExcelChartType.XYScatter: case ExcelChartType.XYScatterLines: case ExcelChartType.XYScatterLinesNoMarkers: case ExcelChartType.XYScatterSmooth: case ExcelChartType.XYScatterSmoothNoMarkers: isTypeScatter = true; break; } if (isTypePieDoughnut == false) { if (isTypeScatter) { XElement valAxElement = new XElement(XName.Get("valAx", ExcelCommon.Schema_Chart)); WriteWorkSheetChart_chart_Ax_Content(excelChart, valAxElement); plotAreaElement.Add(valAxElement); } else { XElement catAxElement = new XElement(XName.Get("catAx", ExcelCommon.Schema_Chart)); WriteWorkSheetChart_chart_Ax_Content(excelChart, catAxElement); WriteWorkSheetChart_chart_catAx_Content(excelChart, catAxElement); plotAreaElement.Add(catAxElement); } WriteWorkSheetChart_chart_valAx(excelChart, plotAreaElement); } }
internal ExcelPieChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) : base(drawings, node, type, topChart, PivotTableSource) { }
private void WriteWorkSheetChart_chart_catAx_Content(ExcelChart excelChart, XElement catAxElement) { XElement autoElement = new XElement(XName.Get("auto", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 1)); catAxElement.Add(autoElement); XElement lblAlgnElement = new XElement(XName.Get("lblAlgn", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "ctr")); catAxElement.Add(lblAlgnElement); XElement lblOffsetElement = new XElement(XName.Get("lblOffset", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "100")); catAxElement.Add(lblOffsetElement); }
internal ExcelPieChart(ExcelChart topChart, XmlNode chartNode) : base(topChart, chartNode) { }
private void WriteWorkSheetChart_chart_plotArea(ExcelChart excelChart, XElement root) { XElement plotAreaElement = new XElement(XName.Get("plotArea", ExcelCommon.Schema_Chart)); XElement layoutElement = new XElement(XName.Get("layout", ExcelCommon.Schema_Chart)); plotAreaElement.Add(layoutElement); if (excelChart.IsType3D()) { XElement view3DElement = new XElement(XName.Get("view3D", ExcelCommon.Schema_Chart)); view3DElement.Add(new XElement(XName.Get("perspective", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 30))); plotAreaElement.Add(view3DElement); } WriteWorkSheetChart_chart_chartType(excelChart, plotAreaElement); WriteWorkSheetChart_chart_Ax(excelChart, plotAreaElement); root.Add(plotAreaElement); }
public ExcelChartExPlotarea(XmlNamespaceManager ns, XmlNode node, ExcelChart chart) : base(ns, node, chart, "cx") { SchemaNodeOrder = new string[] { "plotAreaRegion", "axis", "spPr" }; }
private void WriteWorkSheetChart_chart_varyColors(ExcelChart excelChart, XElement chartTypeElement) { chartTypeElement.Add(new XElement(XName.Get("varyColors", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelChart.VaryColors ? "1" : "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 FileIOHelper.CreateFolder(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); } }
private void WriteWorkSheetChart_chart_plotArea_overlap(ExcelChart excelChart, XElement chartTypeElement) { if (excelChart.ChartType == ExcelChartType.BarStacked100 || excelChart.ChartType == ExcelChartType.BarStacked || excelChart.ChartType == ExcelChartType.ColumnStacked || excelChart.ChartType == ExcelChartType.ColumnStacked100) { chartTypeElement.Add(new XElement(XName.Get("overlap", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 100))); } }
internal ExcelChartPlotArea(XmlNamespaceManager ns, XmlNode node, ExcelChart firstChart) : base(ns, node) { _firstChart = firstChart; }
private void WriteWorkSheetChart_chart_plotArea_marker(ExcelChart excelChart, XElement chartTypeElement) { if (excelChart.ChartType == ExcelChartType.LineMarkers || excelChart.ChartType == ExcelChartType.LineMarkersStacked || excelChart.ChartType == ExcelChartType.LineMarkersStacked100) { chartTypeElement.Add(new XElement(XName.Get("marker", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 1))); } }
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(programOptions, jobConfiguration) == false) { 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(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(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); 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_EVENTS); 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_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>"")", SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_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_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_TIMELINE_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_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 7, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENT_DETAILS); 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_EVENT_DETAILS_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENT_DETAILS_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_EVENT_DETAILS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS); 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_HEALTH_RULE_VIOLATIONS_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS_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_HEALTH_RULE_VIOLATIONS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS); 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_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>"")", AUDIT_SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS_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_AUDIT_EVENTS); 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(AUDIT_SHEET_EVENTS_TIMELINE_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_AUDIT_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_NOTIFICATIONS); 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); #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[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsSummaryReportFilePath(), 0, typeof(ApplicationEventSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Events loggerConsole.Info("List of Events"); sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsReportFilePath(), 0, typeof(Event), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Event Details sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventDetailsReportFilePath(), 0, typeof(EventDetail), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Health Rule Violation Events loggerConsole.Info("List of Health Rule Violation Events"); sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationHealthRuleViolationsReportFilePath(), 0, typeof(HealthRuleViolationEvent), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Audit Events loggerConsole.Info("List of Audit Events"); sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.AuditEventsReportFilePath(), 0, typeof(AuditEvent), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Notifications loggerConsole.Info("List of Notifications"); sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.NotificationsReportFilePath(), 0, typeof(Event), sheet, 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[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 // 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["NumEvents"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEvents"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(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(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(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(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(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(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[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 > 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_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[SHEET_EVENTS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, 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(GRAPH_EVENTS_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; sheet.Column(6).Width = 20; sheet.Column(7).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS_TIMELINE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1], range, 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(GRAPH_EVENTS_TIMELINE, eChartType.Line, 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 Event Details // Make table sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS]; 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_EVENT_DETAILS); 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["DetailAction"].Position + 1).Width = 15; sheet.Column(table.Columns["DetailName"].Position + 1).Width = 15; sheet.Column(table.Columns["DetailValue"].Position + 1).Width = 25; sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_EVENT_DETAILS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "Severity"); addFilterFieldToPivot(pivot, "DetailAction"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "Type"); addRowFieldToPivot(pivot, "DetailName"); addColumnFieldToPivot(pivot, "DataType", eSortType.Ascending); addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EVENT_DETAILS_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 Health Rule Violation Events // Make table sheet = excelReport.Workbook.Worksheets[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 > 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_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[SHEET_HEALTH_RULE_VIOLATIONS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, 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(GRAPH_HEALTH_RULE_VIOLATION_EVENTS_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; sheet.Column(6).Width = 20; } #endregion #region Audit Events // Make table sheet = excelReport.Workbook.Worksheets[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 > 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_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[SHEET_AUDIT_EVENTS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, 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(GRAPH_AUDIT_EVENTS_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 = 30; sheet = excelReport.Workbook.Worksheets[AUDIT_SHEET_EVENTS_TIMELINE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, 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(GRAPH_AUDIT_EVENTS_TIMELINE, eChartType.Line, 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 Notifications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS]; 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_NOTIFICATIONS); 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; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(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); } }
private void WriteWorkSheetChart_chart_plotArea_grouping(ExcelChart excelChart, XElement chartTypeElement) { switch (excelChart.ChartType) { case ExcelChartType.BarClustered3D: case ExcelChartType.BarStacked3D: case ExcelChartType.BarStacked1003D: case ExcelChartType.Column3D: case ExcelChartType.ColumnClustered3D: case ExcelChartType.ColumnStacked3D: case ExcelChartType.ColumnStacked1003D: case ExcelChartType.Bubble3DEffect: case ExcelChartType.ConeBarClustered: case ExcelChartType.ConeBarStacked: case ExcelChartType.ConeBarStacked100: case ExcelChartType.ConeCol: case ExcelChartType.ConeColClustered: case ExcelChartType.ConeColStacked: case ExcelChartType.ConeColStacked100: case ExcelChartType.CylinderBarClustered: case ExcelChartType.CylinderBarStacked: case ExcelChartType.CylinderBarStacked100: case ExcelChartType.CylinderCol: case ExcelChartType.CylinderColClustered: case ExcelChartType.CylinderColStacked: case ExcelChartType.CylinderColStacked100: case ExcelChartType.PyramidBarClustered: case ExcelChartType.PyramidBarStacked: case ExcelChartType.PyramidBarStacked100: case ExcelChartType.PyramidCol: case ExcelChartType.PyramidColClustered: case ExcelChartType.PyramidColStacked: case ExcelChartType.PyramidColStacked100: case ExcelChartType.Line: case ExcelChartType.LineMarkers: case ExcelChartType.LineMarkersStacked100: case ExcelChartType.LineStacked: case ExcelChartType.LineStacked100: case ExcelChartType.Line3D: { if (excelChart.Grouping != ExcelGrouping.Clustered) chartTypeElement.Add(new XElement(XName.Get("grouping", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), GetGroupingText(excelChart.Grouping)))); } break; } }
internal ExcelChartExAxis(ExcelChart chart, XmlNamespaceManager nsm, XmlNode topNode) : base(chart, nsm, topNode, "cx") { SchemaNodeOrder = new string[] { "catScaling", "valScaling", "title", "units", "majorGridlines", "minorGridlines", "majorTickMarks", "minorTickMarks", "tickLabels", "numFmt", "spPr", "txPr" }; }
private void WriteWorkSheetChart_chart_plotArea_scatterStyle(ExcelChart excelChart, XElement chartTypeElement) { switch (excelChart.ChartType) { case ExcelChartType.XYScatter: case ExcelChartType.XYScatterLines: case ExcelChartType.XYScatterLinesNoMarkers: case ExcelChartType.XYScatterSmooth: case ExcelChartType.XYScatterSmoothNoMarkers: chartTypeElement.Add(new XElement(XName.Get("scatterStyle", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), string.Empty))); break; } }
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 FileIOHelper.CreateFolder(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); } }
private void WriteWorkSheetChart_char_showDLblsOverMax(ExcelChart excelChart, XElement chartElement) { if (excelChart.DisplayBlanksAs != ExcelDisplayBlanksAs.Gap) { XElement dispBlanksAsElement = new XElement(XName.Get("showDLblsOverMax", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), "0")); chartElement.Add(dispBlanksAsElement); } }
private static void AddTestSerie(ExcelWorksheet ws, ExcelChart chrt) { AddTestData(ws); chrt.Series.Add("'" + ws.Name + "'!V19:V24", "'" + ws.Name + "'!U19:U24"); }
private void WriteWorkSheetChart_char_plotVisOnly(ExcelChart excelChart, XElement chartElement) { XElement plotVisOnlyElement = new XElement(XName.Get("plotVisOnly", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), 1)); chartElement.Add(plotVisOnlyElement); }
public void MultiChartSeries() { ExcelWorksheet worksheet = _pck.Workbook.Worksheets.Add("MultiChartTypes"); ExcelChart chart = worksheet.Drawings.AddChart("chtPie", eChartType.LineMarkers); chart.SetPosition(100, 100); chart.SetSize(800, 600); AddTestSerie(worksheet, chart); chart.Series[0].Header = "Serie5"; chart.Style = eChartStyle.Style27; worksheet.Cells["W19"].Value = 120; worksheet.Cells["W20"].Value = 122; worksheet.Cells["W21"].Value = 121; worksheet.Cells["W22"].Value = 123; worksheet.Cells["W23"].Value = 125; worksheet.Cells["W24"].Value = 124; worksheet.Cells["X19"].Value = 90; worksheet.Cells["X20"].Value = 52; worksheet.Cells["X21"].Value = 88; worksheet.Cells["X22"].Value = 75; worksheet.Cells["X23"].Value = 77; worksheet.Cells["X24"].Value = 99; var cs2 = chart.PlotArea.ChartTypes.Add(eChartType.ColumnClustered); var s = cs2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]); s.Header = "Serie4"; cs2.YAxis.MaxValue = 300; cs2.YAxis.MinValue = -5.5; var cs3 = chart.PlotArea.ChartTypes.Add(eChartType.Line); s = cs3.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["U19:U24"]); s.Header = "Serie1"; cs3.UseSecondaryAxis = true; cs3.XAxis.Deleted = false; cs3.XAxis.MajorUnit = 20; cs3.XAxis.MinorUnit = 3; cs3.XAxis.TickLabelPosition = eTickLabelPosition.High; cs3.YAxis.LogBase = 10.2; var chart2 = worksheet.Drawings.AddChart("scatter1", eChartType.XYScatterSmooth); s = chart2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]); s.Header = "Serie2"; var c2ct2 = chart2.PlotArea.ChartTypes.Add(eChartType.XYScatterSmooth); s = c2ct2.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["V19:V24"]); s.Header = "Serie3"; s = c2ct2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["V19:V24"]); s.Header = "Serie4"; c2ct2.UseSecondaryAxis = true; c2ct2.XAxis.Deleted = false; c2ct2.XAxis.TickLabelPosition = eTickLabelPosition.High; ExcelChart chart3 = worksheet.Drawings.AddChart("chart", eChartType.LineMarkers); chart3.SetPosition(300, 1000); var s31 = chart3.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]); s31.Header = "Serie1"; var c3ct2 = chart3.PlotArea.ChartTypes.Add(eChartType.LineMarkers); var c32 = c3ct2.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["V19:V24"]); c3ct2.UseSecondaryAxis = true; c32.Header = "Serie2"; XmlNamespaceManager ns = new XmlNamespaceManager(new NameTable()); ns.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); var element = chart.ChartXml.SelectSingleNode("//c:plotVisOnly", ns); if (element != null) { element.ParentNode.RemoveChild(element); } }
private int appendnetworkCompanyToSheet(ExcelWorksheet worksheet, NetworkCompany networkCompany, int row) { string companyName; if (networkCompany.Company.Name.Length > 30) { companyName = networkCompany.Company.Name.Remove(27) + "..."; } else { companyName = networkCompany.Company.Name; } var headlinesColor = Color.MediumSeaGreen; var inprogress = 0; var finished = 0; var total = 0; var saved = 0; var denied = 0; worksheet.Cells[++row, 2].Value = "Kontaktinformationen"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row++, 2].Style.Fill.BackgroundColor.SetColor(headlinesColor); //worksheet.Row(row++).Height = 20; //worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; //worksheet.Cells[row, 6].Style.Numberformat.Format ="#0\\.00%"; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.Street; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.PostalCode + " " + networkCompany.Company.Address.City; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.Website; worksheet.Cells[++row, 2].Value = "Ansprechpartner"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 2].Style.Fill.BackgroundColor.SetColor(headlinesColor); foreach (var employee in networkCompany.Company.Employees) { row++; worksheet.Cells[row++, 2].Value = employee.FirstName + " " + employee.LastName; worksheet.Cells[row++, 2].Value = employee.Email; worksheet.Cells[row++, 2].Value = employee.PhoneNumber; } if (networkCompany.Measures.Any()) { foreach (var measure in networkCompany.Measures) { total = measure.EnergySavings.Aggregate(total, (current, saving) => (int) (current + saving.Value)); if (measure.State.Title == "abgeschlossen") { finished++; saved = measure.EnergySavings.Aggregate(saved, (current, saving) => (int) (current + saving.Value)); } if (measure.State.Title == "abgelehnt") { denied = measure.EnergySavings.Aggregate(denied, (current, saving) => (int) (current + saving.Value)); } if (measure.State.Title == "in Bearbeitung") { inprogress++; } } total -= denied; worksheet.Cells[++row, 2].Value = "Maßnahmen"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2, row, 4].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 2, row, 4].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[++row, 2].Value = "Maßnahmen gesamt"; worksheet.Cells[row++, 4].Value = networkCompany.Measures.Count; worksheet.Cells[row, 2].Value = "in Bearbeitung"; worksheet.Cells[row++, 4].Value = inprogress; worksheet.Cells[row, 2].Value = "abgeschlossene Maßnahmen"; worksheet.Cells[row++, 4].Value = finished; worksheet.Cells[row, 2].Value = "eingesparte Energie"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = saved; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row, 2].Value = "Potiential abgelehnter Maßnahmen"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = denied; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row, 2].Value = "gesamt Einsparungspotiential"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = total; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row - 1, 6].Value = "verbleibendes Potential"; worksheet.Cells[row - 1, 8].Value = "kWh"; worksheet.Cells[row - 1, 9].Formula = new ExcelCellAddress(row - 1, 4).Address + "-" + new ExcelCellAddress(row - 3, 4).Address; int chartrowStart; if ((row - 15) <= 0) { chartrowStart = 1; } else { chartrowStart = row - 15; } ExcelChart chartMeasures = worksheet.Drawings.AddChart("Maßnahmen", eChartType.Pie); chartMeasures.Title.Text = "Maßnahmenpotential"; chartMeasures.SetPosition(chartrowStart, 0, 4, 20); chartMeasures.SetSize(350, 280); var data1Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 1, 9).Address; var data2Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 2, 4).Address; var data3Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 3, 4).Address; var xAddress = "'" + companyName + "'!" + new ExcelCellAddress(row - 1, 6).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 2, 2).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 3, 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 1, 3).Address); var seriesMeasures = chartMeasures.Series.Add(data1Address + "," + data2Address + "," + data3Address, xAddress); seriesMeasures.HeaderAddress = legendAddress; chartMeasures.Style = eChartStyle.Style2; } { var effizienzstrom = new double[3000]; var effizienzerdgas = new double[3000]; var effizienzheizöl = new double[3000]; var effizienzfernwärme = new double[3000]; var effizienz = new double[3000]; var effiemissstrom = new double[3000]; var effiemisserdgas = new double[3000]; var effiemissheizöl = new double[3000]; var effiemissfernwärme = new double[3000]; var effiemiss = new double[3000]; var minYear = 3000; var maxYear = 0; var isEmpty = networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0; if (networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0) { return row; } foreach (var survey in networkCompany.Surveys) { var relevantYear = true; foreach (var reading in survey.Readings) { var value = reading.Value / 1000; effizienz[survey.Date.Year] += value; effiemiss[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; if (reading.EnergySource.Name == "electricity") { effizienzstrom[survey.Date.Year] += value; effiemissstrom[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "naturalGas") { effizienzerdgas[survey.Date.Year] += value; effiemisserdgas[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "fuelOil") { effizienzheizöl[survey.Date.Year] += value; effiemissheizöl[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "districtHeating") { effizienzfernwärme[survey.Date.Year] += value; effiemissfernwärme[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (minYear > survey.Date.Year) { minYear = survey.Date.Year; } if (maxYear < survey.Date.Year) { maxYear = survey.Date.Year; } relevantYear = effizienz[survey.Date.Year] != 0; } if ((!relevantYear) && (minYear == survey.Date.Year)) { minYear++; } if ((!relevantYear) && (maxYear == survey.Date.Year)) { maxYear--; } } row++; var co2Offset = maxYear - minYear + 6; var rowOffset = 0; for (int index = minYear; index <= maxYear; index++) { worksheet.Cells[row + 0, 2].Value = "Energieverbrauch"; worksheet.Cells[row + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + 0, 4 + (index - minYear)].Value = index; worksheet.Cells[row + 0, 4 + (index - minYear)].Style.Font.Bold = true; worksheet.Cells[row + 1, 2].Value = "Strom"; worksheet.Cells[row + 1, 3].Value = "MWh"; worksheet.Cells[row + 1, 4 + (index - minYear)].Value = effizienzstrom[index]; worksheet.Cells[row + 1, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 2, 2].Value = "Erdgas"; worksheet.Cells[row + 2, 3].Value = "MWh"; worksheet.Cells[row + 2, 4 + (index - minYear)].Value = effizienzerdgas[index]; worksheet.Cells[row + 2, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 3, 2].Value = "Heizöl"; worksheet.Cells[row + 3, 3].Value = "MWh"; worksheet.Cells[row + 3, 4 + (index - minYear)].Value = effizienzheizöl[index]; worksheet.Cells[row + 3, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 4, 2].Value = "Fernwärme"; worksheet.Cells[row + 4, 3].Value = "MWh"; worksheet.Cells[row + 4, 4 + (index - minYear)].Value = effizienzfernwärme[index]; worksheet.Cells[row + 4, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 0, (co2Offset + 2)].Value = "CO2-Emissionen"; worksheet.Cells[row + 0, (co2Offset + 2)].Style.Font.Bold = true; worksheet.Cells[row + 0, (co2Offset + 4) + (index - minYear)].Value = index; worksheet.Cells[row + 0, (co2Offset + 4) + (index - minYear)].Style.Font.Bold = true; worksheet.Cells[row + 1, (co2Offset + 2)].Value = "CO2 Strom"; worksheet.Cells[row + 1, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 1, (co2Offset + 4) + (index - minYear)].Value = effiemissstrom[index]; worksheet.Cells[row + 1, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 2, (co2Offset + 2)].Value = "CO2 Erdgas"; worksheet.Cells[row + 2, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 2, (co2Offset + 4) + (index - minYear)].Value = effiemisserdgas[index]; worksheet.Cells[row + 2, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 3, (co2Offset + 2)].Value = "CO2 Heizöl"; worksheet.Cells[row + 3, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 3, (co2Offset + 4) + (index - minYear)].Value = effiemissheizöl[index]; worksheet.Cells[row + 3, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 4, (co2Offset + 2)].Value = "CO2 Fernwärme"; worksheet.Cells[row + 4, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 4, (co2Offset + 4) + (index - minYear)].Value = effiemissfernwärme[index]; worksheet.Cells[row + 4, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; } worksheet.Cells[row + 0, 6 + maxYear - minYear].Value = "Summe"; worksheet.Cells[row + 0, 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row + 0, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 0, 2, row, 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 0, 2, row, 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + 1, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 1, 4, row + 1, 4 + maxYear - minYear).Address); worksheet.Cells[row + 1, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 1, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 2, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 2, 4, row + 2, 4 + maxYear - minYear).Address); worksheet.Cells[row + 2, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 2, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 3, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 3, 4, row + 3, 4 + maxYear - minYear).Address); worksheet.Cells[row + 3, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 3, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 4, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 4, 4, row + 4, 4 + maxYear - minYear).Address); worksheet.Cells[row + 4, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 4, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Value = "Summe"; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 0, co2Offset + 2, row, co2Offset + 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 0, co2Offset + 2, row, co2Offset + 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 1, co2Offset + 4, row + 1, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 2, co2Offset + 4, row + 2, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 3, co2Offset + 4, row + 3, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 4, co2Offset + 4, row + 4, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; row += 5; worksheet.Cells[row, 2].Value = "Summe"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 3].Value = "MWh"; worksheet.Cells[row, 3].Style.Font.Bold = true; worksheet.Cells[row, co2Offset + 2].Value = "Summe"; worksheet.Cells[row, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row, co2Offset + 3].Value = "t"; worksheet.Cells[row, co2Offset + 3].Style.Font.Bold = true; rowOffset = 18; worksheet.Cells[++row + rowOffset + 0, 2].Value = "Entwicklung des Energieverbrauches"; worksheet.Cells[row + rowOffset + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, 2, row + 18, 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, 2, row + 18, 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, 2].Value = "Strom"; worksheet.Cells[row + rowOffset + 2, 2].Value = "Erdgas"; worksheet.Cells[row + rowOffset + 3, 2].Value = "Heizöl"; worksheet.Cells[row + rowOffset + 4, 2].Value = "Fernwärme"; worksheet.Cells[row + rowOffset + 5, 2].Value = "Gesamt"; worksheet.Cells[row + rowOffset + 5, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Value = "Entwicklung der CO2-Emissionen"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, co2Offset + 2].Value = "CO2 Strom"; worksheet.Cells[row + rowOffset + 2, co2Offset + 2].Value = "CO2 Erdgas"; worksheet.Cells[row + rowOffset + 3, co2Offset + 2].Value = "CO2 Heizöl"; worksheet.Cells[row + rowOffset + 4, co2Offset + 2].Value = "CO2 Fernwärme"; worksheet.Cells[row + rowOffset + 5, co2Offset + 2].Value = "CO2 Gesamt"; worksheet.Cells[row + rowOffset + 5, co2Offset + 2].Style.Font.Bold = true; rowOffset = 42; worksheet.Cells[row + rowOffset + 0, 2].Value = "Anteile am Gesamtenergieverbrauch"; worksheet.Cells[row + rowOffset + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, 2, row + rowOffset + 0, 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, 2, row + rowOffset + 0, 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, 2].Value = "Stromanteil"; worksheet.Cells[row + rowOffset + 2, 2].Value = "Erdgasanteil"; worksheet.Cells[row + rowOffset + 3, 2].Value = "Heizölanteil"; worksheet.Cells[row + rowOffset + 4, 2].Value = "Fernwärmeanteil"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Value = "Anteile an den Gesamtemissionen"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, co2Offset + 2].Value = "CO2 Stromanteil"; worksheet.Cells[row + rowOffset + 2, co2Offset + 2].Value = "CO2 Erdgasanteil"; worksheet.Cells[row + rowOffset + 3, co2Offset + 2].Value = "CO2 Heizölanteil"; worksheet.Cells[row + rowOffset + 4, co2Offset + 2].Value = "CO2 Fernwärmeanteil"; for (int index = minYear; index <= maxYear; index++) { worksheet.Cells[row - 1, 4 + index - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row - 5, 4 + index - minYear, row - 2, 4 + index - minYear).Address); worksheet.Cells[row - 1, 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row - 1, 4 + index - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row - 5, co2Offset + 4 + index - minYear, row - 2, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; rowOffset = 18; worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 5, 4).Address, new ExcelCellAddress(row - 5, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 4, 4).Address, new ExcelCellAddress(row - 4, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 3, 4).Address, new ExcelCellAddress(row - 3, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 2, 4).Address, new ExcelCellAddress(row - 2, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 1, 4).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 5, co2Offset + 4).Address, new ExcelCellAddress(row - 5, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 4, co2Offset + 4).Address, new ExcelCellAddress(row - 4, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 3, co2Offset + 4).Address, new ExcelCellAddress(row - 3, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 2, co2Offset + 4).Address, new ExcelCellAddress(row - 2, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 1, co2Offset + 4).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 5, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; rowOffset = 42; worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 5, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 4, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 3, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 2, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 5, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 4, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 3, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 2, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; } ExcelChart chartOverview = worksheet.Drawings.AddChart("Entwicklung der Energieeffizienz", eChartType.Line); chartOverview.Title.Text = "Entwicklung der Energieeffizienz"; int chartrowStart; int chartrowOffset; int chartpixelOffset; int chartsizeDelta; if (co2Offset < 9) { chartrowOffset = co2Offset + 1; chartpixelOffset = 80; chartsizeDelta = 355 + (40 * (maxYear - minYear)); } else { chartrowOffset = co2Offset + 1; chartpixelOffset = 10; chartsizeDelta = 315 + (40 * (maxYear - minYear)); } if ((row - 27) <= 0) { chartrowStart = 1; } else { chartrowStart = row - 27; } chartOverview.SetPosition(chartrowStart, 0, chartrowOffset, chartpixelOffset); chartOverview.SetSize(chartsizeDelta, 380); { var data1Address = new ExcelAddress((row + 23), 4, (row + 23), (4 + maxYear - minYear)).Address; var data2Address = new ExcelAddress((row + 23), 4 + co2Offset, (row + 23), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var series1Overview = chartOverview.Series.Add(data1Address, xAddress); series1Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18), 2).Address); var series2Overview = chartOverview.Series.Add(data2Address, xAddress); series2Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18), co2Offset + 2).Address); } chartOverview.Style = eChartStyle.Style2; ExcelChart chartEnergieVerbrauch = worksheet.Drawings.AddChart("Energieverbrauch", eChartType.ColumnClustered); chartEnergieVerbrauch.Title.Text = "Energieverbrauch"; chartEnergieVerbrauch.SetPosition(row, 0, 1, 0); chartEnergieVerbrauch.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 5 + index), 4, (row - 5 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index), 2).Address); var seriesEnergieVerbrauch = chartEnergieVerbrauch.Series.Add(dataAddress, xAddress); seriesEnergieVerbrauch.HeaderAddress = legendAddress; } chartEnergieVerbrauch.Style = eChartStyle.Style2; chartEnergieVerbrauch.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; ExcelChart chartEmission = worksheet.Drawings.AddChart("CO2-Emissionen", eChartType.ColumnClustered); chartEmission.Title.Text = "CO2-Emissionen"; chartEmission.SetPosition(row, 0, 1 + co2Offset, 0); chartEmission.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 5 + index), 4 + co2Offset, (row - 5 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4 + co2Offset, row - 6, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index), 2 + co2Offset).Address); var seriesEmission = chartEmission.Series.Add(dataAddress, xAddress); seriesEmission.HeaderAddress = legendAddress; } chartEmission.Style = eChartStyle.Style2; chartEmission.YAxis.Format = "#,##0 [$t];[RED] -#,##0 [$t]"; ExcelChart chartEnergieEntwicklung = worksheet.Drawings.AddChart("Energieffizienzentwicklung", eChartType.Line); chartEnergieEntwicklung.Title.Text = "Energieentwicklung"; chartEnergieEntwicklung.SetPosition(row + 24, 0, 1, 0); chartEnergieEntwicklung.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row + 24 - 5 + index), 4, (row + 24 - 5 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index), 2).Address); var seriesEnergieEntwicklung = chartEnergieEntwicklung.Series.Add(dataAddress, xAddress); seriesEnergieEntwicklung.HeaderAddress = legendAddress; } chartEnergieEntwicklung.Style = eChartStyle.Style2; ExcelChart chartCO2entwicklung = worksheet.Drawings.AddChart("CO2-Emissionenentwicklung", eChartType.Line); chartCO2entwicklung.Title.Text = "CO2-Entwicklung"; chartCO2entwicklung.SetPosition(row + 24, 0, 1 + co2Offset, 0); chartCO2entwicklung.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row + 24 - 5 + index), 4 + co2Offset, (row + 24 - 5 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4 + co2Offset, row - 6, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index), 2 + co2Offset).Address); var seriesCO2entwicklung = chartCO2entwicklung.Series.Add(dataAddress, xAddress); seriesCO2entwicklung.HeaderAddress = legendAddress; } chartCO2entwicklung.Style = eChartStyle.Style2; row += 47; ExcelChart chartAnteilGesamt = worksheet.Drawings.AddChart("Anteile am Gesamtenergieverbrauch", eChartType.ColumnStacked100); chartAnteilGesamt.Title.Text = "Anteile am Gesamtenergieverbrauch"; //chartAnteilGesamt.Legend.Position = eLegendPosition.Right; //chartAnteilGesamt.Legend.Add(); chartAnteilGesamt.SetPosition(row, 0, 1, 0); chartAnteilGesamt.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 4 + index), 4, (row - 4 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 53, 4, row - 53, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index), 2).Address); var seriesAnteilGesamt = chartAnteilGesamt.Series.Add(dataAddress, xAddress); seriesAnteilGesamt.HeaderAddress = legendAddress; } //chart.DataLabel.ShowCategory = true; //chart.DataLabel.ShowPercent = true; //chart.DataLabel.ShowLeaderLines = true; //chartAnteilGesamt.Legend.Remove(); chartAnteilGesamt.Style = eChartStyle.Style2; //chartAnteilGesamt.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; ExcelChart chartAnteilGesEmis = worksheet.Drawings.AddChart("Anteile an den CO2-Emissionen", eChartType.ColumnStacked100); chartAnteilGesEmis.Title.Text = "Anteile an den CO2-Emissionen"; chartAnteilGesEmis.SetPosition(row, 0, 1 + co2Offset, 0); chartAnteilGesEmis.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 4 + index), 4 + co2Offset, (row - 4 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 53, 4 + co2Offset, row - 53, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index), 2 + co2Offset).Address); var seriesAnteilGesEmis = chartAnteilGesEmis.Series.Add(dataAddress, xAddress); seriesAnteilGesEmis.HeaderAddress = legendAddress; } chartAnteilGesEmis.Style = eChartStyle.Style2; var pieChartsEnergyperYear = new ExcelChart[maxYear - minYear + 1]; var pieChartsCO2perYear = new ExcelChart[maxYear - minYear + 1]; for (int index = 0; index <= (maxYear - minYear); index++) { { pieChartsEnergyperYear[index] = worksheet.Drawings.AddChart(("Energieverbrauchsanteile in " + (minYear + index)), eChartType.Pie); pieChartsEnergyperYear[index].Title.Text = "Energieverbrauchsanteile in " + (minYear + index); pieChartsEnergyperYear[index].SetPosition(row + 17 + (17 * index), 0, 1, 0); pieChartsEnergyperYear[index].SetSize(305 + (40 * (maxYear - minYear)), 315); var dataAddress = new ExcelAddress(row - 4, 4 + index, row - 1, 4 + index).Address; var xAddress = new ExcelAddress(row - 4, 2, row - 1, 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5, 2).Address); var seriespieChartEnergy = pieChartsEnergyperYear[index].Series.Add(dataAddress, xAddress); seriespieChartEnergy.HeaderAddress = legendAddress; } { pieChartsCO2perYear[index] = worksheet.Drawings.AddChart(("CO2-Emissionsanteile in " + (minYear + index)), eChartType.Pie); pieChartsCO2perYear[index].Title.Text = "CO2-Emissionsanteile in " + (minYear + index); pieChartsCO2perYear[index].SetPosition(row + 17 + (17 * index), 0, co2Offset + 1, 0); pieChartsCO2perYear[index].SetSize(305 + (40 * (maxYear - minYear)), 315); var dataAddress = new ExcelAddress(row - 4, co2Offset + 4 + index, row - 1, co2Offset + 4 + index).Address; var xAddress = new ExcelAddress(row - 4, co2Offset + 2, row - 1, co2Offset + 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5, co2Offset + 2).Address); var seriespieChartCO2 = pieChartsCO2perYear[index].Series.Add(dataAddress, xAddress); seriespieChartCO2.HeaderAddress = legendAddress; } } worksheet.Column(1). Width = 8.43; worksheet.Column(2). Width = 33.57; worksheet.Column(3). Width = 7.43; for (int index = 0; index <= (maxYear - minYear); index++) { worksheet.Column(4 + index). Width = 10; } worksheet.Column(5 + maxYear - minYear). Width = 0.83; worksheet.Column(6 + maxYear - minYear). Width = 9.29; worksheet.Column(co2Offset + 1). Width = 8.43; worksheet.Column(co2Offset + 2). Width = 16.43; worksheet.Column(co2Offset + 3). Width = 6.43; for (int index = 0; index <= (maxYear - minYear); index++) { worksheet.Column(co2Offset + 4 + index). Width = 10; } worksheet.Column(co2Offset + 5 + maxYear - minYear). Width = 0.83; worksheet.Column(co2Offset + 6 + maxYear - minYear). Width = 10; row += 17; return (row); } }
private void CopyDrawing(ExcelWorksheet Copy, ExcelWorksheet workSheet /*, PackageRelationship r*/) { //Check if the worksheet has drawings //if(_xlPackage.Package.PartExists(r.TargetUri)) //{ //First copy the drawing XML string xml = Copy.Drawings.DrawingXml.OuterXml; var uriDraw = new Uri(string.Format("/xl/drawings/drawing{0}.xml", workSheet.SheetID), UriKind.Relative); var part = _pck.Package.CreatePart(uriDraw, "application/vnd.openxmlformats-officedocument.drawing+xml", _pck.Compression); StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(xml); streamDrawing.Close(); XmlDocument drawXml = new XmlDocument(); drawXml.LoadXml(xml); //Add the relationship ID to the worksheet xml. PackageRelationship drawRelation = workSheet.Part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri, uriDraw), TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing"); XmlElement e = workSheet.WorksheetXml.SelectSingleNode("//d:drawing", _namespaceManager) as XmlElement; e.SetAttribute("id", ExcelPackage.schemaRelationships, drawRelation.Id); foreach (ExcelDrawing draw in Copy.Drawings) { if (draw is ExcelChart) { ExcelChart chart = draw as ExcelChart; xml = chart.ChartXml.InnerXml; var UriChart = XmlHelper.GetNewUri(_pck.Package, "/xl/charts/chart{0}.xml"); var chartPart = _pck.Package.CreatePart(UriChart, "application/vnd.openxmlformats-officedocument.drawingml.chart+xml", _pck.Compression); StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write)); streamChart.Write(xml); streamChart.Close(); //Now create the new relationship to the copied chart xml var prevRelID = draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value; var rel = part.CreateRelationship(PackUriHelper.GetRelativeUri(uriDraw, UriChart), TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart"); XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } else if (draw is ExcelPicture) { ExcelPicture pic = draw as ExcelPicture; var uri = pic.UriPic; if (!workSheet.Workbook._package.Package.PartExists(uri)) { var picPart = workSheet.Workbook._package.Package.CreatePart(uri, pic.ContentType, CompressionOption.NotCompressed); pic.Image.Save(picPart.GetStream(FileMode.Create, FileAccess.Write), pic.ImageFormat); } var prevRelID = draw.TopNode.SelectSingleNode("xdr:pic/xdr:blipFill/a:blip/@r:embed", Copy.Drawings.NameSpaceManager).Value; var rel = part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri, uri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/image"); XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//xdr:pic/xdr:blipFill/a:blip/@r:embed[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } } //rewrite the drawing xml with the new relID's streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(drawXml.OuterXml); streamDrawing.Close(); //} }
private static void GenExcel(object data, FileInfo filename) { // Вызов процедуры var tempdate = ""; SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { using (var exclelFile = new ExcelPackage(filename)) { var row = 1; var col = 1; // Добавим новые листы // var gist = exclelFile.Workbook.Worksheets.Add("Графики"); // var ws = exclelFile.Workbook.Worksheets.Add("Данные"); ExcelWorksheet gist = exclelFile.Workbook.Worksheets["Графики"]; ExcelWorksheet ws = exclelFile.Workbook.Worksheets["Данные"]; // Устанавливаем крайнии координаты гистограмме var maxPosRow = 0; //Ищем крайнюю гистограмму на листе for (int i = 0; i < gist.Drawings.Count; i++) { if (gist.Drawings[i].To.Row > maxPosRow) { maxPosRow = gist.Drawings[i].To.Row + 2; } ; } // 20px int x_diag = maxPosRow * 20, y_diag = 0; // Ищем конец таблицы row = ws.Dimension.Rows + 1; // Создать объект Command для вызова процедуры Get_Employee_Info. SqlCommand cmd = new SqlCommand("reports.machine_performance", conn); // Вид Command является StoredProcedure cmd.CommandType = CommandType.StoredProcedure; /* Выборка за период*/ var startOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); //var endOfPreviousMonth = startOfMonth.AddDays(-1); var startOfPreviousMonth = new DateTime(startOfMonth.AddDays(-1).Year, startOfMonth.AddDays(-1).Month, 1); var endOfPreviousPreviousMonth = startOfMonth.AddMonths(0).AddDays(-1); //cmd.Parameters.Add("@date_start", SqlDbType.Date).Value = startOfPreviousMonth; //cmd.Parameters.Add("@date_end", SqlDbType.Date).Value = endOfPreviousPreviousMonth; cmd.Parameters.Add("@date_start", SqlDbType.Date).Value = "01.03.2019"; cmd.Parameters.Add("@date_end", SqlDbType.Date).Value = "31.03.2019"; cmd.Parameters.Add("@machine_list", SqlDbType.Xml).Value = data; // Выполнить процедуру. // получаем список колонок таблицы using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) // Пробегаемся по всем нашим записям { // reader.Read(); var tableSchema = reader.GetSchemaTable(); if (!reader[0].Equals(tempdate)) // Если не таже дата то создаем новый заголовок { // Настраиваем стиль шапки даты using (var range = ws.Cells[row, col, row, tableSchema.Rows.Count - 1]) { range.Merge = true; //range.Style.Font.Bold = true; range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //range.Style.Fill.PatternType = ExcelFillStyle.Solid; //range.Style.Fill.BackgroundColor.SetColor(Color.Blue); //range.Style.Font.Color.SetColor(Color.White); } ws.Cells[row, col].Value = reader[0]; // Создаем шапку с датой row++; foreach (DataRow row_name in tableSchema.Rows) { if (row_name["ColumnName"].Equals("Статус оборудования")) { //задаем стиль колонке ws.Cells[row, col].Style.Font.Bold = true; } if (!row_name["ColumnName"].Equals("Период")) { ws.Cells[row, col++].Value = row_name["ColumnName"]; } } ExcelChart chart = (ExcelChart)gist.Drawings[0]; ExcelChart cc = gist.Drawings.AddChart(Convert.ToString(reader[0]), eChartType.ColumnStacked); // Копируем предыдущую версию стилей var xml = XDocument.Parse(chart.ChartXml.InnerXml); XNamespace nsC = "http://schemas.openxmlformats.org/drawingml/2006/chart"; XNamespace nsA = "http://schemas.openxmlformats.org/drawingml/2006/main"; // Загружаем данные в гистограмму абсолютный путь var fs = xml.Descendants(nsC + "f"); foreach (var f in fs) { f.Value = ws.Cells[f.Value].Offset(row - 2, 0).FullAddressAbsolute; } // Загружаем стиль из xml. cc.ChartXml.InnerXml = xml.ToString(); // Создаем гистограмму // var chart = (ExcelBarChart)gist.Drawings.AddChart(Convert.ToString(reader[0]), eChartType.ColumnStacked); cc.SetSize(1169, 479); cc.SetPosition(x_diag, y_diag); cc.Title.Text = string.Format("{0}", reader[0]); //string serieAddress, xSerieAddress; //serieAddress = ExcelCellBase.GetFullAddress("Данные", ExcelCellBase.GetAddress(row + 1, 2, row + 1, tableSchema.Rows.Count - 1)); //// chart.Legend.Border.Fill.Color = Color.Yellow; // Координаты данных //var headerAddr = ws.Cells[row + 1, 1]; ////headerAddr.Style.Font.Bold = true; //xSerieAddress = ExcelCellBase.GetFullAddress("Данные", ExcelCellBase.GetAddress(row, 2, row, tableSchema.Rows.Count - 1)); //Координаты легедны //cc.Series.Add(serieAddress, xSerieAddress).HeaderAddress = headerAddr; ////chart.Legend.Border.Fill.Color = Color.Yellow; //headerAddr = ws.Cells[row + 2, 1]; //serieAddress = ExcelCellBase.GetFullAddress("Данные", ExcelCellBase.GetAddress(row + 2, 2, row + 2, tableSchema.Rows.Count - 1)); // Координаты данных //cc.Series.Add(serieAddress, xSerieAddress).HeaderAddress = headerAddr; //headerAddr = ws.Cells[row + 3, 1]; //serieAddress = ExcelCellBase.GetFullAddress("Данные", ExcelCellBase.GetAddress(row + 3, 2, row + 3, tableSchema.Rows.Count - 1)); // Координаты данных //cc.Series.Add(serieAddress, xSerieAddress).HeaderAddress = headerAddr; ////chart.Legend.Border.Fill.Color = Color.Yellow; // снова получаем крайнии координаты for (int i = 0; i < gist.Drawings.Count; i++) { if (gist.Drawings[i].To.Row > maxPosRow) { maxPosRow = gist.Drawings[i].To.Row + 2; } ; } x_diag = maxPosRow * 20; row++; col = 1; } ws.Cells[row, col++].Value = Convert.ToString(reader[1]); if (Convert.ToString(reader[1]) == "Статус оборудования") { ws.Cells[row, col - 1].Style.Font.Bold = true; } if (Convert.ToString(reader[1]) == "% работы станка") { ws.Cells[row, col - 1].Style.Font.Bold = true; // Вычисляем процент for (var i = 2; i <= tableSchema.Rows.Count - 1; i++) { // Задаем стиль ws.Cells[row, col].Style.Font.Bold = true; ws.Cells[row, col].Style.Font.Color.SetColor(Color.Red); ws.Cells[row, col].Style.Numberformat.Format = "0%"; ws.Cells[row, col++].Formula = string.Format("=(({0}+{1})/{2})", ws.Cells[row - 4, col - 1].Address, ws.Cells[row - 3, col - 1].Address, ws.Cells[row - 1, col - 1].Address); } } else { // Заполняем таблицу значениями for (var i = 2; i <= tableSchema.Rows.Count - 1; i++) { // Проверка на пустные значания if (!DBNull.Value.Equals(reader[i])) { ws.Cells[row, col++].Value = Convert.ToInt32(reader[i]); } else { ws.Cells[row, col++].Value = 0; } } } row++; col = 1; tempdate = Convert.ToString(reader[0]); } } } // добавим всем ячейкам рамку using (var cells = ws.Cells[ws.Dimension.Address]) { cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; cells.AutoFitColumns(); } // сохраняем в файл //exclelFile.Save(); var bin = exclelFile.GetAsByteArray(); File.WriteAllBytes(Convert.ToString(filename), bin); } } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } }