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);
}
/// <summary>
/// 批量地址解析
/// </summary>
/// <param name="dt">轨迹数据DataTable</param>
/// <returns>地址解析后的DataTable</returns>
private DataTable AddAddress(DataTable dt)
{
try
{
DataTable addrDt = dt.Copy();
List <AddressConvert.DLngLat> convertAddrList = new List <AddressConvert.DLngLat>(); //存放需要解析地址的轨迹点的坐标
List <int> ConvertIndexList = new List <int>(); //ConvertIndexList存放需要解析坐标的轨迹点的索引
int rowcount = dt.Rows.Count;
double LngDiff = double.Parse(ConfigurationManager.AppSettings["DiffLng"]);
double LatDiff = double.Parse(ConfigurationManager.AppSettings["DiffLat"]);
//遍历DataTable，将需要解析地址的坐标，存放到convertAddrList列表中
for (int i = 0; i < rowcount; i++)
{
int l = convertAddrList.Count;
if ((i == 0) || (i > 0 && (Math.Abs(double.Parse(dt.Rows[i]["lng"].ToString()) - convertAddrList[l - 1].Lng) > LngDiff || Math.Abs(double.Parse(dt.Rows[i]["lat"].ToString()) - convertAddrList[l - 1].Lat) > LatDiff)))
{
AddressConvert.DLngLat dlnglat = new AddressConvert.DLngLat();
ConvertIndexList.Add(i);
dlnglat.Lng = double.Parse(dt.Rows[i]["lng"].ToString());
dlnglat.Lat = double.Parse(dt.Rows[i]["lat"].ToString());
convertAddrList.Add(dlnglat);
}
}
string[] conArr = CommLibrary.AddressConvert.AddConvertBatch(convertAddrList);
if (conArr.Length == convertAddrList.Count)
{
for (int i = 0; i < rowcount; i++)
{
int arrIndex = GetArrIndex(i, ConvertIndexList);
if (arrIndex > -1)
{
addrDt.Rows[i]["add"] = conArr[arrIndex];
}
else
{
addrDt.Rows[i]["add"] = addrDt.Rows[i - 1]["add"];
}
}
}
return(addrDt);
}
catch (Exception ex)
{
throw ex;
}
}
//
private Workbook Object2Workbook(JObject jsonObject, HttpContext context)
{
#region Aspose.Cell引用
Aspose.Cells.License licExcel = new License(); //Aspose.Cells申明
if (File.Exists(context.Server.MapPath("~/bin/Service/_extend/cellLic.lic")))
{
licExcel.SetLicense(context.Server.MapPath("~/bin/Service/_extend/cellLic.lic"));
}
#endregion
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Styles styles = workbook.Styles;
int styleIndex = styles.Add();
Aspose.Cells.Style borderStyle = styles[styleIndex];
borderStyle.Borders.DiagonalStyle = CellBorderType.None;
borderStyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
Cells cells = sheet.Cells;
//sheet.FreezePanes(1, 1, 1, 0);//冻结第一行
sheet.Name = jsonObject["sheetName"].ToString();//接受前台的Excel工作表名
//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTitle.Font.Name = "宋体"; //文字字体
styleTitle.Font.Size = 18; //文字大小
styleTitle.Font.IsBold = true; //粗体
//题头样式
Style styleHeader = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleHeader.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleHeader.Font.Name = "宋体"; //文字字体
styleHeader.Font.Size = 14; //文字大小
styleHeader.Font.IsBold = true; //粗体
styleHeader.IsTextWrapped = true; //单元格内容自动换行
styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//内容样式
Style styleContent = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleContent.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleContent.Font.Name = "宋体"; //文字字体
styleContent.Font.Size = 12; //文字大小
styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
var rowCount = jsonObject["rows"].ToArray().Count(); //表格行数
var columnCount = jsonObject["columns"].ToArray().Count(); //表格列数
//生成行1 标题行
cells.Merge(0, 0, 1, columnCount); //合并单元格
cells[0, 0].PutValue(jsonObject["sheetName"]); //填写内容
cells[0, 0].Style = styleTitle;
cells.SetRowHeight(0, 25);
//生成题头列行
for (int i = 0; i < columnCount; i++)
{
cells[1, i].PutValue(jsonObject["columns"].ToArray()[i]["title"]);
cells[1, i].Style = styleHeader;
cells.SetRowHeight(1, 23);
}
//生成地址列
cells[1, columnCount].PutValue("地址");
cells[1, columnCount].Style = styleHeader;
cells.SetRowHeight(1, 23);
//调用接口进行地址解析
List <AddressConvert.DLngLat> convertAddrList = new List <AddressConvert.DLngLat>(); //存放需要解析地址的轨迹点的坐标
for (int i = 0; i < rowCount; i++)
{
//
AddressConvert.DLngLat dlnglat = new AddressConvert.DLngLat();
dlnglat.Lng = double.Parse(jsonObject["rows"].ToArray()[i]["Long"].ToString());
dlnglat.Lat = double.Parse(jsonObject["rows"].ToArray()[i]["Lati"].ToString());
convertAddrList.Add(dlnglat);
}
//
string[] conArr = CommLibrary.AddressConvert.AddConvertBatch(convertAddrList);
//生成内容行,第三行起始
//生成数据行
for (int i = 0; i < rowCount; i++)
{
for (int k = 0; k < columnCount; k++)
{
var currentColumnName = jsonObject["columns"].ToArray()[k]["field"];
var b = jsonObject["rows"].ToArray()[i][currentColumnName.ToString()];
cells[2 + i, k].PutValue(jsonObject["rows"].ToArray()[i][currentColumnName.ToString()]);
//cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);
cells[2 + i, k].Style = styleContent;
}
//地址行
cells[2 + i, columnCount].PutValue(conArr[i]);
//cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);
cells[2 + i, columnCount].Style = styleContent;
cells.SetRowHeight(2 + i, 22);
}
//添加制表日期
cells[2 + rowCount, columnCount].PutValue("制表日期:" + DateTime.Now.ToShortDateString());
sheet.AutoFitColumns(); //让各列自适应宽度
sheet.AutoFitRows(); //让各行自适应宽度
return(workbook);
}
/// <summary>
/// 获取车辆实时数据导出
/// </summary>
/// <param name="inparams"></param>
/// <returns></returns>
public ResponseResult getCarLastTrackOut(Dictionary <string, string> inparams)
{
ResponseResult Result = null;
if (!inparams.Keys.Contains("CIDs"))
{
Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null);
return(Result);
}
string sysflag = inparams["sysflag"];
ResList list_res = new ResList();
try
{
//调用存储过程或WebGIS实时数据服务接口查询车辆轨迹数据
DataTable dt = new DataTable();
//初始化DataTable的列
dt.Columns.Add(new DataColumn("CarNum", typeof(string)));
dt.Columns.Add(new DataColumn("TDateTime", typeof(DateTime)));
dt.Columns.Add(new DataColumn("Speed", typeof(float)));
dt.Columns.Add(new DataColumn("StatusStr", typeof(string)));
dt.Columns.Add(new DataColumn("AlarmStr", typeof(string)));
dt.Columns.Add(new DataColumn("OnlineStatusStr", typeof(string)));
dt.Columns.Add(new DataColumn("HeadingStr", typeof(string)));
dt.Columns.Add(new DataColumn("SumMiles", typeof(int)));
dt.Columns.Add(new DataColumn("Lati", typeof(float)));
dt.Columns.Add(new DataColumn("Long", typeof(float)));
dt.Columns.Add(new DataColumn("Address", typeof(string)));
//dt.Columns.Add(new DataColumn("Alarm", typeof(long)));
//dt.Columns.Add(new DataColumn("Alarm808", typeof(long)));
//dt.Columns.Add(new DataColumn("AlarmExt808", typeof(long)));
//dt.Columns.Add(new DataColumn("Carid", typeof(long)));
//dt.Columns.Add(new DataColumn("Heading", typeof(int)));
//dt.Columns.Add(new DataColumn("Status", typeof(long)));
//dt.Columns.Add(new DataColumn("Status808", typeof(long)));
//dt.Columns.Add(new DataColumn("StatusExt808", typeof(long)));
// dt.Columns.Add(new DataColumn("AltitudeMeters", typeof(int)));
//dt.Columns.Add(new DataColumn("TNO", typeof(long)));
//dt.Columns.Add(new DataColumn("OnlineStatus", typeof(int)));
//sPositionAdditionalInfo
WebGIS.RealtimeDataServer.CarRealData[] res = new WebGIS.RealtimeDataServer.CarRealData[] { };
res = CarRealDataByCids(sysflag, ToIntArray(inparams["CIDs"].Split(',')));
List <AddressConvert.DLngLat> convertAddrList = new List <AddressConvert.DLngLat>(); //存放需要解析地址的轨迹点的坐标
//利用查询结果填充DataTable
foreach (WebGIS.RealtimeDataServer.CarRealData carRealData in res)
{
DataRow dr = dt.NewRow();
dr["AlarmStr"] = carRealData.AlarmStr;
dr["CarNum"] = carRealData.CarNum;
dr["HeadingStr"] = carRealData.HeadingStr;
dr["Lati"] = carRealData.Lati;
dr["Long"] = carRealData.Long;
dr["Speed"] = carRealData.Speed;
dr["StatusStr"] = carRealData.StatusStr;
dr["SumMiles"] = carRealData.SumMiles;
dr["TDateTime"] = carRealData.TDateTime;
dr["OnlineStatusStr"] = carRealData.OnlineStatusStr;
dt.Rows.Add(dr);
AddressConvert.DLngLat dlnglat = new AddressConvert.DLngLat();
dlnglat.Lng = carRealData.Long;
dlnglat.Lat = carRealData.Lati;
convertAddrList.Add(dlnglat);
}
string[] conArr = CommLibrary.AddressConvert.AddConvertBatch(convertAddrList);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["Address"] = conArr[i];
}
NPOIHelper npoiHelper = new NPOIHelper();
string[] headerDataArray = { "车牌号", "GPS时间", "车速", "车辆状态", "报警状态", "在线状态", "方向", "里程", "经度", "纬度", "地址" };
string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(dt);
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);
}
}
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);
}
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);
}
/// <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);
}
}
