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);
        }