private static ExcelWorksheet CreateDetailsSheet(ExcelPackage package, ExtendedStats exdata)
{
string timeFormat = (exdata.LastTick - exdata.FirstTick) / TimeSpan.TicksPerSecond < 40 ? "ss" : "mm:ss";
ExcelWorksheet details = package.Workbook.Worksheets.Add("Details");
details.Cells[1, 1].Value = "Boss";
details.Cells[2, 1].Value = "Seconds";
details.Cells[2, 2].Value = "Time";
details.Column(2).Style.Numberformat.Format = timeFormat;
details.Cells[2, 3].Value = "BuffNum";
details.Cells[2, 4].Value = "Start";
details.Column(4).Style.Numberformat.Format = timeFormat;
details.Cells[2, 5].Value = "Duration";
details.Column(5).Style.Numberformat.Format = timeFormat;
details.Cells[2, 6].Value = "BuffName";
details.Cells[2, 7].Value = "Axis";
details.Cells[2, 8].Value = "Damage";
details.Column(8).Style.Numberformat.Format = @"#,#0\k";
details.Cells[2, 9].Value = "AvgDPS";
details.Column(9).Style.Numberformat.Format = @"#,#0\k\/\s";
details.Cells[1, 1, 1, 9].Merge = true;
for (int t = 0; t <= exdata.LastTick / TimeSpan.TicksPerSecond - exdata.FirstTick / TimeSpan.TicksPerSecond; t++)
{
details.Cells[t + 3, 1].Value = t;
details.Cells[t + 3, 2].Value = (double)t / 86400;
}
int buffnum = 0;
int j = 0;
foreach (var buffPair in exdata.Debuffs)
{
if (buffPair.Value.Count() == 0)
{
continue;
}
buffnum++;
details.Cells[2 + buffnum, 6].Value = buffPair.Key.Name;
details.Cells[2 + buffnum, 7].Value = 0;
foreach (var buff in buffPair.Value.AllDurations())
{
j++;
details.Cells[2 + j, 3].Value = buffnum;
details.Cells[2 + j, 4].Value = (double)(buff.Begin - exdata.FirstTick) / TimeSpan.TicksPerDay;
details.Cells[2 + j, 5].Value = (double)(buff.End - buff.Begin) / TimeSpan.TicksPerDay;
}
}
long totalDamage = 0;
j = 0;
long delta = exdata.FirstTick - (exdata.FirstTick / TimeSpan.TicksPerSecond) * TimeSpan.TicksPerSecond;
for (long curTick = exdata.FirstTick / TimeSpan.TicksPerSecond;
curTick <= exdata.LastTick / TimeSpan.TicksPerSecond;
curTick++)
{
j++;
var damage =
exdata.PlayerSkills.Sum(
x => x.Value.Where(time => time.Key == curTick)
.Sum(skill => skill.Value.Sum(stat => stat.Value.Damage)));
totalDamage += damage;
details.Cells[j + 2, 8].Value = damage / 1000;
if (curTick == exdata.LastTick / TimeSpan.TicksPerSecond)
{
details.Cells[j + 2, 9].Value = totalDamage * TimeSpan.TicksPerSecond / (exdata.LastTick - exdata.FirstTick) / 1000;
}
else if (j != 1)
{
details.Cells[j + 2, 9].Value = totalDamage * TimeSpan.TicksPerSecond / ((j - 1) * TimeSpan.TicksPerSecond + delta) / 1000;
}
}
int i = 3;
foreach (var user in exdata.PlayerBuffs)
{
i += 7;
details.Cells[1, i].Value = user.Key;
details.Cells[2, i].Value = "BuffNum";
details.Cells[2, i + 1].Value = "Start";
details.Column(i + 1).Style.Numberformat.Format = timeFormat;
details.Cells[2, i + 2].Value = "Duration";
details.Column(i + 2).Style.Numberformat.Format = timeFormat;
details.Cells[2, i + 3].Value = "BuffName";
details.Cells[2, i + 4].Value = "Axis";
details.Cells[2, i + 5].Value = "Damage";
details.Column(i + 5).Style.Numberformat.Format = @"#,#0\k";
details.Cells[2, i + 6].Value = "AvgDPS";
details.Column(i + 6).Style.Numberformat.Format = @"#,#0\k\/\s";
details.Cells[1, i, 1, i + 6].Merge = true;
buffnum = 0;
j = 0;
foreach (var buffPair in user.Value.Times)
{
if (buffPair.Value.Count() == 0)
{
continue;
}
buffnum++;
details.Cells[2 + buffnum, i + 3].Value = buffPair.Key.Name;
details.Cells[2 + buffnum, i + 4].Value = 0;
foreach (var buff in buffPair.Value.AllDurations())
{
j++;
details.Cells[2 + j, i].Value = buffnum;
details.Cells[2 + j, i + 1].Value = (double)(buff.Begin - exdata.FirstTick) / TimeSpan.TicksPerDay;
details.Cells[2 + j, i + 2].Value = (double)(buff.End - buff.Begin) / TimeSpan.TicksPerDay;
}
}
if (user.Value.Death.Count() > 0)
{
buffnum++;
details.Cells[2 + buffnum, i + 3].Value = "Death";
details.Cells[2 + buffnum, i + 4].Value = 0;
foreach (var buff in user.Value.Death.AllDurations())
{
j++;
details.Cells[2 + j, i].Value = buffnum;
details.Cells[2 + j, i + 1].Value = (double)(buff.Begin - exdata.FirstTick) / TimeSpan.TicksPerDay;
details.Cells[2 + j, i + 2].Value = (double)(buff.End - buff.Begin) / TimeSpan.TicksPerDay;
}
}
if (user.Value.Aggro(exdata.Entity).Count() > 0)
{
buffnum++;
details.Cells[2 + buffnum, i + 3].Value = "Aggro";
details.Cells[2 + buffnum, i + 4].Value = 0;
foreach (var buff in user.Value.Aggro(exdata.Entity).AllDurations())
{
j++;
details.Cells[2 + j, i].Value = buffnum;
details.Cells[2 + j, i + 1].Value = (double)(buff.Begin - exdata.FirstTick) / TimeSpan.TicksPerDay;
details.Cells[2 + j, i + 2].Value = (double)(buff.End - buff.Begin) / TimeSpan.TicksPerDay;
}
}
totalDamage = 0;
j = 0;
for (long curTick = exdata.FirstTick / TimeSpan.TicksPerSecond;
curTick <= exdata.LastTick / TimeSpan.TicksPerSecond;
curTick++)
{
j++;
var damage =
exdata.PlayerSkills.Where(all => all.Key == user.Key).Sum(
x => x.Value.Where(time => time.Key == curTick)
.Sum(skill => skill.Value.Sum(stat => stat.Value.Damage)));
totalDamage += damage;
details.Cells[j + 2, i + 5].Value = damage / 1000;
if (curTick == exdata.LastTick / TimeSpan.TicksPerSecond)
{
details.Cells[j + 2, i + 6].Value = totalDamage * TimeSpan.TicksPerSecond / (exdata.LastTick - exdata.FirstTick) / 1000;
}
else if (j != 1)
{
details.Cells[j + 2, i + 6].Value = totalDamage * TimeSpan.TicksPerSecond / ((j - 1) * TimeSpan.TicksPerSecond + delta) / 1000;
}
}
}
return(details);
}
// 保存
public void Save()
{
if (File.Exists(langPath))
{
File.Delete(langPath);
}
PathHelper.CheckPath(langPath);
var xlsx = new FileInfo(langPath);
using (var package = new ExcelPackage(xlsx))
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add(sheetName);
Dictionary <int, int> columnWidths = new Dictionary <int, int>();
int colCount = fields.Count;
// 表头
for (int i = 0; i < fields.Count; i++)
{
DataField field = fields[i];
ws.Cells[Setting.Options.xlsxHeadTypeLine, i + 1].Value = field.typeName;
ws.Cells[Setting.Options.xlsxHeadCnLine, i + 1].Value = field.cn;
ws.Cells[Setting.Options.xlsxHeadFieldLine, i + 1].Value = field.field;
if (state != LangStateType.Add)
{
Color color = Color.FromArgb(70, 90, 110);
switch (field.state)
{
case LangStateType.Add:
color = Color.FromArgb(40, 80, 40);
break;
case LangStateType.Remove:
color = Color.FromArgb(70, 70, 70);
break;
}
ExcelRange excelCellHead1 = ws.Cells[Setting.Options.xlsxHeadTypeLine, i + 1];
ExcelRange excelCellHead2 = ws.Cells[Setting.Options.xlsxHeadCnLine, i + 1];
ExcelRange excelCellHead3 = ws.Cells[Setting.Options.xlsxHeadFieldLine, i + 1];
excelCellHead1.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCellHead1.Style.Fill.BackgroundColor.SetColor(color);
excelCellHead1.Style.Font.Color.SetColor(field.textColor);
excelCellHead2.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCellHead2.Style.Fill.BackgroundColor.SetColor(color);
excelCellHead2.Style.Font.Color.SetColor(field.textColor);
excelCellHead3.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCellHead3.Style.Fill.BackgroundColor.SetColor(color);
excelCellHead3.Style.Font.Color.SetColor(field.textColor);
}
columnWidths[i + 1] = Math.Clamp(field.field.Length, 10, 30);
}
using (var range = ws.Cells[1, 1, 3, colCount])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
//range.Style.Fill.PatternType = ExcelFillStyle.Solid;
//range.Style.Fill.BackgroundColor.SetColor(Color.Blue);
}
// 冻结前3行
ws.View.FreezePanes(4, 1);
int rowIndex = 4;
foreach (var kvp in rowDatasById)
{
LangRowData langRowData = kvp.Value;
for (int i = 0; i < fields.Count; i++)
{
DataField field = fields[i];
LangCellData langCellData = langRowData.GetCell(field.field);
ExcelRange excelCell = ws.Cells[rowIndex, i + 1];
excelCell.Style.Font.Color.SetColor(field.textColor);
if (langCellData == null)
{
langCellData = new LangCellData();
langCellData.langRowData = langRowData;
langCellData.value = string.Empty;
langCellData.state = LangStateType.Add;
}
if (langCellData != null)
{
excelCell.Value = langCellData.value;
columnWidths[i + 1] = Math.Max(langCellData.value.Length, columnWidths[i + 1]);
if (!string.IsNullOrEmpty(langCellData.comment))
{
ws.Comments.Add(excelCell, langCellData.comment, "zf");
}
switch (langCellData.showState)
{
case LangStateType.Add:
excelCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCell.Style.Fill.BackgroundColor.SetColor(Color.Green);
break;
case LangStateType.Remove:
excelCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCell.Style.Fill.BackgroundColor.SetColor(Color.Gray);
break;
case LangStateType.Modify:
excelCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCell.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
break;
}
}
}
rowIndex++;
}
foreach (var kvp in columnWidths)
{
ws.Column(kvp.Key).Width = Math.Min(80, kvp.Value * 2);
}
package.Save();
}
}
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);
}
try
{
loggerConsole.Info("Prepare Users, Groups, Roles and Permissions Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER RBAC Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER RBAC Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_CONTROLLERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "Types of Users";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USERS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USERS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "Types of Groups";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_GROUPS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "Types of Roles";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_ROLES_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "Types of Permissions";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_PERMISSIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "Types of User Permissions";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
loggerConsole.Info("Fill Users, Groups, Roles and Permissions Report File");
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RBACControllerSummaryReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Users
loggerConsole.Info("List of Users");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UsersReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Groups
loggerConsole.Info("List of Groups");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Roles
loggerConsole.Info("List of Roles");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RolesReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Permissions
loggerConsole.Info("List of Permissions");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.PermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region User Permissions
loggerConsole.Info("List of User Permissions");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UserPermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Group Memberships
loggerConsole.Info("List of Group Memberships");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Role Memberships
loggerConsole.Info("List of Role Memberships");
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RoleMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Users, Groups, Roles and Permissions Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20;
}
#endregion
#region Users
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["UserName"].Position + 1).Width = 20;
sheet.Column(table.Columns["DisplayName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USERS);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "CreatedBy");
addFilterFieldToPivot(pivot, "UpdatedBy");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "UserName");
addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending);
addDataFieldToPivot(pivot, "UserID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USERS_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
}
#endregion
#region Groups
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUPS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Description"].Position + 1).Width = 20;
sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_GROUPS);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "CreatedBy");
addFilterFieldToPivot(pivot, "UpdatedBy");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "GroupName");
addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending);
addDataFieldToPivot(pivot, "GroupID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_GROUPS_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
}
#endregion
#region Roles
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30;
sheet.Column(table.Columns["Description"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_ROLES);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "CreatedBy");
addFilterFieldToPivot(pivot, "UpdatedBy");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "RoleName");
addDataFieldToPivot(pivot, "RoleID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_ROLES_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
}
#endregion
#region Permissions
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_PERMISSIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["RoleName"].Position + 1).Width = 20;
sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_PERMISSIONS);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "RoleName");
addRowFieldToPivot(pivot, "EntityName");
addRowFieldToPivot(pivot, "PermissionName");
addColumnFieldToPivot(pivot, "Allowed");
addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_PERMISSIONS_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
}
#endregion
#region User Permissions
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USER_PERMISSIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["UserName"].Position + 1).Width = 20;
sheet.Column(table.Columns["UserSecurityProvider"].Position + 1).Width = 15;
sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20;
sheet.Column(table.Columns["GroupSecurityProvider"].Position + 1).Width = 15;
sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30;
sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USER_PERMISSIONS);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "UserName");
addRowFieldToPivot(pivot, "GroupName");
addRowFieldToPivot(pivot, "RoleName");
addRowFieldToPivot(pivot, "EntityName");
addRowFieldToPivot(pivot, "PermissionName");
addColumnFieldToPivot(pivot, "Allowed");
addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USER_PERMISSIONS_GRAPH, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
}
#endregion
#region Group Memberships
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUP_MEMBERSHIPS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20;
sheet.Column(table.Columns["UserName"].Position + 1).Width = 20;
}
#endregion
#region Role Memberships
// Make table
sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLE_MEMBERSHIPS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.RBACExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
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_BIQ) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Detected BIQ 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 BIQ 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 BIQ 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_BIQ_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_SEARCHES_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_SEARCHES_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_SEARCHES_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_SEARCHES_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_SAVED_METRICS_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_SAVED_METRICS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_SAVED_METRICS_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_SAVED_METRICS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_BUSINESS_JOURNEYS_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_BUSINESS_JOURNEYS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_BUSINESS_JOURNEYS_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_BUSINESS_JOURNEYS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EXPERIENCE_LEVELS_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_EXPERIENCE_LEVELS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EXPERIENCE_LEVELS_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_EXPERIENCE_LEVELS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_SCHEMAS_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_FIELDS_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_FIELDS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_FIELDS_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_FIELDS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1);
#endregion
loggerConsole.Info("Fill Detected BIQ 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_BIQ_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Searches
loggerConsole.Info("List of Searches");
sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSearchesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Widgets
loggerConsole.Info("List of Widgets");
sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQWidgetsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Saved Metrics
loggerConsole.Info("List of Saved Metrics");
sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQMetricsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Business Journeys
loggerConsole.Info("List of Business Journeys");
sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQBusinessJourneysReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Experience Levels
loggerConsole.Info("List of Experience Levels");
sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQExperienceLevelsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Schemas
loggerConsole.Info("List of Schemas");
sheet = excelReport.Workbook.Worksheets[SHEET_SCHEMAS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSchemasReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Schema Fields
loggerConsole.Info("List of Schema Fields");
sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSchemaFieldsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Detected BIQ 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_BIQ_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_BIQ);
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["NumSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSearches"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumMultiSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumMultiSearches"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSingleSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSingleSearches"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumLegacySearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumLegacySearches"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSavedMetrics"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSavedMetrics"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumBusinessJourneys"].Position + 1, sheet.Dimension.Rows, table.Columns["NumBusinessJourneys"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumExperienceLevels"].Position + 1, sheet.Dimension.Rows, table.Columns["NumExperienceLevels"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSchemas"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSchemas"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumFields"].Position + 1, sheet.Dimension.Rows, table.Columns["NumFields"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Searches
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_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_SEARCHES);
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["SearchName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SearchType"].Position + 1).Width = 10;
sheet.Column(table.Columns["SearchMode"].Position + 1).Width = 10;
sheet.Column(table.Columns["Visualization"].Position + 1).Width = 10;
sheet.Column(table.Columns["ViewMode"].Position + 1).Width = 10;
sheet.Column(table.Columns["DataSource"].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;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_SEARCHES_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ViewMode");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "DataSource");
addRowFieldToPivot(pivot, "SearchName");
addColumnFieldToPivot(pivot, "SearchMode");
addColumnFieldToPivot(pivot, "SearchType");
addDataFieldToPivot(pivot, "NumWidgets", DataFieldFunctions.Sum, "NumWidgets");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_SEARCHES_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Widgets
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_WIDGETS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SearchName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SearchType"].Position + 1).Width = 10;
sheet.Column(table.Columns["WidgetName"].Position + 1).Width = 15;
sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 10;
sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15;
sheet.Column(table.Columns["StartTime"].Position + 1).Width = 20;
sheet.Column(table.Columns["EndTime"].Position + 1).Width = 20;
sheet.Column(table.Columns["StartTimeUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["EndTime"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_WIDGETS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "Resolution", eSortType.Ascending);
addFilterFieldToPivot(pivot, "IsStacking");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "DataSource");
addRowFieldToPivot(pivot, "SearchName");
addRowFieldToPivot(pivot, "WidgetName");
addColumnFieldToPivot(pivot, "WidgetType");
addDataFieldToPivot(pivot, "WidgetID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WIDGETS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
}
#endregion
#region Saved Metrics
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_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_SAVED_METRICS);
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["MetricName"].Position + 1).Width = 20;
sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15;
sheet.Column(table.Columns["EventType"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_SAVED_METRICS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "IsEnabled");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "DataSource");
addRowFieldToPivot(pivot, "MetricName");
addColumnFieldToPivot(pivot, "LastExecStatus");
addDataFieldToPivot(pivot, "MetricName", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_SAVED_METRICS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Business Journeys
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_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_BUSINESS_JOURNEYS);
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["JourneyName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Stages"].Position + 1).Width = 30;
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;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_BUSINESS_JOURNEY_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "NumStages", eSortType.Ascending);
addFilterFieldToPivot(pivot, "IsEnabled");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "KeyField");
addRowFieldToPivot(pivot, "JourneyName");
addDataFieldToPivot(pivot, "JourneyID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_BUSINESS_JOURNEY_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Experience Levels
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_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_EXPERIENCE_LEVELS);
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["ExperienceLevelName"].Position + 1).Width = 20;
sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15;
sheet.Column(table.Columns["EventField"].Position + 1).Width = 15;
sheet.Column(table.Columns["StartOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["StartOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_EXPERIENCE_LEVELS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "IsActive");
addFilterFieldToPivot(pivot, "EventField", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ThresholdOperator", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ThresholdValue", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "DataSource");
addRowFieldToPivot(pivot, "Criteria");
addRowFieldToPivot(pivot, "ExperienceLevelName");
addColumnFieldToPivot(pivot, "Period");
addDataFieldToPivot(pivot, "ExperienceLevelName", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EXPERIENCE_LEVELS_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 Schemas
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_SCHEMAS_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_SCHEMAS);
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["SchemaName"].Position + 1).Width = 20;
}
#endregion
#region Schema Fields
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_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_FIELDS);
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["SchemaName"].Position + 1).Width = 20;
sheet.Column(table.Columns["FieldName"].Position + 1).Width = 20;
sheet.Column(table.Columns["FieldType"].Position + 1).Width = 15;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_FIELD_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "Category", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumParents", eSortType.Ascending);
addFilterFieldToPivot(pivot, "IsHidden");
addFilterFieldToPivot(pivot, "IsDeleted");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "SchemaName");
addRowFieldToPivot(pivot, "FieldName");
addColumnFieldToPivot(pivot, "FieldType");
addDataFieldToPivot(pivot, "FieldName", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_FIELD_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region 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.BIQEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
private static void SetColumnWidths(ExcelWorksheet worksheet)
{
worksheet.Column(1).Width = 10;
worksheet.Column(2).Width = 8.12;
worksheet.Column(3).Width = 11.88;
worksheet.Column(4).Width = 7.41;
worksheet.Column(5).Width = 1.24;
worksheet.Column(6).Width = 5.82;
worksheet.Column(7).Width = 6;
worksheet.Column(8).Width = 6;
worksheet.Column(9).Width = 5.5;
worksheet.Column(10).Width = 5.5;
worksheet.Column(11).Width = 5.5;
worksheet.Column(12).Width = 5.5;
worksheet.Column(13).Width = 5.5;
}
public byte [] GenararExcelConsultaAusencias(EDAusencia edAusencia)
{
var resultado = ausenciaMg.ConsultarAusencia(edAusencia);
ExcelPackage excel = new ExcelPackage();
excel.Workbook.Worksheets.Add("Reporte de Ausentismo");
ExcelWorksheet hoja = excel.Workbook.Worksheets[1];
hoja.Cells["A1:M1"].Merge = true;
hoja.Cells["A1"].Value = "REPORTE DE AUSENTISMO ";
hoja.Cells["A1"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
hoja.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
hoja.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
hoja.Cells["A1"].Style.Font.Bold = true;
hoja.Cells["A1"].Style.WrapText = true;
hoja.Cells["A2"].Value = "Tipo";
hoja.Cells["A2"].Style.Font.Bold = true;
hoja.Cells["B2"].Value = "Fecha Registro";
hoja.Cells["B2"].Style.Font.Bold = true;
hoja.Cells["C2"].Value = "Nombre Persona";
hoja.Cells["C2"].Style.Font.Bold = true;
hoja.Cells["D2"].Value = "Nro. Documento";
hoja.Cells["D2"].Style.Font.Bold = true;
hoja.Cells["E2"].Value = "Departamento";
hoja.Cells["E2"].Style.Font.Bold = true;
hoja.Cells["F2"].Value = "Municipio";
hoja.Cells["F2"].Style.Font.Bold = true;
hoja.Cells["G2"].Value = "Sede";
hoja.Cells["G2"].Style.Font.Bold = true;
hoja.Cells["H2"].Value = "Contingencia";
hoja.Cells["H2"].Style.Font.Bold = true;
hoja.Cells["I2"].Value = "Fecha Inicio";
hoja.Cells["I2"].Style.Font.Bold = true;
hoja.Cells["J2"].Value = "Fecha Fin";
hoja.Cells["J2"].Style.Font.Bold = true;
hoja.Cells["K2"].Value = "Dias Ausencia";
hoja.Cells["K2"].Style.Font.Bold = true;
hoja.Cells["L2"].Value = "Diagnóstico";
hoja.Cells["L2"].Style.Font.Bold = true;
hoja.Cells["M2"].Value = "Costo";
hoja.Cells["M2"].Style.Font.Bold = true;
hoja.Cells["N2"].Value = "Observacion";
hoja.Cells["N2"].Style.Font.Bold = true;
int nunInicial = 3;
foreach (var dato in resultado)
{
hoja.Cells[string.Format("A{0}", nunInicial)].Value = dato.TipoRegistro;
hoja.Cells[string.Format("A{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("B{0}", nunInicial)].Value = string.Format("{0}/{1}/{2}", dato.FechaModificacion.Value.Day, dato.FechaModificacion.Value.Month, dato.FechaModificacion.Value.Year);
hoja.Cells[string.Format("B{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("C{0}", nunInicial)].Value = dato.NombrePersona;
hoja.Cells[string.Format("C{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("D{0}", nunInicial)].Value = dato.Documento;
hoja.Cells[string.Format("D{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("E{0}", nunInicial)].Value = dato.Departamento;
hoja.Cells[string.Format("E{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("F{0}", nunInicial)].Value = dato.Municipio;
hoja.Cells[string.Format("F{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("G{0}", nunInicial)].Value = dato.nombreRegional;
hoja.Cells[string.Format("G{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("H{0}", nunInicial)].Value = dato.Detalle;
hoja.Cells[string.Format("H{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("I{0}", nunInicial)].Value = string.Format("{0}/{1}/{2}", dato.fechainicio.Day, dato.fechainicio.Month, dato.fechainicio.Year);;
hoja.Cells[string.Format("I{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("J{0}", nunInicial)].Value = string.Format("{0}/{1}/{2}", dato.fechafin.Day, dato.fechafin.Month, dato.fechafin.Year);;
hoja.Cells[string.Format("J{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("K{0}", nunInicial)].Value = Utilidades.Utilitarios.ObtenerValorConformato(dato.diasausencia.ToString());
hoja.Cells[string.Format("K{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("L{0}", nunInicial)].Value = dato.Descripcion;
hoja.Cells[string.Format("L{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("M{0}", nunInicial)].Value = dato.costo;
hoja.Cells[string.Format("M{0}", nunInicial)].Style.WrapText = true;
hoja.Cells[string.Format("M{0}", nunInicial)].OfType <decimal>();
hoja.Cells[string.Format("N{0}", nunInicial)].Value = dato.Observaciones;
hoja.Cells[string.Format("N{0}", nunInicial)].Style.WrapText = true;
nunInicial++;
}
for (int i = 1; i < 14; i++)
{
hoja.Column(i).Width = 30;
}
foreach (var cel in hoja.Cells[string.Format("A1:N{0}", resultado.Count() + 2)])
{
cel.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
//FileInfo fileInfo = new FileInfo(@"D:\Archivos\Actividades.xlsx");
//excel.SaveAs(fileInfo);
return(excel.GetAsByteArray());
}
public void ImportExcel()
{
string fname = "";
string WorkingDate = "";
string DateFile = "Date.txt";
int WorkingDateYear = 0;
int WorkingDateMonth = 0;
int WorkingDateDay = 0;
bool ImportErrorFlag = true;
ExcelPackage xlApp = null;
ExcelWorkbook xlWorkBook;
ExcelWorksheet xlWorkSheet = null;
do
{
fname = Api.OpenDialogFile();
if (fname == null)
{
string error = "Khong file nao duoc chon";
MessageBox.Show(error);
break;
}
FileInfo fileInfo = new FileInfo(fname);
xlApp = new ExcelPackage(fileInfo);
xlWorkBook = xlApp.Workbook;
xlWorkSheet = xlWorkBook.Worksheets.First();
xlWorkSheet.Column(4).Style.Numberformat.Format = "HH:mm";
xlWorkSheet.Column(5).Style.Numberformat.Format = "HH:mm";
WorkingDate = (Convert.ToDateTime(xlWorkSheet.Cells[3, 7].Text)).ToString("yyyMMdd");
WorkingDateYear = (Convert.ToDateTime(xlWorkSheet.Cells[3, 7].Text)).Year;
WorkingDateMonth = (Convert.ToDateTime(xlWorkSheet.Cells[3, 7].Text)).Month;
WorkingDateDay = (Convert.ToDateTime(xlWorkSheet.Cells[3, 7].Text)).Day;
if (!File.Exists(DateFile))
{
ImportErrorFlag = false;
using (StreamWriter sw = File.CreateText(DateFile))
{
sw.WriteLine(WorkingDate);
}
}
else
{
string[] date;
date = File.ReadAllLines(DateFile);
ImportErrorFlag = false;
for (int i = 0; i < date.Count(); i++)
{
if (string.Equals(date[i], WorkingDate))
{
string error = "Du lieu ngay " + WorkingDateDay + "." + WorkingDateMonth + "." + WorkingDateYear + " da co\nVui long chon file khac";
MessageBox.Show(error);
ImportErrorFlag = true;
break;
}
}
}
} while (ImportErrorFlag);
using (StreamWriter sw = new StreamWriter(DateFile, true))
{
sw.WriteLine(WorkingDate);
}
if (!ImportErrorFlag)
{
FileInfo ExcelReport = new FileInfo(StringPath.PathReport + "report_" + WorkingDate + ".xlsx");
ExcelPackage xlReport = new ExcelPackage(ExcelReport);
ExcelWorksheet xlReportWorkSheet = xlReport.Workbook.Worksheets.Add("Report");
xlReportWorkSheet.TabColor = System.Drawing.Color.Black;
xlReportWorkSheet.DefaultRowHeight = 12;
xlReportWorkSheet.Row(1).Height = 20;
xlReportWorkSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
xlReportWorkSheet.Row(1).Style.Font.Bold = true;
xlReportWorkSheet.Cells[1, 1].Value = "Id";
xlReportWorkSheet.Cells[1, 2].Value = "Name";
if (!Directory.Exists(StringPath.PathFolder + "\\" + StringPath.dbName))
{
Directory.CreateDirectory(StringPath.PathFolder + "\\" + StringPath.dbName);
}
if (!Directory.Exists(StringPath.PathFolder + "\\" + "Report"))
{
Directory.CreateDirectory(StringPath.PathFolder + "\\" + "Report");
}
DirectoryInfo dir = new DirectoryInfo(StringPath.PathFolder + "\\" + StringPath.dbName);
ArrayList IDinExcel = new ArrayList();
ArrayList al_DataExcel = new ArrayList();
ArrayList IDinDB = new ArrayList();
ArrayList IDnotinExcel = new ArrayList();
IDinExcel.Clear();
IDinDB.Clear();
IDnotinExcel.Clear();
if (!Api.IsDirectoryEmpty(StringPath.PathFolder + "\\" + StringPath.dbName))
{
foreach (FileInfo fi in dir.GetFiles())
{
IDinDB.Add(Api.GetIdFromFileName(fi.Name));
}
}
for (int i = 6; i <= xlWorkSheet.Dimension.End.Row && xlWorkSheet.Cells[i, 2].Text != ""; i++)
{
DataExcel dt = new DataExcel(xlWorkSheet.Cells[i, 2].Text,
WorkingDate,
Convert.ToDateTime((xlWorkSheet.Cells[i, 4].Value)).Hour,
Convert.ToDateTime((xlWorkSheet.Cells[i, 5].Value)).Hour,
xlWorkSheet.Cells[i, 7].Text);
al_DataExcel.Add(dt);
string FileName = StringPath.PathFile + dt.Id + ".txt";
if (IDinDB.Capacity == 0)
{
Api.UpdateFirstWorkData(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear, dt.TimeIn, dt.TimeOut, dt.Name);
}
else
{
bool EndOfFile = true;
foreach (Object obj in IDinDB)
{
if (string.Equals(dt.Id, obj.ToString()))
{
if (Api.CheckDateWrittenToFile(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear))
{
EndOfFile = false;
//if (Api.GetDayWeek(WorkingDateDay, WorkingDateMonth, WorkingDateYear) == 7)
//{
// Api.UpdateWorkDataOnSat(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear, dt.TimeIn, dt.TimeOut);
//}
//else
{
Api.UpdateWorkData(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear, dt.TimeIn, dt.TimeOut);
}
}
break;
}
}
if (EndOfFile)
{
Api.UpdateFirstWorkData(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear, dt.TimeIn, dt.TimeOut, dt.Name);
}
}
}
foreach (FileInfo fi in dir.GetFiles())
{
string FileName = StringPath.PathFile + fi.Name;
bool IsExist = false;
foreach (DataExcel dt in al_DataExcel)
{
if (string.Equals(Api.GetIdFromFileName(fi.Name), dt.Id))
{
IsExist = true;
break;
}
}
if (!IsExist)
{
if (Api.CheckDateWrittenToFile(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear))
{
Api.UpdateNonWorkingData(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear);
}
}
}
int index = 2;
foreach (DataExcel dt in al_DataExcel)
{
string FileName = StringPath.PathFile + dt.Id + ".txt";
xlReportWorkSheet.Cells[index, 1].Value = dt.Id;
xlReportWorkSheet.Cells[index, 2].Value = dt.Name;
if (!Api.validation(FileName))
{
xlReportWorkSheet.Cells[index, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
xlReportWorkSheet.Cells[index, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
xlReportWorkSheet.Cells[index, 1].Style.Fill.BackgroundColor.SetColor(Color.OrangeRed);
xlReportWorkSheet.Cells[index, 2].Style.Fill.BackgroundColor.SetColor(Color.OrangeRed);
Api.UpdateWorkingDataForInvalidEmployee(FileName, WorkingDateDay, WorkingDateMonth, WorkingDateYear);
}
index++;
}
xlReportWorkSheet.Column(1).AutoFit();
xlReportWorkSheet.Column(2).AutoFit();
xlReportWorkSheet.Column(3).AutoFit();
xlReport.Save();
string message = "Report generated";
MessageBox.Show(message);
xlApp.Dispose();
xlReport.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public static byte[] CrearExcelFolios(List <Folio> folios)
{
byte[] FileBytesArray = { };
try
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
//Set some properties of the Excel document
excelPackage.Workbook.Properties.Author = "Sistema Arctodus";
excelPackage.Workbook.Properties.Title = "Folios";
excelPackage.Workbook.Properties.Subject = "";
excelPackage.Workbook.Properties.Created = DateTime.Now;
//Create the WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Folios");
worksheet.PrinterSettings.PaperSize = ePaperSize.Letter;
worksheet.PrinterSettings.LeftMargin = 0.71M;
worksheet.PrinterSettings.RightMargin = 0.71M;
worksheet.PrinterSettings.TopMargin = 0.75M;
worksheet.PrinterSettings.BottomMargin = 0.75M;
worksheet.PrinterSettings.HeaderMargin = 0.31M;
worksheet.PrinterSettings.FooterMargin = 0.31M;
worksheet.PrinterSettings.Scale = 85;
worksheet.Column(1).Width = GTCW(3.29);
worksheet.Column(2).Width = GTCW(3.00);
worksheet.Column(3).Width = GTCW(1.29);
worksheet.Column(4).Width = GTCW(1.43);
worksheet.Column(5).Width = GTCW(8.57);
worksheet.Column(6).Width = GTCW(9.14);
worksheet.Column(7).Width = GTCW(3.57);
worksheet.Column(8).Width = GTCW(3.29);
worksheet.Column(9).Width = GTCW(4.71);
worksheet.Column(10).Width = GTCW(3.29);
worksheet.Column(11).Width = GTCW(3.00);
worksheet.Column(12).Width = GTCW(1.29);
worksheet.Column(13).Width = GTCW(1.43);
worksheet.Column(14).Width = GTCW(8.57);
worksheet.Column(15).Width = GTCW(9.14);
worksheet.Column(16).Width = GTCW(3.57);
int fila = 4;
int columna = 1;
int contadorRegistros = 0;
int RowCountBackgroundColor = 0;
if (folios != null && folios.Count > 0)
{
foreach (Folio f in folios)
{
if (contadorRegistros % 100 == 0 || contadorRegistros % 50 == 0 || contadorRegistros == 0)
{
if (contadorRegistros % 100 == 0 && contadorRegistros != 0)
{
columna = 1;
fila = fila + 5;
worksheet.Row(fila + 51).PageBreak = true;
//BorderDraw(worksheet,"A" + Convert.ToString(fila - 1), "G" + Convert.ToString(fila + 49));
RowCountBackgroundColor = 0;
}
else if (contadorRegistros % 50 == 0 && contadorRegistros != 0 && contadorRegistros % 100 != 0)
{
columna = 10;
fila = fila - 50;
worksheet.Row(fila + 51).PageBreak = true;
worksheet.Row(fila + 51 - 3).PageBreak = false;
worksheet.Row(fila + 51 - 2).PageBreak = false;
//BorderDraw(worksheet,"J" + Convert.ToString(fila - 1), "P" + Convert.ToString(fila + 49));
RowCountBackgroundColor = 0;
}
worksheet.Cells[fila - 1, columna].Value = "NS";
worksheet.Cells[fila - 1, columna + 3].Value = "C";
worksheet.Cells[fila - 1, columna + 4].Value = "COLOR";
worksheet.Cells[fila - 1, columna + 5].Value = "Fecha";
worksheet.Cells[fila - 1, columna + 6].Value = "NPE";
worksheet.Column(16).PageBreak = true;
worksheet.Row(55).PageBreak = true;
}
worksheet.Cells.Style.Numberformat.Format = "@";
worksheet.Cells[fila, columna].Value = f.Lote; //1
worksheet.Cells[fila, columna + 1].Value = f.ContadorLote; //2
worksheet.Cells[fila, columna + 2].Value = f.Homoclave; //3
//4
worksheet.Cells[fila, columna + 4].Value = f.Color_Producto; // 5
// 6
worksheet.Cells[fila, columna + 6].Value = f.Contador; // 7
BorderCellDraw(worksheet, fila, columna);
BorderCellDraw(worksheet, fila, columna + 1);
BorderCellDraw(worksheet, fila, columna + 2);
BorderCellDraw(worksheet, fila, columna + 3);
BorderCellDraw(worksheet, fila, columna + 4);
BorderCellDraw(worksheet, fila, columna + 5);
BorderCellDraw(worksheet, fila, columna + 6);
if (RowCountBackgroundColor < 10)
{
worksheet.Cells[fila, columna].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[fila, columna].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230));
worksheet.Cells[fila, columna + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[fila, columna + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230));
worksheet.Cells[fila, columna + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[fila, columna + 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230));
worksheet.Cells[fila, columna + 3].Style.Fill.PatternType = ExcelFillStyle.Solid; //4
worksheet.Cells[fila, columna + 3].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230)); //4
worksheet.Cells[fila, columna + 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[fila, columna + 4].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230));
worksheet.Cells[fila, columna + 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // 6
worksheet.Cells[fila, columna + 5].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230)); // 6
worksheet.Cells[fila, columna + 6].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[fila, columna + 6].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(231, 230, 230));
}
else if (RowCountBackgroundColor == 20)
{
RowCountBackgroundColor = 0;
}
fila++;
contadorRegistros++;
RowCountBackgroundColor++;
}
}
worksheet.HeaderFooter.OddFooter.RightAlignedText = "Página " + "&p" + "de " + "&N";
FileBytesArray = excelPackage.GetAsByteArray();
}
}
catch (Exception ex)
{
}
return(FileBytesArray);
}
private void ExportToExcel_OnClick(object sender, RoutedEventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog {
FileName = "Trello.xlsx", Filter = "Excel|*.xlsx"
};
if (saveFileDialog.ShowDialog() == true)
{
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Trello");
ExcelNamedStyleXml hyperlinkStyle = worksheet.Workbook.Styles.CreateNamedStyle("Hyperlink");
hyperlinkStyle.Style.Font.UnderLine = true;
hyperlinkStyle.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
ExcelRange headerCells = worksheet.Cells[1, 1, 1, 6];
headerCells.Style.Font.Bold = true;
worksheet.Cells[1, 1].Value = "Mine";
worksheet.Cells[1, 2].Value = "Title";
worksheet.Cells[1, 3].Value = "Url";
worksheet.Cells[1, 4].Value = "Created";
worksheet.Cells[1, 5].Value = "List";
worksheet.Cells[1, 6].Value = "Assigned To";
int index = 2;
foreach (CardViewModel card in MainWindowViewModel.Sort(MainWindowViewModel.Cards))
{
worksheet.Cells[index, 1].Value = card.AssignedToMe ? "Yes" : "No";
worksheet.Cells[index, 2].Value = card.Title;
worksheet.Cells[index, 3].Formula = string.Format("=hyperlink(\"{0}\")", card.Url);
worksheet.Cells[index, 3].StyleName = hyperlinkStyle.Name;
worksheet.Cells[index, 4].Value = card.CreationDate;
worksheet.Cells[index, 5].Value = card.List;
worksheet.Cells[index, 6].Value = card.AssignedTo;
index++;
}
worksheet.Column(4).Style.Numberformat.Format = "mm/dd/yyyy";
worksheet.Cells.AutoFitColumns(5, 115);
worksheet.Column(3).Width = 28.5;
Stream stream;
try
{
stream = File.Create(saveFileDialog.FileName);
}
catch (IOException)
{
stream = null;
}
if (stream == null)
{
try
{
string suffix = Guid.NewGuid().ToString("N").Substring(0, 5);
string fileName = Path.Combine(Path.GetDirectoryName(saveFileDialog.FileName), string.Format("{0}-{1}{2}",
Path.GetFileNameWithoutExtension(saveFileDialog.FileName),
suffix,
Path.GetExtension(saveFileDialog.FileName)));
stream = File.Create(Path.GetDirectoryName(fileName));
}
catch (IOException)
{
stream = null;
}
}
if (stream != null)
{
package.SaveAs(stream);
stream.Close();
}
}
}
}
private static ExcelWorksheet BuildSheetHeader(ExcelPackage package, FieldInfo item, object att)
{
ExcelConfigAttribute excelAtt = att as ExcelConfigAttribute;
ExcelWorksheet workSheet = package.Workbook.Worksheets[excelAtt.SheetName];
if (workSheet == null)
{
var type = item.FieldType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Dictionary <,>))
{
var pkmRef = package.Workbook.Worksheets["Pokemons"];
workSheet = package.Workbook.Worksheets.Add(excelAtt.SheetName, pkmRef);
Type keyType = type.GetGenericArguments()[0];
Type valueType = type.GetGenericArguments()[1];
int pos = 1;
workSheet.Cells[1, 1].Value = excelAtt.SheetName;
workSheet.Cells[2, 1].Value = excelAtt.Description;
foreach (var vtp in valueType.GetProperties())
{
var att1 = vtp.GetCustomAttributes <ExcelConfigAttribute>(true).FirstOrDefault();
int colIndex = (att1 == null ? pos : att1.Position) + COL_OFFSET;
workSheet.Column(colIndex).AutoFit();
workSheet.Cells[4, colIndex].Value = att1 == null ? vtp.Name : att1.Key;
if (att1 != null)
{
workSheet.Cells[4, colIndex].AddComment(att1.Description, "necrobot2");
AddValidationForType(workSheet, vtp, $"{GetCol(colIndex)}5:{GetCol(colIndex)}155");
}
pos++;
}
workSheet.Cells[$"A1:{GetCol(COL_OFFSET + pos)}1"].Merge = true;
workSheet.Cells[$"A2:{GetCol(COL_OFFSET + pos)}2"].Merge = true;
workSheet.Cells[$"A1:{GetCol(COL_OFFSET + pos)}1"].Style.Font.Size = 16;
}
else
{
workSheet = package.Workbook.Worksheets.Add(excelAtt.SheetName);
workSheet.Cells[1, 1].Value = excelAtt.SheetName;
workSheet.Cells[2, 1].Value = excelAtt.Description;
workSheet.Cells[$"A1:C1"].Merge = true;;
workSheet.Cells[$"A2:C2"].Merge = true;;
workSheet.Cells["A1:C1"].Style.Font.Size = 16;
workSheet.Row(1).CustomHeight = true;
workSheet.Row(1).Height = 30;
workSheet.Cells["A1:C1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
workSheet.Cells["A1:C1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Justify;
workSheet.Cells[4, 1].Value = "Key";
workSheet.Cells[4, 2].Value = "Value";
workSheet.Cells[4, 3].Value = "Description";
}
workSheet.Row(4).Style.Font.Bold = true;
}
return(workSheet);
}
public static void ExportToExcelFileEPPlus(string _sTitle, DataGridView _DGV)
{
int iColCount = _DGV.Columns.Count;
int iRowCount = _DGV.Rows.Count;
using (ExcelPackage ExcelPkg = new ExcelPackage())
{
try
{
ExcelWorksheet Sheet = ExcelPkg.Workbook.Worksheets.Add("sheet1");
//Title Row
Sheet.Cells["A1"].Value = _sTitle;
ExcelRange Range = Sheet.Cells[1, 1, 1, iColCount];
Range.Merge = true;
Range.Style.Font.Size = 14;
Range.Style.Font.Bold = true;
Range.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
Range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
//Name Row
for (int i = 0; i < iColCount; i++)
{
Sheet.Cells[2, i + 1].Value = _DGV.Columns[i].HeaderText;
Sheet.Cells[2, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
Range = Sheet.Cells[2, 1, 2, iColCount];
Range.Style.Font.Size = 12;
Range.Style.Font.Bold = true;
Range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//Value Row
for (int i = 0; i < iRowCount; i++)
{
for (int j = 0; j < iColCount; j++)
{
Sheet.Cells[i + 3, j + 1].Value = _DGV.Rows[i].Cells[j].Value.ToString();
}
Sheet.Cells[i + 3, 1, i + 3, iColCount].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
Sheet.Cells[i + 3, 1, i + 3, iColCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
//Column Line
for (int i = 0; i < iColCount; i++)
{
Sheet.Column(i + 1).AutoFit(5);
Sheet.Cells[1, i + 1, iRowCount + 2, iColCount].Style.Border.Right.Style = ExcelBorderStyle.Thin;
}
Sheet.Cells.Style.WrapText = true;
//BorderAround Line
Range = Sheet.Cells[1, 1, iRowCount + 2, iColCount];
Range.Style.Border.BorderAround(ExcelBorderStyle.Medium);
//Encrypt
Sheet.Protection.IsProtected = true;
Sheet.Protection.SetPassword("verybull");
Sheet.Protection.AllowAutoFilter = true;
Sheet.Protection.AllowSort = true;
Sheet.Protection.AllowFormatCells = true;
Sheet.Protection.AllowFormatColumns = true;
Sheet.Protection.AllowFormatRows = true;
Sheet.Protection.AllowSelectUnlockedCells = true;
Sheet.Protection.AllowSelectLockedCells = true;
Sheet.Protection.AllowDeleteColumns = false;
Sheet.Protection.AllowDeleteRows = false;
Sheet.Protection.AllowEditObject = false;
Sheet.Protection.AllowEditScenarios = false;
Sheet.Protection.AllowInsertColumns = false;
Sheet.Protection.AllowInsertHyperlinks = false;
Sheet.Protection.AllowInsertRows = false;
Sheet.Protection.AllowPivotTables = false;
//Save
SaveFileDialog SaveDlg = new SaveFileDialog();
SaveDlg.AddExtension = true;
SaveDlg.DefaultExt = "xlsx";
SaveDlg.Filter = "Excel Worksheets(*.xlsx)|*.xlsx";
SaveDlg.InitialDirectory = System.Environment.CurrentDirectory;
SaveDlg.ShowDialog();
if (!SaveDlg.FileName.Equals(""))
{
System.IO.Stream stream = SaveDlg.OpenFile();
ExcelPkg.SaveAs(stream);
stream.Close();
Dlg.ShowOKInfo("导出报表文件成功！");
}
}
catch (Exception Ex)
{
Dlg.ShowErrorInfo(string.Format("异常:{0}", Ex.Message));
}
}
}
public string Ranks()
{
string file_out = "Выполнение разрядных норм " + DateTime.Today.ToShortDateString() + ".xlsx";
string file_in = "разрядные нормы.xlsx";
FileInfo newFile = Utils.GetFileInfo(file_out);
FileInfo templateFile = new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}шаблоны" + Path.DirectorySeparatorChar + file_in); //Utils.GetFileInfo(file_in, false);
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.Name = "разрядные нормы";
package.Workbook.Properties.Author = "Тавлея";
package.Workbook.Properties.LastModifiedBy = "Тавлея";
worksheet.Cells["A2"].Value = tvlData.tournir_name;
worksheet.Cells["A3"].Value = tvlData.tournir_start_date + " - " + tvlData.tournir_finish_date + " " + tvlData.tournir_place;
for (int i = 4; i < tvlData.players.Count - 2 + 4; i++)//add columns for opponents
{
worksheet.InsertColumn(i, 1, i - 1);
worksheet.Cells[5, i].Value = i - 2;
}
for (int i = 3; i < tvlData.players.Count - 1 + 4; i++)//set optimal columns width
{
worksheet.Column(i).Width = 65 / tvlData.players.Count;
}
worksheet.Cells[4, 3, 4, tvlData.players.Count + 1].Merge = true;//merge opponents header
int up_shift = 5;
worksheet.InsertRow(up_shift + 2, tvlData.players.Count - 1, up_shift + 1);//insert frmatted rows for players
for (int pl_num = 0; pl_num < tvlData.players.Count; pl_num++)
{
player pl = tvlData.players[pl_num];
worksheet.Cells[pl_num + 1 + up_shift, 1].Value = pl.id;
worksheet.Cells[pl_num + 1 + up_shift, 2].Value = pl.fio();
for (int i = 3; i < 3 + tvlData.players.Count - 1; i++)//results (points)
{
worksheet.Cells[pl_num + 1 + up_shift, i].Value = tvlData.players[MainForm.search_by_id(pl.opponents[i - 3])].rank;
}
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 3].Value = pl.rank_INDEX; //необх кт
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 4].Value = pl.rank_coeft; //турн кт
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 5].Value = pl.point_norm; //норма очков
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 6].Value = pl.points; // очки
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 7].Value = pl.rank; //разр до
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 8].Value = pl.new_rank; //разр после
if (pl.rank_UP == 1)
{
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 8].Style.Font.Color.SetColor(System.Drawing.Color.Green);
}
if (pl.rank_UP == -1)
{
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count - 1 + 8].Style.Font.Color.SetColor(System.Drawing.Color.Red);
}
}
worksheet.Cells[tvlData.players.Count + up_shift + 3, 5].Value = tvlData.tournir_main_judge;
worksheet.Cells[tvlData.players.Count + up_shift + 5, 5].Value = tvlData.tournir_secretary;
// save our new workbook and we are done!
package.Save();
Process.Start(Utils.OutputDir.FullName + Path.DirectorySeparatorChar + file_out);
}
return(newFile.FullName);
}
private void Swiss_report()
{
if (MainForm.st == null)
{
MessageBox.Show("Сначала восстановите игру", "Внимание");
return;
}
if (MainForm.st.finish != true)
{
MessageBox.Show("Турнир не завершен", "Внимание");
return;
}
string file_out = "Турнир " + DateTime.Today.ToShortDateString() + " (швейцарская).xlsx";
string file_in = "швейцарская.xlsx";
FileInfo newFile = Utils.GetFileInfo(file_out);
FileInfo templateFile = new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}шаблоны" + Path.DirectorySeparatorChar + file_in);
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.Name = "Тавлея (швейцарская)";
package.Workbook.Properties.Author = "Тавлея";
package.Workbook.Properties.LastModifiedBy = "Тавлея";
worksheet.Cells["A2"].Value = tvlData.tournir_name;
worksheet.Cells["A3"].Value = tvlData.tournir_start_date + " - " + tvlData.tournir_finish_date + " " + tvlData.tournir_place;
for (int i = 8; i < MainForm.st.tourscount - 1 + 8; i++)//add columns for tours
{
worksheet.InsertColumn(i, 1, i - 1);
worksheet.Cells[5, i].Value = i - 6;
worksheet.Column(i).Width = 34 / MainForm.st.tourscount;//set optimal columns width
}
for (int i = 7; i < MainForm.st.tourscount - 1 + 8; i++)
{
worksheet.Column(i).Width = 34 / MainForm.st.tourscount; //set optimal columns width
}
worksheet.Cells[4, 7, 4, MainForm.st.tourscount + 6].Merge = true; //merge tours header
int up_shift = 5;
worksheet.InsertRow(up_shift + 2, tvlData.players.Count - 1, up_shift + 1);//insert frmatted rows for players
for (int pl_num = 0; pl_num < tvlData.players.Count; pl_num++)
{
player pl = tvlData.players[pl_num];
worksheet.Cells[pl_num + 1 + up_shift, 1].Value = pl.id;
worksheet.Cells[pl_num + 1 + up_shift, 2].Value = (pl.surname + " " + pl.name);
worksheet.Cells[pl_num + 1 + up_shift, 3].Value = pl.city;
worksheet.Cells[pl_num + 1 + up_shift, 4].Value = pl.year;
worksheet.Cells[pl_num + 1 + up_shift, 5].Value = pl.rank;
worksheet.Cells[pl_num + 1 + up_shift, 6].Value = pl.Adam;
for (int i = 7; i < 7 + MainForm.st.tourscount; i++)//results (points)
{
foreach (int g in pl.games)
{
if ((tvlData.games[g - 1].first == pl.id) && (tvlData.games[g - 1].round == i - 6))
{
worksheet.Cells[pl_num + 1 + up_shift, i].Value = tvlData.games[g - 1].pt1 + "(" + tvlData.games[g - 1].second + ")";
}
if ((tvlData.games[g - 1].second == pl.id) && (tvlData.games[g - 1].round == i - 6))
{
worksheet.Cells[pl_num + 1 + up_shift, i].Value = tvlData.games[g - 1].pt2 + "(" + tvlData.games[g - 1].first + ")";
}
}
}
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 7].Value = pl.points;
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 8].Value = pl.coef[1];
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 9].Value = pl.coef[5];
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 10].Value = pl.coef[2];
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 11].Value = pl.coef[3];
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 12].Value = pl.Adam_new;
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 13].Value = (pl.Adam_new - pl.Adam);
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 14].Value = pl.place;
worksheet.Cells[pl_num + 1 + up_shift, MainForm.st.tourscount + 15].Value = pl.new_rank;
}
worksheet.Cells[tvlData.players.Count + up_shift + 3, 5].Value = tvlData.tournir_main_judge;
worksheet.Cells[tvlData.players.Count + up_shift + 5, 5].Value = tvlData.tournir_secretary;
// save our new workbook and we are done!
package.Save();
Process.Start(Utils.OutputDir.FullName + Path.DirectorySeparatorChar + file_out);
}
}
private void Round_report2()
{
string file_out = "Турнир " + DateTime.Today.ToShortDateString() + " (КРУГОВАЯ).xlsx";
string file_in = "круговая2.xlsx";
FileInfo newFile = Utils.GetFileInfo(file_out);
FileInfo templateFile = new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}шаблоны" + Path.DirectorySeparatorChar + file_in); //Utils.GetFileInfo(file_in, false);
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.Name = "Тавлея (круговая)";
package.Workbook.Properties.Author = "Тавлея";
package.Workbook.Properties.LastModifiedBy = "Тавлея";
worksheet.Cells["A2"].Value = tvlData.tournir_name;
worksheet.Cells["A3"].Value = tvlData.tournir_start_date + " - " + tvlData.tournir_finish_date + " " + tvlData.tournir_place;
for (int i = 6; i < tvlData.players.Count - 1 + 6; i++)//add columns for opponents
{
worksheet.InsertColumn(i, 1, i - 1);
worksheet.Cells[5, i].Value = i - 4;
}
for (int i = 5; i < tvlData.players.Count - 1 + 6; i++)//set optimal columns width
{
worksheet.Column(i).Width = 65 / tvlData.players.Count;
}
worksheet.Cells[4, 5, 4, tvlData.players.Count + 4].Merge = true;//merge opponents header
int up_shift = 5;
worksheet.InsertRow(up_shift + 2, tvlData.players.Count - 1, up_shift + 1);//insert frmatted rows for players
for (int pl_num = 0; pl_num < tvlData.players.Count; pl_num++)
{
player pl = tvlData.players[pl_num];
worksheet.Cells[pl_num + 1 + up_shift, 1].Value = pl.id;
worksheet.Cells[pl_num + 1 + up_shift, 2].Value = pl.fio();
worksheet.Cells[pl_num + 1 + up_shift, 3].Value = pl.year;
worksheet.Cells[pl_num + 1 + up_shift, 4].Value = pl.rank;
for (int i = 5; i < 5 + tvlData.players.Count; i++)//results (points)
{
foreach (int g in pl.games)
{
if ((tvlData.games[g - 1].first == pl.id) && (tvlData.games[g - 1].second == i - 4))
{
worksheet.Cells[pl_num + 1 + up_shift, i].Value = tvlData.games[g - 1].pt1;
}
if ((tvlData.games[g - 1].second == pl.id) && (tvlData.games[g - 1].first == i - 4))
{
worksheet.Cells[pl_num + 1 + up_shift, i].Value = tvlData.games[g - 1].pt2;
}
}
}
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count + 5].Value = pl.points;
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count + 6].Value = pl.coef[2];
worksheet.Cells[pl_num + 1 + up_shift, tvlData.players.Count + 7].Value = pl.place;
}
worksheet.Cells[tvlData.players.Count + up_shift + 3, 5].Value = tvlData.tournir_main_judge;
worksheet.Cells[tvlData.players.Count + up_shift + 5, 5].Value = tvlData.tournir_secretary;
// save our new workbook and we are done!
package.Save();
Process.Start(Utils.OutputDir.FullName + Path.DirectorySeparatorChar + file_out);
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column=ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while(column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if(cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
public byte[] CreateExcelPackage(List <AppUserExcelGeneralReportDto> reports)
{
using (var ExcelPkg = new ExcelPackage())
{
ExcelWorksheet workSheet = ExcelPkg.Workbook.Worksheets.Add("Users");
workSheet.TabColor = System.Drawing.Color.Red;
workSheet.DefaultRowHeight = 12;
var modelCells = workSheet.Cells["A1"];
var modelRows = reports.Count() + 1;
string modelRange = "A1:F" + modelRows.ToString();
var modelTable = workSheet.Cells[modelRange];
// Assign borders
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Top.Color.SetColor(Color.Blue);
modelTable.Style.Border.Bottom.Color.SetColor(Color.Blue);
modelTable.Style.Border.Left.Color.SetColor(Color.Blue);
modelTable.Style.Border.Right.Color.SetColor(Color.Blue);
modelTable.AutoFitColumns();
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#4E73DF");
workSheet.Cells["A1:F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells["A1:F1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
workSheet.Cells["A1:F1"].Style.Font.Color.SetColor(Color.White);
workSheet.Row(1).Height = 20;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Cells[1, 1].Value = "Id";
workSheet.Cells[1, 2].Value = "User Name";
workSheet.Cells[1, 3].Value = "Count";
workSheet.Cells[1, 4].Value = "AssigmentCount";
workSheet.Cells[1, 5].Value = "CompletedCount";
workSheet.Cells[1, 6].Value = "DissolvedCount";
var rowCounter = 2;
foreach (var v in reports)
{
workSheet.Cells[rowCounter, 1].Value = v.Id;
workSheet.Cells[rowCounter, 2].Value = v.UserName;
workSheet.Cells[rowCounter, 3].Value = v.Count;
workSheet.Cells[rowCounter, 4].Value = v.AssigmentCount;
workSheet.Cells[rowCounter, 5].Value = v.CompletedCount;
workSheet.Cells[rowCounter, 6].Value = v.DissolvedCount;
rowCounter++;
}
workSheet.Column(1).AutoFit();
workSheet.Column(2).AutoFit();
workSheet.Column(3).AutoFit();
workSheet.Column(4).AutoFit();
workSheet.Column(5).AutoFit();
workSheet.Column(6).AutoFit();
ExcelPkg.Workbook.Properties.Title = "UsersStatus";
return(ExcelPkg.GetAsByteArray());
}
}
private void writeToExcelTT(FileInfo newFile)
{
// EPPlus library is required
ExcelPackage package = new ExcelPackage(newFile);
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inv.TT");
// Set column width
worksheet.Column(1).Width = 4;
worksheet.Column(2).Width = 12;
worksheet.Column(3).Width = 0.8;
worksheet.Column(4).Width = 19;
worksheet.Column(5).Width = 0.8;
worksheet.Column(6).Width = 19;
worksheet.Column(7).Width = 11;
worksheet.Column(8).Width = 12;
worksheet.Column(9).Width = 0.8;
worksheet.Column(10).Width = 25;
MaintainExaminationControl examControl = new MaintainExaminationControl();
MaintainInvigilationDutyControl invControl = new MaintainInvigilationDutyControl();
MaintainStaffControl staffControl = new MaintainStaffControl();
MaintainCourseControl courseControl = new MaintainCourseControl();
//Get lists of examination, timeslot and invigilationDuty
List <string> timeslotList = examControl.getTimeslot();
List <Examination> examList = null;
List <InvigilationDuty> invDutyList = null;
//To keep track of the column number for A, B, D, F, G, H, J
int[] columnNo = new int[7] {
2, 2, 2, 2, 2, 2, 2
};
//Loop through examList and invDutyList with each timeslotID in timeslotList
for (int y = 0; y < timeslotList.Count; y++)
{
//Get new list for every iteration with different timeslotID passed in
examList = examControl.searchExamination(timeslotList[y]);
invDutyList = invControl.searchInvigilationDuty2(timeslotList[y]);
//Get date in string
int year = Convert.ToInt32("20" + timeslotList[y].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[y].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[y].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//Get session in string
string session = timeslotList[y].Substring(0, 2);
if (session.Equals("AM"))
{
session = "MORNING SESSION";
}
else if (session.Equals("PM"))
{
session = "AFTERNOON SESSION";
}
else
{
session = "EVENING SESSION";
}
// Add data into cells, Date and Sessiom
worksheet.Cells["A" + columnNo[0].ToString()].Value = dt.DayOfWeek.ToString().ToUpper() + ", " + dt.Day + " " + dt.ToString("MMMM").ToUpper() + " " + dt.Year;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["A" + columnNo[0].ToString()].Value = session;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// New row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 0)
{
columnNo[a] = columnNo[0] + 1;
}
}
columnNo[0]++;
// Add data into cells, Chief and Relief Invigilator for West Campus
worksheet.Cells["B" + columnNo[1].ToString()].Value = "West Campus";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
worksheet.Cells["H" + columnNo[5].ToString()].Value = "West Campus";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";
// New row
for (int a = 0; a < columnNo.Length; a++)
{
columnNo[a] += 1;
}
//print Chief and Relief title
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";
//print Chief and Relief invigilators for West Campus
for (int i = 0; i < invDutyList.Count; i++)
{
Staff staff = staffControl.getStaffName(invDutyList[i].StaffID);
string staffName = staff.Title + " " + staff.Name;
if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && !invDutyList[i].Location.Equals("Block SA") && !invDutyList[i].Location.Equals("Block SB") && !invDutyList[i].Location.Equals("Block SC") && !invDutyList[i].Location.Equals("Block SD") && !invDutyList[i].Location.Equals("Block SE") && !invDutyList[i].Location.Equals("Block SF") && !invDutyList[i].Location.Equals("Block SG"))
{
worksheet.Cells["D" + columnNo[2].ToString()].Value = "(" + invDutyList[i].Location + ")";
worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
}
else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("West Campus"))
{
worksheet.Cells["I" + columnNo[6].ToString()].Value = "-";
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 2;
}
}
columnNo[6] += 2;
// Add data into cells, Chief and Relief Invigilator for East Campus
worksheet.Cells["B" + columnNo[1].ToString()].Value = "East Campus";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = "";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = "";
worksheet.Cells["G" + columnNo[4]++.ToString()].Value = "";
worksheet.Cells["H" + columnNo[5].ToString()].Value = "East Campus";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.Font.UnderLine = true;
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = "";
// New row
for (int a = 0; a < columnNo.Length; a++)
{
columnNo[a] += 1;
}
//print Chief and Relief title
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Chief";
worksheet.Cells["B" + columnNo[1]++.ToString()].Value = "Invigilators";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Relief";
worksheet.Cells["H" + columnNo[5]++.ToString()].Value = "Invigilators";
//print Chief and Relief invigilators for East Campus
for (int i = 0; i < invDutyList.Count; i++)
{
Staff staff = staffControl.getStaffName(invDutyList[i].StaffID);
string staffName = staff.Title + " " + staff.Name;
if (invDutyList[i].CategoryOfInvigilator.Equals("Chief") && (invDutyList[i].Location.Equals("Block SA") || invDutyList[i].Location.Equals("Block SB") || invDutyList[i].Location.Equals("Block SC") || invDutyList[i].Location.Equals("Block SD") || invDutyList[i].Location.Equals("Block SE") || invDutyList[i].Location.Equals("Block SF") || invDutyList[i].Location.Equals("Block SG")))
{
worksheet.Cells["D" + columnNo[2].ToString()].Value = "(" + invDutyList[i].Location + ")";
worksheet.Cells["E" + columnNo[2]++.ToString()].Value = "-";
worksheet.Cells["F" + columnNo[3]++.ToString()].Value = staffName;
}
else if (invDutyList[i].CategoryOfInvigilator.Equals("Relief") && invDutyList[i].Location.Equals("East Campus"))
{
worksheet.Cells["I" + columnNo[6].ToString()].Value = "-";
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
// New row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 2;
}
}
columnNo[6] += 2;
//print header
worksheet.Cells["A" + columnNo[0].ToString()].Value = "Venue";
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0].ToString()].Merge = true;
worksheet.Cells["A" + columnNo[0].ToString() + ":" + "B" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["D" + columnNo[2].ToString()].Value = "Paper";
worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2].ToString()].Merge = true;
worksheet.Cells["D" + columnNo[2].ToString() + ":" + "F" + columnNo[2]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["G" + columnNo[4].ToString()].Value = "Programme";
worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["H" + columnNo[5].ToString()].Value = "Duration";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["H" + columnNo[5].ToString()].Value = "(Hours)";
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["J" + columnNo[6].ToString()].Value = "Invigilators";
worksheet.Cells["J" + columnNo[6]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//temp var used to store cell index for adding border
int rowFrom = 0;
//set top and bottom border for header
using (var range = worksheet.Cells["A" + (columnNo[0] - 2).ToString() + ":" + "J" + (columnNo[0] - 2).ToString()])
{
range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rowFrom = columnNo[0] - 2;
}
using (var range = worksheet.Cells["A" + columnNo[0].ToString() + ":" + "J" + columnNo[0].ToString()])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 5)
{
columnNo[a] = columnNo[5] + 1;
}
}
columnNo[5]++;
string venueID = "";
string staffID = "";
int examCount = 0;
int invCount = 0;
//print examinations and invigilators
for (int i = 0; i < examList.Count(); i++)
{
//To avoid same repeating VenueID
if (!venueID.Equals(examList[i].VenueID))
{
if (invCount > examCount)
{
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 6)
{
columnNo[a] = columnNo[6] + 1;
}
}
columnNo[6]++;
}
else
{
//new row
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 1)
{
columnNo[a] = columnNo[1] + 1;
}
}
columnNo[1]++;
}
examCount = 0;
invCount = 0;
venueID = examList[i].VenueID;
worksheet.Cells["A" + columnNo[0].ToString()].Value = venueID;
worksheet.Cells["A" + columnNo[0]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
examCount++;
worksheet.Cells["B" + columnNo[1].ToString()].Value = "(" + examList[i].SitFrom + "-" + examList[i].SitTo + ")";
worksheet.Cells["B" + columnNo[1]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["D" + columnNo[2]++.ToString()].Value = examList[i].CourseCode + " " + courseControl.getCourseTitle(examList[i].CourseCode);
if (examList[i].PaperType == 'M')
{
worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year;
}
else
{
worksheet.Cells["G" + columnNo[4].ToString()].Value = examList[i].PaperType + "(" + examList[i].ExamType + examList[i].ProgrammeCode + examList[i].Year + ")";
}
worksheet.Cells["G" + columnNo[4]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//Find duration in invDutyList with the same venueID
var value = invDutyList.First(x => x.VenueID.Equals(examList[i].VenueID));
worksheet.Cells["H" + columnNo[5].ToString()].Value = value.Duration;
worksheet.Cells["H" + columnNo[5]++.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//Get the first staffID provided with timeslotID and venueID, to prevent printing repeated invigilator for same venueID
var v = invDutyList.First(x => x.TimeslotID.Equals(timeslotList[y]) && x.VenueID.Equals(venueID));
staffID = v.StaffID;
//Print invigilator
for (int b = 0; b < invDutyList.Count; b++)
{
if (invDutyList[b].TimeslotID.Equals(timeslotList[y]) && invDutyList[b].VenueID.Equals(examList[i].VenueID))
{
//Avoid printing repeated invigilator for same venueID
if (!staffID.Equals(invDutyList[b].StaffID) || invCount == 0)
{
invCount++;
Staff staff = staffControl.getStaffName(invDutyList[b].StaffID);
string staffName = staff.Title + " " + staff.Name;
//check for in-charge
if (invDutyList[b].CategoryOfInvigilator.Equals("In-charge"))
{
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName + "*";
}
else
{
worksheet.Cells["J" + columnNo[6]++.ToString()].Value = staffName;
}
}
else
{
break;
}
}
}
}
//set border
using (var range = worksheet.Cells["A" + columnNo[1].ToString() + ":" + "J" + columnNo[1].ToString()])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["B" + rowFrom.ToString() + ":" + "B" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["F" + rowFrom.ToString() + ":" + "F" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["G" + rowFrom.ToString() + ":" + "G" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
using (var range = worksheet.Cells["H" + rowFrom.ToString() + ":" + "H" + columnNo[1].ToString()])
{
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
//leaves 20 rows between session
for (int a = 0; a < columnNo.Length; a++)
{
if (a != 1)
{
columnNo[a] = columnNo[1] + 20;
}
}
columnNo[1] += 20;
}
// save new workbook
package.Save();
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
public static void ExcelSave(ExportType exportType, EncounterBase data, TeraData teraData)
{
if ((exportType & (ExportType.Excel | ExportType.ExcelTemp)) == 0)
{
return;
}
lock (Savelock) //can't save 2 excel files at one time
{
NpcInfo boss = teraData.NpcDatabase.GetOrPlaceholder(ushort.Parse(data.areaId), uint.Parse(data.bossId));
var dir =
Path.Combine(
exportType.HasFlag(ExportType.Excel)//if regular export, save in documents, otherwise temp
? SettingsHelper.Instance.GetDocumentsPath()
: SettingsHelper.Instance.GetTempFolderPath(), $"exports/{boss.Area.Replace(":", "-")}");
Directory.CreateDirectory(dir);
var fname = Path.Combine(dir, $"{boss.Name.Replace(":", "-")} {DateTimeTools.UnixTimeStampToDateTime(data.encounterUnixEpoch).ToString("yyyy-MM-dd HH-mm-ss", CultureInfo.InvariantCulture)}.xlsx");
FileInfo file = new FileInfo(fname);
if (!file.Exists)
{
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add("Boss");
var linkStyle = package.Workbook.Styles.CreateNamedStyle("HyperLink");
//This one is language dependent
linkStyle.Style.Font.UnderLine = true;
linkStyle.Style.Font.Color.SetColor(Color.Blue);
ws.DefaultRowHeight = 30;
ws.Cells.Style.Font.Size = 14;
ws.Cells.Style.Font.Name = "Arial";
ws.Cells[1, 1].Value =
$"{boss.Area}: {boss.Name} {TimeSpan.FromSeconds(double.Parse(data.fightDuration)).ToString(@"mm\:ss")}";
ws.Cells[1, 1, 1, 6].Merge = true;
ws.Cells[1, 1, 1, 8].Style.Font.Bold = true;
ws.Cells[1, 7].Value = long.Parse(data.partyDps);
ws.Cells[1, 7].Style.Numberformat.Format = @"#,#00,\k\/\s";
ws.Cells[2, 1].Value = "Ic";
ws.Cells[2, 1].Style.Font.Color.SetColor(Color.Transparent);
ws.Cells[2, 2].Value = "Name";
ws.Cells[2, 3].Value = "Deaths";
ws.Cells[2, 4].Value = "Death time";
ws.Cells[2, 5].Value = "Damage %";
ws.Cells[2, 6].Value = "Crit %";
ws.Cells[2, 7].Value = "DPS";
ws.Cells[2, 8].Value = "Damage";
int i = 2;
foreach (var user in data.members.OrderByDescending(x => long.Parse(x.playerTotalDamage)))
{
i++;
ws.Cells[i, 1].Value = i - 2;
AddImage(ws, i, 1,
Invert(
new Bitmap(
SettingsHelper.Instance.GetImage(
(PlayerClass)Enum.Parse(typeof(PlayerClass), user.playerClass)))));
ws.Cells[i, 2].Value = $"{user.playerServer}: {user.playerName}";
ws.Cells[i, 2].Hyperlink = CreateUserSheet(package.Workbook, user, teraData);
ws.Cells[i, 3].Value = long.Parse(user.playerDeaths);
ws.Cells[i, 4].Value = long.Parse(user.playerDeathDuration);
ws.Cells[i, 4].Style.Numberformat.Format = @"0\s";
ws.Cells[i, 5].Value = double.Parse(user.playerTotalDamagePercentage) / 100;
ws.Cells[i, 5].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 6].Value = double.Parse(user.playerAverageCritRate) / 100;
ws.Cells[i, 6].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 7].Value = long.Parse(user.playerDps);
ws.Cells[i, 7].Style.Numberformat.Format = @"#,#0,\k\/\s";
ws.Cells[i, 8].Value = long.Parse(user.playerTotalDamage);
ws.Cells[i, 8].Style.Numberformat.Format = @"#,#0,\k";
}
ws.Cells[1, 8].Formula = $"SUM(H3:H{i})";
ws.Cells[1, 8].Style.Numberformat.Format = @"#,#0,\k";
var border = ws.Cells[1, 1, i, 8].Style.Border;
border.Bottom.Style =
border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
ws.Cells[2, 1, i, 8].AutoFilter = true;
int j = i + 3;
ws.Cells[j, 1].Value = "Ic";
ws.Cells[j, 1].Style.Font.Color.SetColor(Color.Transparent);
ws.Cells[j, 2].Value = "Debuff name";
ws.Cells[j, 2, j, 7].Merge = true;
ws.Cells[j, 8].Value = "%";
ws.Cells[j, 2, j, 8].Style.Font.Bold = true;
foreach (var buf in data.debuffUptime)
{
j++;
var hotdot = teraData.HotDotDatabase.Get(int.Parse(buf.Key));
ws.Cells[j, 1].Value = j - i - 3;
AddImage(ws, j, 1, BTD.Icons.GetBitmap(hotdot.IconName));
ws.Cells[j, 2].Value = hotdot.Name;
if (!string.IsNullOrEmpty(hotdot.Tooltip))
{
ws.Cells[j, 2].AddComment("" + hotdot.Tooltip, "info");
}
ws.Cells[j, 2, j, 7].Merge = true;
ws.Cells[j, 8].Value = double.Parse(buf.Value) / 100;
ws.Cells[j, 8].Style.Numberformat.Format = "0%";
}
border = ws.Cells[i + 3, 1, j, 8].Style.Border;
border.Bottom.Style =
border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
ws.Column(1).Width = 5.6;
ws.Column(2).AutoFit();
ws.Column(3).AutoFit();
ws.Column(4).AutoFit();
ws.Column(5).AutoFit();
ws.Column(6).AutoFit();
ws.Column(7).AutoFit();
ws.Column(8).Width = 17;
ws.Column(2).Width = GetTrueColumnWidth(ws.Column(2).Width);
ws.Column(3).Width = GetTrueColumnWidth(ws.Column(3).Width);
ws.Column(4).Width = GetTrueColumnWidth(ws.Column(4).Width);
ws.Column(5).Width = GetTrueColumnWidth(ws.Column(5).Width);
ws.Column(6).Width = GetTrueColumnWidth(ws.Column(6).Width);
ws.Column(7).Width = GetTrueColumnWidth(ws.Column(7).Width);
ws.Cells[1, 1, j, 8].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[1, 1, j, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.PrinterSettings.FitToPage = true;
package.Workbook.Properties.Title = boss.Name;
package.Workbook.Properties.Author = "CasualMeter";
package.Workbook.Properties.Company = "github.com/lunyx github.com/Gl0 github.com/neowutran";
package.Save();
}
}
if (exportType == ExportType.ExcelTemp && File.Exists(fname))
{
Process.Start(fname);
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
if (String.IsNullOrWhiteSpace(ddlQuestion1.SelectedValue) ||
String.IsNullOrWhiteSpace(ddlQuestion1.SelectedValue))
{
TopMessage.ErrorMessage = "Please select two questions to generate cross-tabs for.";
return;
}
else if (ddlQuestion1.SelectedValue == ddlQuestion2.SelectedValue)
{
TopMessage.ErrorMessage = "Please select two <b>separate</b> questions to generate cross-tabs for.";
return;
}
SQLParamList sqlParams = new SQLParamList()
.Add("@Question1", ddlQuestion1.SelectedValue)
.Add("@Question2", ddlQuestion2.SelectedValue);
DataSet ds = sql.QueryDataSet(@"
SELECT [SurveyTypeID]
,[ColumnName]
,[LongLabel]
,[ShortLabel]
,[AnswerTypeID]
,[AvailableOnCrossTab]
,[HasNA]
,[SortOrder]
FROM [tblSurveyQuestions]
WHERE [SurveyTypeID] = 1
AND [ColumnName] IN (@Question1, @Question2);
SELECT [ColumnName]
,[DBValue]
,[Label]
,[SortOrder]
FROM [GCC].[dbo].[tblSurveyQuestions_CustomAnswers]
WHERE [SurveyTypeID] = 1
AND [ColumnName] IN (@Question1, @Question2)
ORDER BY [ColumnName], [SortOrder];", sqlParams);
if (sql.HasError || ds.Tables.Count != 2 || ds.Tables[0].Rows.Count != 2)
{
TopMessage.ErrorMessage = "Unable to load question information from the database.";
return;
}
DataRow drQ1, drQ2;
if (ds.Tables[0].Rows[0]["ColumnName"].Equals(ddlQuestion1.SelectedValue))
{
drQ1 = ds.Tables[0].Rows[0];
drQ2 = ds.Tables[0].Rows[1];
}
else
{
drQ1 = ds.Tables[0].Rows[1];
drQ2 = ds.Tables[0].Rows[0];
}
List <Answer> q1Answers = GetAnswers(drQ1, ds.Tables[1]),
q2Answers = GetAnswers(drQ2, ds.Tables[1]);
StringBuilder sbRowBase = new StringBuilder();
StringBuilder sbGroupCols = new StringBuilder();
bool headerNull = false;
foreach (Answer a in q1Answers)
{
if (a.DBValue != "NULL")
{
sbRowBase.AppendFormat("'{0}',", a.DBValue.Replace("'", "''"));
sbGroupCols.AppendFormat("SUM( CASE WHEN {{0}} = '{0}' THEN 1 ELSE 0 END ) AS '{1}',\n", a.DBValue.Replace("'", "''"), a.Label.Replace("'", "''"));
}
else
{
headerNull = true;
sbGroupCols.AppendFormat("SUM( CASE WHEN {{0}} IS NULL THEN 1 ELSE 0 END ) AS '{1}',\n", a.DBValue.Replace("'", "''"), a.Label.Replace("'", "''"));
}
}
if (sbRowBase.Length > 0)
{
sbRowBase.Remove(sbRowBase.Length - 1, 1);
}
StringBuilder sbRows = new StringBuilder();
bool rowNull = false;
foreach (Answer a in q2Answers)
{
if (a.DBValue != "NULL")
{
sbRows.AppendFormat("UNION ALL SELECT '{0}' [Val], '{1}' [Label], {2} [Sort]\n",
a.DBValue.ToString().Replace("'", "''"),
a.Label.ToString().Replace("'", "''"),
a.Sort);
}
else
{
rowNull = true;
sbRows.AppendFormat("UNION ALL SELECT NULL [Val], '{0}' [Label], {1} [Sort]\n",
a.Label.ToString().Replace("'", "''"),
a.Sort);
}
}
string query = String.Format(@"
;WITH grp AS (
SELECT CONVERT(varchar(1000), {0}) AS [ColVal],
SUM( CASE WHEN {1} IN ( {2} ){5} THEN 1 ELSE 0 END ) AS [RowBase],
{3}
GROUPING( {0} ) AS [IsBase]
FROM [tblSurveyGEI] d
WHERE [DateEntered] BETWEEN @BeginDate AND @EndDate
{6}
GROUP BY {0}
WITH ROLLUP
)
SELECT x.Label, grp.*
FROM (
SELECT 'Base' [Val], 'Base' [Label], -10 [Sort]
{4}
) x
LEFT JOIN grp
ON x.Val = grp.ColVal
OR ( x.Sort = -10 AND grp.IsBase = 1 )
OR ( grp.IsBase = 0 AND x.Val IS NULL AND grp.ColVal IS NULL )
ORDER BY x.Sort
",
"[" + drQ2["ColumnName"].ToString() + "]",
"[" + drQ1["ColumnName"].ToString() + "]",
sbRowBase.ToString(),
String.Format(sbGroupCols.ToString(), "[" + drQ1["ColumnName"].ToString() + "]"),
sbRows.ToString(),
(headerNull ? " OR [" + drQ1["ColumnName"].ToString() + "] IS NULL " : String.Empty),
(!headerNull && !rowNull ? "AND [" + drQ2["ColumnName"].ToString() + "] IS NOT NULL" : String.Empty)
);
sqlParams = new SQLParamList()
.Add("@BeginDate", drDateRange.BeginDate)
.Add("@EndDate", drDateRange.EndDate);
DataTable crossTab = sql.QueryDataTable(query, sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "There was an error querying the cross tab data from the database.";
return;
}
Data = crossTab;
HeaderRow = drQ1;
HeaderAnswers = q1Answers;
RowRow = drQ2;
RowAnswers = q2Answers;
if (ddlExport.SelectedIndex == 1)
{
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Worksheets.Add("CrossTabs");
ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
worksheet.Cells.Style.Font.Size = 11; //Default font size for whole sheet
worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
worksheet.Column(1).Width *= 5;
worksheet.Cells[1, 1].Value = "Start Date: " + drDateRange.BeginDate.Value.ToString("yyyy-MM-dd");
worksheet.Cells[2, 1].Value = "End Date: " + drDateRange.EndDate.Value.ToString("yyyy-MM-dd");
//worksheet.Cells[3, 1].Value = "Header Question: " + HeaderRow["LongLabel"].ToString() + ( !HeaderRow["ShortLabel"].Equals( DBNull.Value ) ? " - " + HeaderRow["ShortLabel"].ToString() : String.Empty );
//worksheet.Cells[4, 1].Value = "Row Question: " + RowRow["LongLabel"].ToString() + ( !RowRow["ShortLabel"].Equals( DBNull.Value ) ? " - " + RowRow["ShortLabel"].ToString() : String.Empty );
int rowNum = 4;
using (ExcelRange r = worksheet.Cells[rowNum, 1, rowNum + 1, 1])
{
r.Merge = true;
}
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum + 1, 2])
{
r.Value = "Base";
r.Merge = true;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells[rowNum, 3, rowNum, 2 + HeaderAnswers.Count])
{
r.Value = HeaderRow["LongLabel"].ToString() + (!HeaderRow["ShortLabel"].Equals(DBNull.Value) ? " - " + HeaderRow["ShortLabel"].ToString() : String.Empty);
r.Merge = true;
r.Style.WrapText = true;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
worksheet.Row(rowNum).Height *= 2;
rowNum++;
for (int i = 0; i < HeaderAnswers.Count; i++)
{
using (ExcelRange r = worksheet.Cells[rowNum, 3 + i])
{
r.Value = HeaderAnswers[i].Label;
r.Style.Font.Bold = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
}
rowNum++;
using (ExcelRange r = worksheet.Cells[rowNum, 1, rowNum + 1, 1])
{
r.Value = "Base";
r.Merge = true;
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
}
double totalCount = Data.Rows[0]["RowBase"].ToString().StringToDbl();
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum + 1, 2])
{
r.Value = totalCount;
r.Merge = true;
r.Style.Numberformat.Format = "#,###;-#,###;0";
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
DataRow baseRow = Data.Rows[0];
int c = 3;
foreach (Answer a in HeaderAnswers)
{
AddExcelCellValue(worksheet, rowNum, c, totalCount, baseRow[a.Label]);
c++;
}
rowNum += 2;
worksheet.Row(rowNum).Height *= 3;
using (ExcelRange r = worksheet.Cells[rowNum, 1])
{
r.Value = RowRow["LongLabel"].ToString() + (!RowRow["ShortLabel"].Equals(DBNull.Value) ? " - " + RowRow["ShortLabel"].ToString() : String.Empty);
r.Style.Font.Bold = true;
r.Style.WrapText = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
using (ExcelRange r = worksheet.Cells[rowNum, 2, rowNum, 2 + HeaderAnswers.Count])
{
r.Merge = true;
}
rowNum++;
for (int i = 1; i < Data.Rows.Count; i++)
{
DataRow dr = Data.Rows[i];
using (ExcelRange r = worksheet.Cells[rowNum, 1])
{
r.Value = dr["Label"];
r.Style.Font.Bold = true;
r.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
AddExcelCellValue(worksheet, rowNum, 2, totalCount, dr["RowBase"]);
c = 3;
foreach (Answer a in HeaderAnswers)
{
AddExcelCellValue(worksheet, rowNum, c, totalCount, dr[a.Label]);
c++;
}
rowNum += 2;
}
worksheet.Cells[5, 3, 5, 2 + HeaderAnswers.Count].AutoFitColumns(9.14f);
string lFileName = string.Format("CrossTabs-{0}.xlsx", ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss-fff", User));
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), lFileName);
FileInfo fi = new FileInfo(lOutput);
p.SaveAs(fi);
hlDownload.Text = "Download File - " + lFileName;
hlDownload.NavigateUrl = String.Format("/Files/Cache/{0}", lFileName);
}
}
else
{
hlDownload.Text = String.Empty;
}
}
public static void CreateAndFillWorksheet(ExcelWorksheets wss, string typeName)
{
var dicts = AssetBundleFilesAnalyze.assetFileInfos;
if (dicts == null)
{
return;
}
List <string> columnNames = new List <string>();
foreach (var info in dicts.Values)
{
if (info.type != typeName)
{
continue;
}
foreach (var pair in info.propertys)
{
columnNames.Add(pair.Key);
}
break;
}
if (columnNames.Count == 0)
{
return;
}
string titleName = typeName + " 列表";
ExcelWorksheet ws = wss.Add(titleName);
int abCountCol = columnNames.Count + 2;
int abDetailCol = columnNames.Count + 3;
// 标签颜色
ws.TabColor = ColorTranslator.FromHtml("#b490f5");
AssetBundleReporter.CreateWorksheetBase(ws, titleName, abDetailCol);
// 列头
ws.Cells[2, 1].Value = "资源名称";
for (int i = 0; i < columnNames.Count; i++)
{
ws.Cells[2, i + 2].Value = columnNames[i];
}
ws.Cells[2, abCountCol].Value = "AB文件数量";
ws.Cells[2, abDetailCol].Value = "相应的AB文件";
using (var range = ws.Cells[2, 1, 2, abDetailCol])
{
// 字体样式
range.Style.Font.Bold = true;
// 背景颜色
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
// 开启自动筛选
range.AutoFilter = true;
}
// 列宽
ws.Column(1).Width = 50;
for (int i = 0; i < columnNames.Count; i++)
{
ws.Column(2 + i).Width = 15;
ws.Column(2 + i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Column(2 + i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
ws.Column(abCountCol).Width = 15;
ws.Column(abCountCol).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Column(abCountCol).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
// 冻结前两行
ws.View.FreezePanes(3, 1);
int startRow = 3;
int maxCol = abDetailCol;
foreach (var info in dicts.Values)
{
if (info.type != typeName)
{
continue;
}
ws.Cells[startRow, 1].Value = info.name;
info.detailHyperLink.ReferenceAddress = ws.Cells[startRow, 1].FullAddress;
int startCol = 2;
foreach (var property in info.propertys)
{
ws.Cells[startRow, startCol++].Value = property.Value;
}
ws.Cells[startRow, startCol++].Value = info.includedBundles.Count;
foreach (var bundleFileInfo in info.includedBundles)
{
ws.Cells[startRow, startCol].Value = bundleFileInfo.name;
ws.Cells[startRow, startCol++].Hyperlink = bundleFileInfo.detailHyperLink;
}
maxCol = System.Math.Max(--startCol, maxCol);
startRow++;
}
ws.Cells[1, 1].Value = ws.Cells[1, 1].Value + " (" + (startRow - 3) + ")";
// 具体所需要的列
using (var range = ws.Cells[2, abDetailCol, 2, maxCol])
{
range.Merge = true;
}
for (int i = abDetailCol; i <= maxCol; i++)
{
ws.Column(i).Width = 100;
}
// 不同类型不同处理
switch (typeName)
{
case "Texture2D":
ws.Column(7).Style.Numberformat.Format = "#,##0";
break;
case "Material":
ws.Column(2).Width = 40;
ws.Column(3).Width = 50;
break;
case "AnimationClip":
ws.Column(7).Style.Numberformat.Format = "#,##0";
break;
case "AudioClip":
ws.Column(2).Width = 23;
break;
}
}
public void ExportExcel()
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
try
{
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("Role", typeof(string));
tbl.Columns.Add("Descripción", typeof(string));
ResultSet res = db.getTable("SELECT * FROM ROLES ORDER BY PK1");
while (res.Next())
{
// Here we add five DataRows.
tbl.Rows.Add(res.Get("ROLE"), res.Get("DESCRIPCION"));
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Catalogo Roles");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//ws.Cells["A1:B1"].AutoFitColumns();
ws.Column(1).Width = 20;
ws.Column(2).Width = 80;
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:B2"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Roles.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
Log.write(this, "Start", LOG.CONSULTA, "Exporta Excel Catalogo Roles", sesion);
}
catch (Exception e)
{
ViewBag.Notification = Notification.Error(e.Message);
Log.write(this, "Start", LOG.ERROR, "Exporta Excel Catalogo Roles" + e.Message, sesion);
}
}
public void GenerateReport()
{
var fileName = "Report" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
var outputDir = @"../../Reports/";
var filePath = new FileInfo(outputDir + fileName);
using (var package = new ExcelPackage(filePath))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Results(" + this.start.ToShortDateString() + "," + this.end.ToShortDateString() + ")");
worksheet.Row(1).Height = 20;
worksheet.Row(2).Height = 18;
worksheet.Cells[1, 1].Value = "Report For Results By Dealers from " + this.start.ToShortDateString() + " to " + this.end.ToShortDateString();
var headerRange = worksheet.Cells[1, 1, 1, 4];
headerRange.Style.Font.Bold = true;
headerRange.Style.Font.Size = 16;
headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
worksheet.Cells[2, 1].Value = "Dealer Company";
worksheet.Cells[2, 2].Value = "Sales in EUR";
worksheet.Cells[2, 3].Value = "Costs in EUR";
worksheet.Cells[2, 4].Value = "Results in EUR";
using (var range = worksheet.Cells[2, 1, 2, 4])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Black);
range.Style.Font.Color.SetColor(Color.WhiteSmoke);
range.Style.ShrinkToFit = false;
}
int rowNumber = 3;
var dealerCosts = repoSqLite.DealersCostsSet.Select(c => new { Dealer = c.Dealer, ConstCosts = c.ConstCosts, SaleCosts = c.SaleCosts }).ToList();
foreach (var item in dealerCosts)
{
string dealer = item.Dealer;
decimal sales = 0m;
var records = this.repoMySql.DataAccessIncomes.Where(i => i.DataAccessDealer.Company == dealer);
if (records.Count() > 0)
{
sales = records.Sum(r => r.Amount);
}
decimal costs = item.ConstCosts / 30m * (decimal)(this.end - this.start).TotalDays + item.SaleCosts * sales;
decimal profit = sales - costs;
worksheet.Cells[rowNumber, 1].Value = dealer;
worksheet.Cells[rowNumber, 2].Value = sales;
worksheet.Cells[rowNumber, 3].Value = costs;
worksheet.Cells[rowNumber, 4].Value = profit;
worksheet.Cells[rowNumber, 2, rowNumber, 4].Style.Numberformat.Format = "### ### ### ###";
using (var rowRange = worksheet.Cells[rowNumber, 1, rowNumber, 4])
{
rowRange.Style.Font.Bold = false;
rowRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
rowRange.Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
rowRange.Style.Font.Color.SetColor(Color.Black);
rowRange.Style.ShrinkToFit = false;
rowRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
rowRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rowRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rowRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
rowNumber++;
}
worksheet.Column(1).Width = 50;
worksheet.Column(2).AutoFit();
worksheet.Column(3).AutoFit();
worksheet.Column(4).AutoFit();
package.Save();
}
}
public ExportToExcel(List <List <List <ProjectAmount> > > pAListList_List)
{
//保存文件
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 工作簿|*.xlsx"; //删选、设定文件显示类型
sfd.DefaultExt = ".xlsx";
sfd.FileName = "BIMING模板算量";
sfd.AddExtension = true;
sfd.ShowDialog();
string path = sfd.FileName;
FileInfo newFile = new FileInfo(path);
string fileName = path.Split(new char[] { '\\' }).Reverse().ToList().First();
string directory = Path.GetDirectoryName(path);
//如果新文件存在的话则删除它
if (newFile.Exists)
{
try
{
newFile.Delete();
newFile = new FileInfo(path);
}
catch
{
string filename = fileName.Split(new char[] { '.' })[0] + new Random().Next(1, 10000) + "." + fileName.Split(new char[] { '.' })[1];
path = directory + "\\" + filename;
MessageBox.Show("该文档正在被其他应用使用,无法删除," + "\r\n已将新文件名更换为" + filename, "警告", MessageBoxButtons.OK);
newFile = new FileInfo(path);
}
}
//新建工作簿
using (ExcelPackage package = new ExcelPackage(newFile))
{
int i = 0;
ExcelWorksheet[] sheetList = new ExcelWorksheet[pAListList_List.Count];
foreach (List <List <ProjectAmount> > tpaListList in pAListList_List)
{
//如果为空则返回
if (tpaListList.Count == 0)
{
continue;
}
//第一个文档
ProjectAmount tpa = tpaListList[0][0];
//取得算量模板的属性
List <string> fieldsList = FiieldTxt(tpa);
//转换后的属性表格宽度
List <int> columnSizeList = new List <int>();
//转换后的属性名称
List <string> proNameList = bc.ProTransform(fieldsList, out columnSizeList);
//将工作表的名字命名为该种构件的名称
sheetList[i] = package.Workbook.Worksheets.Add(tpa.TypeName);
//当前所操作的工作表
ExcelWorksheet worksheet = sheetList[i];
//从第二行开始
int row = 2;
//总计的工程量数量
double allAmount = 0;
//表格标题行初始化设置
foreach (string str in fieldsList)
{
//第一行标题字段的对应文字
worksheet.Cells[1, fieldsList.IndexOf(str) + 1].Value = proNameList.ElementAt(fieldsList.IndexOf(str));
worksheet.Column(fieldsList.IndexOf(str) + 1).Width = columnSizeList.ElementAt(fieldsList.IndexOf(str));
}
//如果是模板加一列合计
if (tpa.TypeName.Contains("模板"))
{
worksheet.Cells[1, fieldsList.Count + 1].Value = "合计(m2)";
worksheet.Column(fieldsList.Count + 1).Width = 15;
}
//排列某个工作表
foreach (List <ProjectAmount> tpList in tpaListList)
{
//合计的数据
double componetTotal = 0;
int rowlast = row;
//按行填数据
foreach (ProjectAmount tp in tpList)
{
//按列填数据
for (int col = 1; col < fieldsList.Count + 1; col++)
{
//获得属性集合
PropertyInfo[] pinfo = tp.GetType().GetProperties();
//获得对应属性的值
try
{
PropertyInfo pi = pinfo.First(m => m.Name == fieldsList[col - 1]);
//如果该值不为空，则该空填入该值
if (pi != null)
{
worksheet.Cells[row, col].Value = pi.GetValue(tp);
}
}
catch { }
//自动调整行高
worksheet.Row(row).CustomHeight = true;
}
if (tpa.TypeName.Contains("模板"))
{
componetTotal += tp.TemplateAmount;
}
else
{
componetTotal += tp.ConcretVolumes;
}
row++;
}
if (tpa.TypeName.Contains("模板"))
{
worksheet.Cells[rowlast, fieldsList.Count + 1].Value = componetTotal;
}
allAmount += componetTotal;
//TODO：如果只有一块，不知合并是否会报错
if (row - rowlast - 1 > 0)
{
if (tpa.TypeName.Contains("模板"))
{
//第几行开始为模板数据
int tpColNum = fieldsList.IndexOf("TpId");
//将其它列进行合并
for (int num = 1; num < tpColNum + 1; num++)
{
worksheet.Cells[rowlast, num, row - 1, num].Merge = true;
}
worksheet.Cells[rowlast, fieldsList.Count + 1, row - 1, fieldsList.Count + 1].Merge = true;
}
else//混凝土不知是否有需要
{
}
}
}
i++;
worksheet.Cells[row, 1].Value = "总计";
if (worksheet.Name.Contains("模板"))
{
worksheet.Cells[row, fieldsList.Count + 1].Value = allAmount;
worksheet.Cells[row, fieldsList.Count + 2].Value = "平方米";
}
else
{
worksheet.Cells[row, fieldsList.Count + 1].Value = allAmount;
worksheet.Cells[row, fieldsList.Count + 2].Value = "立方米";
}
//row = row + 2;
//if (worksheet.Name.Contains("工程量"))
//{
// worksheet.Cells[row, 1].Value = "混凝土等级";
// worksheet.Cells[row, 2].Value = "总量";
// worksheet.Cells[row, 3].Value = "单位";
// row++;
// List<string> materiaTypes = new List<string>();
// for (int j = 0; j < tpaListList.Count; j++)
// {
// ProjectAmount tp = tpaListList[j];
// if (materiaTypes.Count == 0 || materiaTypes.Where(m => m == tp.MaterialName).Count() == 0)
// materiaTypes.Add(tp.MaterialName);
// }
// foreach (string materia in materiaTypes)
// {
// int index = materiaTypes.IndexOf(materia);
// double num = 0;
// tpaListList.Where(m => m.MaterialName == materia).ToList().ConvertAll(m => num += m.ConcretVolumes);
// worksheet.Cells[row, 1].Value = materia;
// worksheet.Cells[row, 2].Value = bc.TRF(num, 3);
// worksheet.Cells[row, 3].Value = "立方米";
// row++;
// }
//}
//设置字体，也可以是中文，比如：宋体
worksheet.Cells.Style.Font.Name = "宋体";
//字体加粗
worksheet.Cells.Style.Font.Bold = false;
//字体大小
worksheet.Cells.Style.Font.Size = 12;
//字体颜色
worksheet.Cells.Style.Font.Color.SetColor(System.Drawing.Color.Black);
//单元格背景样式，要设置背景颜色必须先设置背景样式
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
//单元格背景颜色
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
//垂直居中
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//水平居中
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//单元格是否自动换行
worksheet.Cells.Style.WrapText = false;
//设置单元格格式为文本
worksheet.Cells.Style.Numberformat.Format = "@";
//单元格自动适应大小
worksheet.Cells.Style.ShrinkToFit = true;
for (int k = 0; k < columnSizeList.Count; k++)
{
worksheet.Column(k + 1).Width = columnSizeList[k];
}
int column = 1;
if (tpa.TypeName.Contains("模板"))
{
column = 2;
}
for (int p = 1; p < row + 1; p++)
{
worksheet.Row(p).Height = 20;
for (int l = 1; l < columnSizeList.Count + column; l++)
{
worksheet.Cells[p, l].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
}
}
}
package.Save();
}
}
/// <summary>
/// сохранение в файл xlsx
/// </summary>
/// <param name="rang"></param>
/// <param name="filename"></param>
internal override void SaveRange(RawRange rang, string filename)
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
//Set some properties of the Excel document
excelPackage.Workbook.Properties.Author = "Wind Energy";
excelPackage.Workbook.Properties.Title = rang.Name;
excelPackage.Workbook.Properties.Created = DateTime.Now;
//Create the WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Лист 1");
string coordinates = rang.Position.Lat.ToString("0.000000") + " " + rang.Position.Lng.ToString("0.000000");
string[] caption = new string[] { "Местное время", "T, ℃", "U, %", "DD, °", "ff, м/с", "P0, мм рт. ст." };
string title;
if (rang.Meteostation == null)
{
title = "ID=undefined";
}
else
{
title = $"ID={rang.Meteostation.ID}";
}
worksheet.Cells[1, 1].Value = title;
worksheet.Cells[2, 1].Value = coordinates;
worksheet.Cells[2, 2].Value = rang.Height;
worksheet.Cells[3, 1].Value = rang.Name;
for (int j = 1; j <= caption.Length; j++)
{
worksheet.Cells[4, j].Value = caption[j - 1];
}
worksheet.Cells[4, 1, 4, caption.Length].Style.Font.Bold = true;
worksheet.Cells[4, 1, 4, caption.Length].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[4, 1, 4, caption.Length].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
worksheet.View.FreezePanes(5, 1);
worksheet.Column(1).Width = 20;
int i = 5;
foreach (RawItem item in rang)
{
if (double.IsNaN(item.Speed))
{
continue;
}
worksheet.Cells[i, 1].Style.Numberformat.Format = DateTimeFormat;
worksheet.Cells[i, 1].Value = item.Date;
worksheet.Cells[i, 2].Value = item.Temperature;
worksheet.Cells[i, 3].Value = item.Wetness;
worksheet.Cells[i, 4].Value = item.Direction;
worksheet.Cells[i, 5].Value = item.Speed;
worksheet.Cells[i, 6].Value = item.Pressure;
i++;
}
//Save your file
FileInfo fi = new FileInfo(filename);
excelPackage.SaveAs(fi);
excelPackage.Dispose();
}
}
public static void prazoFornecedor()
{
SGAPContexto contexto = new SGAPContexto();
string folder = @"c:\Relatórios";
string arquivo = @"C:\Relatórios\Prazo Fornecedores " + DateTime.Now.ToString("dd-MM-yyyy") + ".xlsx";
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
try
{
if (File.Exists(arquivo))
{
File.Delete(arquivo);
}
List <Andamento> lstAndamentos = new List <Andamento>();
lstAndamentos = contexto.Andamento.Where(x => x.Atendimento.dataEncerramento == null).OrderBy(x => x.prazo).ToList();
FileInfo caminhoNomeArquivo = new FileInfo(arquivo);
ExcelPackage arquivoExcel = new ExcelPackage(caminhoNomeArquivo);
ExcelWorksheet planilha = arquivoExcel.Workbook.Worksheets.Add("Plan1");
planilha.Cells["A1"].Value = "RELATÓRIOS DE PRAZOS DE FORNECEDORES (ATENDIMENTOS ATIVOS)";
planilha.Cells["A1"].Style.Font.Bold = true;
planilha.Cells["A1"].Style.Font.Size = 14;
planilha.Row(1).Height = 32;
planilha.Cells["A1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
planilha.Cells["A1:A2"].Style.Font.Color.SetColor(Color.FromArgb(0, 112, 192));
planilha.Cells["A2"].Value = "Período: " + DateTime.Now.ToShortDateString();
planilha.View.ShowGridLines = false;
int linha = 3;
int coluna = 1;
//INSERINDO VALORES NA PRIMEIRA LINHA
planilha.Cells[++linha, coluna].Value = "PRAZO";
planilha.Cells[linha, ++coluna].Value = "DESCRIÇÃO";
planilha.Cells[linha, ++coluna].Value = "NÚMERO ATENDIMENTO";
planilha.Cells[linha, ++coluna].Value = "DATA INSERÇÃO";
planilha.Cells["A4:D4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
planilha.Cells["A4:D4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
//DEFININDO COR DA FONTE
planilha.Cells["A4:D4"].Style.Font.Color.SetColor(Color.FromArgb(255, 255, 255));
//DEFININDO NEGRITO
planilha.Cells["A4:D4"].Style.Font.Bold = true;
foreach (Andamento andamento in lstAndamentos)
{
planilha.Cells[++linha, coluna = 1].Value = andamento.prazo;
planilha.Cells[linha, ++coluna].Value = andamento.descricao;
planilha.Cells[linha, ++coluna].Value = andamento.Atendimento.numeroProcon;
planilha.Cells[linha, ++coluna].Value = andamento.data;
}
planilha.Cells["A4:D" + linha].Style.Border.Top.Style = ExcelBorderStyle.Thin;
planilha.Cells["A4:D" + linha].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
planilha.Cells["A4:D" + linha].Style.Border.Left.Style = ExcelBorderStyle.Thin;
planilha.Cells["A4:D" + linha].Style.Border.Right.Style = ExcelBorderStyle.Thin;
if (linha < 5)
{
linha = 5;
}
//CENTRALIZANDO
planilha.Cells["A4:D4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
planilha.Cells["A5:A" + linha].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
planilha.Cells["C5:D" + linha].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//TRATANDO FORMATO DE CELULA
planilha.Cells["A5:A" + linha].Style.Numberformat.Format = "dd/MM/yyyy";
planilha.Cells["D5:D" + linha].Style.Numberformat.Format = "dd/MM/yyyy";
//CODIGOS FINAIS
planilha.Cells[planilha.Dimension.Address].AutoFitColumns();
planilha.Column(1).Width = 23;
arquivoExcel.Save();
arquivoExcel.Dispose();
System.Diagnostics.Process.Start(arquivo);
}
catch (System.IO.IOException)
{
MessageBox.Show("Arquivo existente aberto. Feche e tente gerar novamente!", "Alerta", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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 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);
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_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 Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Report";
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_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 + 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];
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 Health Check Results
loggerConsole.Info("List of Health Check Results");
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), 0, 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];
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(HealthCheckRuleResult.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 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 = 30;
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_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT, 1], range, PIVOT_HEALTH_CHECK_RULE_RESULTS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "Application");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "Category", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Name", eSortType.Ascending);
addDataFieldToPivot(pivot, "Grade", DataFieldFunctions.Average);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Health Check Results Display
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY];
List <HealthCheckRuleResult> healthCheckRuleResults = FileIOHelper.ReadListFromCSVFile(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), new HealthCheckRuleResultReportMap());
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 = 30;
// 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));
}
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;
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 = 90;
// Color code it
cfGradeNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, 5, sheet.Dimension.Rows, 5 + numColumns);
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.APMHealthCheckResultsExcelReportFilePath(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 static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn column in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return(result);
}
public static void ExcelSave(ExtendedStats exdata)
{
lock (savelock) //can't save 2 excel files at one time
{
if (!BTD.WindowData.Excel)
{
return;
}
var data = exdata.BaseStats;
NpcInfo Boss = exdata.Entity.Info;
/*
* Select save directory
*/
string dir = "";
if (BTD.WindowData.ExcelSaveDirectory == "")
{
dir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), $"ShinraMeter/{Boss.Area.Replace(":", "-")}");
}
else
{
dir = BTD.WindowData.ExcelSaveDirectory;
}
/*
* Test if you have access to the user choice directory, if not, switch back to the default save directory
*/
try
{
Directory.CreateDirectory(dir);
}
catch
{
dir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), $"ShinraMeter/{Boss.Area.Replace(":", "-")}");
Directory.CreateDirectory(dir);
}
var fname = Path.Combine(dir, $"{Boss.Name.Replace(":", "-")} {DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss", CultureInfo.InvariantCulture)}.xlsx");
FileInfo file = new FileInfo(fname);
if (file.Exists)
{
return; //the only case this can happen is BAM mobtraining, that's not so interesting statistic to deal with more complex file names.
}
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet details = CreateDetailsSheet(package, exdata);
ExcelWorksheet ws = package.Workbook.Worksheets.Add("Boss");
var linkStyle = package.Workbook.Styles.CreateNamedStyle("HyperLink"); //This one is language dependent
linkStyle.Style.Font.UnderLine = true;
linkStyle.Style.Font.Color.SetColor(Color.Blue);
ws.DefaultRowHeight = 30;
ws.Cells.Style.Font.Size = 14;
ws.Cells.Style.Font.Name = "Arial";
ws.Cells[1, 1].Value = $"{Boss.Area}: {Boss.Name} {TimeSpan.FromSeconds(double.Parse(data.fightDuration)).ToString(@"mm\:ss")}";
ws.Cells[1, 1, 1, 6].Merge = true;
ws.Cells[1, 1, 1, 8].Style.Font.Bold = true;
ws.Cells[1, 7].Value = long.Parse(data.partyDps);
ws.Cells[1, 7].Style.Numberformat.Format = @"#,#00,\k\/\s";
ws.Cells[2, 1].Value = "Ic";
ws.Cells[2, 1].Style.Font.Color.SetColor(Color.Transparent);
ws.Cells[2, 2].Value = "Name";
ws.Cells[2, 3].Value = "Deaths";
ws.Cells[2, 4].Value = "Death time";
ws.Cells[2, 5].Value = "Damage %";
ws.Cells[2, 6].Value = "Crit %";
ws.Cells[2, 7].Value = "DPS";
ws.Cells[2, 8].Value = "Damage";
int i = 2;
foreach (var user in data.members.OrderByDescending(x => long.Parse(x.playerTotalDamage)))
{
i++;
ws.Cells[i, 1].Value = i - 2;
AddImage(ws, i, 1, ClassIcons.Instance.GetBitmap((PlayerClass)Enum.Parse(typeof(PlayerClass), user.playerClass)));
ws.Cells[i, 2].Value = $"{user.playerServer}: {user.playerName}";
ws.Cells[i, 2].Hyperlink = CreateUserSheet(package.Workbook, user, exdata, details);
ws.Cells[i, 3].Value = long.Parse(user.playerDeaths);
ws.Cells[i, 4].Value = long.Parse(user.playerDeathDuration);
ws.Cells[i, 4].Style.Numberformat.Format = @"0\s";
ws.Cells[i, 5].Value = double.Parse(user.playerTotalDamagePercentage) / 100;
ws.Cells[i, 5].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 6].Value = double.Parse(user.playerAverageCritRate) / 100;
ws.Cells[i, 6].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 7].Value = long.Parse(user.playerDps);
ws.Cells[i, 7].Style.Numberformat.Format = @"#,#0,\k\/\s";
ws.Cells[i, 8].Value = long.Parse(user.playerTotalDamage);
ws.Cells[i, 8].Style.Numberformat.Format = @"#,#0,\k";
}
ws.Cells[1, 8].Formula = $"SUM(H3:H{i})";
ws.Cells[1, 8].Style.Numberformat.Format = @"#,#0,\k";
var border = ws.Cells[1, 1, i, 8].Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
ws.Cells[2, 1, i, 8].AutoFilter = true;
int j = i + 3;
ws.Cells[j, 1].Value = "Ic";
ws.Cells[j, 1].Style.Font.Color.SetColor(Color.Transparent);
ws.Cells[j, 2].Value = "Debuff name";
ws.Cells[j, 2, j, 7].Merge = true;
ws.Cells[j, 8].Value = "%";
ws.Cells[j, 2, j, 8].Style.Font.Bold = true;
foreach (var buf in data.debuffUptime)
{
j++;
var hotdot = BTD.HotDotDatabase.Get(int.Parse(buf.Key));
ws.Cells[j, 1].Value = j - i - 3;
AddImage(ws, j, 1, BTD.Icons.GetBitmap(hotdot.IconName));
ws.Cells[j, 2].Value = hotdot.Name;
if (!string.IsNullOrEmpty(hotdot.Tooltip))
{
ws.Cells[j, 2].AddComment("" + hotdot.Tooltip, "info");
}
ws.Cells[j, 2, j, 7].Merge = true;
ws.Cells[j, 8].Value = double.Parse(buf.Value) / 100;
ws.Cells[j, 8].Style.Numberformat.Format = "0%";
}
border = ws.Cells[i + 3, 1, j, 8].Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
AddCharts(ws, exdata, details, j);
ws.Column(1).Width = 5.6;
ws.Column(2).AutoFit();
ws.Column(3).AutoFit();
ws.Column(4).AutoFit();
ws.Column(5).AutoFit();
ws.Column(6).AutoFit();
ws.Column(7).AutoFit();
ws.Column(8).Width = 17;
ws.Column(2).Width = GetTrueColumnWidth(ws.Column(2).Width);
ws.Column(3).Width = GetTrueColumnWidth(ws.Column(3).Width);
ws.Column(4).Width = GetTrueColumnWidth(ws.Column(4).Width);
ws.Column(5).Width = GetTrueColumnWidth(ws.Column(5).Width);
ws.Column(6).Width = GetTrueColumnWidth(ws.Column(6).Width);
ws.Column(7).Width = GetTrueColumnWidth(ws.Column(7).Width);
ws.Cells[1, 1, j, 8].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[1, 1, j, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.PrinterSettings.FitToPage = true;
// I don't know why, but sometimes column height setting is lost.
for (int x = 1; x < j; ++x)
{
ws.Row(x).CustomHeight = true;
ws.Row(x).Height = 30;
}
ws.View.TabSelected = true;
details.Hidden = eWorkSheetHidden.Hidden;
package.Workbook.Properties.Title = Boss.Name;
package.Workbook.Properties.Author = "ShinraMeter " + AutoUpdate.UpdateManager.Version;
package.Workbook.Properties.Company = "github.com/neowutran & github.com/Gl0";
package.Save();
}
}
}
public async Task <IActionResult> Download2Async()
{
using (aOmsdCustomConext)
{
var clinics = aOmsdCustomConext.Clinic.ToList();
var users = aOmsdCustomConext.ClinicUser.ToList();
var cities = aOmsdCustomConext.OntarioCity.ToList();
var serviceTypes = aOmsdCustomConext.ServiceType.ToList();
var status = aOmsdCustomConext.Status.ToList();
byte[] fileContents;
var conn = aOmsdCustomConext.Database.GetDbConnection();
await conn.OpenAsync();
var command = conn.CreateCommand();
const string query = queryString;
command.CommandText = query;
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
using (var package = new ExcelPackage())
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Clinic");
worksheet.Cells[1, 1].LoadFromDataReader(reader, true);
for (int i = 2; i < clinics.Count + 2; i++)
{
worksheet.Cells[i, 4].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
worksheet.Column(4).Width = 18;
worksheet.Cells[i, 16].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
worksheet.Column(16).Width = 18;
worksheet.Cells[i, 24].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
worksheet.Column(24).Width = 18;
worksheet.Cells[i, 33].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
worksheet.Column(33).Width = 18;
}
/*ExcelWorksheet usersWorksheet = package.Workbook.Worksheets.Add("ClinicUser");
* usersWorksheet.Cells[1, 1].LoadFromCollection(users, true);*/
ExcelWorksheet citiesWorksheet = package.Workbook.Worksheets.Add("OntarioCity");
citiesWorksheet.Cells[1, 1].LoadFromCollection(cities, true);
ExcelWorksheet serviceTypesWorksheet = package.Workbook.Worksheets.Add("ServiceType");
serviceTypesWorksheet.Cells[1, 1].LoadFromCollection(serviceTypes, true);
ExcelWorksheet statusWorksheet = package.Workbook.Worksheets.Add("Status");
statusWorksheet.Cells[1, 1].LoadFromCollection(status, true);
fileContents = package.GetAsByteArray();
}
if (fileContents == null || fileContents.Length == 0)
{
return(NotFound());
}
return(File(
fileContents: fileContents,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "clinics.xlsx"
));
}
return(Ok());
}
}
private static ExcelHyperLink CreateUserSheet(ExcelWorkbook wb, Members user, ExtendedStats exdata, ExcelWorksheet details)
{
ExcelWorksheet ws = wb.Worksheets.Add($"{user.playerServer}_{user.playerName}");
var rgc = new RaceGenderClass("Common", "Common", user.playerClass);
ws.DefaultRowHeight = 30;
ws.Cells.Style.Font.Size = 14;
ws.Cells.Style.Font.Name = "Arial";
AddImage(ws, 1, 1, ClassIcons.Instance.GetBitmap((PlayerClass)Enum.Parse(typeof(PlayerClass), user.playerClass)));
ws.Cells[1, 2].Value = $"{user.playerServer}: {user.playerName}";
ws.Cells[1, 2, 1, 10].Merge = true;
ws.Cells[1, 2, 1, 10].Style.Font.Bold = true;
ws.Cells[2, 2].Value = "Skill";
ws.Cells[2, 3].Value = "Damage %";
ws.Cells[2, 4].Value = "Damage";
ws.Cells[2, 5].Value = "Crit %";
ws.Cells[2, 6].Value = "Hits";
ws.Cells[2, 7].Value = "Max Crit";
ws.Cells[2, 8].Value = "Min Crit";
ws.Cells[2, 9].Value = "Avg Crit";
ws.Cells[2, 10].Value = "Avg White";
int i = 2;
foreach (var stat in user.skillLog.OrderByDescending(x => long.Parse(x.skillTotalDamage)))
{
i++;
var skill = BTD.SkillDatabase.GetOrNull(rgc, int.Parse(stat.skillId));
if (skill == null)
{
ws.Cells[i, 1].Value = i - 2;
ws.Cells[i, 2].Value = "Some pet skill";
}
else
{
ws.Cells[i, 1].Value = i - 2;
AddImage(ws, i, 1, BTD.Icons.GetBitmap(skill.IconName));
ws.Cells[i, 2].Value = skill.Name;
}
ws.Cells[i, 3].Value = double.Parse(stat.skillDamagePercent) / 100;
ws.Cells[i, 3].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 4].Value = long.Parse(stat.skillTotalDamage);
ws.Cells[i, 4].Style.Numberformat.Format = @"#,#0,\k";
ws.Cells[i, 5].Value = double.Parse(stat.skillCritRate) / 100;
ws.Cells[i, 5].Style.Numberformat.Format = "0.0%";
ws.Cells[i, 6].Value = long.Parse(stat.skillHits);
ws.Cells[i, 7].Value = long.Parse(stat.skillHighestCrit);
ws.Cells[i, 7].Style.Numberformat.Format = @"#,#0,\k";
ws.Cells[i, 8].Value = long.Parse(stat.skillLowestCrit);
ws.Cells[i, 8].Style.Numberformat.Format = @"#,#0,\k";
ws.Cells[i, 9].Value = long.Parse(stat.skillAverageCrit);
ws.Cells[i, 9].Style.Numberformat.Format = @"#,#0,\k";
ws.Cells[i, 10].Value = long.Parse(stat.skillAverageWhite);
ws.Cells[i, 10].Style.Numberformat.Format = @"#,#0,\k";
}
var border = ws.Cells[1, 1, i, 10].Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
ws.Cells[2, 1, i, 10].AutoFilter = true;
int j = i + 3;
ws.Cells[j, 2].Value = "Buff name";
ws.Cells[j, 2, j, 9].Merge = true;
ws.Cells[j, 10].Value = "%";
ws.Cells[j, 2, j, 10].Style.Font.Bold = true;
foreach (var buf in user.buffUptime)
{
j++;
var hotdot = BTD.HotDotDatabase.Get(int.Parse(buf.Key));
ws.Cells[j, 1].Value = j - i - 3;
AddImage(ws, j, 1, BTD.Icons.GetBitmap(hotdot.IconName));
ws.Cells[j, 2].Value = hotdot.Name;
if (!string.IsNullOrEmpty(hotdot.Tooltip))
{
ws.Cells[j, 2].AddComment("" + hotdot.Tooltip, "info");
}
ws.Cells[j, 2, j, 9].Merge = true;
ws.Cells[j, 10].Value = double.Parse(buf.Value) / 100;
ws.Cells[j, 10].Style.Numberformat.Format = "0%";
if (!string.IsNullOrEmpty(hotdot.ItemName))
{
ws.Cells[j, 10].AddComment("" + hotdot.ItemName, "info");
}
}
border = ws.Cells[i + 3, 1, j, 10].Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thick;
AddCharts(ws, exdata, details, j);
ws.Column(1).Width = 5.6;
ws.Column(2).AutoFit();
ws.Column(3).AutoFit();
ws.Column(4).AutoFit();
ws.Column(5).AutoFit();
ws.Column(6).AutoFit();
ws.Column(7).AutoFit();
ws.Column(8).AutoFit();
ws.Column(9).AutoFit();
ws.Column(10).AutoFit();
ws.Column(2).Width = GetTrueColumnWidth(ws.Column(2).Width);
ws.Column(3).Width = GetTrueColumnWidth(ws.Column(3).Width);
ws.Column(4).Width = GetTrueColumnWidth(ws.Column(4).Width);
ws.Column(5).Width = GetTrueColumnWidth(ws.Column(5).Width);
ws.Column(6).Width = GetTrueColumnWidth(ws.Column(6).Width);
ws.Column(7).Width = GetTrueColumnWidth(ws.Column(7).Width);
ws.Column(8).Width = GetTrueColumnWidth(ws.Column(8).Width);
ws.Column(9).Width = GetTrueColumnWidth(ws.Column(9).Width);
ws.Column(10).Width = GetTrueColumnWidth(ws.Column(10).Width);
ws.Cells[1, 1, j, 10].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[1, 1, j, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.PrinterSettings.FitToPage = true;
// I don't know why, but sometimes column height setting is lost.
for (int x = 1; x < j; ++x)
{
ws.Row(x).CustomHeight = true;
ws.Row(x).Height = 30;
}
// If sheet name contains space character, name should be enclosed in single quotes.
return(new ExcelHyperLink($"'{user.playerServer}_{user.playerName}'!A1", $"{user.playerServer}: {user.playerName}"));
}
/// <summary>
/// Copies the current column to a new worksheet
/// </summary>
/// <param name="added">The worksheet where the copy will be created</param>
internal void Clone(ExcelWorksheet added)
{
ExcelColumn newCol = added.Column(ColumnMin);
newCol.ColumnMax = ColumnMax;
newCol.BestFit = BestFit;
newCol.Collapsed = Collapsed;
newCol.Hidden = Hidden;
newCol.OutlineLevel = OutlineLevel;
newCol.PageBreak = PageBreak;
newCol.Phonetic = Phonetic;
newCol.StyleName = StyleName;
newCol.StyleID = StyleID;
newCol.Width = Width;
}