/// <summary> /// 更改Excel工作簿内容-导出按钮导出使用 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para) { //获取工作簿 if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); //设置日期 SetReportDate(sheet, 1, 6, para.StartTime.Value, para.ReportType); if (listExport == null || listExport.Count == 0) { GetData(para); } //获取导出日期数据 if (listExport != null) { for (int i = 0; i < listExport.Count; i++) { SetValue(sheet, i + 3, 0, listExport[i].CalculTime);//数据日期 SetValue(sheet, i + 3, 1, listExport[i].LineSum.ToString("F2"));//总交通量(万辆) SetValue(sheet, i + 3, 2, (double.Parse(listExport[i].SumGrow)).ToString("F2"));//同比增幅(%) SetValue(sheet, i + 3, 3, listExport[i].LineEnSum.ToString("F2"));//进京交通量(万辆) SetValue(sheet, i + 3, 4, listExport[i].LineExSum.ToString("F2"));//出京交通量(万辆) SetValue(sheet, i + 3, 5, listExport[i].ExEnPer.ToString("F2")); //出进京比 SetValue(sheet, i + 3, 6, listExport[i].SmaCarFeeNum.ToString("F2"));//小型客车交通量(万辆) SetValue(sheet, i + 3, 7, (double.Parse(listExport[i].SmaCarCompGrow)).ToString("F2"));//同比增幅(%) SetValue(sheet, i + 3, 8, listExport[i].SmaCarFee.ToString("F2")); //小型客车免收通行费(万元) SetValue(sheet, i + 3, 9, listExport[i].ChagSumNum.ToString("F2")); //收费车辆(万辆) } } } return readworkbook; }
/// <summary> /// 修改excel工作簿 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para) { if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); string title = string.Empty; using (DataSubmittedEntities db = new DataSubmittedEntities()) { var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList(); if (holidayName != null && holidayName.Count > 0) { title = string.Format("天津市高速公路支队{0}年{1}假期重点收费站流量表(出口+入口)", ((DateTime)para.EndTime).Year, holidayName[0].ToString()); } SetValue(sheet, 0, 0, title); } if (listExport != null) { //计算查询天数 int count = (para.EndTime.Value - para.StartTime.Value).Days + 1; if ((para.EndTime.Value - para.StartTime.Value).Days + 1 > 15) count = 15;//最多可查询15天 for (int i = 0; i < listExport.Count; i++) { if (count > 0) { SetValue(sheet, i + 45, 3, listExport[i].Date1); if (i == 0) SetValue(sheet, 2, 3, para.StartTime.Value.AddDays(0).ToString("M月d日")); } if (count > 1) { SetValue(sheet, i + 45, 4, listExport[i].Date2); if (i == 0) SetValue(sheet, 2, 4, para.StartTime.Value.AddDays(1).ToString("M月d日")); } if (count > 2) { SetValue(sheet, i + 45, 5, listExport[i].Date3); if (i == 0) SetValue(sheet, 2, 5, para.StartTime.Value.AddDays(2).ToString("M月d日")); } if (count > 3) { SetValue(sheet, i + 45, 6, listExport[i].Date4); if (i == 0) SetValue(sheet, 2, 6, para.StartTime.Value.AddDays(3).ToString("M月d日")); } if (count > 4) { SetValue(sheet, i + 45, 7, listExport[i].Date5); if (i == 0) SetValue(sheet, 2, 7, para.StartTime.Value.AddDays(4).ToString("M月d日")); } if (count > 5) { SetValue(sheet, i + 45, 8, listExport[i].Date6); if (i == 0) SetValue(sheet, 2, 8, para.StartTime.Value.AddDays(5).ToString("M月d日")); } if (count > 6) { SetValue(sheet, i + 45, 9, listExport[i].Date7); if (i == 0) SetValue(sheet, 2, 9, para.StartTime.Value.AddDays(6).ToString("M月d日")); } if (count > 7) { SetValue(sheet, i + 45, 10, listExport[i].Date8); if (i == 0) SetValue(sheet, 2, 10, para.StartTime.Value.AddDays(7).ToString("M月d日")); } if (count > 8) { SetValue(sheet, i + 45, 11, listExport[i].Date9); if (i == 0) SetValue(sheet, 2, 11, para.StartTime.Value.AddDays(8).ToString("M月d日")); } if (count > 9) { SetValue(sheet, i + 45, 12, listExport[i].Date10); if (i == 0) SetValue(sheet, 2, 12, para.StartTime.Value.AddDays(9).ToString("M月d日")); } if (count > 10) { SetValue(sheet, i + 45, 13, listExport[i].Date11); if (i == 0) SetValue(sheet, 2, 13, para.StartTime.Value.AddDays(10).ToString("M月d日")); } if (count > 11) { SetValue(sheet, i + 45, 14, listExport[i].Date12); if (i == 0) SetValue(sheet, 2, 14, para.StartTime.Value.AddDays(11).ToString("M月d日")); } if (count > 12) { SetValue(sheet, i + 45, 15, listExport[i].Date13); if (i == 0) SetValue(sheet, 2, 15, para.StartTime.Value.AddDays(12).ToString("M月d日")); } if (count > 13) { SetValue(sheet, i + 45, 16, listExport[i].Date14); if (i == 0) SetValue(sheet, 2, 16, para.StartTime.Value.AddDays(13).ToString("M月d日")); } if (count > 14) { SetValue(sheet, i + 45, 17, listExport[i].Date15); if (i == 0) SetValue(sheet, 2, 17, para.StartTime.Value.AddDays(14).ToString("M月d日")); } SetValue(sheet, i + 45, 3 + count, listExport[i].Total); } } } return readworkbook; }
/// <summary> /// 更改Excel工作簿内容 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para) { if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); string title = string.Empty; using (DataSubmittedEntities db = new DataSubmittedEntities()) { var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList(); if (holidayName != null && holidayName.Count > 0) title = string.Format("{0}年“{1}”黄金周京津塘高速公路交通量及客运情况统计表", ((DateTime)para.EndTime).Year, holidayName[0].ToString()); SetValue(sheet, 0, 0, title); } if (listExport != null && listExport.Count > 0) { for (int i = 0; i < listExport.Count; i++) { SetValue(sheet, i + 4, 0, listExport[i].CalcuTime); SetValue(sheet, i + 4, 1, listExport[i].EnTra == null ? "" : listExport[i].EnTra.ToString()); SetValue(sheet, i + 4, 2, listExport[i].EnCar == null ? "" : listExport[i].EnCar.ToString()); SetValue(sheet, i + 4, 3, listExport[i].EnTrav == null ? "0.00" : listExport[i].EnTrav.Value.ToString("F2")); SetValue(sheet, i + 4, 4, listExport[i].ExTra == null ? "" : listExport[i].ExTra.ToString()); SetValue(sheet, i + 4, 5, listExport[i].ExCar == null ? "" : listExport[i].ExCar.ToString()); SetValue(sheet, i + 4, 6, listExport[i].ExTrav == null ? "0.00" : listExport[i].ExTrav.Value.ToString("F2")); } SetValue(sheet, listExport.Count + 4, 0, string.Format("统计人:{0}", qModel.CrtBy)); ; } } return readworkbook; }
/// <summary> /// 更改Excel工作簿内容 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para) { //获取工作簿 if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); string title = string.Empty; using (DataSubmittedEntities db = new DataSubmittedEntities()) { var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList(); if (holidayName != null && holidayName.Count > 0) { title = string.Format("{0}年{1}假期进出京交通流量表(北京段)", ((DateTime)para.EndTime).Year, holidayName[0].ToString()); } SetValue(sheet, 0, 0, title); } if (listExport != null) { for (int i = 0; i < listExport.Count; i++) { SetValue(sheet, i + 3, 0, listExport[i].DataDate); SetValue(sheet, i + 3, 1, listExport[i].LineEnSum == null ? "" : listExport[i].LineEnSum.ToString()); SetValue(sheet, i + 3, 2, listExport[i].LineExSum == null ? "" : listExport[i].LineExSum.ToString()); SetValue(sheet, i + 3, 3, listExport[i].Total); } } } return readworkbook; }
/// <summary> /// 预测导出--如果需要将入出口数据进行区分,则分别放在两个list的集合中,若不需区分,则将数据放入list1中,list2为空即可 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <param name="list1"></param> /// <param name="list2"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List<Common.ViewModels.IReportViewModel> list1, List<Common.ViewModels.IReportViewModel> list2) { //获取工作簿 if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); using (DataSubmittedEntities db = new DataSubmittedEntities()) { if (list1 != null && list1.Count > 0) { ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel; //高速入境 SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString());//小型客车 SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString());//其他客车 SetValue(sheet, 5, 3, pInfo.EnTruk.ToString());//高速入境货车数 SetValue(sheet, 5, 4, 0);//pInfo.EnGre.ToString());//绿色通道数 } } } return readworkbook; }
/// <summary> /// 更改Excel工作簿内容 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para) { //获取工作簿 if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); using (DataSubmittedEntities db = new DataSubmittedEntities()) { //获取导出日期数据 List<RP_EnEx> pAADTList = db.RP_EnEx.Where(a => a.CalcuTime == para.StartTime && a.StaType == para.StationType).ToList(); if (pAADTList != null && pAADTList.Count > 0) { RP_EnEx pInfo = pAADTList.First(); //高速 SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString());//小型客车 SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString());//其他客车 SetValue(sheet, 5, 3, pInfo.EnTruk.ToString());//高速入境货车数 SetValue(sheet, 5, 4, 0);//pInfo.EnGre.ToString());//绿色通道数 } } } return readworkbook; }
/// <summary> /// 预测导出--如果需要将入出口数据进行区分,则分别放在两个list的集合中,若不需区分,则将数据放入list1中,list2为空即可 /// </summary> /// <param name="readworkbook"></param> /// <param name="para"></param> /// <param name="list1"></param> /// <param name="list2"></param> /// <returns></returns> public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List<Common.ViewModels.IReportViewModel> list1, List<Common.ViewModels.IReportViewModel> list2) { //获取工作簿 if (readworkbook != null) { ISheet sheet = readworkbook.GetSheetAt(0); //设置日期 SetReportDate(sheet, 0, 13, DateTime.Parse(DateTime.Now.ToShortDateString()), para.ReportType); using (DataSubmittedEntities db = new DataSubmittedEntities()) { if (list1 != null && list1.Count > 0) { ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel; //高速 SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString());//小型客车 SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString());//其他客车 SetValue(sheet, 5, 3, pInfo.EnTruk.ToString());//货车数 SetValue(sheet, 5, 4, pInfo.EnGre.ToString());//绿色通道数 SetValue(sheet, 5, 5, pInfo.ExSmaCar.ToString());//小型客车 SetValue(sheet, 5, 6, pInfo.ExOthCar.ToString());//其他客车 SetValue(sheet, 5, 7, pInfo.ExTruk.ToString());//货车数 SetValue(sheet, 5, 8, 0);//pInfo.EnGre.ToString());//绿色通道数 //普通 SetValue(sheet, 5, 9, 0);//小型客车 SetValue(sheet, 5, 10, 0);//其他客车 SetValue(sheet, 5, 11, 0);//高速入境货车数 SetValue(sheet, 5, 12, 0);//绿色通道数 SetValue(sheet, 5, 13, 0);//小型客车 SetValue(sheet, 5, 14, 0);//其他客车 SetValue(sheet, 5, 15, 0);//高速入境货车数 SetValue(sheet, 5, 16, 0);//绿色通道数 } } } return readworkbook; }