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 AddTestSerie(ExcelWorksheet ws, ExcelChart chrt)
{
AddTestData(ws);
chrt.Series.Add("'" + ws.Name + "'!V19:V24", "'" + ws.Name + "'!U19:U24");
}
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();
}
}
