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