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;
}
