private void loadData() { string strWhere = " where 1=1 and t1.kind=2 "; if (!string.IsNullOrEmpty(Request["DECLPORTCODE"])) { strWhere = strWhere + " and t1.declport like '%" + Request["DECLPORTCODE"] + "%'"; } if (!string.IsNullOrEmpty(Request["DECLPORTNAME"])) { strWhere = strWhere + " and t2.name like '%" + Request["DECLPORTNAME"] + "%'"; } if (!string.IsNullOrEmpty(Request["ENABLED_S"])) { strWhere = strWhere + " and t1.enabled='" + Request["ENABLED_S"] + "'"; } Sql.RelaHarbor bc = new Sql.RelaHarbor(); DataTable dt = bc.LoaData(strWhere, "", "", ref totalProperty, Convert.ToInt32(Request["start"]), Convert.ToInt32(Request["limit"])); string json = JsonConvert.SerializeObject(dt, iso); Response.Write("{rows:" + json + ",total:" + totalProperty + "}"); Response.End(); }
public Dictionary <int, List <int> > upload_RelaHarbor(string newfile, string fileName, string action, JObject json_formdata) { Sql.RelaHarbor bc = new Sql.RelaHarbor(); DataTable dtExcel = GetExcelData_Table(Server.MapPath(newfile), 0); //DataTable dtExcel = GetExcelData_Table(newfile, 0); List <string> stringList = new List <string>(); //停用人 string stopman = ""; //存放成功信息 List <int> repeatListsuccess = new List <int>(); //存放失败条数 List <int> repeatListerror = new List <int>(); //记住insert成功的条数 int count = 0; //返回信息 Dictionary <int, List <int> > dcInts = new Dictionary <int, List <int> >(); for (int i = 0; i < dtExcel.Rows.Count; i++) { for (int j = 0; j < dtExcel.Columns.Count; j++) { stringList.Add(dtExcel.Rows[i][j].ToString()); } //报关 string DECLPORT = stringList[0]; //报检 string INSPPORT = stringList[2]; string REMARK = stringList[4]; //string ENABLED = stringList[4] == "是" ? "1" : "0"; string ENABLED = "1"; //维护人 string CREATEMANNAME = json_formdata.Value <string>("CREATEMANNAME"); //启用时间 string STARTDATE = json_formdata.Value <string>("STARTDATE") == "" ? DateTime.MinValue.ToShortDateString() : json_formdata.Value <string>("STARTDATE"); //停用日期 string ENDDATE = json_formdata.Value <string>("ENDDATE") == "" ? DateTime.MaxValue.ToShortDateString() : json_formdata.Value <string>("ENDDATE"); if (ENABLED == "1") { stopman = ""; } else { FormsIdentity identity = HttpContext.Current.User.Identity as FormsIdentity; string userName = identity.Name; JObject json_user = Extension.Get_UserInfo(userName); stopman = (string)json_user.GetValue("ID"); } //导入判断条件 List <int> inlist = bc.CheckRepeat("", DECLPORT, INSPPORT); if (inlist.Count > 0) { repeatListerror.Add(i + 2); } else { bc.insert_rela_harbor_excel(DECLPORT, INSPPORT, ENABLED, REMARK, stopman, STARTDATE, ENDDATE); count = count + 1; } //清除 stringList.Clear(); } repeatListsuccess.Add(count); dcInts.Add(1, repeatListsuccess); dcInts.Add(2, repeatListerror); return(dcInts); }
public void export() { string strWhere = " where 1=1 and t1.kind=2 "; if (!string.IsNullOrEmpty(Request["DECLPORTCODE"])) { strWhere = strWhere + " and t1.declport like '%" + Request["DECLPORTCODE"] + "%'"; } if (!string.IsNullOrEmpty(Request["DECLPORTNAME"])) { strWhere = strWhere + " and t2.name like '%" + Request["DECLPORTNAME"] + "%'"; } string combo_ENABLED_S2 = Request["combo_ENABLED_S"]; if (combo_ENABLED_S2 == "null") { combo_ENABLED_S2 = String.Empty; } if (!string.IsNullOrEmpty(combo_ENABLED_S2)) { strWhere = strWhere + " and t1.enabled='" + combo_ENABLED_S2 + "'"; } Sql.RelaHarbor bc = new Sql.RelaHarbor(); DataTable dt = bc.export_rela_harbor(strWhere); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个导出成功sheet NPOI.SS.UserModel.ISheet sheet_S = book.CreateSheet("港口对应关系"); NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0); row1.CreateCell(0).SetCellValue("报关港口代码"); row1.CreateCell(1).SetCellValue("报关港口名称"); row1.CreateCell(2).SetCellValue("报检港口代码"); row1.CreateCell(3).SetCellValue("报检港口名称"); row1.CreateCell(4).SetCellValue("启用情况"); row1.CreateCell(5).SetCellValue("启用时间"); row1.CreateCell(6).SetCellValue("维护人"); row1.CreateCell(7).SetCellValue("维护时间"); row1.CreateCell(8).SetCellValue("停用人"); row1.CreateCell(9).SetCellValue("停用时间"); row1.CreateCell(10).SetCellValue("备注"); for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["DECLPORT"].ToString()); rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["DECLPORTNAME"].ToString()); rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["INSPPORT"].ToString()); rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["INSPPORTNAME"].ToString()); rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["ENABLED"].ToString() == "1" ? "是" : "否"); rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["STARTDATE"].ToString()); rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["CREATEMANNAME"].ToString()); rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["CREATEDATE"].ToString()); rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["STOPMANNAME"].ToString()); rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["ENDDATE"].ToString()); rowtemp.CreateCell(10).SetCellValue(dt.Rows[i]["REMARK"].ToString()); } try { // 输出Excel string filename = "港口对应关系.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(filename))); Response.Clear(); MemoryStream ms = new MemoryStream(); book.Write(ms); Response.BinaryWrite(ms.GetBuffer()); Response.End(); } catch (Exception e) { Console.WriteLine(e.Message); } }
public void save(string formdata) { JObject json = (JObject)JsonConvert.DeserializeObject(formdata); Sql.RelaHarbor bcsql = new Sql.RelaHarbor(); //禁用人 string stopman = ""; //返回重复结果 string repeat = ""; //返回前端的值 string response = ""; if (json.Value <string>("ENABLED") == "1") { stopman = ""; } else { FormsIdentity identity = HttpContext.Current.User.Identity as FormsIdentity; string userName = identity.Name; JObject json_user = Extension.Get_UserInfo(userName); stopman = (string)json_user.GetValue("ID"); } if (String.IsNullOrEmpty(json.Value <string>("ID"))) { List <int> retunRepeat = bcsql.CheckRepeat(json.Value <string>("ID"), json.Value <string>("DECLPORT"), json.Value <string>("INSPPORT")); if (retunRepeat.Count > 0) { repeat = "此报关港口和报检港口已经有对应关系存在,请检查"; } else { int i = bcsql.insert_relaHarbor(json, stopman); repeat = "5"; } } else { List <int> retunRepeat = bcsql.CheckRepeat(json.Value <string>("ID"), json.Value <string>("DECLPORT"), json.Value <string>("INSPPORT")); if (retunRepeat.Count > 0) { repeat = "此报关港口和报检港口已经有对应关系存在,请检查"; } else { DataTable dt = bcsql.LoadDataById(json.Value <string>("ID")); int i = bcsql.update_relaHarbor(json, stopman); if (i > 0) { bcsql.insert_base_alterrecord(json, dt); } repeat = "5"; } } response = "{\"success\":\"" + repeat + "\"}"; Response.Write(response); Response.End(); }