/// <summary> /// Read Apprenticeship Standards and populate ApprenticeshipStandard objects /// </summary> /// <returns></returns> public IEnumerable <ApprenticeshipStandard> ReadStandardsFromFile(XSSFWorkbook jobProfileWorkbook) { var apprenticeshipStandardList = new List <ApprenticeshipStandard>(); using (var reader = new StreamReader(@"SeedData\ApprenticeshipStandards.xlsx")) { var workbook = new XSSFWorkbook(reader.BaseStream); var sheet = workbook.GetSheet("ApprenticeshipStandards"); var nameIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Name").ColumnIndex; var referenceIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Reference").ColumnIndex; var levelIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Level").ColumnIndex; var maximumFundingIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Maximum Funding (£)").ColumnIndex; //Duration is in months var typicalDurationIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Typical Duration").ColumnIndex; var larsCodeIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "LARS code for providers only").ColumnIndex; var routeIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Route").ColumnIndex; var statusIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Status").ColumnIndex; for (int i = 1; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); var status = row.GetCell(statusIndex).StringCellValue; //What to do if a Job Profile has a standard that's not approved? -- Will not associate at the moment if (status.Equals("Approved for delivery", StringComparison.InvariantCultureIgnoreCase)) { var name = this.CleanseStandardName(row.GetCell(nameIndex).StringCellValue); var reference = row.GetCell(referenceIndex).StringCellValue; var level = row.GetCell(levelIndex).CellType == CellType.Numeric ? row.GetCell(levelIndex).NumericCellValue : 0; var maximumFunding = row.GetCell(maximumFundingIndex).CellType == CellType.Numeric ? row.GetCell(maximumFundingIndex).NumericCellValue : 0; var typicalDuration = row.GetCell(typicalDurationIndex).NumericCellValue; var larsCode = row.GetCell(larsCodeIndex).CellType == CellType.Numeric ? row.GetCell(larsCodeIndex).NumericCellValue : 0; var route = row.GetCell(routeIndex).StringCellValue; if (_processAll || _apprenticeshipStandardsRefList.Contains(reference)) { var apprenticeshipStandard = new ApprenticeshipStandard { Name = name, Reference = reference, Level = Convert.ToInt32(level), MaximumFunding = Convert.ToInt32(maximumFunding), Duration = Convert.ToInt32(typicalDuration), LARSCode = Convert.ToInt32(larsCode), Route = route.Split(',').Select(z => $"{char.ToUpper(z.Trim()[0]) + z.Trim().Substring(1)}").ToArray(), Type = "Standard" }; apprenticeshipStandardList.Add(apprenticeshipStandard); } } } } BuildFrameworks(jobProfileWorkbook, apprenticeshipStandardList); return(apprenticeshipStandardList); }
/// <summary> /// Add frameworks into the Standards collection /// </summary> /// <param name="apprenticeshipStandardList"></param> private static void BuildFrameworks(XSSFWorkbook workbook, List <ApprenticeshipStandard> apprenticeshipStandardList) { //Load frameworks where standards are empty var sheet = workbook.GetSheet("JobProfileSoc"); var apprenticeshipStandardsIndex = sheet.GetRow(0).Cells .Single(x => x.StringCellValue == "apprenticeshipstandards").ColumnIndex; var apprenticeshipFrameworksIndex = sheet.GetRow(0).Cells .Single(x => x.StringCellValue == "apprenticeshipframeworks").ColumnIndex; for (int i = 1; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); var standards = row.GetCell(apprenticeshipStandardsIndex).StringCellValue; var frameworks = row.GetCell(apprenticeshipFrameworksIndex).StringCellValue; if (string.IsNullOrWhiteSpace(standards) && !string.IsNullOrWhiteSpace(frameworks)) { var splitFrameworks = frameworks.Split(","); foreach (string framework in splitFrameworks) { var apprenticeshipStandard = new ApprenticeshipStandard { Name = framework, Type = "Framework" }; //Only add frameworks that don't already exist in the list if (!apprenticeshipStandardList.Any(x => x.Type == "Framework" && x.Name == framework)) { apprenticeshipStandardList.Add(apprenticeshipStandard); } } } } }