public ActionResult Create(CandidateExcel ce) { bool isSaved = UpdateCandidateExcel(ce); ViewBag.isSaved = isSaved; ViewBag.selectedBatchId = ce.BatchId; ViewBag.Students = db.Students.Where(s => s.BatchId == ce.BatchId).ToList(); return(View(ce)); }
public bool UpdateCandidateExcel(CandidateExcel ce) { bool isSavedSuccessfully = false; var tpId = Convert.ToInt32(System.Web.HttpContext.Current.User.Identity.Name); if (ModelState.IsValid) { try { if (!ce.ValidExcelFormets.Contains(ce.File.FileName.Split('.').Last())) { ModelState.AddModelError("File", "Upload a valid Candidate Excel File, allowed formats are : " + ce.ValidExcelFormets); } else { using (BinaryReader b = new BinaryReader(ce.File.InputStream)) { Stream stream = new MemoryStream(b.ReadBytes(ce.File.ContentLength)); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); DataSet ds = excelReader.AsDataSet(); stream.Close(); var students = new List <Student>(); for (int i = 1; i < ds.Tables["Sheet1"].Rows.Count; i++) { students.Add(new Student() { BatchId = ce.BatchId, CandidateCode = ds.Tables["Sheet1"].Rows[i][1].ToString(), StudentName = ds.Tables["Sheet1"].Rows[i][2].ToString(), TpId = tpId, }); } if (db.Students.Any(s => s.BatchId == ce.BatchId)) { db.Students.RemoveRange(db.Students.Where(s => s.BatchId == ce.BatchId)); } db.Students.AddRange(students); db.SaveChanges(); isSavedSuccessfully = true; } } } catch (Exception ex) { ModelState.AddModelError("File", "Invalid Excel : " + ex.Message); } } return(isSavedSuccessfully); }