public string DownLoadExcel(string CarId, string TerNo, bool isStart, DateTime st, DateTime ed) { //得到本车最新上传数据的终端编号 //string NewEastTerNo = ""; //if (CarId != "null" && CarId.Trim() != "") //{ // DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("SELECT TI.TER_NO FROM REALTIME_DATA RD JOIN TERMINAL_INFO TI ON RD.TER_NO = TI.TER_NO JOIN CAR_INFO CI ON TI.CAR_ID = CI.CAR_ID and ti.car_id='{0}' order by rd.rtime desc", CarId)); // if (ds != null && ds.Tables[0].Rows.Count > 0) // { // NewEastTerNo = ds.Tables[0].Rows[0][0].ToString(); // } //} //else //{ // NewEastTerNo = TerNo; //} Hashtable ht = new Hashtable(); ht.Add("TerNo", TerNo); if (isStart == true) { ht.Add("EndData", 31); ht.Add("StartData", 0); } else { if (ed != null) { ht.Add("EndData", 0); ht.Add("st", st.ToString("yyyy-MM-dd HH:mm:ss")); ht.Add("ed", ed.ToString("yyyy-MM-dd HH:mm:ss")); } } IList <HistoricalData> ihrd = historicalDataBll.GetHistorical(ht); ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = eu.CreateExcel(ihrd, 0); string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/历史轨迹信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + "。"); return(ppphhh); } else { return("生成文件出错,请重新导出!"); } }
public string DownLoadExcel2(string TerNo, string DeptId, string st, string ed) { Hashtable ht = new Hashtable(); if (DeptId != null && DeptId.Trim() != "") { ht.Add("DeptId", DeptId); } else { UserInfo user = new UserInfo(); user = (UserInfo)Session["LoginUser"]; ht.Add("DeptId", user.EnterId); } ht.Add("EndData", 0); if (TerNo != null && TerNo.Trim() != "") { ht.Add("TerNo", TerNo); } else { ht.Add("TerNo", ""); } if (st != "") { ht.Add("st", st); } if (ed != "") { ht.Add("ed", ed); } IList <TerData> iltd = historicalDataBll.GetTerHistoryData(ht); ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = eu.CreateExcel(iltd); string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/历史轨迹信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + "。"); return(ppphhh); } else { return("生成文件出错,请重新导出!"); } }
public string YXDownLoadExcel2(string TerNo, string DeptId, string st, string ed) { Hashtable ht = new Hashtable(); if (DeptId != null && DeptId.Trim() != "") { ht.Add("DeptId", DeptId); } else { UserInfo user = new UserInfo(); user = (UserInfo)Session["LoginUser"]; ht.Add("DeptId", user.EnterId); } if (TerNo != null && TerNo.Trim() != "") { ht.Add("TerNo", TerNo); } else { ht.Add("TerNo", ""); } string strTrackName = ""; string NewStartDate = ""; string NewEndDate = ""; if (st == "") { strTrackName = "ZTRACK" + DateTime.Now.ToString("yyyyMMdd"); NewStartDate = DateTime.Now.ToString("yyyyMMdd") + " 00:00:00"; NewEndDate = DateTime.Now.ToString("yyyyMMdd") + " 23:59:59"; } else { strTrackName = "ZTRACK" + DateTime.Parse(st).ToString("yyyyMMdd"); NewStartDate = DateTime.Parse(st).ToString("yyyy-MM-dd HH:mm:ss"); NewEndDate = DateTime.Parse(ed).ToString("yyyy-MM-dd HH:mm:ss"); } ht.Add("table", strTrackName); ht.Add("st", NewStartDate); ht.Add("ed", NewEndDate); List <YXHistoricalData> Listhrd = new List <YXHistoricalData>(); DataSet dsYXTrailCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select * from user_tab_cols where table_name='" + strTrackName + "' and column_name='TER_NO'")); if (dsYXTrailCount != null && dsYXTrailCount.Tables[0].Rows.Count > 0) { StringBuilder sb = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); sb.Append("select ZTK.ID AS Id,ZTK.RTIME AS Rtime,nvl(ZTK.POSITION,'设备周边无地理信息描述') AS Position,ZTK.TER_NO AS TerNo,ZTK.REPLYDATANAME as ReplydataName,ZTK.IFPOSITION AS Ifposition,"); sb.Append("ZTK.NORTHORSOUTH AS Northorsouth,ZTK.EASTORWEST AS Eastorwest,ZTK.SPEED AS Speed,ZTK.DIRECTION AS Direction,ZTK.TER_VBATT AS TerVbatt,ZTK.GSMRSSI AS Gsmrssi,ZTK.PROGRAMVERSON AS Programverson,ZTK.GPSVERSON AS Gpsverson,ZTK.PROTOCOLVERSION AS Protocolversion,ZTK.ACCSTATE AS Accstate,"); sb.Append("ZTK.LATITUDE AS Latitude,ZTK.LONGITUDE AS Longitude,ZTK.BAIDU_LATITUDE AS BaiduLatitude,ZTK.BAIDU_LONGITUDE AS BaiduLongitude,ZTK.GOOGLE_LATITUDE AS GoogleLatitude,ZTK.GOOGLE_LONGITUDE AS GoogleLongitude,TI.TER_SIMCARD AS TerSimcard,TI.TER_INNETTIME AS Ter_Innettime from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); sbCount.Append("select count(*) from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); if (ht["TerNo"].ToString() != "") { sb.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); sbCount.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); } if (ht["DeptId"].ToString() != "") { sb.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); sbCount.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); } if (NewStartDate != "") { sb.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); } if (NewEndDate != "") { sb.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); } sb.Append(" order by ZTK.RTIME desc"); DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString()); DataSet dsYXTrailNum = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString()); if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsYXTrail.Tables[0].Rows) { YXHistoricalData yxhd = new YXHistoricalData(); yxhd.Rtime = DateTime.Parse(dr["Rtime"].ToString()); yxhd.Position = dr["Position"].ToString(); yxhd.TerNo = dr["TerNo"].ToString(); yxhd.ReplydataName = dr["ReplydataName"].ToString(); yxhd.Ifposition = dr["Ifposition"].ToString(); yxhd.Northorsouth = dr["Northorsouth"].ToString(); yxhd.Eastorwest = dr["Eastorwest"].ToString(); yxhd.Speed = dr["Speed"].ToString(); yxhd.Direction = dr["Direction"].ToString(); yxhd.TerVbatt = dr["TerVbatt"].ToString(); yxhd.Gsmrssi = dr["Gsmrssi"].ToString(); yxhd.Programverson = dr["Programverson"].ToString(); yxhd.Gpsverson = dr["Gpsverson"].ToString(); yxhd.Latitude = dr["Latitude"].ToString(); yxhd.Longitude = dr["Longitude"].ToString(); yxhd.GoogleLatitude = dr["GoogleLatitude"].ToString(); yxhd.GoogleLongitude = dr["GoogleLongitude"].ToString(); yxhd.BaiduLatitude = dr["BaiduLatitude"].ToString(); yxhd.BaiduLongitude = dr["BaiduLongitude"].ToString(); yxhd.TerSimcard = dr["TerSimcard"].ToString(); yxhd.Ter_Innettime = DateTime.Parse(dr["Ter_Innettime"].ToString()); Listhrd.Add(yxhd); } } } else { ht.Add("EndData", 0); IList <TerData> iltd = historicalDataBll.GetTerHistoryData(ht); Listhrd = iltd.ToList().Select(m => new YXHistoricalData { Rtime = m.Rtime, Position = m.Position, TerNo = m.TerNo, ReplydataName = m.ReplydataName, Ifposition = m.Ifposition, Northorsouth = m.Northorsouth, Eastorwest = m.Eastorwest, Speed = m.Speed, Direction = m.Direction, TerVbatt = m.TerVbatt, Gsmrssi = m.Gsmrssi, Programverson = m.Programverson, Gpsverson = m.Gpsverson, Latitude = m.Latitude.ToString() == null ? "" : m.Latitude.ToString(), Longitude = m.Longitude.ToString() == null ? "" : m.Longitude.ToString(), GoogleLatitude = m.GoogleLatitude.ToString() == null ? "" : m.GoogleLatitude.ToString(), GoogleLongitude = m.GoogleLongitude.ToString() == null ? "" : m.GoogleLongitude.ToString(), BaiduLatitude = m.BaiduLatitude.ToString() == null ? "" : m.BaiduLatitude.ToString(), BaiduLongitude = m.BaiduLongitude.ToString() == null ? "" : m.BaiduLongitude.ToString(), TerSimcard = m.TerSimcard, Ter_Innettime = m.Ter_Innettime }).ToList(); } ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = eu.CreateExcel(Listhrd); string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/历史轨迹信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + "。"); return(ppphhh); } else { return("生成文件出错,请重新导出!"); } }
public string YXDownLoadExcel(string CarId, string TerNo, DateTime st, DateTime ed) { //得到本车最新上传数据的终端编号 //string NewEastTerNo = ""; //if (CarId != "null" && CarId.Trim() != "") //{ // DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("SELECT TI.TER_NO FROM REALTIME_DATA RD JOIN TERMINAL_INFO TI ON RD.TER_NO = TI.TER_NO JOIN CAR_INFO CI ON TI.CAR_ID = CI.CAR_ID and ti.car_id='{0}' order by rd.rtime desc", CarId)); // if (ds != null && ds.Tables[0].Rows.Count > 0) // { // NewEastTerNo = ds.Tables[0].Rows[0][0].ToString(); // } //} //else //{ // NewEastTerNo = TerNo; //} string strTrackName = "ZTRACK" + st.ToString("yyyyMMdd"); string StartDate = st.ToString("yyyy-MM-dd HH:mm:ss"); string EndDate = ed.ToString("yyyy-MM-dd HH:mm:ss"); List <HistoricalData> Listhrd = new List <HistoricalData>(); DataSet dsYXTrailCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select * from user_tab_cols where table_name='" + strTrackName + "' and column_name='TER_NO'")); if (dsYXTrailCount != null && dsYXTrailCount.Tables[0].Rows.Count > 0) { DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select ID AS Id,RTIME AS Rtime,nvl(POSITION,'设备周边无地理信息描述') AS Position,TER_NO AS TerNo,REPLYDATANAME as ReplydataName,IFPOSITION AS Ifposition,BAIDU_LATITUDE AS BaiduLatitude,BAIDU_LONGITUDE AS BaiduLongitude,GOOGLE_LATITUDE AS GoogleLatitude,GOOGLE_LONGITUDE AS GoogleLongitude from " + strTrackName + " where TER_NO='" + TerNo + "' and RTIME BETWEEN to_date('" + StartDate + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate + "','yyyy-mm-dd hh24:mi:ss') order by RTIME desc")); if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsYXTrail.Tables[0].Rows) { HistoricalData hd = new HistoricalData(); hd.Rtime = DateTime.Parse(dr["Rtime"].ToString()); hd.Position = dr["Position"].ToString(); hd.TerNo = dr["TerNo"].ToString(); hd.ReplydataName = dr["ReplydataName"].ToString(); hd.Ifposition = dr["Ifposition"].ToString(); hd.GoogleLatitude = double.Parse(dr["GoogleLatitude"].ToString()); hd.GoogleLongitude = double.Parse(dr["GoogleLongitude"].ToString()); hd.BaiduLatitude = double.Parse(dr["BaiduLatitude"].ToString()); hd.BaiduLongitude = double.Parse(dr["BaiduLongitude"].ToString()); Listhrd.Add(hd); } } } else { Hashtable ht = new Hashtable(); ht.Add("TerNo", TerNo); ht.Add("EndData", 0); ht.Add("st", StartDate); ht.Add("ed", EndDate); IList <HistoricalData> ihrd = historicalDataBll.GetHistorical(ht); Listhrd = ihrd.ToList(); } ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = eu.CreateExcel(Listhrd, 1); string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/历史轨迹信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + "。"); return(ppphhh); } else { return("生成文件出错,请重新导出!"); } }
public string DownLoadExcel(Selectcarmonitor rtv, string DeptCode, string SelType, string ZLflag) { UserInfo user = new UserInfo(); user = (UserInfo)Session["LoginUser"]; DeptInfo dif = new DeptInfo(); Hashtable ht = new Hashtable(); ht.Add("TerStatus", rtv.TerStatus); ht.Add("TerNo", rtv.TerNo); ht.Add("CarNo", rtv.CarNo); ht.Add("StartData", rtv.StartData); ht.Add("EndData", rtv.EndData); ht.Add("ReplydataCode", rtv.ReplydataCode); ht.Add("Northorsouth", rtv.Northorsouth); if (rtv.Businessdivisionid == null || rtv.Businessdivisionid.Trim() == "") { if (user.EnterId != null) { ht.Add("Businessdivisionid", user.EnterId); } else { ht.Add("UserLname", user.UserLname); } } else { ht.Add("Businessdivisionid", rtv.Businessdivisionid); } if (DeptCode == "true") { if (user.EnterId != null) { dif = deptInfoBll.GetDeptInfo(ht["Businessdivisionid"].ToString()); ht.Add("Businessdivisioncode", dif.Businessdivisioncode); ht["Businessdivisionid"] = ""; } } if (rtv.CarWorkVMP > 0) { ht.Add("SelType", ""); } else { if (SelType == "Ter" || SelType == "Dept") { ht.Add("SelType", "true"); } else { ht.Add("SelType", ""); } } IList <Selectcarmonitor> irt = realtimeDataBll.SelectCarMonitor(ht); IList <Selectcarmonitor> lxirt = new List <Selectcarmonitor>(); IList <Selectcarmonitor> zxirt = new List <Selectcarmonitor>(); for (int i = 0; i < irt.Count; i++) { if (irt[i].StateName == "lx") { lxirt.Add(irt[i]); } else if (irt[i].StateName == "zx") { zxirt.Add(irt[i]); } } ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = new MemoryStream(); if (ZLflag == "ZX") { ms = eu.CreateExcel(zxirt); } else if (ZLflag == "LX") { ms = eu.CreateExcel(lxirt); } else { ms = eu.CreateExcel(irt); } string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/车辆位置信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/车辆位置信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/车辆位置信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + ""); return(ppphhh); } else { new LogMessage().Save("生成文件出错,请重新导出!"); return("生成文件出错,请重新导出!"); } }