private void AddDetailsForNonNHSCountAndArea(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, int totalBridgeCountOrDeckAreaStartRow, int nHSBridgeCountOrDeckAreaStartRow, bool isDeckArea) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column); for (var index = 0; index <= simulationYears.Count; index++) { var bridgeGood = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 1, column].Value); var bridgeFair = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 2, column].Value); var bridgePoor = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 3, column].Value); var NHSGood = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 1, column].Value); var NHSFair = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 2, column].Value); var NHSPoor = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 3, column].Value); var nonNHSGood = bridgeGood - NHSGood; var nonNHSFair = bridgeFair - NHSFair; var nonNHSPoor = bridgePoor - NHSPoor; worksheet.Cells[startRow, column].Value = nonNHSGood; worksheet.Cells[startRow + 1, column].Value = nonNHSFair; worksheet.Cells[startRow + 2, column].Value = nonNHSPoor; column++; } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column - 1]); excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, row, column - 1], "Number"); //if (isDeckArea) //{ // excelHelper.ApplyColor(worksheet.Cells[row - 1, startColumn + 1, row - 1, column - 1], Color.Khaki); //} bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row + 1, column); }
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 AddDetailsForTotalDeckArea(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, List <SimulationDataModel> simulationDataModels) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column); AddTotalDeckArea(worksheet, simulationDataModels, startRow, column, 0); foreach (var year in simulationYears) { row = startRow; column = ++column; AddTotalDeckArea(worksheet, simulationDataModels, row, column, year); } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row + 2, column]); excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, row + 2, column], "Number"); excelHelper.ApplyColor(worksheet.Cells[row + 2, startColumn + 1, row + 2, column], Color.Khaki); bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row + 3, column); }
private void AddCountsOfCulvertsWorkedOn(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell, ProjectRowNumberModel projectRowNumberModel) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column); worksheet.Cells[row++, column].Value = Properties.Resources.NoTreatment; worksheet.Cells[row++, column].Value = Properties.Resources.Preservation; worksheet.Cells[row++, column].Value = Properties.Resources.PreservationPoorFix; worksheet.Cells[row++, column].Value = Properties.Resources.Rehabilitation; worksheet.Cells[row++, column].Value = Properties.Resources.Replacement; worksheet.Cells[row++, column].Value = Properties.Resources.Total; column++; var fromColumn = column + 1; foreach (var year in simulationYears) { row = startRow; column = ++column; var noTreatmentCount = bridgeWorkSummaryComputationHelper.CalculateNoTreatmentCountForCulverts(simulationDataModels, year); worksheet.Cells[row, column].Value = noTreatmentCount; projectRowNumberModel.CulvertsNoTreatmentRow = row; int preservationPoorFixrow = row + 2; var preservationPoorFixCount = bridgeWorkSummaryComputationHelper.CalculatePreservationPoorFixCount(simulationDataModels, year); worksheet.Cells[preservationPoorFixrow, column].Value = preservationPoorFixCount; var preservationCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertPreservation) - preservationPoorFixCount; worksheet.Cells[++row, column].Value = preservationCount; projectRowNumberModel.CulvertsPreservationRow = row; row = preservationPoorFixrow + 1; var rehabilitationCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertRehabilitation); worksheet.Cells[row, column].Value = rehabilitationCount; projectRowNumberModel.CulvertsRehabilitationRow = row; var replacementCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertReplacement); worksheet.Cells[++row, column].Value = replacementCount; projectRowNumberModel.CulvertsReplacementRow = row; worksheet.Cells[++row, column].Value = preservationCount + preservationPoorFixCount + rehabilitationCount + replacementCount; } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]); excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.LightSteelBlue); bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column); }
private CurrentCell AddHeadersCells(ExcelWorksheet worksheet, List <string> headers, List <int> simulationYears) { int headerRow = 1; for (int column = 0; column < headers.Count; column++) { worksheet.Cells[headerRow, column + 1].Value = headers[column]; } var currentCell = new CurrentCell { Row = headerRow, Column = headers.Count }; excelHelper.ApplyBorder(worksheet.Cells[headerRow, 1, headerRow + 1, worksheet.Dimension.Columns]); AddDynamicHeadersCells(worksheet, currentCell, simulationYears); return(currentCell); }
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 AddCountsOfCulvertsWorkedOn(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell, ProjectRowNumberModel projectRowNumberModel, List <string> treatments) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column); foreach (var item in treatments) { if (item.ToLower().Contains("culvert")) { worksheet.Cells[row++, column].Value = item; } } worksheet.Cells[row++, column].Value = Properties.Resources.Total; column++; var fromColumn = column + 1; foreach (var year in simulationYears) { row = startRow; column = ++column; double culvertTotalCount = 0; foreach (var item in treatments) { if (item.ToLower().Contains("culvert")) { var culvertCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, item); worksheet.Cells[row, column].Value = culvertCount; projectRowNumberModel.TreatmentsCount.Add(item + "_" + year, row); row++; culvertTotalCount += culvertCount; } } worksheet.Cells[row, column].Value = culvertTotalCount; } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]); excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.LightSteelBlue); bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column); }
private int AddCostsOfCulvertWork(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column); int culvertTotalRow = 0; worksheet.Cells[row++, column].Value = Properties.Resources.Preservation; worksheet.Cells[row++, column].Value = Properties.Resources.Rehabilitation; worksheet.Cells[row++, column].Value = Properties.Resources.Replacement; worksheet.Cells[row++, column].Value = Properties.Resources.CulvertTotal; column++; var fromColumn = column + 1; foreach (var year in simulationYears) { row = startRow; column = ++column; var preservationCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertPreservation); worksheet.Cells[row, column].Value = preservationCost; var rehabilitationCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertRehabilitation); worksheet.Cells[++row, column].Value = rehabilitationCost; var replacementCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertReplacement); worksheet.Cells[++row, column].Value = replacementCost; worksheet.Cells[++row, column].Value = preservationCost + rehabilitationCost + replacementCost; culvertTotalRow = row; } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]); excelHelper.SetCustomFormat(worksheet.Cells[startRow, fromColumn, row, column], "NegativeCurrency"); excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.DarkSeaGreen); bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column); return(culvertTotalRow); }
private void AddDetailsForTotalBridgeAndDeckPercent(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, int dataStartRow) { int startRow, startColumn, row, column; bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column); for (var index = 0; index <= simulationYears.Count; index++) { var sumFormula = "SUM(" + worksheet.Cells[dataStartRow, column, dataStartRow + 2, column] + ")"; worksheet.Cells[startRow, column].Formula = worksheet.Cells[dataStartRow, column] + "/" + sumFormula; worksheet.Cells[startRow + 1, column].Formula = worksheet.Cells[dataStartRow + 1, column] + "/" + sumFormula; worksheet.Cells[startRow + 2, column].Formula = worksheet.Cells[dataStartRow + 2, column] + "/" + sumFormula; column++; } excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, startRow + 2, column - 1]); excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, startRow + 2, column], "Percentage"); bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row, column - 1); }