private static List <SSFundingStream> LoadSpreadsheet(string filename) { var result = new List <SSFundingStream>(); using (var workbook = new XLWorkbook(filename)) { foreach (IXLWorksheet worksheet in workbook.Worksheets) { if (worksheet.Name != "Version control" && worksheet.Name != " FundStreamCode CCMDLC Mapping ") { var fundingStream = new SSFundingStream { DeliverableLineCodes = new List <SSDeliverableLineCode>() }; result.Add(fundingStream); fundingStream.PeriodCode = worksheet.Row(2).Cell(1).GetValue <string>(); var rows = worksheet.RowsUsed().Skip(1); SSDeliverableLineCode currentDLC = null; foreach (var row in rows) { if (row.Cell(2).Value != null && !string.IsNullOrWhiteSpace(row.Cell(2).Value.ToString())) { currentDLC = new SSDeliverableLineCode { FundLines = new List <SSFundLine>() }; fundingStream.DeliverableLineCodes.Add(currentDLC); currentDLC.LineCode = row.Cell(2).GetValue <int>(); currentDLC.DeliverableName = row.Cell(3).GetValue <string>(); currentDLC.FundingType = row.Cell(4).GetValue <string>(); } var fundLine = new SSFundLine { Line = row.Cell(5).GetValue <string>(), LineType = row.Cell(6).GetValue <string>(), ValueCalculation = row.Cell(7).GetValue <string>(), }; currentDLC.FundLines.Add(fundLine); } } } } result = result.OrderByDescending(ssf => ssf.PeriodCode).ToList(); return(result); }
private static SummarisationTypeModel GetSummarisationTypeForDLC(List <SummarisationTypeModel> summarisationTypes, SSDeliverableLineCode fundingStreamDeliverableLineCode) { // Summarisation type is defined from the FundingType column var fundingType = fundingStreamDeliverableLineCode.FundingType; if (fundingType.IndexOf("- ") > 0) { fundingType = fundingType.Substring(fundingType.IndexOf("- ") + 2); } if (fundingType.IndexOf(" & ") > 0) { fundingType = fundingType.Substring(0, fundingType.IndexOf(" & ")); } if (fundingType.IndexOf(" Only ") > 0) { fundingType = fundingType.Substring(0, fundingType.IndexOf(" Only ")); } fundingType = fundingType.Replace("calcs", "").Replace("calc", "").Replace("Trailblazer", "TBL").Trim(); return(summarisationTypes.Single(st => st.SummarisationType == $"{MainYear}{fundingType}")); }