private void AddDynamicHeadersCells(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears)
{
const string HeaderConstText = "Work Done in ";
var column = currentCell.Column;
var row = currentCell.Row;
foreach (var year in simulationYears)
{
worksheet.Cells[row, ++column].Value = HeaderConstText + year;
worksheet.Cells[row + 2, column].Value = year;
excelHelper.ApplyStyle(worksheet.Cells[row + 2, column]);
}
worksheet.Cells[row, ++column].Value = "Work Done more than once";
worksheet.Cells[row, ++column].Value = "Total";
worksheet.Cells[row, ++column].Value = "Poor On/Off Rate";
var poorOnOffRateColumn = column;
foreach (var year in simulationYears)
{
worksheet.Cells[row + 2, column].Value = year;
excelHelper.ApplyStyle(worksheet.Cells[row + 2, column]);
column++;
}
// Merge 2 rows for headers till column before Poor On/Off Rate
worksheet.Row(row).Height = 40;
for (int cellColumn = 1; cellColumn < poorOnOffRateColumn; cellColumn++)
{
excelHelper.MergeCells(worksheet, row, cellColumn, row + 1, cellColumn);
}
// Merge columns for Poor On/Off Rate
excelHelper.MergeCells(worksheet, row, poorOnOffRateColumn, row + 1, column - 1);
currentCell.Column = column;
// Add Years Data headers
var simulationHeaderTexts = GetSimulationHeaderTexts();
worksheet.Cells[row, ++column].Value = simulationYears[0] - 1;
column = currentCell.Column;
column = AddSimulationHeaderTexts(worksheet, currentCell, column, row, simulationHeaderTexts, simulationHeaderTexts.Count - 2);
excelHelper.MergeCells(worksheet, row, currentCell.Column + 1, row, column);
// Empty column
currentCell.Column = ++column;
foreach (var simulationYear in simulationYears)
{
worksheet.Cells[row, ++column].Value = simulationYear;
column = currentCell.Column;
column = AddSimulationHeaderTexts(worksheet, currentCell, column, row, simulationHeaderTexts, simulationHeaderTexts.Count);
excelHelper.MergeCells(worksheet, row, currentCell.Column + 1, row, column);
currentCell.Column = ++column;
}
currentCell.Row = currentCell.Row + 2;
}
private int FillTotalBudgetSection(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, Dictionary <int, List <double> > yearlyBudgetAmounts)
{
bridgeWorkSummaryCommon.AddHeaders(worksheet, currentCell, simulationYears, "Total Budget", "Totals");
worksheet.Cells[currentCell.Row, simulationYears.Count + 3].Value = "Total Analysis Budget (all year)";
excelHelper.ApplyStyle(worksheet.Cells[currentCell.Row, simulationYears.Count + 3]);
excelHelper.ApplyBorder(worksheet.Cells[currentCell.Row, simulationYears.Count + 3]);
var budgetTotalRow = AddDetailsForTotalBudget(worksheet, simulationYears, currentCell, yearlyBudgetAmounts);
return(budgetTotalRow);
}
private void AddMergeSectionHeader(ExcelWorksheet worksheet, string headerText, int yearsCount, CurrentCell currentCell)
{
var row = currentCell.Row;
var column = currentCell.Column;
worksheet.Cells[row, ++column].Value = headerText;
var cells = worksheet.Cells[row, column];
excelHelper.ApplyStyle(cells);
excelHelper.MergeCells(worksheet, row, column, row, column + yearsCount - 1);
cells = worksheet.Cells[row, column, row, column + yearsCount - 1];
excelHelper.ApplyBorder(cells);
++row;
UpdateCurrentCell(currentCell, row, column);
}
private void AddDynamicHeadersCells(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears)
{
const string HeaderConstText = "Work Done in ";
var column = currentCell.Column;
var row = currentCell.Row;
var initialColumn = column;
foreach (var year in simulationYears)
{
worksheet.Cells[row, ++column].Value = HeaderConstText + year;
worksheet.Cells[row + 2, column].Value = year;
excelHelper.ApplyStyle(worksheet.Cells[row + 2, column]);
excelHelper.ApplyColor(worksheet.Cells[row, column], Color.FromArgb(244, 176, 132));
}
worksheet.Cells[row, ++column].Value = "Work Done more than once";
worksheet.Cells[row, ++column].Value = "Total";
worksheet.Cells[row, ++column].Value = "Poor On/Off Rate";
var poorOnOffRateColumn = column;
foreach (var year in simulationYears)
{
worksheet.Cells[row + 2, column].Value = year;
excelHelper.ApplyStyle(worksheet.Cells[row + 2, column]);
column++;
}
// Merge 2 rows for headers till column before Poor On/Off Rate
worksheet.Row(row).Height = 40;
for (int cellColumn = 1; cellColumn < poorOnOffRateColumn; cellColumn++)
{
excelHelper.MergeCells(worksheet, row, cellColumn, row + 1, cellColumn);
}
// Merge columns for Poor On/Off Rate
excelHelper.MergeCells(worksheet, row, poorOnOffRateColumn, row + 1, column - 1);
currentCell.Column = column;
// Add Years Data headers
var simulationHeaderTexts = GetSimulationHeaderTexts();
worksheet.Cells[row, ++column].Value = simulationYears[0] - 1;
column = currentCell.Column;
column = AddSimulationHeaderTexts(worksheet, column, row, simulationHeaderTexts, simulationHeaderTexts.Count - 5);
excelHelper.MergeCells(worksheet, row, currentCell.Column + 1, row, column);
// Empty column
currentCell.Column = ++column;
worksheet.Column(column).Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Column(column).Style.Fill.BackgroundColor.SetColor(Color.Gray);
var yearHeaderColumn = currentCell.Column;
simulationHeaderTexts.RemoveAll(_ => _.Equals("SD") || _.Equals("Posted"));
SpacerColumnNumbers = new List <int>();
foreach (var simulationYear in simulationYears)
{
worksheet.Cells[row, ++column].Value = simulationYear;
column = currentCell.Column;
column = AddSimulationHeaderTexts(worksheet, column, row, simulationHeaderTexts, simulationHeaderTexts.Count);
excelHelper.MergeCells(worksheet, row, currentCell.Column + 1, row, column);
if (simulationYear % 2 != 0)
{
excelHelper.ApplyColor(worksheet.Cells[row, currentCell.Column + 1, row, column], Color.Gray);
}
else
{
excelHelper.ApplyColor(worksheet.Cells[row, currentCell.Column + 1, row, column], Color.LightGray);
}
worksheet.Column(currentCell.Column).Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Column(currentCell.Column).Style.Fill.BackgroundColor.SetColor(Color.Gray);
SpacerColumnNumbers.Add(currentCell.Column);
currentCell.Column = ++column;
}
excelHelper.ApplyBorder(worksheet.Cells[row, initialColumn, row + 1, worksheet.Dimension.Columns]);
currentCell.Row = currentCell.Row + 2;
}