Dictionary <string, Species> ImportMonsterSpecies(ExcelImporter excel, Dictionary <string, Element> elements, Dictionary <string, Ability> abilities) { if (!excel.TryGetTable("Monsters", out ExcelImporter.Table table)) { throw new System.IO.InvalidDataException($"No Monsters table was found!"); } var assets = GetAllAssetsOfType <Species>(); int originalCount = assets.Count; int nonBlankCount = 0; for (int row = 1; row <= table.RowCount; row++) { string name = table.GetValue <string>(row, "Name"); if (string.IsNullOrWhiteSpace(name)) { continue; } nonBlankCount++; var monster = GetOrCreateAsset(name, assets, "Monsters/Species"); string rarityName = table.GetValue <string>(row, "Rarity"); if (!System.Enum.TryParse(rarityName, out monster.rarity)) { Debug.LogError($"Unknown rarity {rarityName} used by monster species {name}."); } string elementName = table.GetValue <string>(row, "Element"); if (!elements.TryGetValue(elementName, out monster.element)) { Debug.LogError($"Element {elementName} used by monster {name} was not found."); } monster.baseStats.health = table.GetValue <int>(row, "Health"); monster.baseStats.stamina = table.GetValue <int>(row, "Stamina"); monster.baseStats.attack = table.GetValue <int>(row, "Attack"); monster.baseStats.defense = table.GetValue <int>(row, "Defense"); monster.baseStats.luck = table.GetValue <int>(row, "Luck"); monster.abilities[0] = SetAbility(row, 1, table, abilities); monster.abilities[1] = SetAbility(row, 2, table, abilities); monster.OnValidate(); } MarkChangesForSaving(speciesCollection); speciesCollection.species.Clear(); foreach (var monster in assets.Values) { speciesCollection.species.Add(monster); } speciesCollection.OnEnable(); Debug.Log($"Successfully imported {nonBlankCount} monsters " + $"({assets.Count - originalCount} new, {assets.Count - nonBlankCount} unused)."); return(assets); }
/// <summary> /// 生成模板 /// </summary> /// <returns></returns> public async Task <ExportFileInfo> GenerateTemplateFileAsync <T>(string filePath) where T : class, new() { FileUtil.DeleteFile(filePath); var result = await ExcelImporter.GenerateTemplate <T>(filePath); return(result); }
public void ImportData_FileDoesntExist_Throws() { var type = TypeExcelFactory.EmptyClass; var ex = Assert.Catch <FileNotFoundException>(() => ExcelImporter.ImportData("notexistedfile.xlsx", type, 0)); StringAssert.Contains("Could not find file", ex.Message); }
public void ImportData_FileNameConsistsOfExtensionOnly_Throws() { var type = TypeExcelFactory.EmptyClass; var ex = Assert.Catch <FileNotFoundException>(() => ExcelImporter.ImportData(".xlsx", type, 0)); StringAssert.Contains("Could not find file", ex.Message); }
public void ImportData_FileNameIsWhitespaces_Throws() { var type = TypeExcelFactory.EmptyClass; var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(" ", type, 0)); StringAssert.Contains("The path is not of a legal form.", ex.Message); }
public void ImportData_FileNameIsEmptyString_Throws() { var type = TypeExcelFactory.EmptyClass; var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(String.Empty, type, 0)); StringAssert.Contains("Empty path name is not legal.", ex.Message); }
public void ImportData_TypeIsNull_Throws() { var stream = new MemoryStream(new byte[1]); var ex = Assert.Catch <ArgumentNullException>(() => ExcelImporter.ImportData(stream, null, 0)); StringAssert.Contains("Type cannot be null.", ex.Message); }
public override void ExecuteCommand(CancellationToken token) { var file = DialogManager.ShowSelectFileDialog(); if (file == null) { return; } var ok = new Action <string>((tableName) => { ExcelImporter importer = new ExcelImporter(); DDLManager ddlManager = new DDLManager(); var meta = importer.GetMetaData(file.FullName); ddlManager.CreateTable(tableName, meta); importer.ImportToTable(file.FullName, tableName, meta); ShellManager.AppendToEndOfSelection("SELECT * FROM " + tableName); ShellManager.AppendToEndOfSelection("--DROP TABLE " + tableName); }); DialogManager.GetDialogInputFromUser("Choose target table name (This table will be created)", "ExcelImport", ok); }
private ActionResult Import(int start = 0) { Stopwatch sw = Stopwatch.StartNew(); try { // Seed VISIT records OracleDbHelper.SeedVisitTable(db); // Use ExcelImporter to import CP_ORDER table. var excelImporter = new ExcelImporter(db); excelImporter.Import(start); // Use OracleDbHelper to import all other tables. OracleDbHelper.PopulateDB <CP_VITAL_SIGN>(db); OracleDbHelper.PopulateDB <CP_EXAM>(db); OracleDbHelper.PopulateDB <CP_LAB_TEST>(db); OracleDbHelper.PopulateDB <CP_MEDICAL_DOC>(db); // alone cost: 3.23801252166667 minutes. //return ("Total cost: " + sw.Elapsed.TotalMinutes + "minutes. "); return(RedirectToAction("Index")); } catch (Exception e) { return(RedirectToAction("Error", new{ message = e.Message })); } }
public async Task <byte[]> GenerateTemplate() { IImporter Importer = new ExcelImporter(); var content = await Importer.GenerateTemplateBytes <ArticleImportDto>(); return(content); }
private static ImportExcelDataCommand CreateImportExcelDataCommand() { var excelDataImporter = new ExcelImporter(); var importExcelDataCommand = new ImportExcelDataCommand(excelDataImporter); return(importExcelDataCommand); }
public async Task MultipleSheetGenerateTemplate_Test() { var importer = new ExcelImporter(); var result = await importer.GenerateTemplateBytes <ImportClassStudentDto>(); var filePath = GetTestFilePath($"{nameof(MultipleSheetGenerateTemplate_Test)}.xlsx"); DeleteFile(filePath); result.ShouldNotBeNull(); result.Length.ShouldBeGreaterThan(0); result.ToExcelExportFileInfo(filePath); File.Exists(filePath).ShouldBeTrue(); using (var pck = new ExcelPackage(new FileInfo(filePath))) { //检查转换结果 pck.Workbook.Worksheets.Count.ShouldBe(2); #if NET461 pck.Workbook.Worksheets[1].Name.ShouldBe("1班导入数据"); pck.Workbook.Worksheets[2].Name.ShouldBe("2班导入数据"); #else pck.Workbook.Worksheets[0].Name.ShouldBe("1班导入数据"); pck.Workbook.Worksheets[1].Name.ShouldBe("2班导入数据"); #endif } }
private void ImportResourceListAction_Execute(object sender, SimpleActionExecuteEventArgs e) { ExcelImporter ei = new ExcelImporter(); ei.ImportResourceList(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, @"C:\repositories\BadgES\Files\Badge_GDC Manila Resource List template v3.xlsx"); }
private void FormStatistics_Load(object sender, EventArgs e) { this.Location = this.Owner.Location; this.Left += this.Owner.ClientSize.Width / 2 - this.Width / 2; this.Top += this.Owner.ClientSize.Height / 2 - this.Height / 2; var today = DateTime.Now; datePickerStart.Value = new DateTime(today.Year, today.Month, 1); datePickerEnd.Value = new DateTime(today.Year, today.Month, DateTime.DaysInMonth(today.Year, today.Month)); var startDate = datePickerStart.Value; var endDate = datePickerEnd.Value; var currentAccount = Account.Instance; currentAccount.Clear(); var importer = new ExcelImporter(); var currentList = ImportFromFile.ImportData(importer); foreach (var transaction in currentList) { currentAccount.AddTransaction(transaction); } labelTotalMoney.Text = string.Format("Your current money: {0}", currentAccount.Balance); ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.RegularIncome, StatisticsTextBox); ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.IrregularIncome, StatisticsTextBox); ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.RegularExpense, StatisticsTextBox); ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.IrregularExpense, StatisticsTextBox); CheckIfEmpty(datePickerStart.Value, datePickerEnd.Value, "Transaction", StatisticsTextBox); }
public static void Main(string[] args) { Console.WriteLine("Iniciando programa..."); string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // obtiene la ruta del escritorio del usuario en sesion Console.Write("Indique el nombre de archivo (Formato .xlsx) ubicado en su escritorio: "); string fileName = Console.ReadLine(); if (fileName.Remove(0, fileName.Length - 4) == ".xlsx") // si es formato ".xlsx" entra { using (var stream = File.OpenRead(path + "/" + fileName)) using (var importer = new ExcelImporter(stream)) { ExcelSheet sheet = importer.ReadSheet(); Event[] events = sheet.ReadRows <Event>().ToArray(); Console.WriteLine(events[0].Name); // Pub Quiz Console.WriteLine(events[1].Name); // Live Music Console.WriteLine(events[2].Name); // Live Football } } else { Console.WriteLine("Solo archivos de formato .xlsx"); } Console.ReadKey(); }
public void SetUp() { _fileStreamWrapperMock = new Mock <IFileStreamWrapper>(); _excelCreatorMock = new Mock <IExcelCreator>(); _excelFileMock = new Mock <IExcelFile>(); _excelImporter = new ExcelImporter <TestExcelObject>(_excelCreatorMock.Object, _fileStreamWrapperMock.Object); }
public static IEnumerable <TEntity> Load <TEntity, TConfiguration>(byte[] content) where TConfiguration : ExcelClassMap <TEntity>, new() { IEnumerable <TEntity> entities; try { using (var ms = new MemoryStream(content)) using (var importer = new ExcelImporter(ms)) { var index = GetHeadingIndex(content); if (index < 0) { throw new InvalidDataException("The file does not contain data-sheet data."); } importer.Configuration.RegisterClassMap <TConfiguration>(); var sheet = importer.ReadSheet(); sheet.HeadingIndex = index; entities = sheet.ReadRows <TEntity>().ToList(); } } catch (Exception ex) { var message = $"Cannot load collection of {typeof(TEntity).Name} type from the give file stream."; throw new ImportFormatException(message, ex); } return(entities); }
private async void ImportExcelDataIntoDatabase() { try { var message = "The program will read into memory all rows from the spreadsheet, " + "and then process saving them to the database.\n" + "Ready to begin the import process?"; var dr = XtraMessageBox.Show(message, "Begin Import?", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (dr == DialogResult.No) { return; } var importer = new ExcelImporter(gridViewExcelSource, MappingInfo, Persistent); EventMediator.GetInstance().OnImportStarted(); EventMediator.GetInstance().ImportProgressUpdated += OnProgressUpdated; marquee.Properties.Stopped = false; await importer.Import(); message = "Data has been imported successfully!"; XtraMessageBox.Show(message, "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information); marquee.Properties.Stopped = true; EventMediator.GetInstance().OnImportComplete(); } catch (Exception ex) { ex.Report(); } }
public void Imports_data_set_instances_and_annotations() { ExcelImporter importer = new ExcelImporter(new ExcelFactory().GetTestDataFolder()); var dataSet = importer.Import("BurningKnight"); var classes = dataSet.GetInstancesOfType(SnippetType.Class); classes.Count.ShouldBe(79); classes.Count(c => c.Annotations.Count == 3).ShouldBe(79); var functions = dataSet.GetInstancesOfType(SnippetType.Function); functions.Count.ShouldBe(313); functions.Count(c => c.Annotations.Count == 1).ShouldBe(7); functions.Count(c => c.Annotations.Count == 2).ShouldBe(10); functions.Count(c => c.Annotations.Count == 3).ShouldBe(296); var annotators = new List <Annotator>() { new Annotator(1, 6, 1), new Annotator(2, 2, 2), new Annotator(3, 2, 3) }; JoinInstancesAndAnnotators(classes, annotators); var allClassAnnotations = classes.SelectMany(c => c.Annotations); allClassAnnotations.First(a => a.Annotator.Id == 1).Annotator.ShouldBe(annotators.Find(a => a.Id == 1)); allClassAnnotations.First(a => a.Annotator.Id == 2).Annotator.ShouldBe(annotators.Find(a => a.Id == 2)); allClassAnnotations.First(a => a.Annotator.Id == 3).Annotator.ShouldBe(annotators.Find(a => a.Id == 3)); }
public void BuildProductivities_IntegrationTest() { var service = GetFakeRaportService(); var sheetTable = ExcelImporter.GetSheetTable("test.xlsx".AppendAssemblyPath("Contexts\\IntegrationTests")); TypeRepository.TryGetPropertyMap(sheetTable, typeof(TestImportModel), out var propertyMap); var visitor = new TestImportModelVisitor(_operations); var actions = ExcelImporter.GetDataFromTable(sheetTable, propertyMap, new ImportModelConverter <TestImportModel, EmployeeActionBase>(visitor)); var shortBreaks = new ShortBreakSchedule { Duration = TimeSpan.FromMinutes(10), FirstBreakTime = new TimeSpan(9, 55, 0), Periodicity = TimeSpan.FromHours(2) }; var shift = new Shift { Lunch = TimeSpan.FromMinutes(30), }; var productivities = service.Build(actions, shortBreaks, shift); var employeeProductivity = new EmployeeProductivity(new Employee(), productivities); var totalTime = employeeProductivity.GetTotalWorkHours(); Assert.That(totalTime, Is.GreaterThan(5.7)); var pause = employeeProductivity.DowntimePeriods.Sum(d => d.Duration.TotalSeconds); Assert.That(pause, Is.GreaterThan(16000)); }
public void ImportData_TypeWithoutPublicParameterlessCtor_Throws() { var type = TypeExcelFactory.ClassWithParameterizedCtor; var stream = StreamFactory.GetExcelMemoryStream(); var ex = Assert.Catch <TypeAccessException> (() => ExcelImporter.ImportData(stream, type, 0)); StringAssert.Contains("has no public parameterless constructor!", ex.Message); }
public void ImportData_FileNameIsNull_Throws() { var type = TypeExcelFactory.EmptyClass; string path = null; var ex = Assert.Catch <ArgumentException> (() => ExcelImporter.ImportData(path, type, 0)); StringAssert.Contains("Path cannot be null.", ex.Message); }
public void ImportData_SheetIndexIsNegative_Throws() { var type = TypeExcelFactory.EmptyClass; var stream = new MemoryStream(new byte[1]); var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(stream, type, -1)); StringAssert.Contains("Index of sheet cannot be less than zero.", ex.Message); }
public ActionResult Index() { string excelPath = ConfigurationManager.AppSettings["exceppath"]; IList <SaleOrder> saleorderlist = ExcelImporter.ReadToList <SaleOrder>(excelPath, 2, 1); return(View()); }
public void Ctor_Stream() { using (var stream = Helpers.GetResource("Primitives.xlsx")) using (var importer = new ExcelImporter(stream)) { Assert.Equal("Primitives", importer.ReadSheet().Name); } }
public void ImportData_StreamDoesNotContainExcelData_Throws() { var type = TypeExcelFactory.EmptyClass; var stream = new MemoryStream(new byte[1]); var ex = Assert.Catch <FileFormatException>(() => ExcelImporter.ImportData(stream, type, 0)); StringAssert.Contains("has invalid data format or has no sheetTable with 0 index.", ex.Message); }
public void ExcelImport(ImportCheckResponse checkResponse) { using (IObjectRepository auditedTdb = DBContext.CreateAuditable(CheckPoint.Instance.UserName, CheckPoint.Instance.HostAddress)) { ExcelImporter importer = new ExcelImporter(auditedTdb); importer.Import(checkResponse); } }
private static async Task <IEnumerable <AdministrativeDistrict> > GetAdministrativeDistrictsAsync(string rootPath) { var file = Path.Combine(rootPath, "Baidu/administrative_districts_and_towns_202003.xlsx"); var importer = new ExcelImporter(); var result = await importer.Import <AdministrativeDistrict>(file); return(result.Data); }
Dictionary <string, Ability> ImportAbilities(ExcelImporter excel, Dictionary <string, Element> elements) { if (!excel.TryGetTable("Abilities", out ExcelImporter.Table table)) { throw new System.IO.InvalidDataException($"No Abilities table was found!"); } var specials = GetAllAssetsOfType <SpecialEffect>(); var assets = GetAllAssetsOfType <Ability>(); int originalCount = assets.Count; int nonBlankCount = 0; for (int row = 1; row <= table.RowCount; row++) { string name = table.GetValue <string>(row, "Name"); if (string.IsNullOrWhiteSpace(name)) { continue; } nonBlankCount++; var ability = GetOrCreateAsset(name, assets, "Abilities"); string elementName = table.GetValue <string>(row, "Element"); if (!elements.TryGetValue(elementName, out ability.element)) { Debug.LogError($"Element {elementName} used by ability {name} was not found."); } ability.staminaCost = table.GetValue <int>(row, "Stamina Cost"); ability.minDamage = table.GetValue <int>(row, "Min Damage"); ability.maxDamage = table.GetValue <int>(row, "Max Damage"); string specialName = table.GetValue <string>(row, "Special Effect"); if (string.IsNullOrWhiteSpace(specialName) || !specials.TryGetValue(specialName, out SpecialEffect special)) { if (!string.IsNullOrWhiteSpace(specialName)) { Debug.LogError($"Special effect {specialName} used by ability {name} was not found."); } ability.special = null; ability.specialChance = 0; } else { ability.special = special; ability.specialChance = table.GetValue <float>(row, "Effect Chance"); } } Debug.Log($"Successfully imported {nonBlankCount} abilities " + $"({assets.Count - originalCount} new, {assets.Count - nonBlankCount} unused)."); return(assets); }
private void InitializeDependencis() { _addressBuilder = new AddressBuilder(); _personBuilder = new PersonBuilder(); _financialStateBuilder = new FinancialStateBuilder(); _agreementBuilder = new AgreementBuilder(); _agreementDao = new AgreementDao(); excelImporter = new ExcelImporter(); }
public void Test() { var models = GetModels(); var exporter = new ExcelExporter(); var bytes = exporter.ObjectToExcelBytes(models); var path = GetFilePath("text.xls"); Console.WriteLine(path); File.WriteAllBytes(path, bytes); Assert.IsTrue(File.Exists(path)); var importer = new ExcelImporter(); var result = importer.ExcelToObject<ReportModel>(path); Assert.AreEqual(models.Count, result.Count()); }
private static void ImportProducesFromExcel() { var db = new ChemicalsDbContext(); IZipExtractor zipExtractor = new ZipExtractor(); ExcelImporter<Produce> k = new ExcelImporter<Produce>(zipExtractor); ICollection<Produce> produces = k.ImportModelsDataFromZipFile("../../../Files/Produces.zip", "./tests1"); foreach (var item in produces) { db.Produces.Add(item); } db.SaveChanges(); }
public void ImportInterviewResult() { if (!string.IsNullOrWhiteSpace(FileName)) { using (var context = new VnrHrmDataContext()) { IUnitOfWork unitOfWork = new UnitOfWork(context); ExcelImporter importer = new ExcelImporter(context); importer.FileName = FileName; var objectType = typeof(Rec_ImportInterviewResultEntity); List<ImportItemInfo> importTemplateItems = new List<ImportItemInfo>(); importTemplateItems.Add(CreateImportItemInfo(0, false, true, 1, "CandidateCode", string.Empty, FieldMappings["CandidateCode"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(1, true, true, 1, "CandidateName", string.Empty, FieldMappings["CandidateName"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(2, false, true, 1, "JobVacancyCode", string.Empty, FieldMappings["JobVacancyCode"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(3, true, true, 1, "CandidateEmail", string.Empty, FieldMappings["CandidateEmail"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(4, true, true, 1, "LanguageCode", string.Empty, FieldMappings["LanguageCode"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(5, true, true, 1, "Group1Score1", string.Empty, FieldMappings["Group1Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(6, true, true, 1, "Group1Score2", string.Empty, FieldMappings["Group1Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(7, true, true, 1, "Group1Score3", string.Empty, FieldMappings["Group1Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(8, false, true, 1, "GroupCondition1", string.Empty, FieldMappings["GroupCondition1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(9, false, true, 1, "GroupResult1", string.Empty, FieldMappings["GroupResult1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(10, true, true, 1, "Group2Score1", string.Empty, FieldMappings["Group2Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(11, true, true, 1, "Group2Score2", string.Empty, FieldMappings["Group2Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(12, true, true, 1, "Group2Score3", string.Empty, FieldMappings["Group2Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(13, false, true, 1, "GroupCondition2", string.Empty, FieldMappings["GroupCondition2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(14, false, true, 1, "GroupResult2", string.Empty, FieldMappings["GroupResult2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(15, true, true, 1, "Group3Score1", string.Empty, FieldMappings["Group3Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(16, true, true, 1, "Group3Score2", string.Empty, FieldMappings["Group3Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(17, true, true, 1, "Group3Score3", string.Empty, FieldMappings["Group3Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(18, false, true, 1, "GroupCondition3", string.Empty, FieldMappings["GroupCondition3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(19, false, true, 1, "GroupResult3", string.Empty, FieldMappings["GroupResult3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(20, true, true, 1, "Group4Score1", string.Empty, FieldMappings["Group4Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(21, true, true, 1, "Group4Score2", string.Empty, FieldMappings["Group4Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(22, true, true, 1, "Group4Score3", string.Empty, FieldMappings["Group4Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(23, false, true, 1, "GroupCondition4", string.Empty, FieldMappings["GroupCondition4"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(24, false, true, 1, "GroupResult4", string.Empty, FieldMappings["GroupResult4"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(25, false, true, 1, "GroupCondition5", string.Empty, FieldMappings["GroupCondition5"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(26, true, true, 1, "Group5Score1", string.Empty, FieldMappings["Group5Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(27, true, true, 1, "Group5Score2", string.Empty, FieldMappings["Group5Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(28, true, true, 1, "Group5Score3", string.Empty, FieldMappings["Group5Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(29, false, true, 1, "GroupResult5", string.Empty, FieldMappings["GroupResult5"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(30, true, true, 1, "Group6Score1", string.Empty, FieldMappings["Group6Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(31, true, true, 1, "Group6Score2", string.Empty, FieldMappings["Group6Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(32, true, true, 1, "Group6Score3", string.Empty, FieldMappings["Group6Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(33, false, true, 1, "GroupCondition6", string.Empty, FieldMappings["GroupCondition6"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(34, false, true, 1, "GroupResult6", string.Empty, FieldMappings["GroupResult6"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(35, true, true, 1, "Group7Score1", string.Empty, FieldMappings["Group7Score1"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(36, true, true, 1, "Group7Score2", string.Empty, FieldMappings["Group7Score2"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(37, true, true, 1, "Group7Score3", string.Empty, FieldMappings["Group7Score3"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(38, false, true, 1, "GroupCondition7", string.Empty, FieldMappings["GroupCondition7"], string.Empty)); importTemplateItems.Add(CreateImportItemInfo(39, false, true, 1, "GroupResult7", string.Empty, FieldMappings["GroupResult7"], string.Empty)); var dtImportObject = importer.ReadExcelData(objectType, 0, 4, 0, importTemplateItems); listInvisibleField = importTemplateItems.Where(d => d.IsOutOfRange).Select(d => d.ExcelField1.TrimAll()).ToList(); var listJobVacancyCode = dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["JobVacancyCode"])).Select(d => d[FieldMappings["JobVacancyCode"]].GetString()).Distinct().ToList(); var listLanguageCode = dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["LanguageCode"])).Select(d => d[FieldMappings["LanguageCode"]].GetString()).Distinct().ToList(); var listCandidateCode = dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["CandidateCode"])).Select(d => d[FieldMappings["CandidateCode"]].GetString()).Distinct().ToList(); var listGroupConditionCode = dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition1"])).Select(d => d[FieldMappings["GroupCondition1"]].GetString()).Distinct().ToList(); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition2"])).Select(d => d[FieldMappings["GroupCondition2"]].GetString()).Distinct().ToList()); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition3"])).Select(d => d[FieldMappings["GroupCondition3"]].GetString()).Distinct().ToList()); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition4"])).Select(d => d[FieldMappings["GroupCondition4"]].GetString()).Distinct().ToList()); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition5"])).Select(d => d[FieldMappings["GroupCondition5"]].GetString()).Distinct().ToList()); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition6"])).Select(d => d[FieldMappings["GroupCondition6"]].GetString()).Distinct().ToList()); listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d => !d.IsNull(FieldMappings["GroupCondition7"])).Select(d => d[FieldMappings["GroupCondition7"]].GetString()).Distinct().ToList()); var listJobVacancy = new List<Rec_JobVacancy>().Select(d => new { d.ID, d.Code }).ToList(); var listLanguage = new List<Cat_NameEntity>().Select(d => new { d.ID, d.Code }).ToList(); var listCandidate = new List<Rec_Candidate>().Select(d => new { d.ID, d.CodeCandidate, d.CandidateName }).ToList(); var listGroupCondition = new List<Rec_GroupCondition>().Select(d => new { d.ID, d.Code, d.GroupName }).ToList(); var listRecruitmentHistory = new List<Rec_RecruitmentHistory>().Select(d => new { d.ID, d.CandidateID, d.DateApply }).ToList(); var listInterview = new List<Rec_Interview>().Select(d => new { d.ID, d.CandidateID, d.GroupConditionID, d.RecruitmentHistoryID }).ToList(); foreach (var item in listLanguageCode.Chunk(1000)) { listLanguage.AddRange(unitOfWork.CreateQueryable<Cat_NameEntity>(d => item.Contains(d.Code)).Select(d => new { d.ID, d.Code }).ToList()); } foreach (var item in listJobVacancyCode.Chunk(1000)) { listJobVacancy.AddRange(unitOfWork.CreateQueryable<Rec_JobVacancy>(d => item.Contains(d.Code)).Select(d => new { d.ID, d.Code }).ToList()); } foreach (var item in listCandidateCode.Chunk(1000)) { listCandidate.AddRange(unitOfWork.CreateQueryable<Rec_Candidate>(d => item.Contains(d.CodeCandidate)).Select(d => new { d.ID, d.CodeCandidate, d.CandidateName }).ToList()); } foreach (var item in listGroupConditionCode.Chunk(1000)) { listGroupCondition.AddRange(unitOfWork.CreateQueryable<Rec_GroupCondition>(d => item.Contains(d.Code)).Select(d => new { d.ID, d.Code, d.GroupName }).ToList()); } var listCandidateID = listCandidate.Select(d => d.ID).Distinct().ToList(); foreach (var item in listCandidateID.Chunk(1000)) { listRecruitmentHistory.AddRange(unitOfWork.CreateQueryable<Rec_RecruitmentHistory>(d => item.Contains(d.CandidateID)).Select(d => new { d.ID, d.CandidateID, d.DateApply }).ToList()); } foreach (var item in listCandidateID.Chunk(1000)) { listInterview.AddRange(unitOfWork.CreateQueryable<Rec_Interview>(d => d.CandidateID.HasValue && item.Contains(d.CandidateID.Value)).Select(d => new { d.ID, d.CandidateID, d.GroupConditionID, d.RecruitmentHistoryID }).ToList()); } dtImportObject.Columns.Add("CandidateID", typeof(Guid)); dtImportObject.Columns.Add("JobVacancyID", typeof(Guid)); dtImportObject.Columns.Add("LanguageID", typeof(Guid)); dtImportObject.Columns.Add("InterviewID", typeof(Guid)); dtImportObject.Columns.Add("RecruitmentHistoryID", typeof(Guid)); dtImportObject.Columns.Add("InterviewID1", typeof(Guid)); dtImportObject.Columns.Add("InterviewID2", typeof(Guid)); dtImportObject.Columns.Add("InterviewID3", typeof(Guid)); dtImportObject.Columns.Add("InterviewID4", typeof(Guid)); dtImportObject.Columns.Add("InterviewID5", typeof(Guid)); dtImportObject.Columns.Add("InterviewID6", typeof(Guid)); dtImportObject.Columns.Add("InterviewID7", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID1", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID2", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID3", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID4", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID5", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID6", typeof(Guid)); dtImportObject.Columns.Add("GroupConditionID7", typeof(Guid)); var dtInvalidObject = new DataTable("InvalidObject"); dtInvalidObject.Columns.Add("DataField", typeof(string)); dtInvalidObject.Columns.Add("InvalidValue", typeof(string)); dtInvalidObject.Columns.Add("ExcelField", typeof(string)); dtInvalidObject.Columns.Add("ExcelValue", typeof(object)); dtInvalidObject.Columns.Add("ValueType", typeof(string)); dtInvalidObject.Columns.Add("Desciption", typeof(string)); List<DataRow> listInvalidRow = new List<DataRow>(); foreach (DataRow excelRow in dtImportObject.Rows) { var excelRowIndex = excelRow.Field<int>(DefaultConstants.ExcelRowIndex); var nullData = HRM.Business.Main.Domain.InvalidDataType.NullData.ToString().TranslateString(); var duplicateInDb = HRM.Business.Main.Domain.InvalidDataType.DuplicateInDb.ToString().TranslateString(); var duplicateInFile = HRM.Business.Main.Domain.InvalidDataType.DuplicateInFile.ToString().TranslateString(); var referenceNotFound = HRM.Business.Main.Domain.InvalidDataType.ReferenceNotFound.ToString().TranslateString(); var listGroupConditionID = new List<Guid>(); Guid recruitmentHistoryID = Guid.Empty; Guid candidateID = Guid.Empty; bool isInvalid = false; foreach (var templateItem in importTemplateItems) { string fieldName = templateItem.ChildFieldLevel1.TrimAll(); var excelField = templateItem.ExcelField1.TrimAll(); string excelAddress = excelField + (excelRowIndex + 1); if (excelRow.IsNull(excelField)) { if (!templateItem.AllowNull.GetBoolean()) { bool isNotNullGroup = false; if (fieldName == "GroupCondition1" || fieldName == "GroupResult1") { if (!excelRow.IsNull(FieldMappings["GroupCondition1"]) || !excelRow.IsNull(FieldMappings["Group1Score1"]) || !excelRow.IsNull(FieldMappings["Group1Score2"]) || !excelRow.IsNull(FieldMappings["Group1Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult1"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition2" || fieldName == "GroupResult2") { if (!excelRow.IsNull(FieldMappings["GroupCondition2"]) || !excelRow.IsNull(FieldMappings["Group2Score1"]) || !excelRow.IsNull(FieldMappings["Group2Score2"]) || !excelRow.IsNull(FieldMappings["Group2Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult2"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition3" || fieldName == "GroupResult3") { if (!excelRow.IsNull(FieldMappings["GroupCondition3"]) || !excelRow.IsNull(FieldMappings["Group3Score1"]) || !excelRow.IsNull(FieldMappings["Group3Score2"]) || !excelRow.IsNull(FieldMappings["Group3Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult3"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition4" || fieldName == "GroupResult4") { if (!excelRow.IsNull(FieldMappings["GroupCondition4"]) || !excelRow.IsNull(FieldMappings["Group4Score1"]) || !excelRow.IsNull(FieldMappings["Group4Score2"]) || !excelRow.IsNull(FieldMappings["Group4Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult4"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition5" || fieldName == "GroupResult5") { if (!excelRow.IsNull(FieldMappings["GroupCondition5"]) || !excelRow.IsNull(FieldMappings["Group5Score1"]) || !excelRow.IsNull(FieldMappings["Group5Score2"]) || !excelRow.IsNull(FieldMappings["Group5Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult5"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition6" || fieldName == "GroupResult6") { if (!excelRow.IsNull(FieldMappings["GroupCondition6"]) || !excelRow.IsNull(FieldMappings["Group6Score1"]) || !excelRow.IsNull(FieldMappings["Group6Score2"]) || !excelRow.IsNull(FieldMappings["Group6Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult6"])) { isNotNullGroup = true; } } else if (fieldName == "GroupCondition7" || fieldName == "GroupResult7") { if (!excelRow.IsNull(FieldMappings["GroupCondition7"]) || !excelRow.IsNull(FieldMappings["Group7Score1"]) || !excelRow.IsNull(FieldMappings["Group7Score2"]) || !excelRow.IsNull(FieldMappings["Group7Score3"]) || !excelRow.IsNull(FieldMappings["GroupResult7"])) { isNotNullGroup = true; } } else { isNotNullGroup = true; } if (isNotNullGroup) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", string.Empty); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", string.Empty); invalidRow.SetField("Desciption", nullData); isInvalid = true; } } } else { var excelValue = excelRow[excelField]; if (fieldName == "JobVacancyCode") { var jobVacancy = listJobVacancy.Where(d => d.Code == excelValue.GetString()).FirstOrDefault(); if (jobVacancy == null) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", referenceNotFound); isInvalid = true; } else { excelRow.SetField("JobVacancyID", jobVacancy.ID); } } else if (fieldName == "LanguageCode") { var language = listLanguage.Where(d => d.Code == excelValue.GetString()).FirstOrDefault(); if (language == null) { if (!string.IsNullOrWhiteSpace(excelValue.GetString())) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", referenceNotFound); isInvalid = true; } } else { excelRow.SetField("LanguageID", language.ID); } } else if (fieldName == "CandidateCode") { candidateID = listCandidate.Where(d => d.CodeCandidate == excelValue.GetString()).Select(d => d.ID).FirstOrDefault(); if (candidateID == Guid.Empty) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", referenceNotFound); isInvalid = true; } else { excelRow.SetField("CandidateID", candidateID); recruitmentHistoryID = listRecruitmentHistory.Where(d => d.CandidateID == candidateID) .OrderByDescending(d => d.DateApply).Select(d => d.ID).FirstOrDefault(); if (recruitmentHistoryID != Guid.Empty) { excelRow.SetField("RecruitmentHistoryID", recruitmentHistoryID); } } } else if (fieldName == "GroupCondition1" || fieldName == "GroupCondition2" || fieldName == "GroupCondition3" || fieldName == "GroupCondition4" || fieldName == "GroupCondition5" || fieldName == "GroupCondition6" || fieldName == "GroupCondition7") { var groupConditionID = listGroupCondition.Where(d => d.Code == excelValue.GetString()).Select(d => d.ID).FirstOrDefault(); if (groupConditionID == Guid.Empty) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", referenceNotFound); isInvalid = true; } else { string groupIDField = fieldName.Substring(14); groupIDField = "GroupConditionID" + groupIDField; excelRow.SetField(groupIDField, groupConditionID); if (listGroupConditionID.Contains(groupConditionID)) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", duplicateInFile); isInvalid = true; } else { listGroupConditionID.Add(groupConditionID); var isDupplicateOnTable = dtImportObject.Rows.OfType<DataRow>().Any(d => d != excelRow && d["CandidateID"].GetString() == candidateID.ToString() && (d["GroupConditionID1"].GetString() == groupConditionID.ToString() || d["GroupConditionID2"].GetString() == groupConditionID.ToString() || d["GroupConditionID3"].GetString() == groupConditionID.ToString() || d["GroupConditionID4"].GetString() == groupConditionID.ToString() || d["GroupConditionID5"].GetString() == groupConditionID.ToString() || d["GroupConditionID6"].GetString() == groupConditionID.ToString() || d["GroupConditionID7"].GetString() == groupConditionID.ToString())); if (isDupplicateOnTable) { var invalidRow = dtInvalidObject.NewRow(); dtInvalidObject.Rows.Add(invalidRow); invalidRow.SetField("DataField", fieldName); invalidRow.SetField("InvalidValue", excelValue.GetString()); invalidRow.SetField("ExcelField", excelAddress); invalidRow.SetField("ExcelValue", excelValue); invalidRow.SetField("Desciption", duplicateInFile); isInvalid = true; } else { var listInterviewByCandidate = listInterview.Where(d => d.CandidateID == candidateID && d.RecruitmentHistoryID == recruitmentHistoryID).ToList(); var interViewDupplicate = listInterviewByCandidate.Where(d => d.GroupConditionID == groupConditionID).FirstOrDefault(); if (interViewDupplicate != null) { string interviewIDField = fieldName.Substring(14); interviewIDField = "InterviewID" + interviewIDField; excelRow.SetField(interviewIDField, interViewDupplicate.ID); } } } } } } } if (isInvalid) { listInvalidRow.Add(excelRow); } } if (listInvalidRow.Count() > 0) { foreach (DataRow item in listInvalidRow) { dtImportObject.Rows.Remove(item); } } if (ImportObjects.ContainsKey(UserID)) { ImportObjects[UserID] = dtImportObject; } else { ImportObjects.Add(UserID, dtImportObject); } if (InvalidObjects.ContainsKey(UserID)) { InvalidObjects[UserID] = dtInvalidObject; } else { InvalidObjects.Add(UserID, dtInvalidObject); } } } }
private static void ImportSalesFromExcel() { var db = new ChemicalsDbContext(); IZipExtractor zipExtractor = new ZipExtractor(); ExcelImporter<Sale> k = new ExcelImporter<Sale>(zipExtractor); ICollection<Sale> sales = k.ImportModelsDataFromZipFile("../../../Files/Sales.zip", "./tests2"); foreach (var item in sales) { db.Sales.Add(item); } db.SaveChanges(); Console.WriteLine("The data was successfully imported to SQL Server."); }
public string[] Unpivot() { string[] result = null; if (!string.IsNullOrWhiteSpace(FileName)) { using (var context = new VnrHrmDataContext()) { IUnitOfWork unitOfWork = new UnitOfWork(context); if (PivotTemplateID != Guid.Empty) { var listPivotItem = unitOfWork.CreateQueryable<Cat_PivotItem>(Guid.Empty, d => d.PivotID == PivotTemplateID).Select(d => new { d.StartColumnIndex, d.StartRowIndex, d.SkipRowNumbers, d.PivotColumnEnd, d.PivotColumnName, d.PivotColumnStart, d.PivotHeaderRowIndex, d.PivotSheetIndex, d.TargetSheetIndex, d.TargetSheetName }).ToList(); string fileExt = Path.GetExtension(FileName); fileExt = string.IsNullOrWhiteSpace(fileExt) ? "xls" : fileExt; fileExt = fileExt.StartsWith(".") ? fileExt : "." + fileExt; string downloadPath = Common.GetPath(Common.DownloadURL); downloadPath = downloadPath.Replace("/", "\\"); string fileSuffix = DateTime.Now.ToString("yyyyMMddHHmmss"); string outputFile = Path.GetFileNameWithoutExtension(FileName); outputFile = downloadPath + "\\" + outputFile + fileSuffix + fileExt; outputFile = Regex.Replace(outputFile, @"[^0-9a-zA-Z_\\:.]+", ""); var listPivotTemplate = listPivotItem.Select(d => new PivotTemplate { PivotFileName = FileName, TargetFileName = outputFile, StartColumnIndex = d.StartColumnIndex.GetInteger(), StartRowIndex = d.StartRowIndex.GetInteger(), SkipRowNumbers = d.SkipRowNumbers.GetInteger(), PivotColumnEnd = d.PivotColumnEnd.GetInteger(), PivotColumnName = d.PivotColumnName, PivotColumnStart = d.PivotColumnStart.GetInteger(), PivotHeaderRowIndex = d.PivotHeaderRowIndex.GetInteger(), PivotSheetIndex = d.PivotSheetIndex.GetInteger(), TargetSheetIndex = d.TargetSheetIndex.GetInteger(), TargetSheetName = d.TargetSheetName }).ToArray(); ExcelImporter importer = new ExcelImporter(); result = importer.Unpivot(listPivotTemplate); } } } return result; }