/// <summary> /// IDataReader to entity list. /// </summary> public void ToEntityTest() { var filePath = "..\\..\\TestUseFile\\TestExport.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); string message; var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message); IDataReader dr = tableTwo.CreateDataReader(); List <PersonGattScheduleInfoDto> tableToList = DataTypeConvertHelper.ToList <PersonGattScheduleInfoDto>(dr); tableTwo.Rows.Count.IsEqualTo(tableToList.Count); }
public JsonResult Upload(string data) { var examFile = JsonHelper.Deserialize <ExamFiles>(data); if (examFile == null) { return(Json(ErrorModel.InputError)); } var result = NpoiHelper.AnalysisExamQuestion(examFile); return(Json(result)); }
public void ExcelToDataTableTest() { var filePath = "..\\..\\TestUseFile\\ExcelToDataTable.xls"; var excelHelper = new NpoiHelper(filePath); string message; var table = excelHelper.ExcelToDataTable(null, true, out message); table.Rows.Count.IsEqualTo(166); filePath = "..\\..\\TestUseFile\\TestExport.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message); tableTwo.Rows.Count.IsEqualTo(162); }
public ActionResult RenderToExcel(PubPage fg) { string sql = "select Telephone,Pwd from SystemUser"; var dt = _iSysUserServices.ToDataTable(sql, null); string[] Telephone = { "手机号", "密码" }; for (int i = 0; i < dt.Columns.Count; i++) { dt.Columns[i].ColumnName = Telephone[i]; } var ms = NpoiHelper.RenderToExcel(dt); var xlsx = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var xls = "application/vnd.ms-excel"; return(File(ms, xls, "用户信息" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls")); }
/// <summary> /// list to datatable fast /// </summary> public void ToDataTableTest() { var filePath = "..\\..\\TestUseFile\\TestExport.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); string message; var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message); List <PersonGattScheduleInfoDto> personGantScheduleInfoDtosList = DataTypeConvertHelper.ToList <PersonGattScheduleInfoDto>(tableTwo); var storgeTable = DataTypeConvertHelper.ToDataTable(personGantScheduleInfoDtosList); tableTwo.Rows.Count.IsEqualTo(storgeTable.Rows.Count); var i = 5; var j = 4; tableTwo.Rows[i][j].IsEqualTo(storgeTable.Rows[i][j]); }
private void GreatExcel() { string path = txt_filepath.Text.Trim(); string exportpath = txt_exportpath.Text.Trim(); string exportphoto = txt_exportphoto.Text.Trim(); if (path == "") { MessageBox.Show("文件路径不能为空", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //判断是否存在目录 if (!Directory.Exists(path)) { MessageBox.Show("文件路径不存在", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (filepath == "") { MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //判断是否存在目录 TableHelper tableHelper = new TableHelper(); DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); //dt = tableHelper.ReplaceData(dt, "sfzz"); //dt = tableHelper.ReplaceData(dt, "sfxr"); FileHelper fileHelper = new FileHelper(); List <DirectoryInfo> folders = fileHelper.GetAllFoldersInPath(path); List <FileInfo> files = fileHelper.GetAllFilesInPaths(folders); for (int i = 0; i < dt.Rows.Count; i++) { string name = dt.Rows[i]["name"].ToString(); string newname = dt.Rows[i]["newname"].ToString(); if (name.Contains(" ")) { name = name.Replace(" ", ""); dt.Rows[i]["name"] = name; } string photo = fileHelper.IsExistFile(name, files, "default.jpg", newname, exportphoto); dt.Rows[i]["photo"] = photo; } NpoiHelper.DataTableToExcel(dt, "mysheet", true, exportpath); MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
/// <summary> /// 获取NPOI工具类实例 /// </summary> /// <param name="isCompatible">是否生成兼容模式EXCEL(XLS),默认为兼容模式</param> /// <returns></returns> public static NpoiHelper GetInstance(bool isCompatible = true) { IsCompatible = isCompatible; if (_obj == null) { _obj = new NpoiHelper(); } if (IsCompatible) { _obj.Extend = @".xls"; } else { _obj.Extend = @".xlsx"; } return(_obj); }
private void tsmiExportGuest_Click(object sender, EventArgs e) { var sfd = new SaveFileDialog { Filter = "(Excel文件)|*.xls;", FileName = "E&E宾客名单" }; if (sfd.ShowDialog() == DialogResult.OK) { // ID 姓名 相片 性别 宾客类型 标签 随行人员 桌号 礼金 List <string> headers = new List <string> { "Id", "姓名", "相片", "性别", "宾客类型", "标签", "随行人员", "桌号", "礼金" }; List <string> arries = new List <string> { "Id", "Name", "Image", "Gender", "GuestTypeStr", "Labels", "Entourage", "TableNo", "CashGift" }; var data = GlobalConfigs.Guests; NpoiHelper.ToExcel(data, sfd.FileName, arries, headers); MessageBox.Show("导出完成"); } }
/// <summary> /// Npoi的测试 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnNpoi_Click(object sender, EventArgs e) { try { this.richTextBox3.Text += LineBreak + " 开始....."; var file_name = string.Format(@"{0}\\{1}", CACHE_FILE_PATH, "Demo.xls"); NpoiHelper.CreateExcel(file_name, "Demo"); this.richTextBox3.Text += LineBreak + string.Format("创建 {0} 成功!", file_name); } catch (Exception exc) { this.richTextBox3.Text += LineBreak + exc.Message; } finally { this.richTextBox3.Text += LineBreak + " 结束....."; } }
public JsonResult Export() { try { //导出所有数据 var All_ListData = UserEntites.UserInfo.ToList(); //将list 转化为datatable类型 var Dt = DatabaseOpreas._.ListToDataTable(All_ListData); NpoiHelper.Export(Dt, "用户信息", Server.MapPath("~/Content/Export.xls"));//这里的路径是需要写入你需要保存的文件格式的,不需要创建自动检测创建 return(Json(new { code = 1, msg = "/Content/Export.xls" })); } catch (Exception ex) { return(Json(new { code = 0, msg = ex.Message })); } }
private DataTable ExcelToTable(string filePath) { DataTable dt = null; if (string.IsNullOrEmpty(filePath)) { return(dt); } if (filePath.ToLower().EndsWith("xls")) { dt = NpoiHelper.GetInstance().ImportFromExcel(filePath, "0", 0); } else if (filePath.ToLower().EndsWith("xlsx")) { dt = NpoiHelper.GetInstance(false).ImportFromExcel(filePath, "0", 0); } return(dt); }
/// <summary> /// Special Schedule Import /// </summary> /// <param name="strPath"></param> /// <param name="adjustmentType"></param> private void AdjustmentTableImport(string strPath, string adjustmentType) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { string printDate = row["Exclude STK of below TN print date"].ToString(); string tnNo = row["Exclude below TN number"].ToString(); string tnProperty = row["TN Property"].ToString(); string serial = row["Serial Number"].ToString(); if (!printDate.IsNullString() || !tnNo.IsNullString() || !tnProperty.IsNullString()) { CBasAdjustmentTable item = new CBasAdjustmentTable(); Dashboard.Authentication.Authentication.UpdateEntity <CBasAdjustmentTable>(item); item.BasAdjustmentTableId = Guid.NewGuid().ToString(); item.AdjustmentType = adjustmentType; item.UserCode = Authentication.Authentication.CurrentUser.SysUserId; if (!printDate.IsNullString()) { DataValidator <DateTime>(row, "Exclude STK of below TN print date", index); item.PrintDate = Convert.ToDateTime(printDate); } item.TnNo = tnNo; item.TnProperty = tnProperty; item.Serial = serial;//排序字段 DataContext.CBasAdjustmentTable.Add(item); } index++; } } } catch (Exception ex) { throw ex; } }
//从excel中导入人员 private void ExcuteCmdInputPersons() { string xpath; List <viewPerson> viewPeople = new List <viewPerson>(); OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls"; if (openFileDialog.ShowDialog() == true) { xpath = openFileDialog.FileName; viewPeople = NpoiHelper.ReadToList <viewPerson>(xpath, 0); } if (viewPeople.Count > 0) { using (var db = new DataModel()) { foreach (var item in viewPeople) { if (db.Persons.Where(p => p.PersonName == item.Name && p.Department.DepartmentName == item.Department).Count() <= 0) { Department department = db.Departments.Where(d => d.DepartmentName == item.Department).First(); if (department != null) { Person person = new Person() { DepId = department.DepartmentId, PersonName = item.Name }; db.Persons.Add(person); } } } db.SaveChanges(); } int tempid = 0; if (QueryDepartment != null) { tempid = QueryDepartment.DepartmentId; } Inital(); QueryDepartment = Departments.Where(d => d.DepartmentId == tempid).FirstOrDefault(); ExcuteCbChangeCommand(); } }
private void btnLoad_Click(object sender, EventArgs e) { string rootpath = Path.Combine(System.Environment.CurrentDirectory, "read"); var files = Directory.GetFiles(rootpath, "*.xls"); float sumOfFloder = 0;//当前文件夹下所有充电 foreach (var item in files) { FileInfo fi = new FileInfo(item); var dt = NpoiHelper.ImportExcelFile(fi.FullName);//通过NPOI读取excel //dgv.DataSource = dt;//测试时候, 看下内容是否正确 //break; var sum = GetSum(dt, "充电量");// dt.Compute("sum(充电量)", "");//由于读取时候全部作为字符串处理,所以这个方法不正确的 sumOfFloder += sum; txtResult.AppendText(fi.Name + "\t" + sum.ToString() + Environment.NewLine); } txtResult.AppendText("充电量合计:" + "\t" + sumOfFloder.ToString() + Environment.NewLine); }
public ActionResult ImportPersonData() { string UnitID = RequestHelper.GetStrByName("UnitID"), RowID = RequestHelper.GetStrByName("RowID"); if (string.IsNullOrEmpty(UnitID) || string.IsNullOrEmpty(RowID)) { return(operateContext.RedirectAjax(1, "参数异常~", "", "")); } var files = Request.Files; if (files == null || files.Count <= 0) { return(null); } string file_name = "工资模板" + operateContext.Usr.login_name; string pathServer = "~/UpFile/WageManager"; string path = Server.MapPath(pathServer); if (!Directory.Exists(path.ToString())) { Directory.CreateDirectory(path.ToString());//文件夹不存在则创建 } HttpPostedFileBase file = Request.Files["filePerson"]; //1.上传文档 string sreUrl = Server.MapPath(pathServer + "/" + file_name + "." + file.FileName.Split('.')[1]); file.SaveAs(sreUrl);//上传文件 System.Data.DataTable dt = NpoiHelper.ImportExcelToDataTable(sreUrl); if (null == dt || dt.Rows.Count <= 0) { return(operateContext.RedirectAjax(1, "导入数据为空~", "", "")); } bool mark = operateContext.bllSession.WGJG02_Template.ImportPersonData(dt, UnitID, RowID); if (mark) { return(operateContext.RedirectAjax(0, "导入成功~", "", "")); } return(operateContext.RedirectAjax(1, "导入失败~", "", "")); }
public Form1() { InitializeComponent(); list.Add(new TestData { Name = "zhangshan", Age = 40, Birthday = DateTime.Now }); list.Add(new TestData { Name = "lisi", Age = 28, Birthday = DateTime.Now }); list.Add(new TestData { Name = "wangwu", Age = 36, Birthday = DateTime.Now }); list1.Add(new ComplexData { Name = "zhangshan", Age = 40, Birthday = DateTime.Now, Detail = new Detail { Tel = "02885394654", HomeAddr = new Address { CommonAddr = "黄河村", PostNo = "610048" } } }); DataTable dt = new DataTable("TestTable"); dt.Columns.Add("Name"); dt.Columns.Add("Age"); dt.Columns.Add("Birthday"); DataRow dr = dt.NewRow(); dr[0] = "zhangshan"; dr[1] = 50; dr[2] = DateTime.Now; dt.Rows.Add(dr); NpoiHelper.GetInstance().ExportToExcel(dt); //NpoiHelper.GetInstance().ExportToExcel<TestData>(list); //NpoiHelper.GetInstance().ExportToExcel<ComplexData>(list1); //string file = AppDomain.CurrentDomain.BaseDirectory + "excel.xls"; //NpoiHelper.GetInstance().ImportFromExcel(file,"0",0); }
/// <summary> /// 开始生成 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { if (this.filenameDing == null || this.filenameHe == null) { MessageBox.Show("请先选择文件!", "错误警告"); return; } DataTable dtDing = NpoiHelper.Import(filenameDing, getFileSuffiex(filenameDing)); DataTable dtHe = NpoiHelper.Import(filenameHe, getFileSuffiex(filenameHe)); for (int i = 3; i < dtDing.Rows.Count; i++) { DataRow dr = dtDing.Rows[i]; this.RowHandleDingToHe(dtHe, dr); for (int j = 0; j < dr.ItemArray.Length; j++) { Console.Out.Write(j + "=>" + dr.ItemArray[j].ToString() + ","); } Console.Out.WriteLine(); } //保存到桌面 saveFile(dtHe); }
/// <summary> /// 导出遥测跳变历史记录 /// </summary> /// <param name="httpRequest"></param> /// <param name="httpResponse"></param> private void ExpYctbls(HttpRequest httpRequest, HttpResponse httpResponse) { try { string tmp = httpRequest["dateBegin"] ?? ""; DateTime dateBegin = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")).AddDays(-1); DateTime dateEnd = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")).AddDays(1); if (!string.IsNullOrEmpty(tmp)) { dateBegin = DateTime.Parse(tmp); } tmp = httpRequest["dateEnd"] ?? ""; if (!string.IsNullOrEmpty(tmp)) { dateEnd = DateTime.Parse(tmp).AddDays(1); } DataTable dt = _wrBll.Query(Constant.YclxYctb, dateBegin, dateEnd); string[] title = new string[] { "充电站", "桩类型", "桩型号", "装编号", "数据项", "阈值最小值", "阈值最大值", "告警值", "出错原因", "是否处理", "灭警方式", "灭警时间", "灭警人" }; dt.Columns.Remove("yxstates"); dt.Columns.Remove("yxwarn"); dt.Columns.Remove("eff_min"); dt.Columns.Remove("eff_max"); //dt.Columns.Remove("mvalue"); dt.Columns.Remove("yxeff"); foreach (DataRow dr in dt.Rows) { if (dr["processflag"].ToString() == "0") { dr["isproc"] = "未处理"; dr["procm"] = "未处理"; } else if (dr["processflag"].ToString() == "1") { dr["isproc"] = "已处理"; dr["procm"] = "自动灭警"; } else { dr["isproc"] = "已处理"; dr["procm"] = "手动灭警"; } } dt.Columns.Remove("processflag"); NpoiHelper nh = new NpoiHelper(title, dt); Stream ms = nh.ToExcel(); if (null == ms) { return; } byte[] exp = new byte[ms.Length]; ms.Read(exp, 0, (int)ms.Length); httpResponse.ContentType = "application/x-zip-compressed"; httpResponse.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("遥测跳变历史.xls", System.Text.Encoding.UTF8)); httpResponse.BinaryWrite(exp); } catch (Exception ex) { Log.Error(ex); httpResponse.Write(ErrorJson); } }
/// <summary> /// 导入确认 /// </summary> /// <param name="matchid"></param> /// <param name="fid"></param> /// <returns></returns> public ActionResult confirm(string matchid, string fid) { try { ViewBag.error = "0"; ViewBag.matchid = matchid; ViewBag.fid = fid; List <tblmatchentity> lstMatchusers = new List <tblmatchentity>(); DataTable data = NpoiHelper.XlSToDataTable(System.IO.Path.Combine(Server.MapPath("~/upload/file"), fid), "TTBM", 0); if (data == null || data.Rows.Count < 1) { return(View(lstMatchusers)); } GroupBll bll = new GroupBll(); tblmatch match = bll.GetMatchById(matchid); ViewBag.matchname = HttpUtility.HtmlEncode(match.Match_name); StringBuilder sbtError = new StringBuilder(); int sn = 0; string lineid = ""; string teamno = ""; string teamname = ""; string company = ""; int year = 0; foreach (DataRow row in data.Rows) { sbtError.Clear(); year = 0; if (string.IsNullOrEmpty(row["队员姓名"].ToString().Trim())) { continue; } //记录序号,以标记团队 if (!string.IsNullOrEmpty(row["序号"].ToString().Trim())) { sn = int.Parse(row["序号"].ToString().Trim()); } if (!string.IsNullOrEmpty(row["路线名称"].ToString().Trim())) { lineid = row["路线名称"].ToString().Trim(); var d = bll.GetLineByName(lineid); if (d == null) { sbtError.AppendFormat("[路线名称:{0}]不存在;", lineid); } } if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim())) { teamno = row["队列号"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim())) { teamname = row["队名(6个字符以内)"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["单位名称"].ToString().Trim())) { company = row["单位名称"].ToString().Trim(); } if (string.IsNullOrEmpty(row["性别"].ToString().Trim())) { sbtError.Append("[性别]不能为空;"); } if (string.IsNullOrEmpty(row["身份证/护照"].ToString().Trim())) { sbtError.Append("[身份证/护照]不能为空;"); } else { if (!System.Text.RegularExpressions.Regex.IsMatch(row["身份证/护照"].ToString().Trim(), @"^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$")) { sbtError.Append("[身份证/护照]格式错误;"); } } if (!System.Text.RegularExpressions.Regex.IsMatch(row["手机号码"].ToString().Trim(), @"^[1]+[0-9]+\d{9}")) { sbtError.AppendFormat("[手机号码:{0}]格式错误;", row["手机号码"]); } if (!int.TryParse(row["年龄"].ToString().Trim(), out year)) { sbtError.AppendFormat("[年龄:{0}]是否输入及正确;", row["年龄"]); } if (sbtError.Length > 0) { ViewBag.error = "-1"; } tblmatchentity muser = new tblmatchentity(); muser.Pnov = sn.ToString(); muser.Teamname = teamname; muser.Cardno = row["身份证/护照"].ToString().Trim(); muser.Cardtype = "1"; muser.Createtime = DateTime.Now;//.ToString("yyyy-MM-dd"); //muser.Leader = row["队员编号"].ToString().Trim() == "队长" ? 1 : 0; muser.Match_Id = ""; muser.Matchuserid = Guid.NewGuid().ToString(); muser.Mobile = row["手机号码"].ToString().Trim(); muser.Nickname = row["队员姓名"].ToString().Trim(); muser.Lineno = lineid; muser.LeaderM = row["队员编号"].ToString().Trim() == "队长" ? "是" : ""; muser.Sexy = row["性别"].ToString().Trim() == "男" ? 1 : 0; muser.Age = year; muser.Mono = row["是否健康"].ToString().Trim(); muser.Content = HttpUtility.HtmlEncode(sbtError.ToString()); lstMatchusers.Add(muser); } return(View(lstMatchusers)); } catch (Exception ex) { ILog log = LogManager.GetLogger(this.GetType()); log.Error(ex); ViewBag.error = "-2"; return(View()); } }
public ActionResult beginimp(string matchid, string fid) { try { List <tblmatchentity> lstMatchusers = new List <tblmatchentity>(); DataTable data = NpoiHelper.XlSToDataTable(System.IO.Path.Combine(Server.MapPath("~/upload/file"), fid), "TTBM", 0); GroupBll bll = new GroupBll(); tblmatch match = bll.GetMatchById(matchid); int sn = 0; string lineid = ""; string teamno = ""; string teamname = ""; string company = ""; int year = 0; int dm = 0; foreach (DataRow row in data.Rows) { year = 0; if (string.IsNullOrEmpty(row["队员姓名"].ToString().Trim())) { continue; } //记录序号,以标记团队 if (!string.IsNullOrEmpty(row["序号"].ToString().Trim())) { sn = int.Parse(row["序号"].ToString().Trim()); } if (!string.IsNullOrEmpty(row["路线名称"].ToString().Trim())) { var d = bll.GetLineByName(row["路线名称"].ToString().Trim()); lineid = d.Lineid; } if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim())) { teamno = row["队列号"].ToString().Trim(); int.TryParse(teamno, out dm); } if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim())) { teamname = row["队名(6个字符以内)"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["单位名称"].ToString().Trim())) { company = row["单位名称"].ToString().Trim(); } tblmatchentity muser = new tblmatchentity(); muser.Pnov = sn.ToString(); muser.Teamname = teamname; muser.Teamno = dm; muser.Cardno = row["身份证/护照"].ToString().Trim(); muser.Cardtype = "1"; muser.Leader = row["队员编号"].ToString().Trim() == "队长" ? 1 : 0; muser.Match_Id = matchid; muser.Mobile = row["手机号码"].ToString().Trim(); muser.Nickname = row["队员姓名"].ToString().Trim(); muser.Lineno = lineid; muser.Sexy = row["性别"].ToString().Trim() == "男" ? 1 : 0; muser.Passwd = company; int.TryParse(row["年龄"].ToString().Trim(), out year); muser.Age = year; muser.Mono = row["是否健康"].ToString().Trim(); lstMatchusers.Add(muser); } int count = 0; TeamRegBll tbll = new TeamRegBll(); int res = tbll.ImpTeams(lstMatchusers, ref count); return(RedirectToAction("importsuccess", new { m = HttpUtility.UrlEncode(match.Match_name), s = HttpUtility.UrlEncode(string.Format("已成功导入{0}个队伍,{1}个队员信息", count, res)) })); } catch (Exception ex) { ILog log = LogManager.GetLogger(this.GetType()); log.Error(ex); return(new EmptyResult()); } }
/// <summary> /// 批量更新已发货 /// </summary> /// <param name="path"></param> /// <returns></returns> public ActionResult BatchDis(string path) { path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + path.Replace("..", ""); var fileArr = path.Split(new[] { "/" }, StringSplitOptions.RemoveEmptyEntries); var fileName = fileArr[fileArr.Length - 1]; IEnumerable <CsOrderView.CsOrderBatchDis> orders; try { orders = NpoiHelper.ReadExcel(path.Replace("/" + fileName, ""), fileName) .ToList <CsOrderView.CsOrderBatchDis>() .Where(x => !x.订单编号.IsNullOrEmpty() && !x.运单号.IsNullOrEmpty()); } catch (Exception ex) { LogHelper.Log(ex.ToJson(), "execl文件读取失败"); return(Json(new ResModel { ResStatus = ResStatue.No, Data = "execl 文件读取失败,请确认文件数据正确, 请检查日志描述" })); } if (!orders.Any()) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "当前Excel中没有数据,请确认文件是否包含规定完整的数据" })); } if (orders.Any(x => x.订单编号.IsNullOrEmpty() || x.运单号.IsNullOrEmpty())) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "当前Excel中存在空数据,请确认数据的完整性, 数据一旦上传将无法撤回" })); } var distinct = orders.Distinct(x => x.订单编号); if (distinct.Count() != orders.Count()) { return(Json(new { code = ResStatue.Warn.GetHashCode(), data = "存在重复的订单编号,请依据给出的数据检查, 数据一旦上传将无法撤回", orderIds = orders.Except(distinct).Select(x => x.订单编号) })); } var data = _csOrderBll.GetModelList($" AND OrderNumber IN ('{string.Join("','", orders.Select(x => x.订单编号))}')"); if (data.Count != orders.Count()) { return(Json(new { code = ResStatue.Warn.GetHashCode(), data = "存在有误的订单编号,请依据给出的数据检查, 数据一旦上传将无法撤回", orderIds = orders.Select(x => x.订单编号.Trim()).Except(data.Select(x => x.OrderNumber)) })); } var count = 0; foreach (var order in orders) { var sh = new SqlHelper <CsOrder>(); sh.AddUpdate("OrderState", OrderState.已发货.GetHashCode()); sh.AddUpdate("OrderDelivery", order.运单号); sh.AddWhere($" AND OrderNumber = '{order.订单编号}'"); count += sh.Update(); } return(Json(new ResModel { ResStatus = ResStatue.Yes, Data = $"更新成功, 共计更新{count}条数据." })); }
/// <summary> /// 数据源导出总运单和承运单清单 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void gv_HAWB_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Eidt") { Guid id = (Guid)Session["UserID"]; ModulePrivilege Authority = _sysUserManagementService.GetPrivilegeByUserid(id); bool aPrivilege = (bool)Authority[Privilege.修改.ToString()]; bool aPrivilege2 = (bool)Authority[Privilege.导出.ToString()]; bool aPrivilege1 = (bool)Authority[Privilege.解锁.ToString()]; Response.Redirect("MawbDetails.aspx?Privilege=" + aPrivilege + "&BarCode=" + e.CommandArgument + "&aPrivilege1=" + aPrivilege1 + "&aPrivilege2=" + aPrivilege2 + ""); } if (e.CommandName == "Derive") { string barcode = e.CommandArgument.ToString(); MAWB mawb = _mawbService.FindMAWBByBarcode(barcode); IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString()); if (hawbs.Count != 0) { var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.运单号); NpoiHelper.ExportMAWB(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barcode + "总运单清单"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else { ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('该总运单下没有运单,不能导出!')", true); } } else if (e.CommandName == "DeriveAccept") { string barcode = e.CommandArgument.ToString(); MAWB mawb = _mawbService.FindMAWBByBarcode(barcode); IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString()); if (hawbs.Count != 0) { var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.承运单号); NpoiHelper.ExportMAWB(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barcode + "承运清单"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else { ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('该总运单下没有运单,不能导出!')", true); } } }
/// <summary> /// Normal Schedule V Cargo Type Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleVCtImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); //替换为从表里获取 var warehouse = DataContext.CBasPdcSequence.Select(p => p.Pdc); //string[] warehouse = new string[] { "BJ", "SH", "GZ", "YZ", "CD" }; Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { //验证指定字段不能为空 NormalScheduleVCtImportCheckData(row, index); string dealerCode = row["Dealer_Code"].ToString(); if (dicDealerCode.ContainsKey(dealerCode)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" <=Duplicate>", dealerCode)); } dicDealerCode.Add(dealerCode, dealerCode); CBasNormalScheduleVCt item = new CBasNormalScheduleVCt(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCt>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleVCtId = Guid.NewGuid().ToString(); item.DealerCode = dealerCode; item.FacingPdc = row["Facing_PDC"].ToString(); item.DealerType = row["Dealer_Type"].ToString(); item.DealerName = row["Dealer_Name"].ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); if (!DataIsNullValidator(row, "1st_V_Cutoff_Day") && !DataIsNullValidator(row, "1st_V_Cutoff_Time") && !DataIsNullValidator(row, "1st_V_Pickup_Day") && !DataIsNullValidator(row, "1st_V_Pickup_Time") && !DataIsNullValidator(row, "1st_V_Trans_Mode") && !DataIsNullValidator(row, "1st_V_Leadtime") && !DataIsNullValidator(row, "1st_V_Leadtime_AM_PM") && !DataIsNullValidator(row, "1st_V_Leadtime_Start") && !DataIsNullValidator(row, "1st_V_Leadtime_End")) { DataValidator <DateTime>(row, "1st_V_Cutoff_Time", index); DataValidator <DateTime>(row, "1st_V_Pickup_Time", index); DataValidator <int>(row, "1st_V_Leadtime", index); DataValidator <DateTime>(row, "1st_V_Leadtime_Start", index); DataValidator <DateTime>(row, "1st_V_Leadtime_End", index); item.FirstVCutoffDay = row["1st_V_Cutoff_Day"].ToString(); item.FirstVCutoffTime = Convert.ToDateTime(row["1st_V_Cutoff_Time"].ToString()); item.FirstVPickupDay = row["1st_V_Pickup_Day"].ToString(); item.FirstVPickupTime = Convert.ToDateTime(row["1st_V_Pickup_Time"].ToString()); item.FirstVTransMode = row["1st_V_Trans_Mode"].ToString(); item.FirstVLeadtime = Convert.ToInt32(row["1st_V_Leadtime"].ToString()); item.FirstVLeadtimeAmPm = row["1st_V_Leadtime_AM_PM"].ToString(); item.FirstVLeadtimeStart = Convert.ToDateTime(row["1st_V_Leadtime_Start"].ToString()); item.FirstVLeadtimeEnd = Convert.ToDateTime(row["1st_V_Leadtime_End"].ToString()); } item.SecondVCutoffDay = row["2nd_V_Cutoff_Day"].ToString(); item.SecondVCutoffTime = Convert.ToDateTime(row["2nd_V_Cutoff_Time"].ToString()); item.SecondVPickupDay = row["2nd_V_Pickup_Day"].ToString(); item.SecondVPickupTime = Convert.ToDateTime(row["2nd_V_Pickup_Time"].ToString()); DataContext.CBasNormalScheduleVCt.Add(item); foreach (var strPdc in warehouse) { if (!dt.Columns.Contains(strPdc + "_2nd_V_Normal_Trans_Mode")) { continue; } DataValidator <int>(row, strPdc + "_2nd_V_Normal_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_Normal_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_Normal_Leadtime_End", index); DataValidator <int>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime_End", index); CBasNormalScheduleVCtDet itemNormal = new CBasNormalScheduleVCtDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCtDet>(itemNormal); itemNormal.BasNormalScheduleId = basNormalScheduleId; itemNormal.BasNormalScheduleVCtId = item.BasNormalScheduleVCtId; itemNormal.BasNormalScheduleVCtDetId = Guid.NewGuid().ToString(); itemNormal.Pdc = strPdc; itemNormal.CargoType = "Normal"; itemNormal.SecondVTransMode = row[strPdc + "_2nd_V_Normal_Trans_Mode"].ToString(); itemNormal.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_Normal_Leadtime"].ToString()); itemNormal.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_Normal_Leadtime_AM_PM"].ToString(); itemNormal.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_Normal_Leadtime_Start"].ToString()); itemNormal.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_Normal_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVCtDet.Add(itemNormal); CBasNormalScheduleVCtDet itemDgBulky = new CBasNormalScheduleVCtDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCtDet>(itemDgBulky); itemDgBulky.BasNormalScheduleId = basNormalScheduleId; itemDgBulky.BasNormalScheduleVCtId = item.BasNormalScheduleVCtId; itemDgBulky.BasNormalScheduleVCtDetId = Guid.NewGuid().ToString(); itemDgBulky.Pdc = strPdc; itemDgBulky.CargoType = "DG/Bulky"; itemDgBulky.SecondVTransMode = row[strPdc + "_2nd_V_DG_Bulky_Trans_Mode"].ToString(); itemDgBulky.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_DG_Bulky_Leadtime"].ToString()); itemDgBulky.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_DG_Bulky_Leadtime_AM_PM"].ToString(); itemDgBulky.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_DG_Bulky_Leadtime_Start"].ToString()); itemDgBulky.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_DG_Bulky_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVCtDet.Add(itemDgBulky); } index++; } } } catch (Exception ex) { throw ex; } }
public void TestEntireReport() { string Setnb = ""; // Set up the database with test publications (and don't forget to add the // publication types!) DB = new Database("Publication Harvester Unit Test"); Harvester harvester = new Harvester(DB); harvester.CreateTables(); PublicationTypes PubTypes = new PublicationTypes( AppDomain.CurrentDomain.BaseDirectory + "\\Unit Tests\\TestPublicationTypes", "PublicationTypes.csv" ); PubTypes.WriteToDB(DB); reports = new Reports(DB, AppDomain.CurrentDomain.BaseDirectory + "\\Unit Tests\\TestReports\\pubmed_jifs.xls"); Assert.IsTrue(reports.Weights.Count == 10); TestHarvester.GetPublicationsFromInput1XLS_Using_MockNCBI(false, new string[] { "eng" }, 22); people = new People(DB); // Write the report StreamWriter writer = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv"); Reports.ReportStatus StatusCallback = delegate(int number, int total, Person person, bool ProgressBarOnly) { // }; Reports.ReportMessage MessageCallback = delegate(string Message) { // }; reports.PeopleReport(null, writer, StatusCallback, MessageCallback); writer.Close(); // Read the report into an array var lines = File.ReadAllLines($"{AppDomain.CurrentDomain.BaseDirectory}\\TestEntireReport.csv") .Select(line => line.Split(new char[] { ',' })); var header = lines.First(); var data = lines.Skip(1).ToList(); Assert.AreEqual(85, data.Count); string ReportData(string setnb, string year, string column) { var index = Array.IndexOf(header, column); var row = data.Where(line => line[0] == setnb && line[1] == year); Assert.AreEqual(1, row.Count(), $"Unable to find setnb={setnb} year={year} in TestEntireReport.csv"); return(row.First()[index]); } var q = ReportData("A5401532", "1997", "wghtd_pubcount_pos1"); // Read the report file that was generated by hand (TestEntireReport_Data.xls) // and check each value against the report that was generated by Reports() string[] Columns = { "setnb", "year", "pubcount", "wghtd_pubcount", "pubcount_pos1", "wghtd_pubcount_pos1", "pubcount_posN", "wghtd_pubcount_posN", "pubcount_posM", "wghtd_pubcount_posM", "pubcount_posNTL", "wghtd_pubcount_posNTL", "pubcount_pos2", "wghtd_pubcount_pos2", "123pubcount", "wghtd_123pubcount", "123pubcount_pos1", "wghtd_123pubcount_pos1", "123pubcount_posN", "wghtd_123pubcount_posN", "123pubcount_posM", "wghtd_123pubcount_posM", "123pubcount_posNTL", "wghtd_123pubcount_posNTL", "123pubcount_pos2", "wghtd_123pubcount_pos2", "1pubcount", "wghtd_1pubcount", "1pubcount_pos1", "wghtd_1pubcount_pos1", "1pubcount_posN", "wghtd_1pubcount_posN", "1pubcount_posM", "wghtd_1pubcount_posM", "1pubcount_posNTL", "wghtd_1pubcount_posNTL", "1pubcount_pos2", "wghtd_1pubcount_pos2", "2pubcount", "wghtd_2pubcount", "2pubcount_pos1", "wghtd_2pubcount_pos1", "2pubcount_posN", "wghtd_2pubcount_posN", "2pubcount_posM", "wghtd_2pubcount_posM", "2pubcount_posNTL", "wghtd_2pubcount_posNTL", "2pubcount_pos2", "wghtd_2pubcount_pos2", "3pubcount", "wghtd_3pubcount", "3pubcount_pos1", "wghtd_3pubcount_pos1", "3pubcount_posN", //"wghtd_3pubcount_posN", "3pubcount_posM", "wghtd_3pubcount_posM", "3pubcount_posNTL", "wghtd_3pubcount_posNTL", "3pubcount_pos2", "wghtd_3pubcount_pos2", "4pubcount", "wghtd_4pubcount", "4pubcount_pos1", "wghtd_4pubcount_pos1", "4pubcount_posN", "wghtd_4pubcount_posN", "4pubcount_posM", "wghtd_4pubcount_posM", "4pubcount_posNTL", "wghtd_4pubcount_posNTL", "4pubcount_pos2", "wghtd_4pubcount_pos2" }; DataTable HandGeneratedData = NpoiHelper.ReadExcelFileToDataTable(AppDomain.CurrentDomain.BaseDirectory + "\\Unit Tests\\TestReports", "TestEntireReport_Data.xls"); Assert.AreEqual(HandGeneratedData.Rows.Count, 85); var valuesChecked = 0; for (int RowNum = 0; RowNum < HandGeneratedData.Rows.Count; RowNum++) { // Find the rows in the hand-generated data and the report DataRow HandGeneratedRow = HandGeneratedData.Rows[RowNum]; Setnb = HandGeneratedRow[0].ToString(); int Year = Convert.ToInt32(HandGeneratedRow[1]); for (int i = 2; i < Columns.Length; i++) { valuesChecked++; String columnName = Columns[i]; var actualValue = ReportData(Setnb, Year.ToString(), columnName); string expectedValue = HandGeneratedRow[columnName].ToString(); Assert.AreEqual(expectedValue, actualValue, Setnb + "/" + Year.ToString() + "/" + columnName + " -- hand generated has " + expectedValue + ", report has" + actualValue); } } Assert.AreEqual((HandGeneratedData.Rows.Count) * (Columns.Length - 2), valuesChecked); // Use BackupReportAndGetSetnbs to back up the report -- check that it // returns the correct list of Setnbs and removes the last one from // the file. The last Setnb should still be in Setnb. ArrayList Setnbs = Reports.BackupReportAndGetSetnbs(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv"); // Read the backup file that was created, make sure that the last setnb in the // file isn't contained and the others are StreamReader reader = new StreamReader(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv.bak"); string Line = reader.ReadLine(); //skip the header row while ((Line = reader.ReadLine()) != null) // Find the last setnb in the original file { Setnb = Line.Substring(0, 8); } string RemovedSetnb = Setnb; Assert.IsFalse(Setnbs.Contains(RemovedSetnb)); reader.Close(); // Verify that the new file contains only the other setnbs Assert.IsTrue(Setnbs.Count == 3); Assert.IsFalse(Setnbs.Contains(RemovedSetnb)); reader = new StreamReader(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv"); Line = reader.ReadLine(); //skip the header row while ((Line = reader.ReadLine()) != null) { Setnb = Line.Substring(0, 8); Assert.IsTrue(Setnbs.Contains(Setnb)); Assert.IsFalse(Setnb == RemovedSetnb); } reader.Close(); // Delete the temporary files File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv"); File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv.bak"); }
private void LoadFile(string file) { var dt = NpoiHelper.ImportExcelFile(file); dgv.DataSource = dt; }
/// <summary> /// 上传主函数 /// </summary> public void Upload() { DataTable dt = new DataTable(); dt.Columns.Add("result"); dt.Columns.Add("message"); if (context.Request.Files.Count == 0) { dt.Rows.Add(new object[] { "False", "文件过大或提交的信息有误!" }); } else { string strFileName = Path.GetFileName(context.Request.Files[0].FileName); string strFileNameSelfDef = ""; if (context.Request["fileName"] != null && context.Request["fileName"].ToString() != "") { strFileNameSelfDef = context.Request["fileName"].ToString(); } string strExtension = Path.GetExtension(context.Request.Files[0].FileName).ToLower(); if (strFileNameSelfDef == "") { strFileName = strFileName.Substring(0, strFileName.Length - strExtension.Length) + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + strExtension; } else { strFileName = strFileNameSelfDef + strExtension; } string strFilePath = context.Request["filepath"]; if (string.IsNullOrEmpty(strFilePath)) { dt.Rows.Add(new object[] { "False", "filepath不能为空" }); } else { int fileSize = 1024 * 1024 * 20; //20M HttpPostedFile postedFile = context.Request.Files[0]; if (postedFile.ContentLength > fileSize) { dt.Rows.Add(new object[] { "False", "最大只能上传20M文件" }); } else { strFilePath = "/" + strFilePath + "/" + strFileName; string strLocation = context.Server.MapPath("upload") + strFilePath; context.Request.Files[0].SaveAs(strLocation); if (GetFileType(strFileName) == FileType.Excel) { //读取EXCEL信息 NpoiHelper excel = new NpoiHelper(); m_dsExcelData = excel.ReadExcel(strLocation); } else if (GetFileType(strFileName) == FileType.Pic) { if (context.Request["filepath"] == "HZY_mat") { CreateThumbnail(strLocation, 200, 200); } dt.Rows.Add(new object[] { "True", "/upload" + strFilePath + "" }); dt.Rows.Add(new object[] { "文件大小", postedFile.ContentLength }); } else { dt.Rows.Add(new object[] { "False", "文件类型不认识!" }); } } } } if (context.Request["WSID"] == null) { string strJSON = HZY.COM.Common.JsonHelper.CreateJsonParameters(dt); context.Response.Write(strJSON); } }
public ActionResult ExportCsOrder(CsOrderView.CsOrderWhere para) { var data = GetList(para, false); if (!data.Data.Any()) { return(Json(new ResModel { Data = "没有任何可以导出数据", ResStatus = ResStatue.No })); } var list = new List <CsOrderView.CsOrderExcel>(); var detail = _csOrderDetailBll.GetModelList($" AND OrderId IN ({string.Join(",", data.Data.Select(x => x.OrderId))})"); // 配件/螃蟹/套餐 var parts = _csPartsBll.GetModelList($" AND PartId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.配件.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")}) AND PartType = {PartType.可选配件.GetHashCode()}"); var products = _csProductsBll.GetModelList($" AND ProductId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.螃蟹.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")})"); IEnumerable <CsPackage> packages = null; if (detail.Any(x => x.ChoseType == ChoseType.套餐.GetHashCode())) { packages = _csPackageBll.GetModelList($" AND PackageId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.套餐.GetHashCode()).Select(x => x.ProductId))})"); } foreach (var order in data.Data) { var total = string.Empty; foreach (var d in detail.Where(x => x.OrderId == order.OrderId).OrderBy(x => x.ChoseType)) { if (d.ChoseType == ChoseType.套餐.GetHashCode()) { total += packages?.FirstOrDefault(x => x.PackageId == d.ProductId)?.PackageNumber + "" + d.ProductNumber; continue; } if (d.ChoseType == ChoseType.螃蟹.GetHashCode()) { total += products.FirstOrDefault(x => x.ProductId == d.ProductId)?.ProductNumber + "" + d.ProductNumber; continue; } if (total.IndexOf("P", StringComparison.Ordinal) < 0 && total.IndexOf("-", StringComparison.Ordinal) < 0) { total += "-"; } total += parts.FirstOrDefault(x => x.PartId == d.ProductId)?.PartNumber; } var sendInfo = order.SendAddress.Split('$'); var putInfo = order.OrderAddress.Split('$'); list.Add(new CsOrderView.CsOrderExcel { 用户订单号 = order.OrderNumber, 寄件公司 = "-", 寄联系人 = sendInfo.Length > 0 ? sendInfo[0] : "-", 寄联系电话 = sendInfo.Length > 1 ? sendInfo[1] : "-", 寄件地址 = sendInfo.Length > 2 ? sendInfo[2] : "-", 收件公司 = putInfo.Length > 0 ? putInfo[0] : "-", 联系人 = putInfo.Length > 1 ? putInfo[1] : "-", 联系电话 = putInfo.Length > 2 ? putInfo[2] : "-", 手机号码 = putInfo.Length > 3 ? putInfo[3] : "-", 收件详细地址 = putInfo.Length > 4 ? putInfo[4] : "-", 付款方式 = "寄付月结", 第三方付月结卡号 = "", 寄托物品 = "其他", 寄托物内容 = total.TrimEnd('-').IsNullOrEmpty() ? "" : $"{total.TrimEnd('-')}-T{detail.Where(x => x.OrderId == order.OrderId && (x.ChoseType == ChoseType.螃蟹.GetHashCode() || x.ChoseType == ChoseType.套餐.GetHashCode())).Sum(x => x.ProductNumber)}", 寄托物编号 = "", 寄托物数量 = order.CargoNumber.ToString(), 件数 = order.OrderCopies.ToString(), 实际重量单位KG = "-", 计费重量单位KG = "-", 业务类型 = "大闸蟹专递", 扩展字段1 = detail.Any(x => x.OrderId == order.OrderId && x.ChoseType == ChoseType.套餐.GetHashCode()) ? "企业团购" : "电商代发", 扩展字段2 = order.OrderRemarks }); } var path = $"excel/{DateTime.Now:yyyyMMddHHmmssffff}.xls"; try { NpoiHelper.ExportToExcel(list.ToDataTable(), "D:/" + path); } catch (Exception e) { return(Json(new ResModel { Data = e.Message, ResStatus = ResStatue.No })); } return(Json(new ResModel { Data = "../" + path, ResStatus = ResStatue.Yes })); }
/// <summary> /// 导出运单发票 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_DeriveSince_Click(object sender, EventArgs e) { HAWB hawb = _hawbService.FindHAWBByBarCode(Txt_BarCode.Text.Trim()); var NpoiHelper = new NpoiHelper(hawb, Txt_BarCode.Text); NpoiHelper.ExportInvoice(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", Txt_BarCode.Text+"运单发票"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }
public JsonResult ImpTeams(string matchid) { try { DataTable data = NpoiHelper.XlSToDataTable(@"C:\Work\Vki\MyDoc\MyFile\dx_dev\定向赛团体报名统计表.xlsx", "test", 0); if (data == null || data.Rows.Count < 1) { return(RepReurnError("上传的文件中没有数据")); } StringBuilder sbtError = new StringBuilder(); List <tblusers> lstUsers = new List <tblusers>(); List <tblteams> lstTeams = new List <tblteams>(); List <tblmatchusers> lstMatchusers = new List <tblmatchusers>(); int sn = 0; string lineid = ""; string teamno = ""; string teamname = ""; string company = ""; foreach (DataRow row in data.Rows) { //记录序号,以标记团队 if (!string.IsNullOrEmpty(row["序号"].ToString().Trim())) { sn = int.Parse(row["序号"].ToString().Trim()); } if (!string.IsNullOrEmpty(row["线路号"].ToString().Trim())) { lineid = row["线路号"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim())) { teamno = row["队列号"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim())) { teamname = row["队名(6个字符以内)"].ToString().Trim(); } if (!string.IsNullOrEmpty(row["公司名称"].ToString().Trim())) { company = row["公司名称"].ToString().Trim(); } if (string.IsNullOrEmpty(row["队员编号"].ToString().Trim())) { continue; } if (string.IsNullOrEmpty(row["性别"].ToString().Trim())) { sbtError.Append("[性别]不能为空;"); } if (string.IsNullOrEmpty(row["身份证/护照"].ToString().Trim())) { sbtError.Append("[身份证/护照]不能为空;"); } if (!System.Text.RegularExpressions.Regex.IsMatch(row["手机号"].ToString().Trim(), @"^[1]+[0-9]+\d{9}")) { sbtError.AppendFormat("[手机号:{0}]格式错误;", row["手机号"]); } tblusers usr = new tblusers(); usr.cardtype = "1"; usr.Mobile = row["手机号"].ToString().Trim(); usr.sexy = row["性别"].ToString().Trim() == "男" ? "1" : "0"; usr.Status = 1; usr.userid = Guid.NewGuid().ToString(); lstUsers.Add(usr); tblteams tm = new tblteams(); tm.teamid = Guid.NewGuid().ToString(); tm.Company = ""; tm.Createtime = DateTime.Now; tm.Eventid = 1; tm.Lineid = lineid; tm.match_id = matchid; tm.Status = 1; tm.Teamname = teamname; tm.Teamno = teamno; //tm.Userid lstTeams.Add(tm); } } catch (Exception ex) { ILog log = LogManager.GetLogger(this.GetType()); log.Error(ex); return(RepReurnError("导入过程中出现错误")); } return(RepReurnOK()); }
/// <summary> /// Normal Schedule S Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleSImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); Dictionary <string, string> dicDealerCodePickupDay = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { NormalScheduleSImportCheckData(row, index); string dealerCode = row["Dealer_Code"].ToString(); string definition = row["Definition"].ToString(); if (dicDealerCode.ContainsKey(dealerCode + "_" + definition)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" Definition ""{1}"" <=Duplicate>", dealerCode, definition)); } dicDealerCode.Add(dealerCode + "_" + definition, dealerCode + "_" + definition); string pickupDay = row["Pickup_Day"].ToString(); if (dicDealerCodePickupDay.ContainsKey(dealerCode + "_" + pickupDay)) { throw new Exception(string.Format(@"Dealer Code ""{0}"",Pickup Day ""{1}"" <=Duplicate>", dealerCode, pickupDay)); } dicDealerCodePickupDay.Add(dealerCode + "_" + pickupDay, dealerCode + "_" + pickupDay); CBasNormalScheduleS item = new CBasNormalScheduleS(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleS>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleSId = Guid.NewGuid().ToString(); item.DealerCode = row["Dealer_Code"].ToString(); item.FacingPdc = row["Facing_PDC"].ToString(); item.Definition = row["Definition"].ToString(); item.DealerType = row["Dealer_Type"].ToString(); item.ShortDealerName = row["Short_Dealer_Name"].ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); item.CutoffDay = Convert.ToInt32(row["Cutoff_Day"].ToString()); item.CutoffTime = Convert.ToDateTime(row["Cutoff_Time"].ToString()); item.PickupDay = Convert.ToInt32(row["Pickup_Day"].ToString()); item.PickupTime = Convert.ToDateTime(row["Pickup_Time"].ToString()); item.TmsRouteName = row["TMS_Route_Name"].ToString(); item.StopOrder = Convert.ToInt32(row["Stop_Order"].ToString()); item.TransMode = row["Trans_Mode"].ToString(); item.Overweek = Convert.ToInt32(row["Overweek"].ToString()); item.ArrivalDay = Convert.ToInt32(row["Arrival_Day"].ToString()); item.Eta = Convert.ToDateTime(row["ETA"].ToString()); item.ArrivalTime = row["Arrival_Time"].ToString(); item.ArrivalTimeStart = Convert.ToDateTime(row["Arrival_Time_Start"].ToString()); item.ArrivalTimeEnd = Convert.ToDateTime(row["Arrival_Time_End"].ToString()); item.Forwarder = row["Forwarder"].ToString(); DataContext.CBasNormalScheduleS.Add(item); index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// 导出承运公司清单 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_DeriveAccept_Click(object sender, EventArgs e) { MAWB mawb = _mawbService.FindMAWBByBarcode(lbl_MAWBBarCode.Text); IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString()); if (hawbs.Count != 0) { var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.承运单号); NpoiHelper.ExportMAWB(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", lbl_MAWBBarCode.Text + "承运清单"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else { Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('该总运单下没有运单,不能导出!')</script>"); } }
/// <summary> /// Normal Schedule V Trans Mode Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleVTmImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); //替换为从表里获取 var pdcs = DataContext.CBasPdcSequence.Select(p => p.Pdc); //string[] pdcs = new string[] { "BJ", "SH", "GZ", "YZ", "CD" }; string[] transModes = new string[] { "FTL_Line", "FTL_DTD", "LTL", "Air", "Courier" }; Dictionary <string, string> dicDestination = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { NormalScheduleVTmImportCheckData(row, index); string destination = row["Destination"].ToString(); if (dicDestination.ContainsKey(destination)) { throw new Exception(string.Format(@"Destination ""{0}"" <=Duplicate>", destination)); } dicDestination.Add(destination, destination); CBasNormalScheduleVTm item = new CBasNormalScheduleVTm(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVTm>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleVTmId = Guid.NewGuid().ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); if (!DataIsNullValidator(row, "1st_V_Cutoff_Day") && !DataIsNullValidator(row, "1st_V_Cutoff_Time") && !DataIsNullValidator(row, "1st_V_Pickup_Day") && !DataIsNullValidator(row, "1st_V_Pickup_Time") && !DataIsNullValidator(row, "1st_V_Trans_Mode") && !DataIsNullValidator(row, "1st_V_Leadtime") && !DataIsNullValidator(row, "1st_V_Leadtime_AM_PM") && !DataIsNullValidator(row, "1st_V_Leadtime_Start") && !DataIsNullValidator(row, "1st_V_Leadtime_End")) { DataValidator <DateTime>(row, "1st_V_Cutoff_Time", index); DataValidator <DateTime>(row, "1st_V_Pickup_Time", index); DataValidator <int>(row, "1st_V_Leadtime", index); DataValidator <DateTime>(row, "1st_V_Leadtime_Start", index); DataValidator <DateTime>(row, "1st_V_Leadtime_End", index); item.FirstVCutoffDay = row["1st_V_Cutoff_Day"].ToString(); item.FirstVCutoffTime = Convert.ToDateTime(row["1st_V_Cutoff_Time"].ToString()); item.FirstVPickupDay = row["1st_V_Pickup_Day"].ToString(); item.FirstVPickupTime = Convert.ToDateTime(row["1st_V_Pickup_Time"].ToString()); item.FirstVTransMode = row["1st_V_Trans_Mode"].ToString(); item.FirstVLeadtime = Convert.ToInt32(row["1st_V_Leadtime"].ToString()); item.FirstVLeadtimeAmPm = row["1st_V_Leadtime_AM_PM"].ToString(); item.FirstVLeadtimeStart = Convert.ToDateTime(row["1st_V_Leadtime_Start"].ToString()); item.FirstVLeadtimeEnd = Convert.ToDateTime(row["1st_V_Leadtime_End"].ToString()); } item.SecondVCutoffDay = row["2nd_V_Cutoff_Day"].ToString(); item.SecondVCutoffTime = Convert.ToDateTime(row["2nd_V_Cutoff_Time"].ToString()); item.SecondVPickupDay = row["2nd_V_Pickup_Day"].ToString(); item.SecondVPickupTime = Convert.ToDateTime(row["2nd_V_Pickup_Time"].ToString()); DataContext.CBasNormalScheduleVTm.Add(item); foreach (var strPdc in pdcs) { foreach (var strTransMode in transModes) { if (!dt.Columns.Contains(strPdc + "_2nd_V_" + strTransMode + "_Leadtime")) { continue; } if (row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"] != null && !row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"].ToString().IsNullString()) { DataValidator <int>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime_End", index); CBasNormalScheduleVTmDet itemDet = new CBasNormalScheduleVTmDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVTmDet>(itemDet); itemDet.BasNormalScheduleId = basNormalScheduleId; itemDet.BasNormalScheduleVTmId = item.BasNormalScheduleVTmId; itemDet.BasNormalScheduleVTmDetId = Guid.NewGuid().ToString(); itemDet.Pdc = strPdc; itemDet.TransMode = strTransMode; itemDet.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"].ToString()); itemDet.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_AM_PM"].ToString(); itemDet.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_Start"].ToString()); itemDet.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVTmDet.Add(itemDet); } } } index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// 数据源操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Gv_HAWB_RowCommand(object sender, GridViewCommandEventArgs e) { Guid id = (Guid)Session["UserID"]; ModulePrivilege Authority = _SysUserManagementService.GetPrivilegeByUserid(id); if (e.CommandName == "Eidt") { bool aPrivilege = (bool)Authority[Privilege.修改.ToString()]; bool aPrivilege1 = (bool)Authority[Privilege.导出.ToString()]; int index = Convert.ToInt32(e.CommandArgument); string barCode = Gv_HAWB.DataKeys[index].Value.ToString(); Response.Redirect("HAWBDetails.aspx?BarCode=" + barCode + "&Privilege=" + aPrivilege + "&Privilege1=" + aPrivilege1 + ""); } else if (e.CommandName == "Updata") { string barCode = e.CommandArgument.ToString(); int Update = 1; Response.Redirect("HAWBAdd.aspx?BarCode=" + barCode + "&update=" + Update + ""); } else if (e.CommandName == "Del") { string barCode = e.CommandArgument.ToString(); //_hawbService.RemoveHAWB(barCode); } else if (e.CommandName == "Derive") { string barCode = e.CommandArgument.ToString(); HAWB hawb = _hawbService.FindHAWBByBarCode(barCode); var NpoiHelper = new NpoiHelper(hawb, barCode); NpoiHelper.ExportInvoice(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barCode+"运单发票"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else if (e.CommandName == "DeriveAccept") { int index = ((GridViewRow)((LinkButton)(e.CommandSource)).Parent.Parent).RowIndex; string CarrierHAWBBarCode = e.CommandArgument.ToString(); string barCode = Gv_HAWB.DataKeys[index].Value.ToString(); HAWB hawb = _hawbService.FindHAWBByBarCode(barCode); if (!string.IsNullOrEmpty(CarrierHAWBBarCode)) { var NpoiHelper = new NpoiHelper(hawb, CarrierHAWBBarCode); NpoiHelper.ExportInvoice(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barCode+"承运发票"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else { ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('没有承运公司编号,不能导出!')", true); } } }
private void Btn_Change_Click(object sender, EventArgs e) { //获取列名称 string columnname = txt_columnname.Text.Trim(); if (columnname == "") { MessageBox.Show("请填写列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (!dt.Columns.Contains(columnname)) { MessageBox.Show("不存在此列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //条件判断,智能取其中一个 #region 条件一 if (Ckb_isuseone.Checked) { //查找相同 删除重复数据,并把重复数据导入到新建的excel中 if (ckb_issame.Checked) { string path = txt_filepath.Text.Trim(); string exportpath = txt_exportpath.Text.Trim(); DataTable table = dt.Clone(); List <string> names = new List <string>(); Regex reg = new Regex(@"\s"); for (int i = 0; i < dt.Rows.Count; i++) { string name = reg.Replace(dt.Rows[i][columnname].ToString(), "", 999); //去除中间空格及左右空格,防止数据错误 dt.Rows[i][columnname] = name; //判断是否已经处理过这名字 if (names.Contains(name)) { continue; } names.Add(name); List <DataRow> rows = dt.Select().Where(t => reg.Replace(t[columnname].ToString(), "", 999) == name).ToList(); if (rows.Count > 1) { foreach (DataRow item in rows) { //删除空格 item[columnname] = reg.Replace(item[columnname].ToString(), "", 999); //解决该行已经属于另一个表 table.Rows.Add(item.ItemArray); //删除重复数据 dt.Rows.Remove(item); } } } NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath); MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } #endregion #region 条件二 if (Ckb_isusetwo.Checked) { string where_one = txt_whereone.Text.Trim(); string where_two = txt_wheretwo.Text.Trim(); string where_three = txt_wherethree.Text.Trim(); for (int i = 0; i < dt.Rows.Count; i++) { string value = dt.Rows[i][columnname].ToString(); //去除中间空格及左右空格,防止数据错误 value = value.Trim().Replace(" ", ""); if (value == where_one) { value = txt_resultone.Text.Trim(); } else if (value == where_two) { value = txt_resulttwo.Text.Trim(); } else if (value == where_three) { value = txt_resultthree.Text.Trim(); } dt.Rows[i][columnname] = value; } } #endregion #region 条件三 if (Ckb_isusethree.Checked) { //是否是身份证 if (ckb_isidcard.Checked) { ValidateHelper validateHelper = new ValidateHelper(); for (int i = 0; i < dt.Rows.Count; i++) { string value = dt.Rows[i][columnname].ToString(); //去除中间空格及左右空格,防止数据错误 value = value.Trim().Replace(" ", ""); if (value != "") { bool istrue = validateHelper.IsIdCard(value); if (!istrue) { dt.Rows[i][columnname] = value + "数据错误"; } } } } } #endregion MessageBox.Show("更改成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public ActionResult ImportCsOrder(string path) { path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + path.Replace("..", ""); var fileArr = path.Split(new[] { "/" }, StringSplitOptions.RemoveEmptyEntries); var fileName = fileArr[fileArr.Length - 1]; IList <CsOrderView.CsOrderImport> orders; try { orders = NpoiHelper.ReadExcel(path.Replace("/" + fileName, ""), fileName).ToList <CsOrderView.CsOrderImport>(); } catch (Exception ex) { LogHelper.Log(ex.ToJson(), "execl文件读取失败"); return(Json(new ResModel { ResStatus = ResStatue.No, Data = "execl 文件读取失败,请确认文件数据正确, 请检查日志描述" })); } if (!orders.Any()) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "当前Excel中没有数据,请确认文件是否包含规定完整的数据" })); } var users = _csUsersBll.GetModelList($" AND (UserName IN ('{string.Join("','", orders.Select(x => x.收货人))}') OR UserPhone IN ('{string.Join("','", orders.Select(x => x.收货人电话))}'))"); var products = _csProductsBll.GetModelList(""); var parts = _csPartsBll.GetModelList(""); var data = new List <CsOrderView.CsOrderAndDetail>(); var item = new CsOrderView.CsOrderAndDetail(); var lastType = ExcelRow.Other; // 记录上一行状态 var endEmpty = new List <int>(); // 记录尾部空行 var overEndEmpty = false; // 尾部空行是否结束 for (var i = orders.Count - 1; i >= 0; i--) { var type = ExcelRowType(orders[i]); if (type == ExcelRow.Empty && !overEndEmpty) { endEmpty.Add(i); } else { overEndEmpty = true; } if (type == ExcelRow.Empty && lastType == ExcelRow.Empty && overEndEmpty) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "当前Excel中,两个订单信息中间,存在连续多空行.请仔细确认数据, 数据一旦上传将无法撤回" })); } lastType = type; } // 将尾部空行删除只保留一个 endEmpty.ForEach(x => orders.RemoveAt(x)); orders.Add(new CsOrderView.CsOrderImport()); foreach (var order in orders) { var type = ExcelRowType(order); if (type == ExcelRow.Other) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "当前Excel中不满足格式的单元格,请仔细确认数据, 数据一旦上传将无法撤回" })); } if (type == ExcelRow.Empty) { data.Add(item); item = new CsOrderView.CsOrderAndDetail(); continue; } var product = products.FirstOrDefault(x => x.ProductNumber == order.商品编码); var part = parts.FirstOrDefault(x => x.PartNumber == order.商品编码); var pId = 0; // 商品Id var cType = ChoseType.螃蟹; // 蟹或配件 if (product == null && part == null) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = $"商品编码:{order.商品编码},不存在与数据库中,请仔细确认数据" })); } if (product != null) { pId = product.ProductId; cType = ChoseType.螃蟹; } if (part != null) { pId = part.PartId; cType = ChoseType.配件; } if (type == ExcelRow.Order) { var user = users.FirstOrDefault(x => x.UserPhone == order.收货人电话 && x.UserName == order.收货人); int userId; if (user == null) { user = new CsUsers { UserSex = "先生", UserName = order.收货人, UserPhone = order.收货人电话, OpenId = "", Remarks = "", TotalWight = 0, UserBalance = 0, UserState = 1 }; userId = _csUsersBll.Add(user); } else { userId = user.UserId; } Thread.Sleep(5); item.CsOrder = new CsOrder { RowStatus = RowStatus.效.GetHashCode(), DeleteDescribe = "", OrderState = OrderState.已发货.GetHashCode(), UserId = userId, ActualMoney = order.实收金额.ToDecimal(), OrderDelivery = order.货运单号, SendAddress = $"{order.发货人}${order.发货人电话}", OrderAddress = $"${order.收货人}" + $"({user.UserSex})$${order.收货人电话}${order.收货地址})", DeleteDate = "1900-1-1".ToDate(), DiscountMoney = order.总金额.ToDecimal() - order.实收金额.ToDecimal(), OrderDate = DateTime.Now, OrderNumber = DateTime.Now.ToString(OrderNumberFormat), TotalMoney = order.总金额.ToDecimal() }; item.CsOrderDetails.Add(new CsOrderDetail { ProductId = pId, ProductNumber = order.数量.ToInt(), ChoseType = cType.GetHashCode(), TotalPrice = order.单价.ToDecimal() * order.数量.ToInt(), UnitPrice = order.单价.ToDecimal() }); } if (type == ExcelRow.Detail) { item.CsOrderDetails.Add(new CsOrderDetail { ProductId = pId, ProductNumber = order.数量.ToInt(), ChoseType = cType.GetHashCode(), TotalPrice = order.单价.ToDecimal() * order.数量.ToInt(), UnitPrice = order.单价.ToDecimal() }); } } var count = 0; var countDetail = 0; foreach (var d in data) { if (d.CsOrder.RowStatus == RowStatus.无效.GetHashCode()) { return(Json(new ResModel { ResStatus = ResStatue.No, Data = "存在无效订单,请检查表格总的数据是否满足要求" })); } var orderId = _csOrderBll.Add(d.CsOrder); foreach (var detail in d.CsOrderDetails) { detail.OrderId = orderId; _csOrderDetailBll.Add(detail); countDetail++; } count++; } return(Json(new ResModel { ResStatus = ResStatue.Yes, Data = $"导入{count}条订单记录,以及共计{countDetail}条商品记录" })); }
/// <summary> /// 导出承运单发票 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_DeriveAccept_Click(object sender, EventArgs e) { HAWB hawb = _hawbService.FindHAWBByBarCode(Txt_BarCode.Text.Trim()); if (!string.IsNullOrEmpty(Txt_CarrierHAWBBarCode.Text)) { var NpoiHelper = new NpoiHelper(hawb, Txt_CarrierHAWBBarCode.Text); NpoiHelper.ExportInvoice(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.UTF8; resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", Txt_BarCode.Text + "承运发票"), System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } else { Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('没有承运公司编号,不能导出!')</script>"); } }
/// <summary> /// 导出报关文件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { IList<HAWB> hawbs = new List<HAWB>();//实例化 for (int i = 0; i < RGMAWB.MasterTableView.Items.Count; i++) { GridDataItem row = RGMAWB.MasterTableView.Items[i]; if (((CheckBox)row.FindControl("CBSelect")).Checked) { //获取选中的运单编号 string hawbBarcode = RGMAWB.MasterTableView.DataKeyValues[row.ItemIndex]["BarCode"].ToString(); //获取运单信息 HAWB hawb = _hawbService.FindHAWBByBarCode(hawbBarcode); hawbs.Add(hawb); } } MAWB mawb = _mawbService.FindMAWBByBarcode(MAWBNo); var NpoiHelper = new NpoiHelper(mawb, hawbs); NpoiHelper.ExportClearance(); var str = (MemoryStream)NpoiHelper.RenderToExcel(); if (str == null) return; var data = str.ToArray(); var resp = Page.Response; resp.Buffer = true; resp.Clear(); resp.Charset = "utf-8"; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.ContentType = "application/ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("电子出口清单.xls", Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(data); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }