public IActionResult UploadExcel() { var lessons = _dbContext.Lessons .Include(l => l.Teacher) .Include(l => l.Technology); ApplicantExcelUploadViewModel model = new ApplicantExcelUploadViewModel() { Lessons = Utilities.GetSelectListItem(lessons) }; return(View(model)); }
public async Task <IActionResult> UploadExcel(ApplicantExcelUploadViewModel model) { if (ModelState.IsValid && model.LessonId > 0) { if (model.ExcelFile == null || model.ExcelFile.Length == 0) { return(Content("file not selected")); } string fileName = model.ExcelFile.FileName; string filePath = Path.Combine( Directory.GetCurrentDirectory(), "wwwroot", "files", "UploadedExcels", fileName); using (var stream = new FileStream(filePath, FileMode.Create)) { await model.ExcelFile.CopyToAsync(stream); } List <Applicant> applicantList = new List <Applicant>(); try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; string cellValue = string.Empty; foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts) { int rowNumber = 0; OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); while (reader.Read()) { int columnNumber = 0; if (reader.ElementType == typeof(Row)) { reader.ReadFirstChild(); Applicant applicant = new Applicant() { Lesson = _dbContext.Lessons.Find(model.LessonId), Date = DateTime.Now }; do { if (rowNumber == 0) { continue; } if (reader.ElementType == typeof(Cell)) { Cell c = (Cell)reader.LoadCurrentElement(); if (c.DataType != null && c.DataType == CellValues.SharedString) { SharedStringItem ssi = workbookPart.SharedStringTablePart .SharedStringTable .Elements <SharedStringItem>() .ElementAt(Int32.Parse(c.CellValue.InnerText)); var s = ssi.Text; cellValue = ssi.Text.Text; } else { cellValue = c.CellValue.InnerText; } switch (columnNumber) { case 0: applicant.FirstName = cellValue; break; case 1: applicant.LastName = cellValue; break; case 2: applicant.Email = cellValue; break; case 3: applicant.Phone1 = cellValue.RemoveWhiteSpace(); break; case 4: if (cellValue != "x") { applicant.Phone2 = cellValue.RemoveWhiteSpace(); } break; case 7: applicant.Date = cellValue.UNIXTimeToDateTime(); break; default: break; } } columnNumber++; } while (reader.ReadNextSibling()); if (rowNumber != 0) { applicantList.Add(applicant); } rowNumber++; } } } } } catch (Exception e) { throw new Exception(e.Message); } finally { System.IO.File.Delete(filePath); } _dbContext.Applicants.AddRange(applicantList); _dbContext.SaveChanges(); return(RedirectToAction("AllApplicants", "Applicant")); } return(RedirectToAction("UploadExcel")); }