/// <summary> /// 下载模板 /// </summary> public void StorerDemoExecl() { DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataTable dtDetail = new DataTable(); //主表信息 dt.Columns.Add("StorerKey", typeof(string)); dt.Columns.Add("状态", typeof(string)); dt.Columns.Add("店铺名称", typeof(string)); dt.Columns.Add("SAP代码", typeof(string)); dt.Columns.Add("类型", typeof(string)); dt.Columns.Add("地址1", typeof(string)); dt.Columns.Add("地址2", typeof(string)); dt.Columns.Add("地址3", typeof(string)); dt.Columns.Add("地址4", typeof(string)); dt.Columns.Add("城市", typeof(string)); dt.Columns.Add("州", typeof(string)); dt.Columns.Add("邮编", typeof(string)); dt.Columns.Add("国家", typeof(string)); dt.Columns.Add("国家代码", typeof(string)); dt.Columns.Add("联系人1", typeof(string)); dt.Columns.Add("联系人2", typeof(string)); dt.Columns.Add("联系电话1", typeof(string)); dt.Columns.Add("联系电话2", typeof(string)); dt.Columns.Add("传真1", typeof(string)); dt.Columns.Add("传真2", typeof(string)); dt.Columns.Add("邮箱1", typeof(string)); dt.Columns.Add("邮箱2", typeof(string)); dt.Columns.Add("创建人", typeof(string)); dt.Columns.Add("更新人", typeof(string)); DataRow dr1 = dt.NewRow(); dr1["StorerKey"] = "3602"; dr1["店铺名称"] = "耐克成都双流换季优惠店"; dr1["SAP代码"] = "8006902"; dr1["类型"] = "SELLER"; dr1["地址1"] = "成都市双流区东升街道双楠大道中段633号时代奥特莱斯S-1184,1185,S-2189,2190"; dr1["国家代码"] = "CN"; dr1["联系人1"] = "刘力"; dr1["联系电话1"] = "028-61915601"; dr1["创建人"] = "NIKENFSCD"; dt.Rows.Add(dr1); ds.Tables.Add(dt); //明细信息 //dtDetail.Columns.Add("SKU", typeof(string)); //dtDetail.Columns.Add("UPC", typeof(string)); //dtDetail.Columns.Add("UPC名称", typeof(string)); //dtDetail.Columns.Add("UPC类型", typeof(string)); //dtDetail.Columns.Add("UPC数量", typeof(string)); //ds.Tables.Add(dtDetail); //ExportDataToExcelHelper.ExportDataSetToExcel(ds, "店铺导入模板.xls");//生成Excel EPPlusOperation.ExportByEPPlus(ds.Tables[0], "店铺导入模板"); }
/// <summary> /// 导出箱清单 /// </summary> /// <param name="id">orderid</param> /// <param name="type">1</param> /// <param name="OrderID">0</param> public void ExportBoxDetail(string id, string type, string OrderID) { DataSet ds = new OrderManagementService().ExportBoxDetailsYXDR(id.ToString()); DataTable dt = ds.Tables[0]; //这段代码我承认写的很lj if (dt.Rows.Count > 0) { DataTable dtheader = new DataTable(); //订单主信息 DataTable dtBoxdetail = new DataTable(); //箱明细信息 dtheader.Columns.Add("dc1", typeof(string)); dtheader.Columns.Add("dc2", typeof(string)); dtheader.Columns.Add("dc3", typeof(string)); dtheader.Columns.Add("dc4", typeof(string)); dtheader.Columns.Add("dc5", typeof(string)); dtheader.Columns.Add("dc6", typeof(string)); dtheader.Columns.Add("dc7", typeof(string)); dtheader.Columns.Add("dc8", typeof(string)); dtheader.Columns.Add("dc9", typeof(string)); dtheader.Columns.Add("dc10", typeof(string)); dtheader.Columns.Add("dc11", typeof(string)); dtheader.Columns.Add("dc12", typeof(string)); dtheader.Columns.Add("dc13", typeof(string)); #region 先把一单的头信息加上 //第一行 DataRow dr = dtheader.NewRow(); dr["dc1"] = "箱清单"; dr["dc2"] = "订单号:"; dr["dc3"] = dt.Rows[0]["ExternOrderNumber"].ToString().Trim(); dr["dc4"] = "货主:"; dr["dc5"] = "Haddad"; dr["dc6"] = "门店代码:"; dr["dc7"] = dt.Rows[0]["str4"].ToString().Trim(); dr["dc8"] = "门店名称:"; dr["dc9"] = dt.Rows[0]["Company"].ToString(); dr["dc10"] = "门店地址:"; dr["dc11"] = dt.Rows[0]["AddressLine1"].ToString(); dr["dc12"] = "出货日期:"; dr["dc13"] = DateTime.Now; dtheader.Rows.Add(dr); //第二行明细列 DataRow dr2 = dtheader.NewRow(); dr2["dc1"] = "箱唛号"; dr2["dc2"] = "箱号"; dr2["dc3"] = "款号"; dr2["dc4"] = "描述"; dr2["dc5"] = "尺码"; dr2["dc6"] = "数量"; dr2["dc7"] = "订单号"; dr2["dc8"] = "条形码"; dr2["dc9"] = ""; dr2["dc10"] = ""; dr2["dc11"] = ""; dtheader.Rows.Add(dr2); #endregion List <string> list = new List <string>(); int boxnumber = 1; //箱号 int packageSumQty = 0; //每箱总数 string packagenumber = ""; //箱唛号 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr3 = dtheader.NewRow(); if (i == 0) { packagenumber = dt.Rows[i]["PackageNumber"].ToString(); dr3["dc1"] = dt.Rows[i]["ShippingMark"].ToString(); dr3["dc2"] = boxnumber.ToString(); dr3["dc3"] = dt.Rows[i]["Atrcle"].ToString(); dr3["dc4"] = dt.Rows[i]["GoodsName"].ToString(); dr3["dc5"] = dt.Rows[i]["Size"].ToString(); dr3["dc6"] = dt.Rows[i]["Qty"].ToString(); dr3["dc7"] = dt.Rows[i]["ExternOrderNumber"].ToString(); dr3["dc8"] = dt.Rows[i]["SKU"].ToString(); } else { if (packagenumber != dt.Rows[i]["PackageNumber"].ToString()) { DataRow dr4 = dtheader.NewRow(); dr4["dc1"] = "箱总数:"; dr4["dc2"] = packageSumQty.ToString(".00"); dr4["dc3"] = ""; dr4["dc4"] = ""; dr4["dc5"] = ""; dr4["dc6"] = ""; dr4["dc7"] = ""; dr4["dc8"] = ""; dtheader.Rows.Add(dr4); packagenumber = dt.Rows[i]["PackageNumber"].ToString(); packageSumQty = 0; boxnumber++; } dr3["dc1"] = dt.Rows[i]["ShippingMark"].ToString(); dr3["dc2"] = boxnumber; dr3["dc3"] = dt.Rows[i]["Atrcle"].ToString(); dr3["dc4"] = dt.Rows[i]["GoodsName"].ToString(); dr3["dc5"] = dt.Rows[i]["Size"].ToString(); dr3["dc6"] = dt.Rows[i]["Qty"].ToString(); dr3["dc7"] = dt.Rows[i]["ExternOrderNumber"].ToString(); dr3["dc8"] = dt.Rows[i]["SKU"].ToString(); } dtheader.Rows.Add(dr3); packageSumQty += Convert.ToInt32(Convert.ToDouble(dt.Rows[i]["Qty"].ToString())); } DataRow dr5 = dtheader.NewRow(); dr5["dc1"] = "箱总数:"; dr5["dc2"] = packageSumQty.ToString(".00"); dr5["dc3"] = ""; dr5["dc4"] = ""; dr5["dc5"] = ""; dr5["dc6"] = ""; dr5["dc7"] = ""; dr5["dc8"] = ""; dtheader.Rows.Add(dr5); //ExportDataToExcelHelper.ExportDataSetToExcel(dtheader, "箱清单" + DateTime.Now.ToString("yyyy-MM-dd"), ""); EPPlusOperation.ExportByEPPlus(dtheader, "箱清单" + DateTime.Now.ToString("yyyy-MM-dd")); } else { //ExportDataToExcelHelper.ExportDataSetToExcel(dt, "箱清单" + DateTime.Now.ToString("yyyy-MM-dd"), ""); EPPlusOperation.ExportByEPPlus(dt, "箱清单" + DateTime.Now.ToString("yyyy-MM-dd")); } }
public string ExeclQuotedPrices() { if (Request.Files.Count > 0) { HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase; if (hpf.ContentLength > 0) { //DataSet ds = this.GetDataFromExcel(hpf); DataSet ds = EPPlusOperation.ReadExcelToDataSet(hpf); if (ds != null && ds.Tables[0] != null) { IList <QuotedPrices> qprice = new List <QuotedPrices>(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { qprice.Add(new QuotedPrices() { ProjectID = base.UserInfo.ProjectID, ProjectName = base.UserInfo.ProjectName, Target = Convert.ToInt32(ds.Tables[0].Rows[i]["Target"].ToString()), TargetName = ds.Tables[0].Rows[i]["TargetName"].ToString(), StartCityName = ds.Tables[0].Rows[i]["StartCityName"].ToString(), EndCityName = ds.Tables[0].Rows[i]["EndCityName"].ToString(), PodTypeName = ds.Tables[0].Rows[i]["PodTypeName"].ToString(), ShipperTypeName = ds.Tables[0].Rows[i]["ShipperTypeName"].ToString(), P200 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P200"].ToString()), P500 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P500"].ToString()), P1000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P1000"].ToString()), P2000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P2000"].ToString()), P5000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P5000"].ToString()), P10000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P10000"].ToString()), P20000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P20000"].ToString()), P30000 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P30000"].ToString()), P99999 = Convert.ToDecimal(ds.Tables[0].Rows[i]["P99999"].ToString()), RelatedCustomerName = ds.Tables[0].Rows[i]["RelatedCustomerName"].ToString() }); } AddQuotedPriceRequest request = new AddQuotedPriceRequest() { QuotedPrices_New = qprice.Select(q => new QuotedPrices() { ProjectID = base.UserInfo.ProjectID, ProjectName = base.UserInfo.ProjectName, Target = q.Target, TargetName = q.TargetName, StartCityName = q.StartCityName, EndCityName = q.EndCityName, PodTypeName = q.PodTypeName, ShipperTypeName = q.ShipperTypeName, P200 = q.P200 ?? 0, P500 = q.P500 ?? 0, P1000 = q.P1000 ?? 0, P2000 = q.P2000 ?? 0, P5000 = q.P5000 ?? 0, P10000 = q.P10000 ?? 0, P20000 = q.P20000 ?? 0, P30000 = q.P30000 ?? 0, P99999 = q.P99999 ?? 0, RelatedCustomerName = q.RelatedCustomerName }) }; var response = new QuotedPriceService().AddQuotedPrices(request); if (response.IsSuccess) { return(new { result = "导入成功", IsSuccess = true }.ToJsonString()); } } return(new { result = "<h3>导入失败!</h3><br/>excel内容有误!", IsSuccess = false }.ToString()); } return(new { result = "文件内容为空", IsSuccess = false }.ToString()); } return(new { result = "请选择文件", IsSuccess = false }.ToString()); }
public string ExeclQuotedPrice() { if (Request.Files.Count > 0) { HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase; if (hpf.ContentLength > 0) { //DataSet ds = this.GetDataFromExcel(hpf); DataSet ds = EPPlusOperation.ReadExcelToDataSet(hpf); if (ds != null && ds.Tables[0] != null) { IList <QuotedPrice> Info = new List <QuotedPrice>(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Info.Add(new QuotedPrice() { ProjectID = base.UserInfo.ProjectID, ProjectName = base.UserInfo.ProjectName, Target = Convert.ToInt32(ds.Tables[0].Rows[i]["Target"]), TargetID = Convert.ToInt32(ds.Tables[0].Rows[i]["TargetID"]), TargetName = ds.Tables[0].Rows[i]["TargetName"].ToString(), ShipperTypeID = 0, ShipperTypeName = ds.Tables[0].Rows[i]["ShipperTypeName"].ToString(), PodTypeID = 0, PodTypeName = ds.Tables[0].Rows[i]["PodTypeName"].ToString(), TplOrTtlID = 0, TplOrTtlName = ds.Tables[0].Rows[i]["TplOrTtlName"].ToString(), TransportationLineID = 0, StartCityID = 0, StartCityName = ds.Tables[0].Rows[i]["StartCityName"].ToString(), EndCityID = 0, EndCityName = ds.Tables[0].Rows[i]["EndCityName"].ToString(), SegmentDetailID = 0, StartVal = Convert.ToInt32(ds.Tables[0].Rows[i]["StartVal"]), EndVal = Convert.ToInt32(ds.Tables[0].Rows[i]["EndVal"]), //hjq MinPrice = 0, Point = 0,// Convert.ToDecimal(ds.Tables[0].Rows[i]["Point"]), Price = Convert.ToDecimal(ds.Tables[0].Rows[i]["Price"]), EffectiveStartTime = Convert.ToDateTime(ds.Tables[0].Rows[i]["EffectiveStartTime"]), EffectiveEndTime = null, Creator = base.UserInfo.Name, CreateTime = DateTime.Now, Remark = "", State = true, RelatedCustomerID = Convert.ToInt32(ds.Tables[0].Rows[i]["RelatedCustomerID"]), RelatedCustomerName = ds.Tables[0].Rows[i]["RelatedCustomerName"].ToString(), //hjq EmptyCarryPrice = 0, Decimal1 = 0, Decimal2 = 0 }); } AddQuotedPriceRequest request = new AddQuotedPriceRequest() { QuotedPrices = Info.Select(q => new QuotedPrice() { ProjectID = base.UserInfo.ProjectID, ProjectName = base.UserInfo.ProjectName, Target = q.Target, TargetID = q.TargetID, TargetName = q.TargetName, ShipperTypeID = q.ShipperTypeID, ShipperTypeName = q.ShipperTypeName, PodTypeID = q.PodTypeID, PodTypeName = q.PodTypeName, TplOrTtlID = q.TplOrTtlID, TplOrTtlName = q.TplOrTtlName, TransportationLineID = q.TransportationLineID, StartCityID = q.StartCityID, StartCityName = q.StartCityName, EndCityID = q.EndCityID, EndCityName = q.EndCityName, SegmentDetailID = q.SegmentDetailID, StartVal = q.StartVal, EndVal = q.EndVal, MinPrice = q.MinPrice ?? 0, Point = q.Point ?? 0, Price = q.Price, EffectiveStartTime = q.EffectiveStartTime, EffectiveEndTime = q.EffectiveEndTime, Creator = base.UserInfo.Name, CreateTime = DateTime.Now, Remark = q.Remark, State = true, RelatedCustomerID = q.RelatedCustomerID, RelatedCustomerName = q.RelatedCustomerName, EmptyCarryPrice = q.EmptyCarryPrice ?? 0, Decimal1 = q.Decimal1 ?? 0, Decimal2 = q.Decimal2 ?? 0 }) }; // var groupedInfos = from g in Info select new QuotedPrice() { ID = g.ID, Remark = g.Remark }; var response = new QuotedPriceService().AddQuotedPrice(request); if (response.IsSuccess) { return(new { result = "导入成功!", IsSuccess = true }.ToJsonString()); } } return(new { result = "<h3>导入失败!</h3><br/>excel内容有误!", IsSuccess = false }.ToString()); } return(new { result = "文件内容为空", IsSuccess = false }.ToString()); } return(new { result = "请选择文件", IsSuccess = false }.ToString()); }
public string ExeclStorer(int StorerID) { if (Request.Files.Count > 0) { HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase; if (hpf.ContentLength > 0) { //DataSet ds = this.GetDataFromExcel(hpf); DataSet ds = EPPlusOperation.ReadExcelToDataSet(hpf); IList <WMS_Customer> Info = new List <WMS_Customer>(); if (ds != null && ds.Tables[0] != null) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Info.Add(new WMS_Customer() { StorerKey = ds.Tables[0].Rows[i]["StorerKey"].ToString().Trim(), Active = true,//1 Status = ds.Tables[0].Rows[i]["状态"].ToString().Trim(), Company = ds.Tables[0].Rows[i]["店铺名称"].ToString().Trim(), ReceiptPrefix = "", OrderPrefix = "", CompanyCode = ds.Tables[0].Rows[i]["SAP代码"].ToString().Trim(), Type = ds.Tables[0].Rows[i]["类型"].ToString().Trim(),//SELLER AddressLine1 = ds.Tables[0].Rows[i]["地址1"].ToString().Trim(), AddressLine2 = ds.Tables[0].Rows[i]["地址2"].ToString().Trim(), AddressLine3 = ds.Tables[0].Rows[i]["地址3"].ToString().Trim(), AddressLine4 = ds.Tables[0].Rows[i]["地址4"].ToString().Trim(), City = ds.Tables[0].Rows[i]["城市"].ToString().Trim(), State = ds.Tables[0].Rows[i]["州"].ToString().Trim(), PostCode = ds.Tables[0].Rows[i]["邮编"].ToString().Trim(), Country = ds.Tables[0].Rows[i]["国家"].ToString().Trim(), CountryCode = ds.Tables[0].Rows[i]["国家代码"].ToString().Trim(),//CN Contact1 = ds.Tables[0].Rows[i]["联系人1"].ToString().Trim(), Contact2 = ds.Tables[0].Rows[i]["联系人2"].ToString().Trim(), PhoneNum1 = ds.Tables[0].Rows[i]["联系电话1"].ToString().Trim(), PhoneNum2 = ds.Tables[0].Rows[i]["联系电话2"].ToString().Trim(), FaxNum1 = ds.Tables[0].Rows[i]["传真1"].ToString().Trim(), FaxNum2 = ds.Tables[0].Rows[i]["传真2"].ToString().Trim(), Email1 = ds.Tables[0].Rows[i]["邮箱1"].ToString().Trim(), Email2 = ds.Tables[0].Rows[i]["邮箱2"].ToString().Trim(), TriggerDog = false,//0 CreateUser = ds.Tables[0].Rows[i]["创建人"].ToString().Trim(), CreateDate = DateTime.Now, UpdateUser = ds.Tables[0].Rows[i]["更新人"].ToString().Trim(), UpdateDate = DateTime.Now, CustomerID = StorerID }); } } var groupedInfos = from g in Info select new WMS_Customer() { StorerKey = g.StorerKey, Active = g.Active, Status = g.Status, Company = g.Company, ReceiptPrefix = g.ReceiptPrefix, OrderPrefix = g.OrderPrefix, CompanyCode = g.CompanyCode, Type = g.Type, AddressLine1 = g.AddressLine1, AddressLine2 = g.AddressLine2, AddressLine3 = g.AddressLine3, AddressLine4 = g.AddressLine3, City = g.City, State = g.State, PostCode = g.PostCode, Country = g.Country, CountryCode = g.CountryCode, Contact1 = g.Contact1, Contact2 = g.Contact2, PhoneNum1 = g.PhoneNum1, PhoneNum2 = g.PhoneNum2, FaxNum1 = g.FaxNum1, FaxNum2 = g.FaxNum2, Email1 = g.Email1, Email2 = g.Email2, TriggerDog = g.TriggerDog, CreateUser = g.CreateUser, CreateDate = g.CreateDate, UpdateUser = g.UpdateUser, UpdateDate = g.UpdateDate, CustomerID = g.CustomerID }; var repeat = groupedInfos.GroupBy(a => a.StorerKey).Where(g => g.Count() >= 2); if (repeat.Count() > 0) { return(new { result = "有重复店铺!", IsSuccess = false }.ToJsonString()); } WMS_CustomerService service = new WMS_CustomerService(); Response <IEnumerable <WMS_Customer> > response = service.AddCustomer(new AddWMS_CustomerRequest() { customers = groupedInfos }); if (response.IsSuccess) { if (response.Result.Count() > 0) { //ApplicationConfigHelper.RefreshGetProductStorerList(StorerID.ToString()); return(new { result = "导入成功!", IsSuccess = true }.ToJsonString()); } else { return(new { result = "<h3>导入失败!</h3><br/>excel内容有误!", IsSuccess = false }.ToString()); } } return(new { result = "<h3>导入失败!</h3><br/>excel内容有误!", IsSuccess = false }.ToString()); } return(new { result = "文件内容为空", IsSuccess = false }.ToString()); } return(new { result = "请选择文件", IsSuccess = false }.ToString()); }