public Range(ExcelRange range, bool is1Base)
{
_range = range;
_is1Base = is1Base;
_style = new Style.Style(range.Style);
ExcelComment _ecomment = _range.Worksheet.Cells[_range.Address].Comment;
if (_ecomment != null)
{
_comment = new Comment(_ecomment.Text, _ecomment.Author);
_comment.OnValueChanged += CommentChanged;
}
}
void CommentHandling(string commentText, ExcelCellAddress address)
{
if (commentText != "")
{
if (worksheet.Cells[address.Row, address.Column].Comment == null)
{
worksheet.Cells[address.Row, address.Column].AddComment(commentText, "MTSM");
}
else
{
ExcelComment comment = worksheet.Cells[address.Row, address.Column].Comment;
worksheet.Comments.Remove(comment);
worksheet.Cells[address.Row, address.Column].AddComment(commentText, "MTSM");
}
}
}
public void AddingCommentsWithRichText()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Rich Comments");
ExcelComment comment = sheet.Cells["A1"].AddComment("Bold title:\r\n", "evil corp");
comment.Font.Bold = true;
comment.AutoFit = true;
ExcelRichText rt = comment.RichText.Add("Unbolded subtext");
rt.Bold = false;
// A more extensive example can be found in Sample6.cs::AddComments of the official examples project
// https://github.com/JanKallman/EPPlus/blob/master/SampleApp/Sample6.cs
package.SaveAs(new FileInfo(BinDir.GetPath()));
}
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Comments");
ws.Cells[0, 0].Value = "Comment examples:";
ws.Cells[2, 1].Comment.Text = "Empty cell.";
ws.Cells[4, 1].Value = 5;
ws.Cells[4, 1].Comment.Text = "Cell with a number.";
ws.Cells["B7"].Value = "Cell B7";
ExcelComment comment = ws.Cells["B7"].Comment;
comment.Text = "Some formatted text.\nComment is:\na) multiline,\nb) large,\nc) visible, and \nd) formatted.";
comment.IsVisible = true;
comment.TopLeftCell = new AnchorCell(ws.Columns[3], ws.Rows[4], true);
comment.BottomRightCell = new AnchorCell(ws.Columns[5], ws.Rows[10], false);
// Get first 20 characters of a string
FormattedCharacterRange characters = comment.GetCharacters(0, 20);
// Apply color, italic and size to selected characters
characters.Font.Color = SpreadsheetColor.FromName(ColorName.Orange);
characters.Font.Italic = true;
characters.Font.Size = 300;
// Apply color to 'formatted' part of text
comment.GetCharacters(5, 9).Font.Color = SpreadsheetColor.FromName(ColorName.Red);
ef.Save("Comments.xlsx");
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(programOptions, jobConfiguration) == false)
{
return(true);
}
try
{
loggerConsole.Info("Prepare APM Health Check 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 APM Health Check 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);
var timelineStyle = sheet.Workbook.Styles.CreateNamedStyle("TimelineStyle");
timelineStyle.Style.Font.Name = "Consolas";
timelineStyle.Style.Font.Size = 8;
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER APM Health Check 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_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_HEALTH_CHECK_RULE_RESULTS);
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 Rating";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Summary";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT - 4 + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill APM Health Check Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications - All");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, typeof(ControllerApplication), sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Health Check Results
loggerConsole.Info("List of Health Check Results");
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), 0, typeof(HealthCheckRuleResult), sheet, LIST_SHEET_START_TABLE_AT, 1);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), 1, typeof(HealthCheckRuleResult), sheet, sheet.Dimension.Rows + 1, 1);
}
else
{
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), 0, typeof(HealthCheckRuleResult), sheet, LIST_SHEET_START_TABLE_AT, 1);
}
#endregion
loggerConsole.Info("Finalize APM Health Check Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_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 Health Check Results
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_HEALTH_CHECK_RULE_RESULTS);
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["Application"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Category"].Position + 1).Width = 30;
sheet.Column(table.Columns["Code"].Position + 1).Width = 15;
sheet.Column(table.Columns["Name"].Position + 1).Width = 50;
sheet.Column(table.Columns["Description"].Position + 1).Width = 40;
ExcelAddress cfAddressGrade = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["Grade"].Position + 1, sheet.Dimension.Rows, table.Columns["Grade"].Position + 1);
var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfAddressGrade);
cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.LowValue.Color = colorRedFor3ColorScales;
cfGrade.LowValue.Value = 1;
cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.MiddleValue.Value = 3;
cfGrade.MiddleValue.Color = colorYellowFor3ColorScales;
cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.HighValue.Color = colorGreenFor3ColorScales;
cfGrade.HighValue.Value = 5;
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT - 4, 1], range, PIVOT_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "Application");
addRowFieldToPivot(pivot, "Category");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "Name");
addRowFieldToPivot(pivot, "Description");
addColumnFieldToPivot(pivot, "Grade", eSortType.Ascending);
addDataFieldToPivot(pivot, "Name", DataFieldFunctions.Count, "Rating");
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 30;
sheet.Column(6).Width = 30;
}
#endregion
#region Health Check Results Display
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY];
List <HealthCheckRuleResult> healthCheckRuleResults = new List <HealthCheckRuleResult>();
List <HealthCheckRuleResult> healthCheckRuleResultsController = FileIOHelper.ReadListFromCSVFile(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), new HealthCheckRuleResultReportMap());
if (healthCheckRuleResultsController != null)
{
healthCheckRuleResults.AddRange(healthCheckRuleResultsController);
}
List <HealthCheckRuleResult> healthCheckRuleResultsAPM = FileIOHelper.ReadListFromCSVFile(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), new HealthCheckRuleResultReportMap());
if (healthCheckRuleResultsAPM != null)
{
healthCheckRuleResults.AddRange(healthCheckRuleResultsAPM);
}
if (healthCheckRuleResults != null)
{
#region Output summary of Applications by Category table
// Make this following table out of the list of health rule evaluations
// Controller | Application | Category1 | Category2 | Category 3
// -------------------------------------------------------------
// CntrVal | AppName | Avg(Grade)| Avg(Grade)| Avg(Grade)
// To do this, we measure number of rows (Controller/Application pairs) and Columns (Category values), and build a table
int numRows = healthCheckRuleResults.Select(h => String.Format("{0}/{1}", h.Controller, h.Application)).Distinct().Count();
int numColumns = healthCheckRuleResults.Select(h => h.Category).Distinct().Count();
Dictionary <string, int> categoryTableRowsLookup = new Dictionary <string, int>(numRows);
Dictionary <string, int> categoryTableColumnsLookup = new Dictionary <string, int>(numColumns);
List <HealthCheckRuleResult>[,] categoryTableValues = new List <HealthCheckRuleResult> [numRows, numColumns];
foreach (HealthCheckRuleResult healthCheckRuleResult in healthCheckRuleResults)
{
int rowIndex = 0;
int columnIndex = 0;
string rowIndexValue = String.Format("{0}/{1}", healthCheckRuleResult.Controller, healthCheckRuleResult.Application);
string columnIndexValue = healthCheckRuleResult.Category;
if (categoryTableRowsLookup.ContainsKey(rowIndexValue) == true)
{
rowIndex = categoryTableRowsLookup[rowIndexValue];
}
else
{
rowIndex = categoryTableRowsLookup.Count;
categoryTableRowsLookup.Add(rowIndexValue, rowIndex);
}
if (categoryTableColumnsLookup.ContainsKey(columnIndexValue) == true)
{
columnIndex = categoryTableColumnsLookup[columnIndexValue];
}
else
{
columnIndex = categoryTableColumnsLookup.Count;
categoryTableColumnsLookup.Add(columnIndexValue, columnIndex);
}
// Fill in the cell
List <HealthCheckRuleResult> healthCheckRuleResultsInCell = categoryTableValues[rowIndex, columnIndex];
if (healthCheckRuleResultsInCell == null)
{
healthCheckRuleResultsInCell = new List <HealthCheckRuleResult>();
categoryTableValues[rowIndex, columnIndex] = healthCheckRuleResultsInCell;
}
healthCheckRuleResultsInCell.Add(healthCheckRuleResult);
}
// Output headers
int rowTableStart = 4;
int gradeColumnStart = 4;
int fromRow = rowTableStart;
int fromColumn = gradeColumnStart;
sheet.Cells[fromRow, 1].Value = "Controller";
sheet.Cells[fromRow, 2].Value = "Application";
sheet.Cells[fromRow, 3].Value = "ApplicationID";
foreach (KeyValuePair <string, int> categoriesKVP in categoryTableColumnsLookup)
{
sheet.Cells[fromRow, fromColumn].Value = categoriesKVP.Key;
sheet.Cells[fromRow - 1, fromColumn].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<See Details>"")", String.Format(SHEET_HEALTH_CHECK_RULE_CATEGORY_RESULTS_DISPLAY, categoriesKVP.Key));
sheet.Cells[fromRow - 1, fromColumn].StyleName = "HyperLinkStyle";
fromColumn++;
}
fromRow++;
// Output table
for (int rowIndex = 0; rowIndex < numRows; rowIndex++)
{
for (int columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
List <HealthCheckRuleResult> healthCheckRuleResultsInCell = categoryTableValues[rowIndex, columnIndex];
if (healthCheckRuleResultsInCell != null && healthCheckRuleResultsInCell.Count > 0)
{
double gradeAverage = Math.Round((double)healthCheckRuleResultsInCell.Sum(h => h.Grade) / healthCheckRuleResultsInCell.Count, 1);
sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = gradeAverage;
sheet.Cells[fromRow + rowIndex, 1].Value = healthCheckRuleResultsInCell[0].Controller;
sheet.Cells[fromRow + rowIndex, 2].Value = healthCheckRuleResultsInCell[0].Application;
sheet.Cells[fromRow + rowIndex, 3].Value = healthCheckRuleResultsInCell[0].ApplicationID;
}
else
{
sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = "-";
}
}
}
fromRow = fromRow + numRows;
// Insert the table
range = sheet.Cells[4, 1, 4 + numRows, 3 + numColumns];
table = sheet.Tables.Add(range, TABLE_HEALTH_CHECK_RULE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.None;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
// Resize the columns
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["Application"].Position + 1).Width = 25;
for (int columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
sheet.Column(gradeColumnStart + columnIndex).Width = 15;
// Make the header column cells wrap text for Categories headings
sheet.Cells[rowTableStart, gradeColumnStart + columnIndex].Style.WrapText = true;
}
// Make header row taller
sheet.Row(rowTableStart).Height = 40;
if (sheet.Dimension.Rows > rowTableStart)
{
// Color code it
ExcelAddress cfGradeNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, 4, sheet.Dimension.Rows, 4 + numColumns);
var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfGradeNum);
cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.LowValue.Color = colorRedFor3ColorScales;
cfGrade.LowValue.Value = 1;
cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.MiddleValue.Value = 3;
cfGrade.MiddleValue.Color = colorYellowFor3ColorScales;
cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.HighValue.Color = colorGreenFor3ColorScales;
cfGrade.HighValue.Value = 5;
}
#endregion
#region Output individual categories on separate sheets
// Get list of categories for which we'll be making things
List <string> listOfCategories = healthCheckRuleResults.Select(h => h.Category).Distinct().ToList <string>();
foreach (string category in listOfCategories)
{
sheet = excelReport.Workbook.Worksheets.Add(String.Format(SHEET_HEALTH_CHECK_RULE_CATEGORY_RESULTS_DISPLAY, category));
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Display";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
// Make this following table out of the list of health rule evaluations
// Controller | Application | EntityType | EntityName | Rule 1 | Rule 2 | Rule 3
// -----------------------------------------------------------------------------
// CntrVal | AppName | APMApp | AppName | Grade | Grade | Grade (with comment of value)
// To do this, we measure number of rows (Controller/Application/EntityType/EntityName quads) and Columns (Rules within Category), and build a table
numRows = healthCheckRuleResults.Where(h => h.Category == category).Select(h => String.Format("{0}/{1}/{2}/{3}", h.Controller, h.Application, h.EntityType, h.EntityName)).Distinct().Count();
numColumns = healthCheckRuleResults.Where(h => h.Category == category).Select(h => h.Name).Distinct().Count();
Dictionary <string, int> nameTableRowsLookup = new Dictionary <string, int>(numRows);
Dictionary <string, int> nameTableColumnsLookup = new Dictionary <string, int>(numColumns);
List <HealthCheckRuleResult>[,] nameTableValues = new List <HealthCheckRuleResult> [numRows, numColumns];
foreach (HealthCheckRuleResult healthCheckRuleResult in healthCheckRuleResults)
{
// Only process the rules with the desired category
if (healthCheckRuleResult.Category != category)
{
continue;
}
int rowIndex = 0;
int columnIndex = 0;
string rowIndexValue = String.Format("{0}/{1}/{2}/{3}", healthCheckRuleResult.Controller, healthCheckRuleResult.Application, healthCheckRuleResult.EntityType, healthCheckRuleResult.EntityName);
string columnIndexValue = healthCheckRuleResult.Name;
if (nameTableRowsLookup.ContainsKey(rowIndexValue) == true)
{
rowIndex = nameTableRowsLookup[rowIndexValue];
}
else
{
rowIndex = nameTableRowsLookup.Count;
nameTableRowsLookup.Add(rowIndexValue, rowIndex);
}
if (nameTableColumnsLookup.ContainsKey(columnIndexValue) == true)
{
columnIndex = nameTableColumnsLookup[columnIndexValue];
}
else
{
columnIndex = nameTableColumnsLookup.Count;
nameTableColumnsLookup.Add(columnIndexValue, columnIndex);
}
// Fill in the cell
List <HealthCheckRuleResult> healthCheckRuleResultsInCell = nameTableValues[rowIndex, columnIndex];
if (healthCheckRuleResultsInCell == null)
{
healthCheckRuleResultsInCell = new List <HealthCheckRuleResult>();
nameTableValues[rowIndex, columnIndex] = healthCheckRuleResultsInCell;
}
healthCheckRuleResultsInCell.Add(healthCheckRuleResult);
}
// Output headers
rowTableStart = 4;
gradeColumnStart = 6;
fromRow = rowTableStart;
fromColumn = gradeColumnStart;
sheet.Cells[fromRow, 1].Value = "Controller";
sheet.Cells[fromRow, 2].Value = "Application";
sheet.Cells[fromRow, 3].Value = "ApplicationID";
sheet.Cells[fromRow, 4].Value = "EntityType";
sheet.Cells[fromRow, 5].Value = "EntityName";
foreach (KeyValuePair <string, int> namesKVP in nameTableColumnsLookup)
{
sheet.Cells[fromRow, fromColumn].Value = namesKVP.Key;
fromColumn++;
}
fromRow++;
// Output table
for (int rowIndex = 0; rowIndex < numRows; rowIndex++)
{
for (int columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
List <HealthCheckRuleResult> healthCheckRuleResultsInCell = nameTableValues[rowIndex, columnIndex];
if (healthCheckRuleResultsInCell != null && healthCheckRuleResultsInCell.Count > 0)
{
double gradeAverage = Math.Round((double)healthCheckRuleResultsInCell.Sum(h => h.Grade) / healthCheckRuleResultsInCell.Count, 1);
sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = gradeAverage;
sheet.Cells[fromRow + rowIndex, 1].Value = healthCheckRuleResultsInCell[0].Controller;
sheet.Cells[fromRow + rowIndex, 2].Value = healthCheckRuleResultsInCell[0].Application;
sheet.Cells[fromRow + rowIndex, 3].Value = healthCheckRuleResultsInCell[0].ApplicationID;
sheet.Cells[fromRow + rowIndex, 4].Value = healthCheckRuleResultsInCell[0].EntityType;
sheet.Cells[fromRow + rowIndex, 5].Value = healthCheckRuleResultsInCell[0].EntityName;
StringBuilder sb = new StringBuilder(healthCheckRuleResultsInCell.Count * 128);
for (int k = 0; k < healthCheckRuleResultsInCell.Count; k++)
{
HealthCheckRuleResult healthCheckRuleResult = healthCheckRuleResultsInCell[k];
sb.AppendFormat("{0}: {1}\n", k + 1, wordWrapString(healthCheckRuleResult.Description, 100));
}
// Limit the size of the comment generated to ~2K of text because I think the Excel barfs when the comments are too long.
if (sb.Length > 2500)
{
sb.Length = 2547;
sb.Append("...");
}
// Excessive comments in the workbook lead to poor use experience. Need to rethink and refactor this
ExcelComment comment = sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].AddComment(sb.ToString(), healthCheckRuleResultsInCell[0].Code);
comment.AutoFit = true;
}
else
{
sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = "-";
}
}
}
fromRow = fromRow + numRows;
// Insert the table
range = sheet.Cells[4, 1, 4 + numRows, 5 + numColumns];
table = sheet.Tables.Add(range, getExcelTableOrSheetSafeString(String.Format(TABLE_HEALTH_CHECK_RULE_CATEGORY_RESULTS, category)));
table.ShowHeader = true;
table.TableStyle = TableStyles.None;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
// Resize the columns
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["Application"].Position + 1).Width = 25;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 25;
sheet.Column(table.Columns["EntityType"].Position + 1).Width = 25;
for (int columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
sheet.Column(gradeColumnStart + columnIndex).Width = 15;
// Make the header column cells wrap text for Categories headings
sheet.Cells[rowTableStart, gradeColumnStart + columnIndex].Style.WrapText = true;
}
// Make header row taller
sheet.Row(rowTableStart).Height = 50;
if (sheet.Dimension.Rows > rowTableStart)
{
// Color code it
ExcelAddress cfGradeNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, 5, sheet.Dimension.Rows, 5 + numColumns);
var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfGradeNum);
cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.LowValue.Color = colorRedFor3ColorScales;
cfGrade.LowValue.Value = 1;
cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.MiddleValue.Value = 3;
cfGrade.MiddleValue.Color = colorYellowFor3ColorScales;
cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfGrade.HighValue.Color = colorGreenFor3ColorScales;
cfGrade.HighValue.Value = 5;
}
}
#endregion
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath());
string reportFilePath = FilePathMap.HealthCheckResultsExcelReportFilePath(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>
/// <param name="filePath">表格路径</param>
/// <param name="_commentList">注释</param>
/// <param name="_fieldNameList">变量名</param>
/// <param name="_typeNameList">变量类型</param>
/// <param name="_valueList">数据</param>
public ExcelData Decode(string filePath)
{
UnityEngine.Debug.Log("----->" + filePath);
if (File.Exists(filePath))
{
FileInfo file = new FileInfo(filePath);
using (var package = new ExcelPackage(file))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
//解析注释
List <string> commentList = new List <string>();
for (int i = 1; i <= sheet.Dimension.End.Column; i++)
{
ExcelRange item = sheet.Cells[1, i];
string value = (item.Value ?? "").ToString().Trim();
if (!string.IsNullOrEmpty(value))
{
ExcelComment comment = item.Comment;
string str_comment = "";
if (comment != null)
{
str_comment = comment.Text.Replace("\n", " ").Replace("\r", " ");
}
str_comment = value + str_comment;
commentList.Add(str_comment);
}
else
{
break;
}
}
//变量名 类型名
int columCount = commentList.Count;
List <string> fieldNameList = new List <string>();
List <string> typeNameList = new List <string>();
for (int i = 1; i <= columCount; i++)
{
fieldNameList.Add((sheet.Cells[2, i].Value ?? "").ToString().Trim());
typeNameList.Add((sheet.Cells[3, i].Value ?? "").ToString().Trim());
}
//表中的值
List <string[]> valueList = new List <string[]>();
int maxRow = sheet.Dimension.End.Row;
for (int row = 4; row <= maxRow; row++)
{
var obj = sheet.Cells[row, 1].Value;
if (obj != null)
{
string[] arrayItem = new string[columCount];
arrayItem[0] = obj.ToString();
for (int col = 2; col <= columCount; col++)
{
arrayItem[col - 1] = sheet.Cells[row, col].Text == null ? "" : sheet.Cells[row, col].Text.ToString();
}
valueList.Add(arrayItem);
}
else
{
break;
}
}
string name = Path.GetFileNameWithoutExtension(file.Name);
ExcelData data = new ExcelData(name, commentList, typeNameList, valueList, fieldNameList);
return(data);
}
}
return(null);
}
public IActionResult Index(string startDate, string endDate)
{
var title = "";
if (startDate == null)
{
bills = _statisticalRepository.GetAllBills();
products = _statisticalRepository.GetTopProducts();
cataloges = _statisticalRepository.GetTopCataloges();
}
else
{
if (startDate.Equals(endDate))
{
title = " NGÀY " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy");
}
else
{
title = " TỪ " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy") + " ĐẾN " + Convert.ToDateTime(endDate).ToString("dd-MM-yyyy");
}
bills = _statisticalRepository.GetAllBillsByDate(startDate, endDate);
products = _statisticalRepository.GetTopProductsByDate(startDate, endDate);
cataloges = _statisticalRepository.GetTopCatalogesByDate(startDate, endDate);
}
byte[] fileContents;
using (var package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("Sheet1");
var titlePosition = "B2:F3";
var datePosition = "B5:D5";
var tablePosition = 6;
var tableFirstRow = 7;
//===========================================Bang doanh thu==================================================
ws.Cells["B" + tablePosition].LoadFromCollection(bills, true, TableStyles.Medium14);
var tableLastRow = ws.Dimension.End.Row;
var tbBillLastRow = tableLastRow + 1;
ws.Cells[titlePosition].Merge = true;
ws.Cells[titlePosition].Value = "THỐNG KÊ DOANH THU" + title;
ws.Cells[titlePosition].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[titlePosition].Style.Font.Size = 20;
ws.Cells[titlePosition].Style.Font.Bold = true;
ws.Cells[datePosition].Merge = true;
ws.Cells[datePosition].Value = "Ngày tạo: " + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
for (int i = tableFirstRow; i < tbBillLastRow; i++)
{
using (ExcelRange Rng = ws.Cells["D" + i])
{
if (!string.IsNullOrEmpty(Rng.Text))
{
ExcelComment cmd = Rng.AddComment(Rng.Value.ToString(), "Apt. Notes");
}
}
}
;
ws.Column(5).Style.Numberformat.Format = "dd-MM-yyyy HH:mm:ss";
ws.Column(6).Style.Numberformat.Format = "#,##0 [$VND]";
ws.Cells["B" + tbBillLastRow].Value = "Tổng Cộng";
ws.Cells["F" + tbBillLastRow].Formula = "=SUM(F" + tableFirstRow + ":F" + (tbBillLastRow - 1) + ")";
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(112, 173, 71));
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Color.SetColor(Color.White);
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Bold = true;
ws.Cells["B" + tablePosition].Value = "#";
for (int i = 0; i < bills.Count(); i++)
{
ws.Cells["B" + (tableFirstRow + i)].Value = i + 1;
}
//===========================================Bang cataloges==================================================
var titleCataPosition = "H4:J4";
var tbCataLastRow = tablePosition + cataloges.Count();
var totalCata = tbCataLastRow + 1;
ws.Cells["I" + tablePosition].LoadFromCollection(cataloges, true);
ws.Cells["H" + tablePosition].Value = "#";
for (int i = 0; i < cataloges.Count(); i++)
{
ws.Cells["H" + (tableFirstRow + i)].Value = i + 1;
}
ws.Cells[titleCataPosition].Value = "TOP DANH MỤC";
ws.Cells[titleCataPosition].Style.Font.Size = 14;
ws.Cells[titleCataPosition].Style.Font.Bold = true;
ws.Cells[titleCataPosition].Merge = true;
ExcelRange range = ws.Cells["H" + tablePosition + ":J" + tbCataLastRow];
ExcelTable tab = ws.Tables.Add(range, "TopCataloges");
tab.TableStyle = TableStyles.Light9;
ws.Cells["H" + totalCata].Value = "Tổng Cộng";
ws.Cells["J" + totalCata].Formula = "=SUM(J" + tableFirstRow + ":J" + tbCataLastRow + ")";
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(155, 194, 230));
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Color.SetColor(Color.White);
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Bold = true;
//===========================================Bang product==================================================
var titleProductPosition = "L4:P4";
ws.Cells["M" + tablePosition].LoadFromCollection(products, true);
ws.Cells["L" + tablePosition].Value = "#";
for (int i = 0; i < products.Count(); i++)
{
ws.Cells["L" + (7 + i)].Value = i + 1;
}
ws.Cells[titleProductPosition].Value = "TOP SẢN PHẨM";
ws.Cells[titleProductPosition].Style.Font.Size = 14;
ws.Cells[titleProductPosition].Style.Font.Bold = true;
ws.Cells[titleProductPosition].Merge = true;
ExcelRange range2 = ws.Cells["L" + tablePosition + ":" + ws.Dimension.End.Address];
ExcelTable tab2 = ws.Tables.Add(range2, "TopProducts");
tab2.TableStyle = TableStyles.Medium4;
ws.Column(14).Style.Numberformat.Format = "#,##0 [$VND]";
ws.Column(16).Style.Numberformat.Format = "#,##0 [$VND]";
//================================================================================================
ws.Cells["B" + tableFirstRow + ":B" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["E" + tableFirstRow + ":E" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Row(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Row(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[ws.Dimension.Address].AutoFitColumns();
for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
ws.Column(col).Width = ws.Column(col).Width + 2;
}
ws.Cells["D7"].AutoFitColumns(30.00, 40.00);
ws.Column(1).Width = 4;
// Send to browser
fileContents = package.GetAsByteArray();
}
if (fileContents == null || fileContents.Length == 0)
{
return(NotFound());
}
return(File(
fileContents: fileContents,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "report.xlsx"
));
}
