private void btnIPsMasivo_Click(object sender, EventArgs e) { List <string> archivos = commons.showOpenFile("Archivos de Excel (*.xlsx)|*.xlsx", true); if (archivos == null) { return; } if (archivos.Count <= 0) { return; } foreach (string archivo in archivos) { byte[] bits = File.ReadAllBytes(archivo); ImportFromExcel importer = new ImportFromExcel(); bool res = importer.LoadXlsx(bits); List <Excel.Ip> excel_rows = importer.ExcelToList <Excel.Ip>(0, 1); clsRepo repo = new clsRepo(); foreach (Excel.Ip ip in excel_rows) { repo.Insertar <Dominio.Ip>(ip.getDBip()); } } }
private void readFileToImport() { List <string> archivos = commons.showOpenFile("Archivos de Excel (*.xlsx)|*.xlsx", true); if (archivos == null) { return; } clsRepo repo = new clsRepo(); foreach (string archivo in archivos) { var data = File.ReadAllBytes(archivo); ImportFromExcel import = new ImportFromExcel(); import.LoadXlsx(data); List <UsuarioExcel> output = import.ExcelToList <UsuarioExcel>(0, 1); foreach (UsuarioExcel usr in output) { repo.Insertar <Dominio.Entidad>(usr.getEntidad()); } } }
public IHttpActionResult ImportSongs() { var data = System.IO.File.ReadAllBytes(HttpContext.Current.Server.MapPath("~/App_Data/BillboardTo2013.xlsx")); var import = new ImportFromExcel(); import.LoadXlsx(data); int peak = 0; var songs = import.ExcelToList <Song2>(1, 1).Select(s => new Song { Artist = s.Artist, Title = s.Title, ReleaseYear = int.Parse(s.ReleaseYear), RecordLabel = s.RecordLabel, Duration = s.Duration, PeakChartPosition = int.TryParse(s.PeakChartPosition, out peak) ? peak : 0 }); string conn = ConfigurationManager.ConnectionStrings["DefaultConn"].ConnectionString; using (var connection = new SqlConnection(conn)) { connection.Open(); connection.DeleteAll <Song>(); connection.Insert(songs); } return(Ok(songs)); }
//protected void preambleLists_SelectedIndexChanged(object sender, EventArgs e) //{ // try // { // var selectedQtype = long.Parse(DropDownList1.SelectedValue); // var QType = _db.T_Question.Select(s => s.PreambleId).Distinct(); // var Qt = _db.T_QuestionTypes.FirstOrDefault(s => s.Id == selectedQtype); // var QTypeCount = QType.Count(); // var QTC = QTypeCount++; // var selectedpreamble = long.Parse(preambleLists.SelectedValue); // if (selectedpreamble == 0) // { // PreambleName.Text = Qt.Name + " " + QTC; // preambleText.Text = ""; // PreamblePreview.Text = ""; // preview.Visible = false; // preambleRow.Visible = true; // preambleNameRow.Visible = true; // } // else if (selectedpreamble != -1 && selectedpreamble != 0) // { // var id = QPid.Value; // if (!(string.IsNullOrEmpty(id))) // { // var preamble = _db.T_QuestionPreamble.FirstOrDefault(s => s.Id == selectedpreamble); // PreamblePreview.Text = ""; // PreambleNamePreview.Text = ""; // preview.Visible = false; // QPid.Value = preamble.Id.ToString(); // PreambleName.Text = preamble.Name; // preambleText.Text = preamble.Body; // preambleNameRow.Visible = true; // preambleRow.Visible = true; // } // else // { // var preamble = _db.T_QuestionPreamble.FirstOrDefault(s => s.Id == selectedpreamble); // PreambleName.Text = ""; // preambleText.Text = ""; // preambleNameRow.Visible = false; // preambleRow.Visible = false; // PreambleNamePreview.Text = preamble.Name; // PreamblePreview.Text = preamble.Body; // preview.Visible = true; // } // } // else // { // PreambleName.Text = ""; // preambleText.Text = ""; // PreamblePreview.Text = ""; // PreambleNamePreview.Text = ""; // preview.Visible = false; // preambleNameRow.Visible = false; // preambleRow.Visible = false; // } // } // catch (Exception ex) // { // ErecruitHelper.SetErrorData(ex, Session); // Response.Redirect("ErrorPage.aspx", false); // } //} protected void PreviewUpload_Click(object sender, EventArgs e) { var tenantId = long.Parse(SessionHelper.GetTenantID(HttpContext.Current.Session)); HttpPostedFile file = QuestionFile.PostedFile; if (file != null && file.ContentLength > 0) { string fname = Path.GetFileName(file.FileName); string fullFilePath = Path.GetFullPath(file.FileName); string ext = System.IO.Path.GetExtension(file.FileName); string fileID = Guid.NewGuid().ToString(); string path = ""; // var all = new List<string[]>(); if (Directory.Exists(Server.MapPath("~/UploadedQuestions/"))) { path = Server.MapPath(Path.Combine("~/UploadedQuestions/", fileID + ext)); // PPath = Path.Combine("~/Passports/", cand.Code + ext); file.SaveAs(path); } else { Directory.CreateDirectory(Server.MapPath("~/UploadedQuestions/")); path = Server.MapPath(Path.Combine("~/UploadedQuestions/", fileID + ext)); // PPath = Path.Combine("~/Passports/", cand.Code + ext); file.SaveAs(path); } var data = File.ReadAllBytes(path); ImportFromExcel import = new ImportFromExcel(); if (ext == ".xls") { import.LoadXls(data); } else if (ext == ".xlsx") { import.LoadXlsx(data); } //first parameter it's the sheet number in the excel workbook //second paramter it's the number of rows to skip at the start(we have an header in the file) List <QuestionUploadModel> all = import.ExcelToList <QuestionUploadModel>(0, 1); var obj = new List <object>(); foreach (var s in all) { obj.Add(new { Question = s.Question, Section = s.Section, A = s.A, B = s.B, C = s.C, D = s.D, E = s.E, Answer = s.Answer }); } DetailGrid.DataSource = obj; DetailGrid.DataBind(); ViewActiveTitle.Value = "Import Preview"; ViewActive.Value = "1"; FileID.Value = path; EXT.Value = ext; resultLbl.Text = ""; //} // QuestionFile.Value = fullFilePath; } }
void BtnLoadExcelDataClick(object sender, EventArgs e) { var data = System.IO.File.ReadAllBytes(Path.Combine(epFolderPath, "EP_Student.xlsx")); var import = new ImportFromExcel(); import.LoadXlsx(data); //first parameter it's the sheet number in the excel workbook //second parameter it's the number of rows to skip at the start(we have a header in the file) epStudentMasterlist = import.ExcelToList <Student>(0, 1); epStudentMasterlist = epStudentMasterlist.Where(x => !string.IsNullOrWhiteSpace(x.IDNumber)).ToList(); bindingSourceEPStudents.DataSource = epStudentMasterlist; var roomLookup = epStudentMasterlist.Select(s => s.Room).Distinct().ToArray(); comboBoxRooms.Items.AddRange(roomLookup); }
protected void proceedtoUpload_Click(object sender, EventArgs e) { var tenantId = long.Parse(SessionHelper.GetTenantID(HttpContext.Current.Session)); string fId = FileID.Value; //long preaId = 0; if (!string.IsNullOrEmpty(fId)) { string ext = EXT.Value; var data = File.ReadAllBytes(fId); ImportFromExcel import = new ImportFromExcel(); if (ext == ".xls") { import.LoadXls(data); } else if (ext == ".xlsx") { import.LoadXlsx(data); } //first parameter it's the sheet number in the excel workbook //second paramter it's the number of rows to skip at the start(we have an header in the file) List <QuestionUploadModel> all = import.ExcelToList <QuestionUploadModel>(0, 1); if (all.Count() < 1) { resultLbl.Text = "Either you uploaded an empty file or you provided wrong file type. Only allowed file type is .csv, .xls or.xlsx!"; } else { resultLbl.Text = ""; var Qtype = DropDownList1.SelectedValue; var ex = 0; var obj = new List <object>(); foreach (var s in all) { ex += _db.SearchQuestion(s.Question.ToLower()).Where(x => x.TypeId == long.Parse(Qtype)).Count(); T_Question q = new T_Question { PreambleId = 0, TypeId = long.Parse(Qtype), OptionType = (long)ErecruitHelper.OptionType.Single, IsActive = true, Details = s.Question, Section = s.Section, TenantId = tenantId, AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }; _db.T_Question.Add(q); _db.SaveChanges(); var opts = new List <T_Option>(); if (!string.IsNullOrEmpty(s.A)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.A, Answer = s.Answer == ErecruitHelper.OptionIndex.A.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.B)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.B, Answer = s.Answer == ErecruitHelper.OptionIndex.B.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.C)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.C, Answer = s.Answer == ErecruitHelper.OptionIndex.C.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.D)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.D, Answer = s.Answer == ErecruitHelper.OptionIndex.D.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.E)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.E, Answer = s.Answer == ErecruitHelper.OptionIndex.E.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } ExtensionMethods.InsertAllOnSubmit(_db.T_Option, opts); //_db.T_Option.InsertAllOnSubmit(opts); _db.SaveChanges(); } if (ex > 0) { resultLbl.Text = "Some Question(s) already exist in the system therefore were skipped."; } else { resultLbl.Text = "Questions Uploaded"; } } } else { resultLbl.Text = "No file to upload. Kindly chose a file."; } }
protected void Save_Click(object sender, EventArgs e) { try { // var all = new List<string[]>(); var tenantId = long.Parse(SessionHelper.GetTenantID(HttpContext.Current.Session)); string fId = FileID.Value; //long preaId = 0; if (!string.IsNullOrEmpty(fId)) { string ext = EXT.Value; //all = ErecruitHelper.GetLinesFromFile(fId, ext); var data = File.ReadAllBytes(fId); ImportFromExcel import = new ImportFromExcel(); if (ext == ".xls") { import.LoadXls(data); } else if (ext == ".xlsx") { import.LoadXlsx(data); } //first parameter it's the sheet number in the excel workbook //second paramter it's the number of rows to skip at the start(we have an header in the file) List <QuestionUploadModel> all = import.ExcelToList <QuestionUploadModel>(0, 1); if (all.Count() < 1) { resultLbl.Text = "Either you uploaded an empty file or you provided wrong file type. Only allowed file type is .csv, .xls or.xlsx!"; } else { resultLbl.Text = ""; var Qtype = DropDownList1.SelectedValue; var ex = 0; var obj = new List <object>(); foreach (var s in all) { ex += _db.SearchQuestion(s.Question.ToLower()).Where(x => x.TypeId == long.Parse(Qtype)).Count(); T_Question q = new T_Question { PreambleId = 0, TypeId = long.Parse(Qtype), OptionType = (long)ErecruitHelper.OptionType.Single, IsActive = true, Details = s.Question, TenantId = tenantId, Section = s.Section, AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }; _db.T_Question.Add(q); _db.SaveChanges(); var opts = new List <T_Option>(); if (!string.IsNullOrEmpty(s.A)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.A, Answer = s.Answer == ErecruitHelper.OptionIndex.A.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.B)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.B, Answer = s.Answer == ErecruitHelper.OptionIndex.B.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.C)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.C, Answer = s.Answer == ErecruitHelper.OptionIndex.C.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.D)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.D, Answer = s.Answer == ErecruitHelper.OptionIndex.D.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.E)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.E, Answer = s.Answer == ErecruitHelper.OptionIndex.E.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } // _db.T_Option.InsertAllOnSubmit(opts); ExtensionMethods.InsertAllOnSubmit(_db.T_Option, opts); _db.SaveChanges(); } if (ex > 0) { resultLbl.Text = "Some Question(s) already exist in the system therefore were skipped."; } else { resultLbl.Text = "Questions Uploaded"; } } } else { HttpPostedFile file = QuestionFile.PostedFile; if (file != null && file.ContentLength > 0) { string fname = Path.GetFileName(file.FileName); string ext = System.IO.Path.GetExtension(file.FileName); string fileID = Guid.NewGuid().ToString(); string path = ""; // var all = new List<string[]>(); if (Directory.Exists(Server.MapPath("~/UploadedQuestions/"))) { path = Server.MapPath(Path.Combine("~/UploadedQuestions/", fileID + ext)); // PPath = Path.Combine("~/Passports/", cand.Code + ext); file.SaveAs(path); } else { Directory.CreateDirectory(Server.MapPath("~/UploadedQuestions/")); path = Server.MapPath(Path.Combine("~/UploadedQuestions/", fileID + ext)); // PPath = Path.Combine("~/Passports/", cand.Code + ext); file.SaveAs(path); } var data = File.ReadAllBytes(path); ImportFromExcel import = new ImportFromExcel(); if (ext == ".xls") { import.LoadXls(data); } else if (ext == ".xlsx") { import.LoadXlsx(data); } //first parameter it's the sheet number in the excel workbook //second paramter it's the number of rows to skip at the start(we have an header in the file) List <QuestionUploadModel> all = import.ExcelToList <QuestionUploadModel>(0, 1); if (all.Count() < 1) { resultLbl.Text = "Either you uploaded an empty file or you provided wrong file type. Only allowed file type is .csv, .xls or.xlsx!"; } else { resultLbl.Text = ""; var Qtype = DropDownList1.SelectedValue; var ex = 0; var obj = new List <object>(); foreach (var s in all) { ex += _db.SearchQuestion(s.Question.ToLower()).Where(x => x.TypeId == long.Parse(Qtype)).Count(); T_Question q = new T_Question { PreambleId = 0, TypeId = long.Parse(Qtype), OptionType = (long)ErecruitHelper.OptionType.Single, IsActive = true, Details = s.Question, Section = s.Section, TenantId = tenantId, AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }; _db.T_Question.Add(q); _db.SaveChanges(); var opts = new List <T_Option>(); if (!string.IsNullOrEmpty(s.A)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.A, Answer = s.Answer == ErecruitHelper.OptionIndex.A.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.B)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.B, Answer = s.Answer == ErecruitHelper.OptionIndex.B.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.C)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.C, Answer = s.Answer == ErecruitHelper.OptionIndex.C.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.D)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.D, Answer = s.Answer == ErecruitHelper.OptionIndex.D.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } if (!string.IsNullOrEmpty(s.E)) { opts.Add(new T_Option { Q_Id = q.Id, Details = s.E, Answer = s.Answer == ErecruitHelper.OptionIndex.E.ToString(), AddedBy = SessionHelper.FetchEmail(Session), DateAdded = DateTime.Now }); } ExtensionMethods.InsertAllOnSubmit(_db.T_Option, opts); //_db.T_Option.InsertAllOnSubmit(opts); _db.SaveChanges(); } if (ex > 0) { resultLbl.Text = "Some Question(s) already exist in the system therefore were skipped."; } else { resultLbl.Text = "Questions Uploaded"; } } } } } catch (Exception ex) { ErecruitHelper.SetErrorData(ex, Session); Response.Redirect("ErrorPage.aspx", false); } }
public async Task <ActionResult> ImportUsers(HttpPostedFileBase ExcelFile, string temppass) { var message = string.Empty; try { var output = new List <RegisterExcelViewModel>(); if (ExcelFile != null) { var data = ExcelFile.ToFileByteArray(); ImportFromExcel import = new ImportFromExcel(); if (Path.GetExtension(ExcelFile.FileName.ToLower()) == ".xlsx") { import.LoadXlsx(data); } else if (Path.GetExtension(ExcelFile.FileName.ToLower()) == ".xls") { import.LoadXls(data); } else { TempData[BSMessage.TYPE] = BSMessage.MessageType.DANGER; TempData[BSMessage.DIALOGBOX] = "Invalid Excel worksheet: " + Path.GetExtension(ExcelFile.FileName); return(RedirectToAction("Index", "Roles", new { area = "" })); } output = import.ExcelToList <RegisterExcelViewModel>(0, 1); if (output.Count == 0) { TempData[BSMessage.TYPE] = BSMessage.MessageType.WARNING; TempData[BSMessage.DIALOGBOX] = "Excel worksheet has no user records."; return(RedirectToAction("Index", "Roles", new { area = "" })); } var duplicates = output.GroupBy(x => x.UserName) .Select(g => new { Value = g.Key, Count = g.Count() }) .Where(h => h.Count > 1) .Select(s => s.Value); if (duplicates.Count() > 0) { TempData[BSMessage.TYPE] = BSMessage.MessageType.DANGER; message = "<p>We have found the following duplicate records: " + string.Join(", ", duplicates) + " </p>"; } //var users = output.GroupBy(x => x.UserName).Select(x => x.First()).ToList(); TextInfo textInfo = new CultureInfo("en-US", false).TextInfo; foreach (var s in output) { s.PhoneNumber = string.IsNullOrEmpty(s.PhoneNumber) ? null : s.PhoneNumber.TrimStart('0'); using (var db = new ApplicationDbContext()) { var phoneExist = db.Users.Where(x => (x.CountryCode + x.PhoneNumber) == (s.CountryCode + s.PhoneNumber)).FirstOrDefault() != null; var emailExist = db.Users.Where(u => u.UserName == s.Email || u.Email == s.Email).FirstOrDefault() != null; if (emailExist || phoneExist) { s.CountryCode = null; s.PhoneNumber = null; s.Email = null; } } DateTime?bdate; try { bdate = DateTime.ParseExact(s.BirthDate, "M/d/yyyy", new CultureInfo("en-US")); } catch { bdate = null; } var user = new ApplicationUser() { UserName = s.UserName, Email = s.Email, PhoneNumber = s.PhoneNumber, CountryCode = string.IsNullOrEmpty(s.PhoneNumber) ? null : s.CountryCode, UserProfile = new UserProfile { UserName = s.UserName, RegistrationType = s.RegistrationType, LastName = textInfo.ToTitleCase(s.LastName.ToLower()), FirstName = textInfo.ToTitleCase(s.FirstName.ToLower()), BirthDate = bdate, Gender = string.IsNullOrEmpty(s.Gender) ? null : s.Gender[0].ToString().ToUpper(), RegistrationDate = DateTime.Now, IsActive = true } }; var result = await UserManager.CreateAsync(user, string.IsNullOrEmpty(temppass)?user.UserName : temppass); if (result.Succeeded) { if (!string.IsNullOrEmpty(user.UserProfile.RegistrationType)) { RegisterViewModel.AddRole(user.UserName, user.UserProfile.RegistrationType); } } else { message += user.UserName + " / " + user.UserProfile.LastName + ", " + user.UserProfile.FirstName + " was not added. <br>"; } } } } catch (Exception ex) { TempData[BSMessage.TYPE] = BSMessage.MessageType.DANGER; TempData[BSMessage.PANEL] = "Oops! Something went wrong. " + ex.GetBaseException(); } if (!string.IsNullOrEmpty(message)) { TempData[BSMessage.PANEL] = message; } return(RedirectToAction("Index", "Roles", new { area = "" })); }
// GET: Examples/ImportExcel public ActionResult Index(HttpPostedFileBase ExcelFile) { var output = new List <StudentExcelViewModel>(); try { if (ExcelFile != null) { var data = ExcelFile.ToFileByteArray(); ImportFromExcel import = new ImportFromExcel(); if (Path.GetExtension(ExcelFile.FileName.ToLower()) == ".xlsx") { import.LoadXlsx(data); } else if (Path.GetExtension(ExcelFile.FileName.ToLower()) == ".xls") { import.LoadXls(data); } else { TempData[BSMessage.TYPE] = BSMessage.MessageType.DANGER; TempData[BSMessage.DIALOGBOX] = "Invalid Excel worksheet: " + Path.GetExtension(ExcelFile.FileName); return(RedirectToAction("Index")); } output = import.ExcelToList <StudentExcelViewModel>(0, 1); var duplicates = output.GroupBy(x => x.IdNumber) .Select(g => new { Value = g.Key, Count = g.Count() }) .Where(h => h.Count > 1) .Select(s => s.Value); if (duplicates.Count() > 0) { TempData[BSMessage.TYPE] = BSMessage.MessageType.DANGER; TempData[BSMessage.PANEL] = "The following duplicate record/s has been removed: " + string.Join(", ", duplicates); } output = output.GroupBy(x => x.IdNumber).Select(x => x.First()).ToList(); foreach (var stud in output) { Char[] separators = new Char[] { ',' }; // only the space character, in this case var names = stud.FullName.Split(separators); stud.FullName = names[0]; separators = new Char[] { ' ' }; stud.FirstName = names[1].Split(separators)[1]; } } else { using (var db = new ExamplesDbContext()) { output = db.Students.Select(s => new StudentExcelViewModel { IdNumber = s.IdNumber, FullName = s.LastName, FirstName = s.FirstName, YearSection = s.YearSection }).ToList(); ViewBag.FromDb = true; } } } catch { } return(View(output)); }