public ActionResult SearchWholeSaler() { List <WholeSaler_Inventory> lst = wholeSalerRepository.List.ToList(); DataTable Dt = ExcelPackageExtensions.ToDataTable(lst); return(View(Dt)); }
public ActionResult ImportExcel(HttpPostedFileBase file) { try { if (Path.GetExtension(file.FileName) == ".xlsx" || Path.GetExtension(file.FileName) == ".xls") { ExcelPackage package = new ExcelPackage(file.InputStream); DataTable Dt = ExcelPackageExtensions.ToDataTable(package); var employee = new Employee(); for (int i = 0; i < Dt.Rows.Count; i++) { employee.EmployeeName = Dt.Rows[i][1].ToString(); employee.Position = Dt.Rows[i][2].ToString(); var x = Dt.Rows[i][3].ToString(); employee.DepartmentId = Int32.Parse(x); db.Employees.Add(employee); db.SaveChanges(); } } } catch { } return(RedirectToAction("Index")); }//end import excel file to data base
public ActionResult GetAllChildren(FormCollection form) { SalesForcVIewModel obj = new SalesForcVIewModel(); List <string> selectedList = new List <string>(); obj.StateModel = new List <Parent>(); obj.StateModel = GetAllParrent(); int level = 3; /// var value = Request.Form["SalesForce.StateModel"] ?? Request.Form["StateModel"]; selectedList.Add(form["dd1"]); selectedList.Add(form["dd2"]); selectedList.Add(form["dd3"]); if (!string.IsNullOrEmpty(value)) { level = 2; } var value1 = Request.Form["ddlcity"]; if (!string.IsNullOrEmpty(Request.Form["dd2"])) { level = 3; } string strDDLValue = value.ToString(); //strDDLValue = "MB-RSM-BTB"; var lstEmp = db.sp_GetAllChildrenForParent(strDDLValue, level).Select(i => new MT_SellOut { Day = i.Day, SalesOrg = i.SalesOrg, CustomerCode = i.CustomerCode, SalesRouteCode = i.SalesRouteCode, ID = i.ID, Name = i.Name, Store = i.Store, Target = i.Target, Perform = i.Perform, Rate = i.Rate, LineID = i.LineID, SalesForceLevel = i.SalesForceLevel, ParentCode = i.ParentCode, SalesForceCode = i.SalesForceCode } ).OrderBy(x => x.LineID) .ToList();; DataTable Dt = ExcelPackageExtensions.ToDataTable(lstEmp); MultiModel model = new MultiModel { Dt = Dt, SalesForce = obj, SelectedList = selectedList }; return(View(model)); }
public ActionResult ReadExcel(HttpPostedFileBase upload) { if (Path.GetExtension(upload.FileName) == ".xlsx" || Path.GetExtension(upload.FileName) == ".xls") { ExcelPackage package = new ExcelPackage(upload.InputStream); DataTable Dt = ExcelPackageExtensions.ToDataTable(package); // Getting those error because we don't have any ReadExcel view . you can use this datatatable where you need it. } return(View()); }
public ActionResult SearchResultSellIn(DateTime bday) { //string date = bday.Date.ToShortDateString(); string date = String.Format("{0:dd/MM/yyyy}", bday); var searchResults = sellInRepository.Search(p => p.Day == date); List <MT_SellIn> tab1 = searchResults.Where(x => x.Tab == "1").ToList(); List <MT_SellIn> tab2 = searchResults.Where(x => x.Tab == "2").ToList(); DataTable Dt1 = ExcelPackageExtensions.ToDataTable(tab1); DataTable Dt2 = ExcelPackageExtensions.ToDataTable(tab2); DataTable[] TotalDT = { Dt1, Dt2 }; return(View(TotalDT)); }
public ActionResult ImportExcel(HttpPostedFileBase file)//(out int count)// { //if (file != null) //{ if (Path.GetExtension(file.FileName) == ".xlsx" || Path.GetExtension(file.FileName) == ".xls") { ExcelPackage package = new ExcelPackage(file.InputStream); DataTable Dt = ExcelPackageExtensions.ToDataTable(package); // DataTable dtExcel = new DataTable(); var employee = new Employee(); //employee.FirstName.Add("First Name", typeof(String)); //dtExcel.Columns.Add("Last Name", typeof(String)); //dtExcel.Columns.Add("Position", typeof(String)); //dtExcel.Columns.Add("Departmnet ID", typeof(Int32)); int d; for (int i = 0; i < Dt.Rows.Count; i++) { employee.FirstName = Dt.Rows[i][1].ToString(); employee.LastName = Dt.Rows[i][2].ToString(); employee.Position = Dt.Rows[i][3].ToString(); var x = Dt.Rows[i][4].ToString(); employee.DepartmentID = Int32.Parse(x); db.Employees.Add(employee); db.SaveChanges(); } return(RedirectToAction("Index")); } return(View()); }
public ActionResult ImportExcelFileToUserDataTable() { objResponse Response = new objResponse(); session = new SessionHelper(); List <Users> users = new List <Users>(); DataTable dt = new DataTable(); // string Result=""; try { string fname; if (Request.Files.Count > 0) { HttpFileCollectionBase files = Request.Files; for (int i = 0; i < files.Count; i++) { HttpPostedFileBase file = files[i]; // Checking for Internet Explorer if (Request.Browser.Browser.ToUpper() == "IE" || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER") { string[] testfiles = file.FileName.Split(new char[] { '\\' }); fname = testfiles[testfiles.Length - 1]; } else { fname = file.FileName; } if ((file != null) && (file.ContentLength != 0) && !string.IsNullOrEmpty(file.FileName)) { string fileName = file.FileName; string fileContentType = file.ContentType; byte[] fileBytes = new byte[file.ContentLength]; var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength)); } var excel = new ExcelPackage(file.InputStream); dt = ExcelPackageExtensions.ToDataTable(excel); int count = 0; foreach (DataRow dr in dt.Rows) { Users objUser = new Users(); objUser.Username = dr[0].ToString(); objUser.FName = dr[1].ToString(); objUser.LName = dr[2].ToString(); objUser.Mobile = dr[3].ToString(); objUser.Email = dr[4].ToString(); objUser.URole = dr[5].ToString(); objUser.Group = dr[6].ToString(); objUser.TimeZone = dr[7].ToString(); objUser.Agent_App_Link = dr[8].ToString(); objUser.Processer = dr[9].ToString(); objUser.Sales_No = dr[10].ToString(); objUser.Sales_Id = dr[11].ToString(); objUser.BirthDay = dr[12].ToString(); objUser.Password = BAL.Helper.Helper.GenerateRandomPassword(); objUser.CratedBy_ID = Convert.ToInt64(session.UserSession.UserId); Response = objUserManager.AddUser(objUser); if (Response.ErrorCode == 0) { if (Response.ErrorMessage != "User Already Exists") { count++; } } else { return(Json("Fail", JsonRequestBehavior.AllowGet)); } } return(Json("Success," + count.ToString(), JsonRequestBehavior.AllowGet)); } return(Json("fail", JsonRequestBehavior.AllowGet)); } else { return(Json("fail", JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { BAL.Common.LogManager.LogError("ImportExcelFileToUserDataTable", 1, Convert.ToString(ex.Source), Convert.ToString(ex.Message), Convert.ToString(ex.StackTrace)); return(Json("fail", JsonRequestBehavior.AllowGet)); } }
public ActionResult ReadExcel(HttpPostedFileBase chooseFile) { if (chooseFile == null) { TempData["message"] = "Vui lòng chọn file"; return(RedirectToAction("ImportReport")); } if (Path.GetExtension(chooseFile.FileName) != ".xlsx" && Path.GetExtension(chooseFile.FileName) != ".xls") { TempData["message"] = "Định dạng file excel không hợp lệ"; return(RedirectToAction("ImportReport")); } ExcelPackage package = new ExcelPackage(chooseFile.InputStream); DataTable[] Dt = ExcelPackageExtensions.ToDataTable(package); //List<Account> ls = sellInRepository.List.ToList(); //List<object> lst = Dt.AsEnumerable().ToList<object>(); //List<Person> employeeList = Dt.DataTableToList<Person>(); //if (Dt[0].Columns.Count != 8) //{ // TempData["message"] = "Vui lòng chọn templete sell in để có thể import!"; // return RedirectToAction("ImportReport", "Home", ViewBag.message); //} List <MT_SellIn> tab1 = Dt[0].DataTableToList <MT_SellIn>(); List <MT_SellIn> tab2 = Dt[1].DataTableToList <MT_SellIn>(); String date = tab1.ElementAt(tab1.Count - 1).Day; // 07/22/2013 int numberOfWork = 0; int month = 0; int year = 0; if (date.Length != 9 && date.Length != 10) { TempData["message"] = "Vui lòng chọn templete sell in để có thể import!"; return(RedirectToAction("ImportReport")); } if (date.Length == 9) //7/22/2013 { month = int.Parse(date.Substring(0, 1)); year = int.Parse(date.Substring(5, 4)); } else //07/22/2013 { month = int.Parse(date.Substring(0, 2)); year = int.Parse(date.Substring(6, 4)); } numberOfWork = ExcelPackageExtensions.CountWorkingDay(year, month); for (int i = 1; i < tab1.Count; i++) { var day = tab1.ElementAt(i).Day.ToString(); if (day.Length == 9) { day = "0" + day; tab1.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture) .ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); } else if (day.Length == 10) { tab1.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture) .ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); } if (tab1.ElementAt(i).TargetMonth.Trim() == "0" || tab1.ElementAt(i).TargetMonth.Trim() == "-") { tab1.ElementAt(i).TargetDate = "0"; } else { var targetDate = (double.Parse(tab1.ElementAt(i).TargetMonth) / numberOfWork); tab1.ElementAt(i).TargetDate = String.Format("{0:0.00}", targetDate); } if (tab1.ElementAt(i).Archive.Trim() == "0" || tab1.ElementAt(i).Archive.Trim() == "-" || tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-") { tab1.ElementAt(i).Growth = "0"; } else { var growth = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).Archive)) * 100; tab1.ElementAt(i).Growth = String.Format("{0:0.00}", growth); } if (tab1.ElementAt(i).LastMonth.Trim() == "0" || tab1.ElementAt(i).LastMonth.Trim() == "-" || tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-") { tab1.ElementAt(i).GrowthLastMonth = "0"; } else { var growthLastMonth = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).LastMonth)) * 100; tab1.ElementAt(i).GrowthLastMonth = String.Format("{0:0.00}", growthLastMonth); } if (tab1.ElementAt(i).TargetMonth.Trim() == "0" || tab1.ElementAt(i).TargetMonth.Trim() == "-" || tab1.ElementAt(i).Actual.Trim() == "0" || tab1.ElementAt(i).Actual.Trim() == "-") { tab1.ElementAt(i).PercentTarget = "0"; } else { var percentTarget = (double.Parse(tab1.ElementAt(i).Actual) / double.Parse(tab1.ElementAt(i).TargetMonth)) * 100; tab1.ElementAt(i).PercentTarget = String.Format("{0:0.00}", percentTarget); } if (tab1.ElementAt(i).TargetWeek.Trim() == "0" || tab1.ElementAt(i).TargetWeek.Trim() == "-" || tab1.ElementAt(i).ActualWeek.Trim() == "0" || tab1.ElementAt(i).ActualWeek.Trim() == "-") { tab1.ElementAt(i).PercentWeek = "0"; } else { var percentWeek = (double.Parse(tab1.ElementAt(i).ActualWeek) / double.Parse(tab1.ElementAt(i).TargetWeek)) * 100; tab1.ElementAt(i).PercentWeek = String.Format("{0:0.00}", percentWeek); } tab1.ElementAt(i).LastUpdated = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); tab1.ElementAt(i).Tab = "1"; tab1.ElementAt(i).CompanyCode = DMSEnum.CompanyCode; tab1.ElementAt(i).SalesOrg = DMSEnum.MTSalesOrg; } for (int i = 1; i < tab2.Count; i++) { var day = tab2.ElementAt(i).Day.ToString(); if (day.Length == 9) { day = "0" + day; } tab2.ElementAt(i).Day = DateTime.ParseExact(day, "MM/dd/yyyy", CultureInfo.InvariantCulture) .ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); if (tab2.ElementAt(i).TargetMonth.Trim() == "0" || tab2.ElementAt(i).TargetMonth.Trim() == "-") { tab2.ElementAt(i).TargetDate = "0"; } else { var targetDate = (double.Parse(tab2.ElementAt(i).TargetMonth) / numberOfWork); tab2.ElementAt(i).TargetDate = String.Format("{0:0.00}", targetDate); } if (tab2.ElementAt(i).Archive.Trim() == "0" || tab2.ElementAt(i).Archive.Trim() == "-" || tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-") { tab2.ElementAt(i).Growth = "0"; } else { var growth = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).Archive)) * 100; tab2.ElementAt(i).Growth = String.Format("{0:0.00}", growth); } if (tab2.ElementAt(i).LastMonth.Trim() == "0" || tab2.ElementAt(i).LastMonth.Trim() == "-" || tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-") { tab2.ElementAt(i).GrowthLastMonth = "0"; } else { var growthLastMonth = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).LastMonth)) * 100; tab2.ElementAt(i).GrowthLastMonth = String.Format("{0:0.00}", growthLastMonth); } if (tab2.ElementAt(i).TargetMonth.Trim() == "0" || tab2.ElementAt(i).TargetMonth.Trim() == "-" || tab2.ElementAt(i).Actual.Trim() == "0" || tab2.ElementAt(i).Actual.Trim() == "-") { tab2.ElementAt(i).PercentTarget = "0"; } else { var percentTarget = (double.Parse(tab2.ElementAt(i).Actual) / double.Parse(tab2.ElementAt(i).TargetMonth)) * 100; tab2.ElementAt(i).PercentTarget = String.Format("{0:0.00}", percentTarget); } if (tab2.ElementAt(i).TargetWeek.Trim() == "0" || tab2.ElementAt(i).TargetWeek.Trim() == "-" || tab2.ElementAt(i).ActualWeek.Trim() == "0" || tab2.ElementAt(i).ActualWeek.Trim() == "-") { tab2.ElementAt(i).PercentWeek = "0"; } else { var percentWeek = (double.Parse(tab2.ElementAt(i).ActualWeek) / double.Parse(tab2.ElementAt(i).TargetWeek)) * 100; tab2.ElementAt(i).PercentWeek = String.Format("{0:0.00}", percentWeek); } tab2.ElementAt(i).LastUpdated = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); tab2.ElementAt(i).Tab = "2"; tab2.ElementAt(i).CompanyCode = DMSEnum.CompanyCode; tab2.ElementAt(i).SalesOrg = DMSEnum.MTSalesOrg; } //sellInRepository.BatchInsert(tab1); DataTable Dt1 = ExcelPackageExtensions.ToDataTable(tab1); DataTable Dt2 = ExcelPackageExtensions.ToDataTable(tab2); DataRow recRow1 = Dt1.Rows[0]; recRow1[0] = string.Empty; recRow1.Delete(); Dt1.AcceptChanges(); DataRow recRow2 = Dt2.Rows[0]; recRow2[0] = string.Empty; recRow2.Delete(); Dt2.AcceptChanges(); DataTable[] TotalDT = { Dt1, Dt2 }; //ModelDt[1].DataTableToList<SellinFirstTab>();State.AddModelError("Error", "Ex: This login failed " + employeeList.ElementAt(0).Fullname); //ModelState.AddModelError("Error", "Ex: This login failed 1"); var searchResults = sellInRepository.Search(p => p.Growth == "0"); //sellInRepository.BatchInsert(tab1); //sellInRepository.BatchInsert(tab2); List <MT_SellIn> ls = sellInRepository.List.ToList(); foreach (var item in tab1) { sellInRepository.InsertOrUpdate(item); } foreach (var item in tab2) { sellInRepository.InsertOrUpdate(item); } if (TotalDT != null) { TempData["success"] = DMSEnum.Success; } return(View(TotalDT)); }
public ActionResult ImportPerform(HttpPostedFileBase chooseFile) { if (chooseFile == null) { TempData["message"] = "Vui lòng chọn file"; return(RedirectToAction("ImportSellOut", "Home", ViewBag.message)); } if (Path.GetExtension(chooseFile.FileName) != ".xlsx" && Path.GetExtension(chooseFile.FileName) != ".xls") { TempData["message"] = "Định dạng file excel không hợp lệ"; return(RedirectToAction("ImportSellOut", "Home", ViewBag.message)); } ExcelPackage package = new ExcelPackage(chooseFile.InputStream); DataTable Dt = ExcelPackageExtensions.ConvertToDataTable(package); //if (Dt.Columns.Count > 8) //{ // TempData["message"] = "Vui lòng chọn templete sell out để có thể import!"; // return RedirectToAction("ImportSellOut", "Home", ViewBag.message); //} List <MT_SellOut> lstTarget = Dt.DataTableToListBaseHeader <MT_SellOut>(); String salesOrg = lstTarget.ElementAt(0).SalesOrg; if (salesOrg != "1100" && salesOrg != "1500") { TempData["message"] = "Vui lòng chọn templete out in để có thể import!"; return(RedirectToAction("ImportSellOut", "Home", ViewBag.message)); } //var db = new DemoEntities1(); //var lstEmp = db.sp_Employee_GetAll(); //List<SellOutViewModel> empViewModel = lstEmp.Select(c => new SellOutViewModel //{ // EmployeeCode = c.EmployeeCode, // EmployeeName = c.EmployeeName, // SalesForceCode = c.SalesForceCode, // Parentcode = c.Parentcode, // SalesForceName = c.SalesForceName, // SalesForceLevel = c.SalesForceLevel //}).ToList(); //DataTable Dt11 = ExcelPackageExtensions.ToDataTable(empViewModel); //-------------------------------------------------------- foreach (var item in lstTarget) { sellOutRepository.Update(item, x => x.Perform); } lstTarget = sellOutRepository.List.ToList(); foreach (var item in lstTarget) { item.Rate = Math.Round((Double.Parse(item.Perform) / Double.Parse(item.Target) * 100)).ToString(); sellOutRepository.Update(item, x => x.Rate); } var lstSalesForce = salesForceRepository.List.ToList(); string day = DateTime.Now.ToString("dd/MM/yyyy"); var result = lstSalesForce.Join(lstTarget, dep => dep.EmployeeCode, e => e.ID, (e, dep) => new { e, dep }) .Select(i => new MT_SellOut { Day = day, SalesOrg = i.dep.SalesOrg, CustomerCode = i.dep.CustomerCode, SalesRouteCode = i.dep.SalesRouteCode, ID = i.dep.ID, Name = i.dep.Name, Store = i.dep.Store, Target = i.dep.Target, Perform = i.dep.Perform, Rate = i.dep.Rate, LineID = i.dep.LineID, CompanyCode = DMSEnum.CompanyCode, SalesForceLevel = i.e.SalesForceLevel, ParentCode = i.e.ParentCode, SalesForceCode = i.e.SalesForceCode } ).OrderBy(x => x.LineID) .ToList(); foreach (var item in result) { sellOutRepository.InsertOrUpdate(item); } DataTable Dt1 = ExcelPackageExtensions.ToDataTable(result); return(View(Dt1)); }
public ActionResult ImportExcelFileToLeadDataTable() { objResponse Response = new objResponse(); session = new SessionHelper(); List <Project.Entity.Leads> leads = new List <Entity.Leads>(); BAL.Leads.LeadsManager objLeadsManager = new LeadsManager(); DataTable dt = new DataTable(); // string Result=""; try { string fname; if (Request.Files.Count > 0) { HttpFileCollectionBase files = Request.Files; for (int i = 0; i < files.Count; i++) { HttpPostedFileBase file = files[i]; // Checking for Internet Explorer if (Request.Browser.Browser.ToUpper() == "IE" || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER") { string[] testfiles = file.FileName.Split(new char[] { '\\' }); fname = testfiles[testfiles.Length - 1]; } else { fname = file.FileName; } if ((file != null) && (file.ContentLength != 0) && !string.IsNullOrEmpty(file.FileName)) { string fileName = file.FileName; string fileContentType = file.ContentType; byte[] fileBytes = new byte[file.ContentLength]; var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength)); } var excel = new ExcelPackage(file.InputStream); dt = ExcelPackageExtensions.ToDataTable(excel); int count = 0; foreach (DataRow dr in dt.Rows) { Project.Entity.Leads objLead = new Entity.Leads(); objLead.Name = dr[0].ToString(); objLead.CompanyName = dr[1].ToString(); objLead.Email = dr[2].ToString(); objLead.Alternate_Email = dr[3].ToString(); objLead.ContactNo = dr[4].ToString(); objLead.SkypeNo = dr[5].ToString(); objLead.AddressLine1 = dr[6].ToString(); objLead.AddressLine2 = dr[7].ToString(); objLead.City = dr[8].ToString(); objLead.State = dr[9].ToString(); objLead.Country = dr[10].ToString(); objLead.ZipCode = dr[11].ToString(); objLead.Source = dr[12].ToString(); Response = objLeadsManager.ImportLead(objLead, session.UserSession.Username, Convert.ToInt64(session.UserSession.PIN)); if (Response.ErrorCode == 0) { if (Response.ErrorMessage != "Lead with same Email Already Exists") { count++; } } else { return(Json("Fail", JsonRequestBehavior.AllowGet)); } } return(Json("Success," + count.ToString(), JsonRequestBehavior.AllowGet)); } return(Json("fail", JsonRequestBehavior.AllowGet)); } else { return(Json("fail", JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { BAL.Common.LogManager.LogError("ImportExcelFileToLeadDataTable", 1, Convert.ToString(ex.Source), Convert.ToString(ex.Message), Convert.ToString(ex.StackTrace)); return(Json("fail", JsonRequestBehavior.AllowGet)); } }