private void SeedKod_Pocztowy_i_ulica(ProContext context) { var kody_excel = Excels.getExcelFile(); var kody = kody_excel.GroupBy(t => t.kod_pocztowy).OrderBy(t => t.Key).Select(t => new Kod_Pocztowy { kod_pocztowy = t.Key, zwyzka = 0, Potwierdzone = false, okolice_miasta = false, Miasto = t.FirstOrDefault().Miasto, Wojewodztwo = t.FirstOrDefault().Wojewodztwo, Odzial = Odzial.Bial, Ulice = t.GroupBy(a => a.Ulica).Select(a => new Ulica() { Nazwa = a.First().Ulica }).ToList() }).ToList(); foreach (var a in kody) { context.Set <Kod_Pocztowy>().AddOrUpdate(a); } context.SaveChanges(); }
public ActionResult ExportExcel() { var date = Request["fromDate"] ?? DateTime.Now.ToString("yyyy-MM-dd"); var model = _api.GetListValueDetail(UserItem.AgencyID, date); var fileName = "Danh_sach_nguyen_lieu.xlsx"; var filePath = Path.Combine(Request.PhysicalApplicationPath, "File\\ExportImport", fileName); var folder = Request.PhysicalApplicationPath + "File\\ExportImport"; if (!Directory.Exists(folder)) { Directory.CreateDirectory(folder); } var di = new DirectoryInfo(folder); foreach (var file in di.GetFiles()) { file.Delete(); } foreach (var dir in di.GetDirectories()) { dir.Delete(true); } Excels.ExportListValueDetail(filePath, model, date); var bytes = System.IO.File.ReadAllBytes(filePath); return(File(bytes, "text/xls", fileName)); }
public ActionResult ViewAttandence(HttpPostedFileBase Attendance) { string ContentType = System.Web.MimeMapping.GetMimeMapping(Attendance.FileName); if (Session["Employee"] != null && ((Employee)Session["Employee"]).isAdmin == true) { if (ContentType == "application/vnd.ms-excel" || ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { Excels WorkBook = new Excels(); try { List <Attendance> x = WorkBook.Read(Attendance.InputStream); Session["AttFile"] = x; return(View("ViewAttandence", x)); } catch (FormatException e) { return(Content(e.Message + " Required format:mm/dd/yyyy")); } //Get data for only absecent employees.... } else { return(RedirectToAction("Index")); } } return(RedirectToAction("Index", "ApplyForLeave")); }
private void ButtonOpen_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { var dialog = new OpenFileDialog(); if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { var extension = Path.GetExtension(dialog.FileName); List <Excels> excels = null; switch (extension) { case ".uad": var stream = File.ReadAllText(dialog.FileName); excels = JsonConvert.DeserializeObject <List <Excels> >(stream); break; case ".xlsx": case ".xls": var excel = new Excel(dialog.FileName); var table = excel.GetFirstTableName(); excels = Excels.Parse(excel.QueryDatatable("SELECT * FROM [" + table + "]", "sheet1")); break; default: MessageBox.Show("不能识别的文件"); break; } Tree.DataSource = excels; } }
public ActionResult ExportExcel() { var model = _api.GetListExport(UserItem.AgencyID, Request.Url.Query); var fileName = "Danh_sach_danh_gia_nv.xlsx"; var filePath = Path.Combine(Request.PhysicalApplicationPath, "File\\ExportImport", fileName); var folder = Request.PhysicalApplicationPath + "File\\ExportImport"; if (!Directory.Exists(folder)) { Directory.CreateDirectory(folder); } var di = new DirectoryInfo(folder); foreach (var file in di.GetFiles()) { file.Delete(); } foreach (var dir in di.GetDirectories()) { dir.Delete(true); } Excels.ExportListCard(filePath, model); var bytes = System.IO.File.ReadAllBytes(filePath); return(File(bytes, "text/xls", fileName)); }
protected void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("4-2")) { if (ispost) { string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; int importdata_count_Stores = 0; int importdata_count_Staff = 0; int importdata_count_StaffB = 0; int importdata_count_All = 0; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } //文件上传 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } } } } if (filearr.Count > 0) { StoresInfo si = new StoresInfo(); StaffInfo sfi = new StaffInfo(); StaffInfo sfiB = new StaffInfo(); StaffStoresInfo ssi = new StaffStoresInfo(); YHsysInfo yhi = new YHsysInfo(); RegionInfo ri = new RegionInfo(); string Stores_sCode = ""; string Stores_sName = ""; string Stores_sType = ""; int Stores_YHsysID = 0; int Stores_RegionID = 0; string Staff_Name = ""; string Staff_NameB = ""; DateTime Staff_sDateTime = DateTime.Parse("2009-1-1");//默认值 try { for (int j = 0; j < filearr.Count; j++) { try { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); DataTable dt = new DataTable(); try { dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString() != "" && dr[1].ToString() != "")//客户名称 { //客户、门店 Stores_sCode = dr[0].ToString().Trim(); Stores_sName = dr[1].ToString().Trim(); Stores_sType = dr[2].ToString().Trim(); Stores_YHsysID = 0; Stores_RegionID = 0; if (dr[3].ToString().Trim() != "") { yhi = tbYHsysInfo.GetYHsysInfoModelByName(dr[3].ToString().Trim()); if (yhi != null) { Stores_YHsysID = yhi.YHsysID; } } if (dr[5].ToString().Trim() != "") { ri = tbRegionInfo.GetRegionInfoModelLikeName(dr[5].ToString().Trim()); if (ri != null) { Stores_RegionID = ri.RegionID; } } si = tbStoresInfo.GetStoresInfoModelByName(Stores_sName); if (si == null) { si = new StoresInfo(); si.sName = Stores_sName; si.sCode = Stores_sCode; si.sType = Stores_sType; si.YHsysID = Stores_YHsysID; si.RegionID = Stores_RegionID; si.sState = 0; si.sAppendTime = DateTime.Now; si.StoresID = tbStoresInfo.AddStoresInfo(si); importdata_count_Stores++; } else { si.sName = Stores_sName; si.sCode = Stores_sCode; si.sType = Stores_sType; si.YHsysID = Stores_YHsysID; si.RegionID = Stores_RegionID; si.sState = 0; try { tbStoresInfo.UpdateStoresInfo(si); importdata_count_Stores++; } catch { } } //业务员 Staff_Name = dr[6].ToString().Trim(); if (Staff_Name.Trim() != "") { sfi = tbStaffInfo.GetStaffInfoModelByName(Staff_Name.Trim()); if (sfi == null) { sfi = new StaffInfo(); sfi.sName = Staff_Name.Trim(); sfi.sSex = ""; sfi.sType = 1; sfi.sTel = ""; sfi.sState = 0; sfi.sAppendTime = DateTime.Now; sfi.StaffID = tbStaffInfo.AddStaffInfo(sfi); importdata_count_Staff++; } } else { sfi = new StaffInfo(); sfi.StaffID = 0; } //促销员 Staff_NameB = dr[7].ToString().Trim(); if (Staff_NameB.Trim() != "") { sfiB = tbStaffInfo.GetStaffInfoModelByName(Staff_NameB.Trim()); if (sfiB == null) { sfiB = new StaffInfo(); sfiB.sName = Staff_NameB.Trim(); sfiB.sSex = "女"; sfiB.sType = 2; sfiB.sTel = dr[8].ToString().Trim(); sfiB.sState = 0; sfiB.sAppendTime = DateTime.Now; sfiB.StaffID = tbStaffInfo.AddStaffInfo(sfiB); importdata_count_StaffB++; } else { sfiB.sTel = dr[8].ToString().Trim(); tbStaffInfo.UpdateStaffInfo(sfiB); } } else { sfiB = new StaffInfo(); sfiB.StaffID = 0; } if (dr[9].ToString().Trim() != "") { Staff_sDateTime = DateTime.Parse(dr[9].ToString().Trim()); } else { Staff_sDateTime = DateTime.Parse("2009-1-1"); } //人员门店绑定 if (si.StoresID > 0) { ssi.StoresID = si.StoresID; if (sfi.StaffID > 0)//绑定业务员 { ssi.StaffID = sfi.StaffID; ssi.sType = 0; ssi.sDateTime = DateTime.Parse("2009-1-1"); ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); importdata_count++; } if (sfiB.StaffID > 0)//绑定促销员 { ssi.StaffID = sfiB.StaffID; ssi.sType = 0; ssi.sDateTime = Staff_sDateTime; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); importdata_count++; } } importdata_count_All++; } } } finally { ds.Clear(); ds.Dispose(); } } catch (Exception ex) { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>" + ex); //AddScript("window.setTimeout('history.back(1);',5000);"); } } if (importdata_count > 0) { AddMsgLine("数据导入成功!共导入数据[" + importdata_count_All.ToString() + "]条,门店[" + importdata_count_Stores.ToString() + "]条,业务员[" + importdata_count_Staff.ToString() + "]条,促销员[" + importdata_count_StaffB.ToString() + "]条,绑定上岗记录[" + importdata_count.ToString() + "]条."); AddScript("window.setTimeout('window.parent.HidBox();',5000);"); } else { AddErrLine("系统忙!导入失败!"); //AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { si = null; sfi = null; sfiB = null; ssi = null; yhi = null; ri = null; } } else { AddErrLine("为发现任何数据!导入失败!"); AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { files = null; filearr.Clear(); } } } else { AddErrLine("权限不足!"); AddScript("window.parent.HidBox();"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
public int StoresID; //匹配状态记录:匹配未成功=0 protected virtual void Page_Load(object sender, EventArgs e) { if (ispost) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("3-4-6-1")) { //获取前台的门店名称与仓库名称 sName = HTTPRequest.GetString("SName").Trim(); stName = HTTPRequest.GetString("stName").Trim(); //根据仓库名称找到仓库编号--预留 stNum = "默认值"; //获取前台选择的时间 DateTime sDateTime = Utils.IsDateString(Utils.ChkSQL(HTTPRequest.GetString("dtime"))) ? DateTime.Parse(Utils.ChkSQL(HTTPRequest.GetString("dtime"))) : DateTime.Now; //根据门店名称取出门店编号 sNum = storehouseStorage.GetScodeBySname(sName); StoresID = Utils.StrToInt(HTTPRequest.GetString("StoresID"), 0); //获得门店编号 if (sNum.Rows.Count > 0) { //判断选择时间是否大于当前时间 if (sDateTime > DateTime.Now) { AddErrLine("日期选择错误,不能大于当前日期,请重新选择!"); } else { //获取上传文件路径 string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } //上传文件 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } else { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>"); } } } } if (filearr.Count > 0) { storehouseproductsprice pi = new storehouseproductsprice(); for (int j = 0; j < filearr.Count; j++) { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); DataTable dt = new DataTable(); try { dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString().Trim() != "" && Utils.IsNumeric(dr[2].ToString().Trim())) { string pName = dr[0].ToString().Trim(); //产品名称 decimal pPrice = decimal.Parse(dr[2].ToString().Trim()); //产品单价 string pBarcode = Utils.ChkSQL(dr[1].ToString().Trim()); //产品条码 if (pBarcode == "") { pBarcode = "null"; int proCode = Utils.StrToInt(storehouseStorage.SelectPcodeByName(pName), 0); //通过产品名称获得产品编号 int pbrCode = Utils.StrToInt(storehouseStorage.SelectpBarcodeByName(pBarcode).ToString(), 0); //通过产品条码获得产品编号 if (pbrCode > 0 || proCode > 0) { bool state = storehouseStorage.GetStorehouseProductPriceInfoList(StoresID, stNum, proCode, pName, pBarcode, pPrice, sDateTime); if (state) { AddErrLine("价格重复:产品条码[" + pBarcode + "];产品名称[" + pName + "]"); } else if (pbrCode > 0) { pi.StoresID = StoresID; pi.StoresName = sName; pi.StCode = stNum; pi.StName = "默认值"; pi.ProductsID = pbrCode; pi.ProductsName = pName; pi.ProductsBarcode = pBarcode; pi.PPrice = pPrice; pi.PAppendTime = sDateTime; if (storehouseStorage.AddStorehouseStorageProductsPriceInfo(pi) > 0) { importdata_count++; } } else if (pbrCode == 0) { pi.StoresID = StoresID; pi.StoresName = sName; pi.StCode = stNum; pi.StName = "默认值"; pi.ProductsID = proCode; pi.ProductsName = pName; pi.ProductsBarcode = "null"; pi.PPrice = pPrice; pi.PAppendTime = sDateTime; if (storehouseStorage.AddStorehouseStorageProductsPriceInfo(pi) > 0) { importdata_count++; } } } else { AddMsgLine("该产品[" + pName + "];产品条码[" + pBarcode + "]库中不存在!"); } } else { int proCode = Utils.StrToInt(storehouseStorage.SelectPcodeByName(pName), 0); //通过产品名称获得产品编号 int pbrCode = Utils.StrToInt(storehouseStorage.SelectpBarcodeByName(pBarcode).ToString(), 0); //通过产品条码获得产品编号 if (pbrCode > 0 || proCode > 0) { bool state = storehouseStorage.GetStorehouseProductPriceInfoList(StoresID, stNum, pbrCode, pName, pBarcode, pPrice, sDateTime); if (state) { AddErrLine("价格重复:产品条码[" + pBarcode + "];产品名称[" + pName + "]"); } else if (pbrCode > 0) { pi.StoresID = StoresID; pi.StoresName = sName; pi.StCode = stNum; pi.StName = "默认值"; pi.ProductsID = pbrCode; pi.ProductsName = pName; pi.ProductsBarcode = pBarcode; pi.PPrice = pPrice; pi.PAppendTime = sDateTime; if (storehouseStorage.AddStorehouseStorageProductsPriceInfo(pi) > 0) { importdata_count++; } } else if (pbrCode == 0) { pi.StoresID = StoresID; pi.StoresName = sName; pi.StCode = stNum; pi.StName = "默认值"; pi.ProductsID = proCode; pi.ProductsName = pName; pi.ProductsBarcode = "null"; pi.PPrice = pPrice; pi.PAppendTime = sDateTime; if (storehouseStorage.AddStorehouseStorageProductsPriceInfo(pi) > 0) { importdata_count++; } } } else { AddMsgLine("该产品[" + pName + "];产品条码[" + pBarcode + "]库中不存在!"); } } } } } catch (Exception ex) { AddErrLine(ex.Message); } finally { ds.Clear(); ds.Dispose(); } } if (importdata_count > 0) { AddMsgLine("数据导入成功!共导入数据[" + importdata_count.ToString() + "]条."); //AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } else { AddErrLine("系统忙!导入失败!"); } } } catch (Exception ex) { AddErrLine("错误信息" + ex.Message); } } } else { AddMsgLine("该门店[" + sName + "]编号不存在!请确认后再次输入或先添加该门店!"); } } else { AddErrLine("权限不足!"); AddScript("window.parent.HidBox();"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } } }
private void btnExcel_Click(object sender, EventArgs e) { //Excels.Input_Excel(dgvWageCount); Excels.Input_Excel(dgvWageCount); }
protected virtual void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("3-4-1")) { steps = HTTPRequest.GetInt("steps", 1); format = HTTPRequest.GetString("format"); act = HTTPRequest.GetString("act"); if (ispost) { string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; string tSName = ""; int sDateTime_S = Utils.StrToInt(HTTPRequest.GetString("sDateTime_S"), 0); sDateTime = Utils.IsDateString(Utils.ChkSQL(HTTPRequest.GetString("sDateTime"))) ? DateTime.Parse(Utils.ChkSQL(HTTPRequest.GetString("sDateTime"))) : DateTime.Now; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } if (steps == 1) { try { //文件上传 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } } } } if (filearr.Count > 0) { for (int j = 0; j < filearr.Count; j++) { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); try { dList = ds.Tables[0]; dList.Rows[0].Delete(); dList.AcceptChanges(); steps = 2; break; } finally { ds = null; } } } } finally { files = null; filearr.Clear(); } } catch (Exception ex) { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>" + ex); AddScript("window.setTimeout('history.back(1);',5000);"); } } if (format == "Json") { if (steps == 2) { if (act == "add") { string sStoresID = Utils.ChkSQL(HTTPRequest.GetString("sStoresID")).Trim(); string sStoresName = Utils.ChkSQL(HTTPRequest.GetString("sStoresName")).Trim(); string pBarcode = Utils.ChkSQL(HTTPRequest.GetString("pBarcode")).Trim(); string sProductsName = Utils.ChkSQL(HTTPRequest.GetString("sProductsName")).Trim(); string sNum = Utils.ChkSQL(HTTPRequest.GetString("sNum")).Trim(); string sPrice = Utils.ChkSQL(HTTPRequest.GetString("sPrice")).Trim(); string tID = Utils.ChkSQL(HTTPRequest.GetString("tID")).Trim(); tJson = ",\"tID\":\"" + tID + "\""; SalesInfo si = new SalesInfo(); StoresInfo so = new StoresInfo(); ProductsInfo pi = new ProductsInfo(); try { si.sDateTime = sDateTime; si.sAppendTime = DateTime.Now; sStoresName = Utils.ReplaceString(Utils.ReplaceString(sStoresName.ToString().Trim(), "(", "(", false), ")", ")", false); so = tbStoresInfo.GetStoresInfoModelByName(sStoresName); if (so == null) { so = tbStoresInfo.GetStoresInfoModelByCode(sStoresID.ToString().Trim()); } pi = tbProductsInfo.GetProductsInfoModelByName(sProductsName.ToString().Trim()); if (pi == null) { pi = tbProductsInfo.GetProductsInfoModelByBarcode(pBarcode.ToString().Trim()); } if (so != null) { si.StoresID = so.StoresID; si.StoresName = so.sName; } else { si.StoresID = 0; si.StoresName = sStoresName; } if (pi != null) { si.ProductsID = pi.ProductsID; si.ProductsName = pi.pName; } else { si.ProductsID = 0; si.ProductsName = sProductsName; } si.sStoresID = sStoresID; si.sNum = int.Parse(Utils.StrToFloat(sNum, 0).ToString()); if (sPrice.ToString().IndexOf(",") > -1) { si.sPrice = decimal.Parse(sPrice.ToString().Replace(",", "")); } else { si.sPrice = decimal.Parse(sPrice.ToString()); } si.sIsYH = 1; int sID = tbSalesInfo.AddSalesInfo(si); if (sID > 0) { tJson += ",\"sID\":\"" + sID + "\""; AddMsgLine("添加成功!"); } else { AddErrLine("添加失败!"); } } finally { si = null; so = null; pi = null; } } if (act == "del") { try { string sIdStr = HTTPRequest.GetString("sIDStr"); sIdStr = Utils.ReSQLSetTxt(sIdStr); if (sIdStr.Trim() != "") { tbSalesInfo.DeleteSalesInfo(sIdStr, sDateTime); } AddMsgLine("撤销成功!"); } catch (Exception ex) { AddErrLine("撤销失败!"); } } Response.ClearContent(); Response.Buffer = true; Response.ExpiresAbsolute = System.DateTime.Now.AddYears(-1); Response.Expires = 0; Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.ContentType = "application/json"; string Json_Str = "{\"results\": {\"msg\":\"" + this.msgbox_text + "\",\"state\":\"" + (!IsErr()).ToString() + "\"" + tJson + "}}"; Response.Write(Json_Str); Response.End(); } } } } else { AddErrLine("权限不足!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
protected virtual void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("6-1")) { if (ispost) { int sType = Utils.StrToInt(HTTPRequest.GetString("sType"), 0); string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } //文件上传 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } } } } if (filearr.Count > 0) { MarketingFeesInfo mi = new MarketingFeesInfo(); StoresInfo si = new StoresInfo(); FeesSubjectInfo fi = new FeesSubjectInfo(); StaffInfo ft = new StaffInfo(); try { for (int j = 0; j < filearr.Count; j++) { try { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); DataTable dt = new DataTable(); try { dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { mi.mAppendTime = DateTime.Now; if (sType == 0)//营销费用 { if (dr[0].ToString() != "" && dr[1].ToString() != "" && dr[2].ToString() != "" && dr[3].ToString() != "" && dr[4].ToString() != "") { if (Utils.IsNumeric(dr[3].ToString())) { si = tbStoresInfo.GetStoresInfoModelByName(dr[2].ToString().Trim()); if (si == null) { si = tbStoresInfo.GetStoresInfoModelByCode(dr[1].ToString().Trim()); } if (si != null) { mi.StoresID = si.StoresID; } fi = tbFeesSubjectInfo.GetFeesSubjectInfoModelByName(dr[4].ToString().Trim()); if (fi != null) { mi.FeesSubjectID = fi.FeesSubjectID; } mi.mRemark = dr[5].ToString().Trim(); mi.mFees = decimal.Parse(Utils.StrToFloat(dr[3].ToString().Trim(), 0).ToString()); mi.mDateTime = DateTime.Parse(dr[0].ToString().Trim()); mi.mType = 0; mi.StaffID = 0; if (tbMarketingFeesInfo.AddMarketingFeesInfo(mi) > 0) { importdata_count++; } } } } if (sType == 1)//公司费用 { if (dr[0].ToString() != "" && dr[1].ToString() != "" && dr[2].ToString() != "" && dr[3].ToString() != "") { if (Utils.IsNumeric(dr[2].ToString())) { mi.StoresID = 0; mi.mDateTime = DateTime.Parse(dr[0].ToString().Trim()); fi = tbFeesSubjectInfo.GetFeesSubjectInfoModelByName(dr[1].ToString().Trim()); if (fi != null) { mi.FeesSubjectID = fi.FeesSubjectID; } mi.mFees = decimal.Parse(Utils.StrToFloat(dr[2].ToString().Trim(), 0).ToString()); ft = tbStaffInfo.GetStaffInfoModelByName(dr[3].ToString().Trim()); if (ft != null) { mi.StaffID = ft.StaffID; } mi.mType = 1; mi.mRemark = dr[4].ToString().Trim(); if (tbMarketingFeesInfo.AddMarketingFeesInfo(mi) > 0) { importdata_count++; } } } } if (sType == 2)//收入 { if (dr[0].ToString() != "" && dr[1].ToString() != "" && dr[2].ToString() != "") { if (Utils.IsNumeric(dr[1].ToString())) { mi.StoresID = 0; mi.mDateTime = DateTime.Parse(dr[0].ToString().Trim()); if (dr[3].ToString().Trim() != "") { fi = tbFeesSubjectInfo.GetFeesSubjectInfoModelByName(dr[3].ToString().Trim()); if (fi != null) { mi.FeesSubjectID = fi.FeesSubjectID; } } else { mi.FeesSubjectID = 0; } mi.mFees = decimal.Parse(Utils.StrToFloat(dr[1].ToString().Trim(), 0).ToString()); ft = tbStaffInfo.GetStaffInfoModelByName(dr[2].ToString().Trim()); if (ft != null) { mi.StaffID = ft.StaffID; } mi.mIsIncomeExpenditure = 1; mi.mType = -1; mi.mRemark = dr[4].ToString().Trim(); if (tbMarketingFeesInfo.AddMarketingFeesInfo(mi) > 0) { importdata_count++; } } } } } } finally { ds.Clear(); ds.Dispose(); } } catch (Exception ex) { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>" + ex); //AddScript("window.setTimeout('history.back(1);',5000);"); } } if (importdata_count > 0) { AddMsgLine("数据导入成功!共导入数据[" + importdata_count.ToString() + "]条."); AddScript("window.setTimeout('window.parent.HidBox();',5000);"); } else { AddErrLine("系统忙!导入失败!"); //AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { mi = null; si = null; ft = null; } } else { AddErrLine("为发现任何数据!导入失败!"); AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { files = null; filearr.Clear(); } } } else { AddErrLine("权限不足!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
protected virtual void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("2-2-2") || CheckUserPopedoms("5-1")) { if (ispost) { string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } //文件上传 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } } } } if (filearr.Count > 0) { ProductsInfo pi = new ProductsInfo(); try { for (int j = 0; j < filearr.Count; j++) { try { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); DataTable dt = new DataTable(); try { dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr[1].ToString() != "" && dr[3].ToString() != "" && dr[4].ToString() != "" && dr[5].ToString() != "" && dr[6].ToString() != "") { if (Utils.IsInt(dr[5].ToString())) { pi = tbProductsInfo.GetProductsInfoModelByName(dr[1].ToString()); if (pi == null) { pi = new ProductsInfo(); pi.pName = dr[1].ToString(); pi.pBarcode = dr[0].ToString(); pi.pBrand = dr[2].ToString(); pi.pStandard = dr[3].ToString(); pi.pUnits = dr[4].ToString(); pi.pToBoxNo = Utils.StrToInt(dr[5].ToString(), 0); pi.pPrice = decimal.Parse(Utils.StrToFloat(dr[6].ToString(), 0).ToString()); pi.pState = 0; pi.pAppendTime = DateTime.Now; pi.pCode = ""; if (tbProductsInfo.AddProductsInfo(pi) > 0) { importdata_count++; } } else { pi.pBarcode = dr[0].ToString(); pi.pBrand = dr[2].ToString(); pi.pStandard = dr[3].ToString(); pi.pUnits = dr[4].ToString(); pi.pToBoxNo = Utils.StrToInt(dr[5].ToString(), 0); pi.pPrice = decimal.Parse(Utils.StrToFloat(dr[6].ToString(), 0).ToString()); try { tbProductsInfo.UpdateProductsInfo(pi); importdata_count++; } catch { } } } } } } finally { ds.Clear(); ds.Dispose(); } } catch (Exception ex) { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>" + ex); //AddScript("window.setTimeout('history.back(1);',5000);"); } } if (importdata_count > 0) { AddMsgLine("数据导入成功!共导入数据[" + importdata_count.ToString() + "]条."); AddScript("window.setTimeout('window.parent.HidBox();',5000);"); } else { AddErrLine("系统忙!导入失败!"); //AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { pi = null; } } else { AddErrLine("为发现任何数据!导入失败!"); AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { files = null; filearr.Clear(); } } } else { AddErrLine("权限不足!"); AddScript("window.parent.HidBox();"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
private void btnExcel_Click(object sender, EventArgs e) { Excels.Input_Excel(dgvOutlay); }
public IActionResult UpLoadExcel(IFormFile fExcel, CancellationToken cancellationToken) { ResetDatabase(); Excels fileToImport = new Excels { ExcelUploadedDate = DateTime.Now }; if (fExcel != null && CheckGetExtentionsFileIsSupported(fExcel)) { ViewBag.Error = null; string path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "Excels", fExcel.FileName); using (var file = new FileStream(path, FileMode.Create)) { fExcel.CopyTo(file); } fileToImport.ExcelFileName = fExcel.FileName; _ctx.Add(fileToImport); _ctx.SaveChanges(); /// Save excel file history // import data using (var stream = new MemoryStream()) { fExcel.CopyTo(stream); using (ExcelPackage package = new ExcelPackage(stream)) { ExcelWorksheet workSheet = package.Workbook.Worksheets["DynamicReport"]; if (workSheet != null) { // List to ADD database List <RawData> rawDatas = new List <RawData>(); int totalRows = workSheet.Dimension.Rows; for (int i = 6; i < totalRows; i++) { var dataString = workSheet.Cells[i, 46].Value; string dateString = dataString != null?dataString.ToString().Split('-')[0] + "/" + dataString.ToString().Split('-')[1] + "/" + dataString.ToString().Split('-')[2] : DateTime.MaxValue.ToString("dd/MM/yyyy"); rawDatas.Add(new RawData { OrderNo = workSheet.Cells[i, 1].Value.ToString(), JobNo = workSheet.Cells[i, 41].Value.ToString(), DeliveryCustCode = workSheet.Cells[i, 14].Value.ToString(), DeliveryAddress = workSheet.Cells[i, 17].Value != null ? workSheet.Cells[i, 17].Value.ToString() : String.Empty, ServiceLevel = workSheet.Cells[i, 24].Value != null ? workSheet.Cells[i, 24].Value.ToString() : "servicesLevel-unknown-" + i, TruckId = workSheet.Cells[i, 40].Value != null ? workSheet.Cells[i, 40].Value.ToString() : "trucks-unknown-" + i, TruckType = workSheet.Cells[i, 43].Value != null ? workSheet.Cells[i, 43].Value.ToString() : String.Empty, TransportAgent = workSheet.Cells[i, 45].Value != null ? workSheet.Cells[i, 45].Value.ToString() : String.Empty, AtdcompleteDate = DateTime.ParseExact(dateString, "dd/MM/yyyy", CultureInfo.InvariantCulture), DriverName = workSheet.Cells[i, 50].Value != null ? workSheet.Cells[i, 50].Value.ToString() : String.Empty, DriverPhone = workSheet.Cells[i, 51].Value != null ? workSheet.Cells[i, 51].Value.ToString() : String.Empty, }); } _ctx.RawData.AddRange(rawDatas); _ctx.SaveChanges(); // Insert data for per table ImportDataToPerTable(); ViewBag.ImportSuccess = "Import dữ liệu thành công !"; return(View("Index", _ctx.Excels.ToList())); } else { ViewBag.Error = "Không tìm thấy sheet cần thiết của hệ thống để import dữ liệu! Vui lòng kiểm tra tên của Sheet theo yêu cầu của hệ thống !"; return(View("Index", _ctx.Excels.ToList())); } } } } else { ViewBag.Error = "Vui lòng chọn file excel hoặc định dạng file của bạn không được hỗ trợ. Lưu ý những file được hỗ trợ bao gồm : .xlsx, .csv "; return(View("Index", _ctx.Excels.ToList())); } }