public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_MOBILE) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Detected MOBILE Entities Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Detected MOBILE Entities Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Detected MOBILE Entities Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_MOBILE_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
#endregion
loggerConsole.Info("Fill Detected MOBILE Entities Report File");
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications - All
loggerConsole.Info("List of Applications - All");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_MOBILE_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILEApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Network Requests
loggerConsole.Info("List of Network Requests");
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILENetworkRequestsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Network Request Business Transactions
loggerConsole.Info("List of Network Request Business Transactions");
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MOBILENetworkRequestsBusinessTransactionsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Detected MOBILE Entities Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications - All
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Description"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_MOBILE_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_MOBILE);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNetworkRequests"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNetworkRequests"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNoActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNoActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Network Requests
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_NETWORK_REQUESTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["RequestName"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestNameInternal"].Position + 1).Width = 20;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_NETWORK_REQUESTS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "RequestName");
addRowFieldToPivot(pivot, "RequestNameInternal");
addDataFieldToPivot(pivot, "RequestID", DataFieldFunctions.Count, "NumReqs");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "TimeTotal", DataFieldFunctions.Sum, "Time");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_NETWORK_REQUESTS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
}
#endregion
#region Network Request Business Transactions
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["RequestName"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestNameInternal"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTType"].Position + 1).Width = 15;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "RequestName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addDataFieldToPivot(pivot, "BTID", DataFieldFunctions.Count, "NumBTs");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_NETWORK_REQUESTS_BUSINESS_TRANSACTIONS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.MOBILEEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Events and Health Rule Violations Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Events and Health Rule Violations Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Events and Health Rule Violations Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 7, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 4, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 3, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Events and Health Rule Violations Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllersReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Events
loggerConsole.Info("List of Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.EventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Health Rule Violation Events
loggerConsole.Info("List of Health Rule Violation Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.HealthRuleViolationsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Audit Events
loggerConsole.Info("List of Audit Events");
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.AuditEventsReportFilePath(), 0, sheet, REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Events and Health Rule Violations Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_CONTROLLERS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["UserName"].Position + 1).Width = 25;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEvents"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEvents"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsInfo"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsInfo"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolations"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolations"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsCritical"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsCritical"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["Summary"].Position + 1).Width = 35;
sheet.Column(table.Columns["Type"].Position + 1).Width = 20;
sheet.Column(table.Columns["SubType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "SubType");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "NodeName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT + 3, 1], range, REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ApplicationName");
addFilterFieldToPivot(pivot, "TierName");
addFilterFieldToPivot(pivot, "BTName");
addFilterFieldToPivot(pivot, "TriggeredEntityName");
addFilterFieldToPivot(pivot, "ApplicationName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "SubType", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_EVENTS_TIMELINE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Health Rule Violation Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_HEALTH_RULE_VIOLATION_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["From"].Position + 1).Width = 25;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 25;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["HealthRuleName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_HEALTH_RULE_VIOLATIONS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Status");
addRowFieldToPivot(pivot, "HealthRuleName");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
}
#endregion
#region Audit Events
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_DETECTED_EVENTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_AUDIT_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["Username"].Position + 1).Width = 20;
sheet.Column(table.Columns["LoginType"].Position + 1).Width = 15;
sheet.Column(table.Columns["Action"].Position + 1).Width = 15;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 30;
sheet.Column(table.Columns["EntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_SHEET_AUDIT_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "Action");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "LoginType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "UserName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TYPE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 30;
sheet = excelReport.Workbook.Worksheets[REPORT_DETECTED_EVENTS_AUDIT_SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_DETECTED_EVENTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_DETECTED_EVENTS_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "UserName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Action", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(REPORT_DETECTED_EVENTS_PIVOT_AUDIT_EVENTS_TIMELINE_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[REPORT_SHEET_TOC];
sheet.Cells[1, 1].Value = "Sheet Name";
sheet.Cells[1, 2].Value = "# Entities";
sheet.Cells[1, 3].Value = "Link";
int rowNum = 1;
foreach (ExcelWorksheet s in excelReport.Workbook.Worksheets)
{
rowNum++;
sheet.Cells[rowNum, 1].Value = s.Name;
sheet.Cells[rowNum, 3].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", s.Name);
sheet.Cells[rowNum, 3].StyleName = "HyperLinkStyle";
if (s.Tables.Count > 0)
{
table = s.Tables[0];
sheet.Cells[rowNum, 2].Value = table.Address.Rows - 1;
}
}
range = sheet.Cells[1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_DETECTED_EVENTS_TABLE_TOC);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Sheet Name"].Position + 1).Width = 25;
sheet.Column(table.Columns["# Entities"].Position + 1).Width = 25;
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.EventsAndHealthRuleViolationsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
///<summary>
/// 生成图表
/// </summary>
public static void CreateCharset()
{
FileInfo newFile = new FileInfo(@"e:\test.xlsx");
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(@"e:\test.xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
worksheet.Cells.Style.WrapText = true;
worksheet.View.ShowGridLines = false;//去掉sheet的网格线
worksheet.Cells[1, 1].Value = "名称";
worksheet.Cells[1, 2].Value = "价格";
worksheet.Cells[1, 3].Value = "销量";
worksheet.Cells[2, 1].Value = "大米";
worksheet.Cells[2, 2].Value = 56;
worksheet.Cells[2, 3].Value = 100;
worksheet.Cells[3, 1].Value = "玉米";
worksheet.Cells[3, 2].Value = 45;
worksheet.Cells[3, 3].Value = 150;
worksheet.Cells[4, 1].Value = "小米";
worksheet.Cells[4, 2].Value = 38;
worksheet.Cells[4, 3].Value = 130;
worksheet.Cells[5, 1].Value = "糯米";
worksheet.Cells[5, 2].Value = 22;
worksheet.Cells[5, 3].Value = 200;
using (ExcelRange range = worksheet.Cells[1, 1, 5, 3])
{
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
using (ExcelRange range = worksheet.Cells[1, 1, 1, 3])
{
range.Style.Font.Bold = true;
range.Style.Font.Color.SetColor(Color.White);
range.Style.Font.Name = "微软雅黑";
range.Style.Font.Size = 12;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
}
worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]);
serie.HeaderAddress = worksheet.Cells[1, 3];
chart.SetPosition(150, 10);
chart.SetSize(500, 300);
chart.Title.Text = "销量走势";
chart.Title.Font.Color = Color.FromArgb(89, 89, 89);
chart.Title.Font.Size = 15;
chart.Title.Font.Bold = true;
chart.Style = eChartStyle.Style15;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);
package.Save();
}
}
public void exportExcelFile()
{
//double timeScale = getTimeScaleFromString(scale);
//bool summaryPage = summaryPage;
string combindReportName = exportPath.Replace("\r", "");
Sample exemplarSample = groups[0].samples[0];
Dataset strainDataset = exemplarSample.strain.dataSetInfo;
Dataset stressDataset = exemplarSample.stress.dataSetInfo;
Dataset strainRateDataset = exemplarSample.strainRate.dataSetInfo;
Dataset timeDataset = exemplarSample.time.dataSetInfo;
Dataset frontFaceForceDataset = null;
Dataset backFaceForceDataset = null;
bool hasFaceForces = false;
if (exemplarSample.hasFaceForce())
{
hasFaceForces = true;
frontFaceForceDataset = exemplarSample.frontFaceForce.dataSetInfo;
backFaceForceDataset = exemplarSample.backFaceForce.dataSetInfo;
}
FileInfo newFile = new FileInfo(combindReportName);
if (newFile.Exists)
{
try
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(combindReportName);
}
catch
{
Console.WriteLine("Close File to Save Over");
//MessageBox.Show("Close File to save over");
}
}
ExcelPackage combinedReport = new ExcelPackage(newFile);
String strainHeaderUnits = (strainDataset.dataType == "" ? "" : strainDataset.dataType + " ") + strainDataset.dataName + " " + strainDataset.dataUnits;
String stressHeaderUnits = (stressDataset.dataType == "" ? "" : stressDataset.dataType + " ") + stressDataset.dataName + " " + stressDataset.dataUnits;
String strainRateHeaderUnits = (strainRateDataset.dataType == "" ? "" : strainRateDataset.dataType + " ") + strainRateDataset.dataName + " " + strainRateDataset.dataUnits;
String timeHeaderUnits = timeDataset.dataName + " " + timeDataset.dataUnits;//"Time (" + scale + ")";
String frontFaceForceUnits = "";
String backFaceForceUnits = "";
if (hasFaceForces)
{
frontFaceForceUnits = (frontFaceForceDataset.dataType == "" ? "" : frontFaceForceDataset.dataType + " ") + frontFaceForceDataset.dataName + " " + frontFaceForceDataset.dataUnits;
backFaceForceUnits = (backFaceForceDataset.dataType == "" ? "" : backFaceForceDataset.dataType + " ") + backFaceForceDataset.dataName + " " + backFaceForceDataset.dataUnits;
}
String strainTitle = strainDataset.dataName;
String stressTitle = stressDataset.dataName;
String strainRateTitle = strainRateDataset.dataName;
String timeTitle = timeDataset.dataName;
String frontFaceForceTitle = "";
String backFaceForceTitle = "";
if (hasFaceForces)
{
frontFaceForceTitle = frontFaceForceDataset.dataName;
backFaceForceTitle = backFaceForceDataset.dataName;
}
var SummarySheet = combinedReport.Workbook.Worksheets.Add("Summary");
var SumstrainRateChart = SummarySheet.Drawings.AddChart(strainRateHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstrainRateChart.SetPosition(400, 0);
SumstrainRateChart.SetSize(600, 400);
SumstrainRateChart.XAxis.Title.Text = timeHeaderUnits;
SumstrainRateChart.YAxis.Title.Text = strainRateHeaderUnits;
SumstrainRateChart.Title.Text = strainRateTitle + " vs " + timeTitle;
SumstrainRateChart.YAxis.MinValue = 0;
SumstrainRateChart.XAxis.MinValue = 0;
var SumstrainChart = SummarySheet.Drawings.AddChart(strainHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstrainChart.SetPosition(0, 0);
SumstrainChart.SetSize(600, 400);
SumstrainChart.XAxis.Title.Text = timeHeaderUnits;
SumstrainChart.YAxis.Title.Text = strainHeaderUnits;
SumstrainChart.Title.Text = strainTitle + " vs " + timeTitle;
SumstrainChart.YAxis.MinValue = 0;
SumstrainChart.XAxis.MinValue = 0;
var SumstressChart = SummarySheet.Drawings.AddChart(stressHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstressChart.SetPosition(0, 600);
SumstressChart.SetSize(600, 400);
SumstressChart.XAxis.Title.Text = timeHeaderUnits;
SumstressChart.YAxis.Title.Text = stressHeaderUnits;
SumstressChart.Title.Text = stressTitle + " vs " + timeTitle;
SumstressChart.YAxis.MinValue = 0;
SumstressChart.XAxis.MinValue = 0;
var SumstressStrainChart = SummarySheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstressStrainChart.SetPosition(400, 600);
SumstressStrainChart.SetSize(600, 400);
SumstressStrainChart.XAxis.Title.Text = strainHeaderUnits;
SumstressStrainChart.YAxis.Title.Text = stressHeaderUnits;
SumstressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
SumstressStrainChart.YAxis.MinValue = 0;
SumstressStrainChart.XAxis.MinValue = 0;
int idx = 0;
foreach (Group group in groups)
{
var Sheet = combinedReport.Workbook.Worksheets.Add(group.name);
//FileInfo placeHolderFile = new FileInfo("tmp");
//ExcelPackage placeHolderExcel = new ExcelPackage(placeHolderFile);
//var fakeSheet = placeHolderExcel.Workbook.Worksheets.Add("placeholder");
int columnCountName = 21;
int columnCountLabels = 21;
int spaceName = 5;
foreach (Sample sample in group.samples)
{
double[] timeData = sample.time.data;
double[] stressData = sample.stress.data;
double[] strainData = sample.strain.data;
double[] strainRateData = sample.strainRate.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "1"].Value = sample.name;
columnCountName += spaceName;
//time
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = timeHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = timeData[i];
}
columnCountLabels++;
//Strain Rate
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainRateHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainRateData[i];
}
columnCountLabels++;
//Strain
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainData[i];
}
columnCountLabels++;
//Stress
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = stressHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = stressData[i];
}
columnCountLabels++;
if (sample.hasFaceForce())
{
double[] frontFaceData = sample.frontFaceForce.data;
double[] backFaceData = sample.backFaceForce.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = frontFaceForceUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = frontFaceData[i];
}
columnCountLabels++;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = backFaceForceUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = backFaceData[i];
}
columnCountLabels++;
columnCountLabels++;
}
else
{
columnCountLabels += 3;
}
}
var strainChart = Sheet.Drawings.AddChart(strainHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
strainChart.SetPosition(0, 0);
strainChart.SetSize(600, 400);
strainChart.XAxis.Title.Text = timeHeaderUnits;
strainChart.YAxis.Title.Text = strainHeaderUnits;
strainChart.Title.Text = strainTitle + " vs " + timeTitle;
strainChart.YAxis.MinValue = 0;
strainChart.XAxis.MinValue = 0;
var strainRateChart = Sheet.Drawings.AddChart(strainRateHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
strainRateChart.SetPosition(400, 0);
strainRateChart.SetSize(600, 400);
strainRateChart.XAxis.Title.Text = timeHeaderUnits;
strainRateChart.YAxis.Title.Text = strainRateHeaderUnits;
strainRateChart.Title.Text = strainRateTitle + " vs " + timeTitle;
strainRateChart.YAxis.MinValue = 0;
strainRateChart.XAxis.MinValue = 0;
var stressChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressChart.SetPosition(0, 600);
stressChart.SetSize(600, 400);
stressChart.XAxis.Title.Text = timeHeaderUnits;
stressChart.YAxis.Title.Text = stressHeaderUnits;
stressChart.Title.Text = stressTitle + " vs " + timeTitle;
stressChart.YAxis.MinValue = 0;
stressChart.XAxis.MinValue = 0;
var stressStrainChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressStrainChart.SetPosition(400, 600);
stressStrainChart.SetSize(600, 400);
stressStrainChart.XAxis.Title.Text = strainHeaderUnits;
stressStrainChart.YAxis.Title.Text = stressHeaderUnits;
stressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
stressStrainChart.YAxis.MinValue = 0;
stressStrainChart.XAxis.MinValue = 0;
ExcelChart frontFaceForceChart = null;
ExcelChart backFaceForceChart = null;
if (hasFaceForces)
{
frontFaceForceChart = Sheet.Drawings.AddChart("front " + frontFaceForceUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
backFaceForceChart = Sheet.Drawings.AddChart("back " + backFaceForceUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
frontFaceForceChart.SetPosition(800, 0);
frontFaceForceChart.SetSize(600, 400);
frontFaceForceChart.XAxis.Title.Text = timeHeaderUnits;
frontFaceForceChart.YAxis.Title.Text = frontFaceForceUnits;
frontFaceForceChart.Title.Text = frontFaceForceTitle + " vs " + timeTitle;
frontFaceForceChart.YAxis.MinValue = 0;
frontFaceForceChart.XAxis.MinValue = 0;
backFaceForceChart.SetPosition(800, 600);
backFaceForceChart.SetSize(600, 400);
backFaceForceChart.XAxis.Title.Text = timeHeaderUnits;
backFaceForceChart.YAxis.Title.Text = backFaceForceUnits;
backFaceForceChart.Title.Text = backFaceForceTitle + " vs " + timeTitle;
backFaceForceChart.YAxis.MinValue = 0;
backFaceForceChart.XAxis.MinValue = 0;
}
int newColumnHunter = 21;
int trialnumber = 1;
bool trialAddedToSummary = false;
foreach (Sample sample in group.samples)
{
int endIndex = sample.time.data.Length - 1;
var timeExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter) + "3:" + GetExcelColumnName(newColumnHunter) + (endIndex + 2).ToString()];
var StrainRateExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 1) + "3:" + GetExcelColumnName(newColumnHunter + 1) + (endIndex + 2).ToString()];
var strainExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 2) + "3:" + GetExcelColumnName(newColumnHunter + 2) + (endIndex + 2).ToString()];
var stressExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 3) + "3:" + GetExcelColumnName(newColumnHunter + 3) + (endIndex + 2).ToString()];
var forceIncidentExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 4) + "3:" + GetExcelColumnName(newColumnHunter + 4) + (endIndex + 2).ToString()];
var forceTransmissionExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 5) + "3:" + GetExcelColumnName(newColumnHunter + 5) + (endIndex + 2).ToString()];
newColumnHunter += 7;
var strainRateChartSeries = strainRateChart.Series.Add(StrainRateExcel, timeExcel);
var strainChartSeries = strainChart.Series.Add(strainExcel, timeExcel);
var stressChartSeries = stressChart.Series.Add(stressExcel, timeExcel);
var stressStrainSeries = stressStrainChart.Series.Add(stressExcel, strainExcel);
//initialize the face force charts
ExcelChartSerie forceIncidentSeries = null; //always_fake.Series.Add(stressExcel, strainExcel);
ExcelChartSerie forceTransmissionSeries = null; //always_fake.Series.Add(stressExcel, strainExcel);
if (sample.hasFaceForce())
{
forceIncidentSeries = frontFaceForceChart.Series.Add(forceIncidentExcel, timeExcel);
forceTransmissionSeries = backFaceForceChart.Series.Add(forceTransmissionExcel, timeExcel);
}
if ((oneTrialOnSummaryPerGroup && !trialAddedToSummary) || !oneTrialOnSummaryPerGroup)
{
var sumstrainSeries = SumstrainChart.Series.Add(strainExcel, timeExcel);
var sumStressSeries = SumstressChart.Series.Add(stressExcel, timeExcel);
var sumStrainRateSeries = SumstrainRateChart.Series.Add(StrainRateExcel, timeExcel);
var sumStressStrainSeries = SumstressStrainChart.Series.Add(stressExcel, strainExcel);
sumstrainSeries.Header = group.name;
sumStressSeries.Header = group.name;
sumStrainRateSeries.Header = group.name;
sumStressStrainSeries.Header = group.name;
// int sumColorSpot = (idx) % summaryColors.Length;
sumstrainSeries.LineColor = group.color.TrimStart('#').Substring(0, 6); //summaryColors[sumColorSpot];
sumStressSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
sumStrainRateSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
sumStressStrainSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
}
strainRateChartSeries.Header = sample.name;
strainChartSeries.Header = sample.name;
stressChartSeries.Header = sample.name;
stressStrainSeries.Header = sample.name;
int colorSpot = (trialnumber - 1) % trialColors.Length;
int transColor = (trialnumber) % trialColors.Length;
strainRateChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
strainChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
stressChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
stressStrainSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
if (sample.hasFaceForce())
{
forceIncidentSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
forceTransmissionSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
forceIncidentSeries.Header = sample.name;
forceTransmissionSeries.Header = sample.name;
}
trialnumber++;
trialAddedToSummary = true;
}
idx++;
}
if (this.referenceSamples.Count > 0)
{
String SHEET_NAME = "Reference Samples";
var Sheet = combinedReport.Workbook.Worksheets.Add(SHEET_NAME);
int columnCountName = 11;
int columnCountLabels = 11;
int spaceName = 5;
foreach (ReferenceSample sample in this.referenceSamples)
{
double[] stressData = sample.stressData.data;
double[] strainData = sample.strainData.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "1"].Value = sample.name;
columnCountName += spaceName;
//Strain
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainHeaderUnits;
for (int i = 0; i < strainData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainData[i];
}
columnCountLabels++;
//Stress
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = stressHeaderUnits;
for (int i = 0; i < stressData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = stressData[i];
}
columnCountLabels++;
columnCountLabels += 1;
}
var stressStrainChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressStrainChart.SetPosition(0, 0);
stressStrainChart.SetSize(600, 400);
stressStrainChart.XAxis.Title.Text = strainHeaderUnits;
stressStrainChart.YAxis.Title.Text = stressHeaderUnits;
stressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
stressStrainChart.YAxis.MinValue = 0;
stressStrainChart.XAxis.MinValue = 0;
int newColumnHunter = 10;
int trialnumber = 1;
foreach (ReferenceSample sample in this.referenceSamples)
{
int endIndex = sample.strainData.data.Length - 1;
var strainExcel = combinedReport.Workbook.Worksheets[SHEET_NAME].Cells[GetExcelColumnName(newColumnHunter + 1) + "3:" + GetExcelColumnName(newColumnHunter + 1) + (endIndex + 2).ToString()];
var stressExcel = combinedReport.Workbook.Worksheets[SHEET_NAME].Cells[GetExcelColumnName(newColumnHunter + 2) + "3:" + GetExcelColumnName(newColumnHunter + 2) + (endIndex + 2).ToString()];
newColumnHunter += 3;
var stressStrainSeries = stressStrainChart.Series.Add(stressExcel, strainExcel);
var sumStressStrainSeries = SumstressStrainChart.Series.Add(stressExcel, strainExcel);
sumStressStrainSeries.Header = SHEET_NAME;
int sumColorSpot = (idx) % summaryColors.Length;
sumStressStrainSeries.LineColor = summaryColors[sumColorSpot];
stressStrainSeries.Header = sample.name;
int colorSpot = (trialnumber - 1) % trialColors.Length;
int transColor = (trialnumber) % trialColors.Length;
stressStrainSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
trialnumber++;
}
}
if (!makeSummarypage)
{
combinedReport.Workbook.Worksheets.Delete(combinedReport.Workbook.Worksheets["Summary"]);
}
combinedReport.Save();
}
//[TestMethod]
//[Ignore]
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);
}
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Snapshots Method Calls Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Snapshots Method Call Lines Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Snapshots Method Call Lines Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivot
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Type";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Location";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.Cells[4, 1].Value = "See Timeline";
sheet.Cells[4, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[4, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 5, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 9, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Snapshots Method Call Lines Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationSnapshotsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Method Call Lines
loggerConsole.Info("List of Method Call Lines");
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.SnapshotsMethodCallLinesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
loggerConsole.Info("Finalize Snapshots Method Call Lines Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshots"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshots"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsNormal"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsNormal"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsVerySlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsVerySlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsStall"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsStall"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsSlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsSlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Method Call Lines
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT + 1)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT + 1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_METHOD_CALL_LINES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
try
{
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["SnapshotUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestID"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Type"].Position + 1).Width = 10;
sheet.Column(table.Columns["Framework"].Position + 1).Width = 15;
sheet.Column(table.Columns["FullNameIndent"].Position + 1).Width = 45;
sheet.Column(table.Columns["ExitCalls"].Position + 1).Width = 15;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
}
catch (OutOfMemoryException ex)
{
// Do nothing, we must have a lot of cells
logger.Warn("Ran out of memory due to too many rows/cells");
logger.Warn(ex);
}
ExcelAddress cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SegmentUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SegmentUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SnapshotUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SnapshotUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "FullName");
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTYPE_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "Framework");
addRowFieldToPivot(pivot, "FullName");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addColumnFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESLOCATION_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 6, 1], range, PIVOT_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Class", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Method", eSortType.Ascending);
addFilterFieldToPivot(pivot, "FullName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTIMELINE_EXEC_AVERAGE, eChartType.Line, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.SnapshotMethodCallsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
public IActionResult OnPostExport(int currentPage, int pageSize, string month, string year)
{
int monthInt = 0;
if (!string.IsNullOrEmpty(month))
{
monthInt = DateTimeFormatInfo.CurrentInfo.MonthNames.ToList().IndexOf(month) + 1;
}
else
{
if (!string.IsNullOrEmpty(SelectedMonth))
{
monthInt = DateTimeFormatInfo.CurrentInfo.MonthNames.ToList().IndexOf(SelectedMonth) + 1;
}
}
int yearInt = Convert.ToInt32(year);
PopulatePage(monthInt, yearInt, currentPage, pageSize);
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"Monthly_Sales_Report.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
ExcelPackage package = new ExcelPackage();
package.Workbook.Worksheets.Add("Figures");
ExcelWorksheet figuresWorkSheet = package.Workbook.Worksheets["Figures"];
var productsShape = figuresWorkSheet.Drawings.AddShape("productsShape", eShapeStyle.Rect);
productsShape.SetPosition(1, 5, 1, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
productsShape.SetSize(350, 150); //Size in pixels
productsShape.Text = string.Format("Total Products Sold{0}{1}{2}{3}", Environment.NewLine, TotProdSold, Environment.NewLine, TotProdSoldPerc);
productsShape.Font.Size = 18;
productsShape.Fill.Color = Color.FromArgb(0, 192, 239);
var ordersShape = figuresWorkSheet.Drawings.AddShape("ordersShape", eShapeStyle.Rect);
ordersShape.SetPosition(1, 5, 9, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
ordersShape.SetSize(350, 150); //Size in pixels
ordersShape.Text = string.Format("Total Orders{0}{1}{2}{3}", Environment.NewLine, TotOrders, Environment.NewLine, TotOrdersPerc);
ordersShape.Font.Size = 18;
ordersShape.Fill.Color = Color.FromArgb(0, 192, 239);
var ordersVoidedShape = figuresWorkSheet.Drawings.AddShape("ordersVoidedShape", eShapeStyle.Rect);
ordersVoidedShape.SetPosition(1, 5, 17, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
ordersVoidedShape.SetSize(350, 150); //Size in pixels
ordersVoidedShape.Text = string.Format("Total Orders Voided{0}{1}{2}{3}", Environment.NewLine, TotOrdersVoided, Environment.NewLine, TotOrdersVoidedPerc);
ordersVoidedShape.Font.Size = 18;
ordersVoidedShape.Fill.Color = Color.FromArgb(221, 75, 57);
var salesShape = figuresWorkSheet.Drawings.AddShape("salesShape", eShapeStyle.Rect);
salesShape.SetPosition(10, 5, 1, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
salesShape.SetSize(350, 150); //Size in pixels
salesShape.Text = string.Format("Total Sales{0}R {1}{2}{3}", Environment.NewLine, TotSales, Environment.NewLine, TotSalesPerc);
salesShape.Font.Size = 18;
salesShape.Fill.Color = Color.FromArgb(0, 115, 183);
var costShape = figuresWorkSheet.Drawings.AddShape("costShape", eShapeStyle.Rect);
costShape.SetPosition(10, 5, 9, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
costShape.SetSize(350, 150); //Size in pixels
costShape.Text = string.Format("Total Cost{0}R {1}{2}{3}", Environment.NewLine, TotCost, Environment.NewLine, TotCostPerc);
costShape.Font.Size = 18;
costShape.Fill.Color = Color.FromArgb(255, 133, 27);
var profitShape = figuresWorkSheet.Drawings.AddShape("profitShape", eShapeStyle.Rect);
profitShape.SetPosition(10, 5, 17, 5); //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
profitShape.SetSize(350, 150); //Size in pixels
profitShape.Text = string.Format("Total Profit{0}R {1}{2}{3}", Environment.NewLine, TotProfit, Environment.NewLine, TotProfitPerc);
profitShape.Font.Size = 18;
profitShape.Fill.Color = Color.FromArgb(0, 166, 90);
package.Workbook.Worksheets.Add("By Store");
ExcelWorksheet storeWorkSheet = package.Workbook.Worksheets["By Store"];
ExcelChart storeChart = storeWorkSheet.Drawings.AddChart("chart", eChartType.Pie);
storeChart.SetPosition(1, 0, 5, 0);
storeChart.Legend.Remove();
storeChart.Title.Text = "By Store";
storeChart.Title.Font.Bold = true;
storeChart.Title.Font.Size = 12;
storeChart.SetSize(500, 400);
storeWorkSheet.Cells[1, 1].LoadFromCollection(storeAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var storeWorkSheetSeries = storeChart.Series.Add(("B1:" + "B" + (storeAmounts.Count + 1)), ("A1:" + "A" + (storeAmounts.Count + 1)));
var storeSeries = (ExcelPieChartSerie)storeWorkSheetSeries;
storeSeries.DataLabel.ShowCategory = true;
storeSeries.DataLabel.ShowPercent = true;
storeSeries.DataLabel.ShowValue = true;
storeSeries.DataLabel.ShowLeaderLines = true;
storeSeries.DataLabel.Separator = "- ";
storeSeries.DataLabel.Position = eLabelPosition.OutEnd;
package.Workbook.Worksheets.Add("By User");
ExcelWorksheet userWorkSheet = package.Workbook.Worksheets["By User"];
ExcelChart userChart = userWorkSheet.Drawings.AddChart("chart", eChartType.Pie);
userChart.SetPosition(1, 0, 5, 0);
userChart.Legend.Remove();
userChart.Title.Text = "By User";
userChart.Title.Font.Bold = true;
userChart.Title.Font.Size = 12;
userChart.SetSize(500, 400);
userWorkSheet.Cells[1, 1].LoadFromCollection(userAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var userWorkSheetSeries = userChart.Series.Add(("B1:" + "B" + (userAmounts.Count + 1)), ("A1:" + "A" + (userAmounts.Count + 1)));
var userSeries = (ExcelPieChartSerie)userWorkSheetSeries;
userSeries.DataLabel.ShowCategory = true;
userSeries.DataLabel.ShowPercent = true;
userSeries.DataLabel.ShowValue = true;
userSeries.DataLabel.ShowLeaderLines = true;
userSeries.DataLabel.Separator = "- ";
userSeries.DataLabel.Position = eLabelPosition.OutEnd;
package.Workbook.Worksheets.Add("By Category");
ExcelWorksheet categoryWorkSheet = package.Workbook.Worksheets["By Category"];
ExcelChart categoryChart = categoryWorkSheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
categoryChart.Title.Text = "By Category";
categoryChart.XAxis.Title.Text = "Categories";
categoryChart.YAxis.Title.Text = "Amounts";
categoryChart.SetSize(1200, 300);
categoryChart.SetPosition(1, 0, 5, 0);
categoryChart.Legend.Remove();
categoryWorkSheet.Cells[1, 1].LoadFromCollection(categoryAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var workSheetSeries = categoryChart.Series.Add(("B1:" + "B" + (categoryAmounts.Count + 1)), ("A1:" + "A" + (categoryAmounts.Count + 1)));
var categorySeries = (ExcelBarChartSerie)workSheetSeries;
categorySeries.DataLabel.Position = eLabelPosition.OutEnd;
categorySeries.DataLabel.ShowPercent = false;
categorySeries.DataLabel.ShowValue = true;
categorySeries.DataLabel.Font.Bold = true;
package.Workbook.Worksheets.Add("Sales Overview");
ExcelWorksheet salesWorkSheet = package.Workbook.Worksheets["Sales Overview"];
ExcelChart salesChart = salesWorkSheet.Drawings.AddChart("chart", eChartType.Line);
salesChart.Title.Text = "Sales Overview";
salesChart.XAxis.Title.Text = "Dates";
salesChart.YAxis.Title.Text = "Amounts";
salesChart.SetSize(1200, 300);
salesChart.SetPosition(1, 0, 5, 0);
salesChart.Legend.Remove();
salesWorkSheet.Cells[1, 1].LoadFromCollection(salesAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var salesWorkSheetSeries = salesChart.Series.Add(("B1:" + "B" + (salesAmounts.Count + 1)), ("A1:" + "A" + (salesAmounts.Count + 1)));
var salesSeries = (ExcelLineChartSerie)salesWorkSheetSeries;
salesSeries.DataLabel.Position = eLabelPosition.OutEnd;
salesSeries.DataLabel.ShowPercent = false;
salesSeries.DataLabel.ShowValue = true;
salesSeries.DataLabel.Font.Bold = true;
package.Workbook.Worksheets.Add("Profit");
ExcelWorksheet profitWorkSheet = package.Workbook.Worksheets["Profit"];
ExcelChart profitChart = profitWorkSheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
profitChart.Title.Text = "Profit";
profitChart.XAxis.Title.Text = "Dates";
profitChart.YAxis.Title.Text = "Amounts";
profitChart.SetSize(1200, 300);
profitChart.SetPosition(1, 0, 5, 0);
profitChart.Legend.Remove();
profitWorkSheet.Cells[1, 1].LoadFromCollection(profitAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var profitWorkSheetSeries = profitChart.Series.Add(("B1:" + "B" + (profitAmounts.Count + 1)), ("A1:" + "A" + (profitAmounts.Count + 1)));
var profitSeries = (ExcelBarChartSerie)profitWorkSheetSeries;
profitSeries.DataLabel.Position = eLabelPosition.OutEnd;
profitSeries.DataLabel.ShowPercent = false;
profitSeries.DataLabel.ShowValue = true;
profitSeries.DataLabel.Font.Bold = true;
package.Workbook.Worksheets.Add("Orders");
ExcelWorksheet ordersWorkSheet = package.Workbook.Worksheets["Orders"];
ExcelChart ordersChart = ordersWorkSheet.Drawings.AddChart("chart", eChartType.Line);
ordersChart.Title.Text = "Orders";
ordersChart.XAxis.Title.Text = "Dates";
ordersChart.YAxis.Title.Text = "Amounts";
ordersChart.SetSize(1200, 300);
ordersChart.SetPosition(1, 0, 5, 0);
ordersChart.Legend.Remove();
ordersWorkSheet.Cells[1, 1].LoadFromCollection(ordersAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var ordersWorkSheetSeries = ordersChart.Series.Add(("B1:" + "B" + (ordersAmounts.Count + 1)), ("A1:" + "A" + (ordersAmounts.Count + 1)));
var ordersSeries = (ExcelLineChartSerie)ordersWorkSheetSeries;
ordersSeries.DataLabel.Position = eLabelPosition.OutEnd;
ordersSeries.DataLabel.ShowPercent = false;
ordersSeries.DataLabel.ShowValue = true;
ordersSeries.DataLabel.Font.Bold = true;
package.Workbook.Worksheets.Add("By Order Status");
ExcelWorksheet orderStatusWorkSheet = package.Workbook.Worksheets["By Order Status"];
ExcelChart orderStatusChart = orderStatusWorkSheet.Drawings.AddChart("chart", eChartType.Pie);
orderStatusChart.SetPosition(1, 0, 5, 0);
orderStatusChart.Legend.Remove();
orderStatusChart.Title.Text = "By User";
orderStatusChart.Title.Font.Bold = true;
orderStatusChart.Title.Font.Size = 12;
orderStatusChart.SetSize(500, 400);
orderStatusWorkSheet.Cells[1, 1].LoadFromCollection(orderStatusAmounts, false, OfficeOpenXml.Table.TableStyles.Medium1);
var orderStatusWorkSheetSeries = orderStatusChart.Series.Add(("B1:" + "B" + (orderStatusAmounts.Count + 1)), ("A1:" + "A" + (orderStatusAmounts.Count + 1)));
var orderStatusSeries = (ExcelPieChartSerie)orderStatusWorkSheetSeries;
orderStatusSeries.DataLabel.ShowCategory = true;
orderStatusSeries.DataLabel.ShowPercent = true;
orderStatusSeries.DataLabel.ShowValue = true;
orderStatusSeries.DataLabel.ShowLeaderLines = true;
orderStatusSeries.DataLabel.Separator = "- ";
orderStatusSeries.DataLabel.Position = eLabelPosition.OutEnd;
package.Workbook.Worksheets.Add("Order Data");
ExcelWorksheet orderDataSheet = package.Workbook.Worksheets["Order Data"];
using (var range = orderDataSheet.Cells["A1:K1"])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(51, 122, 183));
range.Style.Font.Color.SetColor(Color.White);
}
orderDataSheet.Cells[1, 1].Value = "Order Status";
orderDataSheet.Cells[1, 2].Value = "Prep Status";
orderDataSheet.Cells[1, 3].Value = "Order Id";
orderDataSheet.Cells[1, 4].Value = "Payment Status";
orderDataSheet.Cells[1, 5].Value = "Payment Method";
orderDataSheet.Cells[1, 6].Value = "Order Date";
orderDataSheet.Cells[1, 7].Value = "Paid";
orderDataSheet.Cells[1, 8].Value = "Change";
orderDataSheet.Cells[1, 9].Value = "Order Discount";
orderDataSheet.Cells[1, 10].Value = "Sub Total";
orderDataSheet.Cells[1, 11].Value = "VAT Total";
orderDataSheet.Cells[1, 12].Value = "Grand Total";
orderDataSheet.Cells[1, 13].Value = "User";
orderDataSheet.Cells[1, 14].Value = "Store Name";
orderDataSheet.Cells[1, 1, 1, 14].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
int count = 0;
for (int r = 0; r < Orders.Count(); r++)
{
orderDataSheet.Cells[r + count + 2, 1].Value = Orders.ToList()[r].OrderStatus;
orderDataSheet.Cells[r + count + 2, 2].Value = Orders.ToList()[r].PreparationStatus;
orderDataSheet.Cells[r + count + 2, 3].Value = Orders.ToList()[r].OrderId;
orderDataSheet.Cells[r + count + 2, 4].Value = Orders.ToList()[r].PaymentStatus;
orderDataSheet.Cells[r + count + 2, 5].Value = Orders.ToList()[r].PaymentMethod;
orderDataSheet.Cells[r + count + 2, 6].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
orderDataSheet.Cells[r + count + 2, 6].Value = Orders.ToList()[r].OrderDate;
orderDataSheet.Cells[r + count + 2, 7].Value = Orders.ToList()[r].Paid;
orderDataSheet.Cells[r + count + 2, 8].Value = Orders.ToList()[r].Change;
orderDataSheet.Cells[r + count + 2, 9].Value = Orders.ToList()[r].OrderDiscount;
orderDataSheet.Cells[r + count + 2, 10].Value = Orders.ToList()[r].OrderTotal;
orderDataSheet.Cells[r + count + 2, 11].Value = Orders.ToList()[r].VatTotal;
orderDataSheet.Cells[r + count + 2, 12].Value = Orders.ToList()[r].GrandTotal;
orderDataSheet.Cells[r + count + 2, 13].Value = Orders.ToList()[r].Username;
orderDataSheet.Cells[r + count + 2, 14].Value = _context.Stores.FirstOrDefault(s => s.Store_ID == Orders.ToList()[r].StoreId).StoreName;
orderDataSheet.Cells[1, 1, 1, 14].AutoFitColumns();
}
package.SaveAs(fs);
package.Dispose();
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
stream.CopyTo(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}