internal void Fill(ExcelWorksheet worksheet) { var initialRow = 1; worksheet.Cells["A1"].Value = "Bridge Care Work Type"; worksheet.Cells["B1"].Value = "Short Bridge Care Work Type"; excelHelper.ApplyStyle(worksheet.Cells["A1:B1"]); var abbreviatedTreatmentNames = ShortNamesForTreatments.GetShortNamesForTreatments(); var row = 2; var column = 1; foreach (var treatment in abbreviatedTreatmentNames) { worksheet.Cells[row, column++].Value = treatment.Key; worksheet.Cells[row, column].Value = treatment.Value; column = 1; row++; } excelHelper.ApplyBorder(worksheet.Cells[initialRow, 1, row, 2]); row += 2; worksheet.Cells[row, 1].Value = "Color Key"; excelHelper.MergeCells(worksheet, row, 1, row, 2); row += 2; worksheet.Cells[row, 1].Value = "Work Done Columns"; excelHelper.MergeCells(worksheet, row, 1, row, 2); row++; worksheet.Cells[row, 1].Value = "Bridge being worked on has a parallel bridge - Project came from BAMS"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(0, 204, 255)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.Black); row++; worksheet.Cells[row, 1].Value = "Bridge being worked on has a parallel bridge - project is being cash flowed"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(0, 204, 255)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(255, 0, 0)); row++; worksheet.Cells[row, 1].Value = "Bridge being worked on has a parallel bridge - project came from MPMS"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(0, 204, 255)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.White); row++; worksheet.Cells[row, 1].Value = "Bridge project is being cashed flowed"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(0, 255, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.Red); row++; worksheet.Cells[row, 1].Value = "MPMS Project selected for consecutive years"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(255, 153, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.White); row += 2; worksheet.Cells[row, 1].Value = "Details Colums"; excelHelper.MergeCells(worksheet, row, 1, row, 2); row++; worksheet.Cells[row, 1].Value = "Project is being cash flowed"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(0, 255, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.Black); row++; worksheet.Cells[row, 1].Value = "P3 Bridge where minimum condition is less than 5"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(255, 255, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.Black); row++; worksheet.Cells[row, 1].Value = "Min Condition is less than or equal to 3.5"; excelHelper.ApplyBorder(worksheet.Cells[row, 1, row, 2]); excelHelper.MergeCells(worksheet, row, 1, row, 2); excelHelper.ApplyColor(worksheet.Cells[row, 1, row, 2], Color.FromArgb(112, 48, 160)); excelHelper.SetTextColor(worksheet.Cells[row, 1, row, 2], Color.White); row += 3; worksheet.Cells[row, 1].Value = "Example: "; worksheet.Cells[row, 2].Value = "2021"; worksheet.Cells[row, 3].Value = "2022"; worksheet.Cells[row, 4].Value = "2023"; row++; worksheet.Cells[row, 2].Value = "Brdg_Repl"; excelHelper.ApplyColor(worksheet.Cells[row, 2], Color.FromArgb(0, 204, 255)); excelHelper.SetTextColor(worksheet.Cells[row, 2], Color.FromArgb(255, 0, 0)); worksheet.Cells[row, 3].Value = "--"; excelHelper.ApplyColor(worksheet.Cells[row, 3], Color.FromArgb(0, 255, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 3], Color.Red); worksheet.Cells[row, 4].Value = "--"; excelHelper.ApplyColor(worksheet.Cells[row, 4], Color.FromArgb(0, 255, 0)); excelHelper.SetTextColor(worksheet.Cells[row, 4], Color.Red); row++; excelHelper.ApplyBorder(worksheet.Cells[row - 1, 2, row - 1, 4]); worksheet.Cells.AutoFitColumns(70); worksheet.Cells[row, 2].Value = "(Bridge being replaced also has a parallel bridge. Bridge replacement is cash flowed over 3 years.)"; }
private void AddDynamicDataCells(ExcelWorksheet worksheet, List <Section> sectionsForSummaryReport, List <SimulationDataModel> simulationDataModels, List <BridgeDataModel> bridgeDataModels, CurrentCell currentCell, int columnForRiskScore) { var row = 4; // Data starts here var startingRow = row; var column = currentCell.Column; int totalColumn = 0; int totalColumnValue = 0; var abbreviatedTreatmentNames = ShortNamesForTreatments.GetShortNamesForTreatments(); // making dictionary to remove if else, which was used to enter value for MinC valueForMinC = new Dictionary <MinCValue, Func <ExcelWorksheet, int, int, YearsData, int> >(); valueForMinC.Add(MinCValue.defaultValue, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterDefaultMinCValue)); valueForMinC.Add(MinCValue.valueEqualsCulv, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterValueEqualsCulv)); valueForMinC.Add(MinCValue.minOfDeckSubSuper, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterMinDeckSuperSub)); valueForMinC.Add(MinCValue.minOfCulvDeckSubSuper, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterMinDeckSuperSubCulv)); foreach (var bridgeDataModel in bridgeDataModels) { if (row % 2 == 0) { excelHelper.ApplyColor(worksheet.Cells[row, 1, row, worksheet.Dimension.Columns], Color.LightGray); } column = currentCell.Column; var brKey = bridgeDataModel.BRKey; var familyId = bridgeDataModel.BridgeFamily; var workDoneMoreThanOnce = 0; var section = sectionsForSummaryReport.Where(s => Convert.ToInt32(s.FACILITY) == brKey).FirstOrDefault(); var simulationDataModel = simulationDataModels.Where(s => s.SectionId == section.SECTIONID).FirstOrDefault(); // Save DeckArea for further use simulationDataModel.DeckArea = bridgeDataModel.DeckArea; simulationDataModel.BRKey = brKey; bridgeDataModel.RiskScore = simulationDataModel.RiskScore; worksheet.Cells[row, columnForRiskScore].Value = simulationDataModel.RiskScore; var yearsData = simulationDataModel.YearsData; var projectPickByYear = new Dictionary <int, int>(); // Add work done cells for (var index = 1; index < yearsData.Count(); index++) { var cost = yearsData[index].Cost; var range = worksheet.Cells[row, ++column]; projectPickByYear.Add(yearsData[index].Year, yearsData[index].ProjectPickType); setColor(bridgeDataModel.ParallelBridge, yearsData[index].Treatment, projectPickByYear, yearsData[index].Year, index, yearsData[index].Project, worksheet, row, column); if (abbreviatedTreatmentNames.ContainsKey(yearsData[index].Treatment)) { range.Value = string.IsNullOrEmpty(abbreviatedTreatmentNames[yearsData[index].Treatment]) ? "--" : abbreviatedTreatmentNames[yearsData[index].Treatment]; } else { range.Value = string.IsNullOrEmpty(yearsData[index].Treatment) ? "--" : yearsData[index].Treatment; } workDoneMoreThanOnce = !range.Value.Equals("--") ? workDoneMoreThanOnce + 1 : workDoneMoreThanOnce; } worksheet.Cells[row, ++column].Value = workDoneMoreThanOnce > 1 ? "Yes" : "--"; totalColumnValue = workDoneMoreThanOnce > 1 ? totalColumnValue + 1 : totalColumnValue; // Empty Total column column++; // Add Total of count of Work done more than once column cells if "Yes" totalColumn = column; // Add Poor On/Off Rate column: Formula (prev yr MinC < 5 and curr yr Minc >= 5 then "Off"), (prev yr MinC >= 5 and curr ye MinC < 5 then "On") for (var index = 1; index < yearsData.Count(); index++) { var prevYrMinc = yearsData[index - 1].MinC; var thisYrMinc = yearsData[index].MinC; worksheet.Cells[row, ++column].Value = prevYrMinc < 5 ? (thisYrMinc >= 5 ? "Off" : "--") : (thisYrMinc < 5 ? "On" : "--"); yearsData[index].PoorOnOffRate = worksheet.Cells[row, column].Value.ToString(); } // Empty column column++; worksheet.Column(column).Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Column(column).Style.Fill.BackgroundColor.SetColor(Color.Gray); // Last Year simulation data var lastYearData = yearsData.FirstOrDefault(); column = AddSimulationYearData(worksheet, row, column, lastYearData, familyId, bridgeDataModel, projectPickByYear); // Add all yrs from current year simulation data for (var index = 1; index < yearsData.Count(); index++) { column = AddSimulationYearData(worksheet, row, column, yearsData[index], familyId, bridgeDataModel, projectPickByYear); } row++; } if (totalColumn != 0) { worksheet.Cells[3, totalColumn].Value = totalColumnValue; } currentCell.Row = row - 1; currentCell.Column = column - 1; }