public static async Task <RepositoryResponse <bool> > ImportData( string culture, Lib.ViewModels.MixDatabases.ReadViewModel mixDatabase, IFormFile file) { var result = new RepositoryResponse <bool>() { IsSucceed = true }; UnitOfWorkHelper <MixCmsContext> .InitTransaction(null, null, out MixCmsContext context, out IDbContextTransaction transaction, out bool isRoot); try { List <ImportViewModel> data = LoadFileData(culture, mixDatabase, file); var fields = MixDatabaseColumns.UpdateViewModel.Repository.GetModelListBy(f => f.MixDatabaseId == mixDatabase.Id, context, transaction).Data; foreach (var item in data) { if (result.IsSucceed) { var isCreateNew = string.IsNullOrEmpty(item.Id); item.Fields = fields; item.MixDatabaseName = mixDatabase.Name; item.Status = MixService.GetEnumConfig <MixContentStatus>(MixAppSettingKeywords.DefaultContentStatus); var saveResult = await item.SaveModelAsync(true, context, transaction); ViewModelHelper.HandleResult(saveResult, ref result); } } UnitOfWorkHelper <MixCmsContext> .HandleTransaction(result.IsSucceed, isRoot, transaction); return(result); } catch (Exception ex) { return(UnitOfWorkHelper <MixCmsContext> .HandleException <bool>(ex, isRoot, transaction)); } finally { if (isRoot) { //if current Context is Root UnitOfWorkHelper <MixCmsContext> .CloseDbContext(ref context, ref transaction); } } }
private static List <ImportViewModel> LoadFileData( string culture, Lib.ViewModels.MixDatabases.ReadViewModel mixDatabase, IFormFile file) { //create a list to hold all the values List <ImportViewModel> excelData = new List <ImportViewModel>(); //create a new Excel package in a memorystream using (var stream = file.OpenReadStream()) using (ExcelPackage excelPackage = new ExcelPackage(stream)) { //loop all worksheets foreach (ExcelWorksheet worksheet in excelPackage.Workbook.Worksheets) { // First row is supose to be headers (list field name) => start from row 2 int startRow = 2;//worksheet.Dimension.Start.Row //loop all rows for (int i = startRow; i <= worksheet.Dimension.End.Row; i++) { JObject obj = new JObject(); //loop all columns in a row for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++) { obj.Add(new JProperty(worksheet.Cells[1, j].Value.ToString(), worksheet.Cells[i, j].Value)); } ImportViewModel data = new ImportViewModel() { Id = obj["id"]?.ToString(), MixDatabaseId = mixDatabase.Id, MixDatabaseName = mixDatabase.Name, Specificulture = culture, Obj = obj }; excelData.Add(data); } } return(excelData); } }