private Dictionary <TalentExcelImport, ITalent> ExcelImportAddDeductions(Dictionary <TalentExcelImport, ITalent> talentDic) { foreach (var talent in talentDic) { if (IsValidDeduction(talent.Key.VerwandteFertigkeiten, talent.Key.Ableiten)) { var deductionTalentStrings = GetSplitDeduction(talent.Key.VerwandteFertigkeiten, talent.Key.Ableiten); foreach (var deductionString in deductionTalentStrings) { var value = deductionString; var valueint = -1; var mainReqg = new Regex("[(][+][0-9]?[0-9][)]"); var innerReqg = new Regex("[0-9]?[0-9]"); var stringTalentReqg = new Regex("[(][A-Za-zäüß]{1,}[ ]?[A-Za-zäüß]{1,}[)]"); if (mainReqg.IsMatch(deductionString)) { value = mainReqg.Split(deductionString)[0].Trim(); if (!int.TryParse(innerReqg.Match(deductionString).ToString(), out valueint)) { valueint = -1; } } if (valueint == -1) { valueint = talent.Value.BaseDeduction; } ITalentDeduction deduction = null; var deductionTalent = talentDic.Where(x => x.Value.Name.StartsWith(value, StringComparison.CurrentCulture)); if (deductionTalent.Any()) { deduction = new TalentDeductionTalent(deductionTalent.First().Value, valueint, talent.Value.BaseDeduction); } else { var stringTalent = stringTalentReqg.Split(deductionString)[0].Trim(); var innerTalent = talentDic.Where(x => x.Value.Name.StartsWith(value, StringComparison.CurrentCulture)); if (innerTalent.Any()) { value = mainReqg.Split(deductionString)[0].Trim(); if (stringTalentReqg.IsMatch(value)) { var description = stringTalentReqg.Match(value).Value; description = description.Replace("(", "").Replace(")", ""); deduction = new TalentDeductionTalent(innerTalent.First().Value, valueint, talent.Value.BaseDeduction, description); } else { deduction = new TalentDeductionFreeText(deductionString); } } else { deduction = new TalentDeductionFreeText(deductionString); } } talent.Value.Deductions.Add(deduction); } } } return(talentDic); }
public static List <ITalent> ExcelImport(string importFile, out List <LanguageFamily> familieList) { familieList = new List <LanguageFamily>(); var ret = new List <ITalent>(); var excelTalentDic = new Dictionary <string, List <ExcelTalent> >(); var talentsWithDeduction = new Dictionary <ITalent, ExcelTalent>(); var talentWithRequirements = new Dictionary <ITalent, ExcelTalent>(); #region Import der Exel Datei SpreadsheetDocument document = SpreadsheetDocument.Open(importFile, false); WorkbookPart wbPart = document.WorkbookPart; List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList(); foreach (var sheet in sheets) { var orginalPosition = 0; var contentType = sheet.Name; var currentTitle = string.Empty; var excelTalents = new List <ExcelTalent>(); WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id)); var rowList = wsPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().ToList(); var titleRowHeaders = new List <string>(); var titleRow = rowList[0]; rowList.RemoveAt(0); //Titel Leiste Entfernen foreach (var cell in titleRow.Descendants <Cell>().ToList()) { var cellValue = ExcelImportGetCellValue(wbPart, cell); titleRowHeaders.Add(cellValue); } foreach (var row in rowList) { var excelTalent = new ExcelTalent(); var celllist = row.Descendants <Cell>().ToList(); var counter = 0; excelTalent.OrginalPosition = orginalPosition++; foreach (var cell in celllist) { var cellValue = ExcelImportGetCellValue(wbPart, cell); excelTalent.AddValue(titleRowHeaders[counter], cellValue); counter++; } var excelRowType = excelTalent.ExcelRowType(); if (excelRowType == ExcleRowType.Title) { var title = excelTalent.Talent.Replace(EXCELTITLE, ""); currentTitle = title; } else if (excelRowType == ExcleRowType.ValidTalent) { excelTalent.Title = currentTitle; excelTalents.Add(excelTalent); } } excelTalentDic.Add(contentType, excelTalents); } #endregion #region Talente Erstellen foreach (var talentGroup in excelTalentDic) { LanguageFamily currentLanguageFamily = null; var talentList = talentGroup.Value; var pos = 0; foreach (var excelTalent in talentList) { var name = string.Empty; var nameExtension = string.Empty; if (excelTalent.Talent.Contains("(")) { var items = excelTalent.Talent.Split('('); name = items[0]; nameExtension = items[1].Split(')').First(); } else { name = excelTalent.Talent; } ITalent newTalent = null; if (!string.IsNullOrEmpty(name)) { newTalent = SearchTalent(name, ret, GetTypeFromString(talentGroup.Key)); if (newTalent == null) { newTalent = CreateTalent( contentType: talentGroup.Key, probe: excelTalent.GetConvertAttribute(), be: excelTalent.BE, name: name, nameExtension: nameExtension, orginalPos: excelTalent.OrginalPosition); } } #region Sprache if (talentGroup.Key == nameof(TalentSpeaking)) { TalentSpeaking talentLanguage = null; TalentWriting talentWriting = null; if (newTalent != null) { talentLanguage = (TalentSpeaking)newTalent; } if (!string.IsNullOrEmpty(excelTalent.Schrift)) { talentWriting = (TalentWriting)SearchTalent(excelTalent.Schrift, ret, typeof(TalentWriting)); if (talentWriting == null) { talentWriting = (TalentWriting)CreateTalent( contentType: nameof(TalentWriting), probe: excelTalent.GetConvertAttribute(), be: excelTalent.Komplex2, name: excelTalent.Schrift, nameExtension: nameExtension); } } if (currentLanguageFamily == null || currentLanguageFamily.Name != excelTalent.Title) { currentLanguageFamily = new LanguageFamily(excelTalent.Title); familieList.Add(currentLanguageFamily); pos = 0; } if (talentLanguage != null) { currentLanguageFamily.Languages.Add(pos, talentLanguage); } if (talentWriting != null) { currentLanguageFamily.Writings.Add(pos, talentWriting); } if (talentWriting != null && !ret.Contains(talentWriting)) { ret.Add(talentWriting); } } #endregion if (excelTalent.IsValidVerwanteFertigkeit()) { talentsWithDeduction.Add(newTalent, excelTalent); } if (excelTalent.IsValidAnforderung()) { talentWithRequirements.Add(newTalent, excelTalent); } if (newTalent != null && !ret.Contains(newTalent)) { ret.Add(newTalent); } pos++; } } ret = new List <ITalent>(ret.OrderBy(x => x.Name)); foreach (var talentwithDeduction in talentsWithDeduction) { var deductionTalentStrings = talentwithDeduction.Value.GetSplitDeduction(); foreach (var deductionString in deductionTalentStrings) { var value = deductionString; var valueint = -1; var mainReqg = new Regex("[(][+][0-9]?[0-9][)]"); var innerReqg = new Regex("[0-9]?[0-9]"); var stringTalentReqg = new Regex("[(][A-Za-zäüß]{1,}[ ]?[A-Za-zäüß]{1,}[)]"); if (mainReqg.IsMatch(deductionString)) { value = mainReqg.Split(deductionString)[0].Trim(); if (!int.TryParse(innerReqg.Match(deductionString).ToString(), out valueint)) { valueint = -1; } } if (valueint == -1) { valueint = talentwithDeduction.Key.BaseDeduction; } ITalentDeduction deduction = null; var deductionTalent = ret.Where(x => x.Name.StartsWith(value, StringComparison.CurrentCulture)); if (deductionTalent.Any()) { deduction = new TalentDeductionTalent(deductionTalent.First(), valueint, talentwithDeduction.Key.BaseDeduction); } else { var stringTalent = stringTalentReqg.Split(deductionString)[0].Trim(); var innerTalent = ret.Where(x => x.Name == stringTalent).FirstOrDefault(); if (innerTalent != null) { value = mainReqg.Split(deductionString)[0].Trim(); if (stringTalentReqg.IsMatch(value)) { var description = stringTalentReqg.Match(value).Value; description = description.Replace("(", "").Replace(")", ""); deduction = new TalentDeductionTalent(innerTalent, valueint, talentwithDeduction.Key.BaseDeduction, description); } else { deduction = new TalentDeductionFreeText(deductionString); } } else { deduction = new TalentDeductionFreeText(deductionString); } } talentwithDeduction.Key.Deductions.Add(deduction); } } foreach (var talentWithRequirement in talentWithRequirements) { var splitRequirement = talentWithRequirement.Value.GetSplitRequirement(); foreach (var requirementString in splitRequirement) { ITalentRequirement requirement; var talent = (AbstractTalentGeneral)talentWithRequirement.Key; var value = requirementString; var valueStart = -1; var valueEnd = -1; var reqTalent = ret.Where(x => x.Name.StartsWith(value, StringComparison.CurrentCulture)); var startReqg = new Regex("[0-9]?[0-9][+][:]"); var endReqg = new Regex("[ ][0-9]?[0-9]"); if (endReqg.IsMatch(value)) { if (startReqg.IsMatch(value)) { var innerStartReq = new Regex("[0-9]?[0-9]"); var startvalue = startReqg.Match(value).ToString(); var truestartValue = innerStartReq.Match(startvalue).ToString(); valueStart = Int32.Parse(truestartValue, Helper.CultureInfo); value = startReqg.Split(value)[1]; } var startSplit = startReqg.Split(value); valueEnd = Int32.Parse(endReqg.Match(value).ToString(), Helper.CultureInfo); value = endReqg.Split(value)[0].Trim(); } requirement = new TalentRequirementFreeText(requirementString); if (reqTalent.Any() && valueEnd != -1 && valueStart != -1) { requirement = new TalentRequirementTalent(reqTalent.First(), valueEnd, valueStart); } else if (reqTalent.Any() && valueEnd != -1) { var trueReqTalent = reqTalent.First(); requirement = new TalentRequirementTalent(reqTalent.First(), valueEnd); } else { requirement = new TalentRequirementFreeText(requirementString); } talent.Requirements.Add(requirement); } } #endregion return(ret); }