protected void Button3_Click(object sender, EventArgs e) { ComSqlHelper csh = new ComSqlHelper(); DataTable contentData = csh.Query("SELECT * FROM [Qc_CarRunStatus]", "HRBKY").Tables[0]; contentData.Columns.Remove("CID"); contentData.Columns.Add("StartAddress"); contentData.Columns.Add("EndAddress"); List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>(); for (int i = 0; i < contentData.Rows.Count; i++) { string sc = contentData.Rows[i]["StartCoord"].ToString(); sc = string.IsNullOrEmpty(sc) ? "0-0" : sc; string slng = sc.Split('-')[1]; string slat = sc.Split('-')[0]; string ec = contentData.Rows[i]["EndCoord"].ToString(); ec = string.IsNullOrEmpty(ec) ? "0-0" : ec; string elng = ec.Split('-')[1]; string elat = ec.Split('-')[0]; CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat(); sdl.Lat = double.Parse(slat); sdl.Lng = double.Parse(slng); CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat(); edl.Lat = double.Parse(elat); edl.Lng = double.Parse(elng); corrds.Add(sdl); corrds.Add(edl); } string[] address = AddressConvert.AddConvertBatch(corrds); for (int i = 0; i < contentData.Rows.Count; i++) { contentData.Rows[i]["StartAddress"] = address[i * 2]; contentData.Rows[i]["EndAddress"] = address[i * 2 + 1]; } NPOIHelper npoiHelper = new NPOIHelper(); string[] headerDataArray = { "车牌号", "所属企业", "车辆用途", "运营线路", "开始时间", "开始速度", "结束时间", "结束速度", "开始经纬度", "结束经纬度", "开始地址", "结束地址" }; string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData); npoiHelper.WorkbookName = "车辆运行统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; // 设置导入内容 npoiHelper.HeaderData = headerDataArray; npoiHelper.ContentData = contentDataArray; string basepath = HttpRuntime.AppDomainAppPath.ToString(); string filePath = @"UI\Excel\Query\"; string sd = basepath + filePath; npoiHelper.saveExcel(sd); }
public ResponseResult getOfflineStatisticDetailOutput(Dictionary <string, string> inparams) { ResponseResult Result = null; string stime; string etime; string sysflag; string num; string cid; if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("cid") || !inparams.Keys.Contains("sysflag") || !inparams.Keys.Contains("num")) { Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null); return(Result); } try { if (inparams["stime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null); return(Result); } if (inparams["etime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null); return(Result); } if (inparams["sysflag"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null); return(Result); } stime = inparams["stime"]; etime = inparams["etime"]; sysflag = inparams["sysflag"]; num = inparams["num"]; cid = inparams["cid"]; DataTable dtStatDetail = GetStatDetailTable(sysflag, cid, stime, etime); DataTable dtDetailOrg = OrgnizeTableDetailNew(dtStatDetail); List <OfflineStatDetail> offlineStatDetailList = OrgnizeOfflineStatDetail(dtDetailOrg, Int32.Parse(num)); DataTable contentData = ListToDetailDataTable(offlineStatDetailList); contentData.Columns.Remove("CID"); // 添加列 contentData.Columns.Add("DurationStr"); // 处理时间,转换为“时、分、秒” foreach (DataRow row in contentData.Rows) { if ((int)row["OffDuration"] != 0) { row["DurationStr"] = AnalysisDuration((int)row["OffDuration"]); } else { row["DurationStr"] = "0"; } } contentData.Columns.Remove("OffDuration"); contentData.Columns.Add("StartAddress"); contentData.Columns.Add("EndAddress"); List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>(); for (int i = 0; i < contentData.Rows.Count; i++) { string sc = contentData.Rows[i]["FromAddress"].ToString(); sc = string.IsNullOrEmpty(sc) ? "0,0" : sc; string slng = sc.Split(',')[0]; string slat = sc.Split(',')[1]; string ec = contentData.Rows[i]["ToAddress"].ToString(); ec = string.IsNullOrEmpty(ec) ? "0,0" : ec; string elng = ec.Split(',')[0]; string elat = ec.Split(',')[1]; CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat(); sdl.Lat = double.Parse(slat); sdl.Lng = double.Parse(slng); corrds.Add(sdl); CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat(); edl.Lat = double.Parse(elat); edl.Lng = double.Parse(elng); corrds.Add(edl); } string[] address = AddressConvert.AddConvertBatch(corrds); for (int i = 0; i < contentData.Rows.Count; i++) { contentData.Rows[i]["StartAddress"] = address[i * 2]; contentData.Rows[i]["EndAddress"] = address[i * 2 + 1]; } contentData.Columns.Remove("FromAddress"); contentData.Columns.Remove("ToAddress"); NPOIHelper npoiHelper = new NPOIHelper(); string[] headerDataArray = { "车牌号", "所属企业", "车辆用途", "运营线路", "开始时间", "结束时间", "离线时长", "开始地址", "结束地址" }; string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData); npoiHelper.WorkbookName = "车辆离线统计明细" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; // 设置导入内容 npoiHelper.HeaderData = headerDataArray; npoiHelper.ContentData = contentDataArray; string basepath = HttpRuntime.AppDomainAppPath.ToString(); string filePath = @"UI\Excel\Query\"; string sd = basepath + filePath; npoiHelper.saveExcel(sd); Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName); } catch (Exception ex) { Result = new ResponseResult(ResState.OperationFailed, ex.Message, ""); } return(Result); }
public ResponseResult DetailExport(Dictionary <string, string> inparams) { ResponseResult Result = null; string stime; string etime; string sysflag; string sysuid; string cid; if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("cid") || !inparams.Keys.Contains("sysflag")) { Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null); return(Result); } try { if (inparams["stime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null); return(Result); } if (inparams["etime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null); return(Result); } if (inparams["sysflag"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null); return(Result); } stime = inparams["stime"]; etime = inparams["etime"]; cid = inparams["cid"]; sysflag = inparams["sysflag"]; //调用存储过程查询能耗分析明细表 //todo DataTable contentData = EnergyAnalysisDetail(sysflag, cid, stime, etime); contentData.Columns.Remove("CID"); contentData.Columns.Add("StartAddress"); contentData.Columns.Add("EndAddress"); // 添加列 contentData.Columns.Add("DurationStr"); // 处理时间,转换为“时、分、秒” foreach (DataRow row in contentData.Rows) { if ((int)row["Duration"] != 0) { row["DurationStr"] = AnalysisDuration((long)row["Duration"]); } else { row["DurationStr"] = "0"; } } contentData.Columns.Remove("Duration"); List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>(); for (int i = 0; i < contentData.Rows.Count; i++) { string sc = contentData.Rows[i]["FromLatLng"].ToString(); sc = string.IsNullOrEmpty(sc) ? "0,0" : sc; string slng = sc.Split(',')[1]; string slat = sc.Split(',')[0]; string ec = contentData.Rows[i]["ToLatLng"].ToString(); ec = string.IsNullOrEmpty(ec) ? "0,0" : ec; string elng = ec.Split(',')[1]; string elat = ec.Split(',')[0]; CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat(); sdl.Lat = double.Parse(slat); sdl.Lng = double.Parse(slng); CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat(); edl.Lat = double.Parse(elat); edl.Lng = double.Parse(elng); corrds.Add(sdl); corrds.Add(edl); } string[] address = AddressConvert.AddConvertBatch(corrds); for (int i = 0; i < contentData.Rows.Count; i++) { contentData.Rows[i]["StartAddress"] = address[i * 2]; contentData.Rows[i]["EndAddress"] = address[i * 2 + 1]; } NPOIHelper npoiHelper = new NPOIHelper(); string[] headerDataArray = { "车牌号", "开始时间", "结束时间", "开始位置坐标", "结束位置坐标", "行驶里程", "油耗", "尿素消耗", "平均速度", "开始地址", "结束地址", "行驶时长" }; string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData); npoiHelper.WorkbookName = "能耗分析明细表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; // 设置导入内容 npoiHelper.HeaderData = headerDataArray; npoiHelper.ContentData = contentDataArray; string basepath = HttpRuntime.AppDomainAppPath.ToString(); string filePath = @"UI\Excel\"; string sd = basepath + filePath; npoiHelper.saveExcel(sd); Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName); return(Result); } catch (Exception ex) { Result = new ResponseResult(ResState.OperationFailed, ex.Message, ""); } return(Result); }
/// <summary> /// 车辆异常下线统计导出 /// </summary> /// <param name="inparams"></param> /// <returns></returns> public ResponseResult getAbnormalOfflineStatisticsoutput(Dictionary <string, string> inparams) { ResponseResult Result = null; string stime; string etime; string CarNum; string CUID; string CarOwnName; string Line; string sysuid; string sysflag; string onecaruser; string num; if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("CarNum") || !inparams.Keys.Contains("sysflag") || !inparams.Keys.Contains("CUID") || !inparams.Keys.Contains("CarOwnName") || !inparams.Keys.Contains("Line")) { Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null); return(Result); } try { if (inparams["stime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null); return(Result); } if (inparams["etime"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null); return(Result); } if (inparams["sysflag"] == "") { Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null); return(Result); } stime = inparams["stime"]; etime = inparams["etime"]; CarNum = inparams["CarNum"]; CUID = inparams["CUID"]; CarOwnName = inparams["CarOwnName"]; Line = inparams["Line"]; sysflag = inparams["sysflag"]; sysuid = inparams["sysuid"]; onecaruser = inparams["onecaruser"]; num = inparams["num"]; //调用存储过程查询警情数据 //todo DataTable contentData = GetAbnormalStatistics(sysflag, sysuid, stime, etime, CarNum, CUID, CarOwnName, Line, onecaruser, num); //contentData.Columns.Remove("CID"); //contentData.Columns.Add("StartAddress"); // contentData.Columns.Add("EndAddress"); List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>(); for (int i = 0; i < contentData.Rows.Count; i++) { //string sc = contentData.Rows[i]["StartCoord"].ToString(); //sc = string.IsNullOrEmpty(sc) ? "0-0" : sc; //string slng = sc.Split('-')[1]; //string slat = sc.Split('-')[0]; string ec = contentData.Rows[i]["OffCoord"].ToString(); ec = string.IsNullOrEmpty(ec) ? "0-0" : ec; string elng = ec.Split(',')[1]; string elat = ec.Split(',')[0]; //CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat(); //sdl.Lat = double.Parse(slat); //sdl.Lng = double.Parse(slng); CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat(); edl.Lat = double.Parse(elat); edl.Lng = double.Parse(elng); //corrds.Add(sdl); corrds.Add(edl); } string[] address = AddressConvert.AddConvertBatch(corrds); for (int i = 0; i < contentData.Rows.Count; i++) { //contentData.Rows[i]["StartAddress"] = address[i * 2]; contentData.Rows[i]["address"] = address[i]; } NPOIHelper npoiHelper = new NPOIHelper(); string[] headerDataArray = { "车牌号", "车辆用途", "所属企业", "下线时间", "下线经纬度", "下线地址", "运营线路" }; string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData); npoiHelper.WorkbookName = "车辆异常下线统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; // 设置导入内容 npoiHelper.HeaderData = headerDataArray; npoiHelper.ContentData = contentDataArray; string basepath = HttpRuntime.AppDomainAppPath.ToString(); string filePath = @"UI\Excel\Query\"; string sd = basepath + filePath; npoiHelper.saveExcel(sd); Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName); return(Result); } catch (Exception ex) { Result = new ResponseResult(ResState.OtherError, "", ex.Message + ex.StackTrace); return(Result); } }