导出前台
<t:dgToolBar title="导出" icon="fa fa-upload" funname="ExportXldiaodu"></t:dgToolBar>
一、
function ExportXldiaodu(title,url,gname) { gridname=gname; var ids = []; var rows = $("#"+gname).datagrid('getSelections'); for ( var i = 0; i < rows.length; i++) { ids.push(rows[i].id); } var ordercode=$("input[name='crOrderno1']").val();//业务编号 var crOrderstate=$("select[name='crOrderstate']").val();//运输状态 var crNo=$("input[name='crNo']").val();//车队编号 var crEtdGettime_begin=$("input[name='crEtdGettime_begin']").val();//车队编号 var crEtdGettime_end=$("input[name='crEtdGettime_end']").val();//车队编号 var ordertype=$('#crPcname1').val();//业务类型 var crInno=$("input[name='crInno']").val();//进仓编号 var orderman=$('#crOrderFromcompany').val();//委托单位 var crBoxbillno = $("input[name='crBoxbillno']").val();//提单号 var ordertype2=$('#crPcname2').val();//业务类型安顺 var busname =$("select[name='busname']").val();//业务性质 var createDatebegin = $("input[name='createDate_begin']").val();//下单开始时间 var createDateend = $("input[name='createDate_end']").val();//下单结束时间 var crFromDispatcher = $("select[name='crFromDispatcher']").val();//接单人 var createBy = $("select[name='createBy']").val();//下单人 var updateName = $("select[name='updateName']").val();//更新人 var crGettime_begin = $("input[name='crGettime_begin']").val(); //实际提货时间开始 var crGettime_end = $("input[name='crGettime_end']").val();//实际提货时间结束 var crTxweituono = $("input[name='crTxweituono']").val();//提货单号 var crSumitstate = $("input[name='crSumitstate']").val();//提箱委托单号 var crFromCarno = $("input[name='crFromCarno']").val();//提货车牌 JeecgExcelExport("carRouteController.do?exportXlsthqddiaodu&ids="+ids+"&ordercode="+ordercode+'&crOrderstate='+crOrderstate
+'&crNo='+crNo+'&crEtdGettime_begin='+crEtdGettime_begin+'&crEtdGettime_end='+crEtdGettime_end+'&ordertype='+ordertype
+'&crInno='+crInno+'&orderman='+orderman+'&crBoxbillno='+crBoxbillno+'&ordertype2='+ordertype2+'&busname='+busname
+'&createDatebegin='+createDatebegin+'&createDateend='+createDateend+'&crFromDispatcher='+crFromDispatcher
+'&createBy ='+createBy+'&updateName='+updateName+'&type=6'+'&crGettime_begin='+crGettime_begin+'&crGettime_end='+crGettime_end
+'&crTxweituono='+crTxweituono+'&crSumitstate='+crSumitstate+'&crFromCarno='+crFromCarno , "carRouteList"); }
二、
function newbaoguan(title,url, id,width,height,isRestful) { gridname=id; var rowsData = $('#'+id).datagrid('getSelections'); if (!rowsData || rowsData.length==0) { tip('请选择导出项目'); return; } if (rowsData.length>1) { tip('请选择一条记录再导出'); return; } if (rowsData.length==1) { JeecgExcelExport("decMainController.do?newbaoguan&ids="+rowsData[0].id,"decMainList"); } }
后台代码
一、
/** * 导出提货一览表动作 */ public void ExportXlthylbOut(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook work, List<Map<String, Object>> list, String sysdate) throws ParseException { String cr_From_Carno_last = ""; int cnt = 0; SimpleDateFormat formatter = new SimpleDateFormat("HH:mm"); String dateString =""; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date dBegin = sdf.parse(sysdate); Calendar c = Calendar.getInstance();//可以对每个时间域单独修改 c.setTime(dBegin); int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONDAY)+1; int weekday = c.get(Calendar.DAY_OF_WEEK); int day = c.get(Calendar.DATE); String week =""; if (weekday == 1) { week = "周日"; } else if (weekday == 2) { week = "周一"; } else if (weekday == 3) { week = "周二"; } else if (weekday == 4) { week = "周三"; } else if (weekday == 5) { week = "周四"; } else if (weekday == 6) { week = "周五"; } else if (weekday == 7) { week = "周六"; } XSSFSheet sheetAt = work.createSheet(year+"."+month+"."+day); //第一样式（大标题） XSSFCellStyle setBorder = work.createCellStyle(); setBorder.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 XSSFFont font = work.createFont(); font.setFontHeightInPoints((short) 16);//设置字体大小 setBorder.setFont(font);//选择需要用到的字体格式 //第二样式 XSSFCellStyle setBorder1 = work.createCellStyle(); setBorder1.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);// 设置背景色 setBorder1.setFillPattern(setBorder1.SOLID_FOREGROUND); setBorder1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder1.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder1.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 //第二样式2 XSSFCellStyle setBorder2 = work.createCellStyle(); setBorder2.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置背景色 setBorder2.setFillPattern(setBorder2.SOLID_FOREGROUND); setBorder2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder2.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder2.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 //第三样式（背景色） XSSFCellStyle setBorder3 = work.createCellStyle(); setBorder3.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景色 setBorder3.setFillPattern(setBorder3.SOLID_FOREGROUND); setBorder3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder3.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder3.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 //第四样式（背景色） XSSFCellStyle setBorder4 = work.createCellStyle(); setBorder4.setFillForegroundColor(HSSFColor.GREEN.index);// 设置背景色 setBorder4.setFillPattern(setBorder4.SOLID_FOREGROUND); setBorder4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder4.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder4.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 //第五样式（背景色） XSSFCellStyle setBorder5 = work.createCellStyle(); setBorder5.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);// 设置背景色 setBorder5.setFillPattern(setBorder5.SOLID_FOREGROUND); setBorder5.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder5.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder5.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder5.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder5.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 //第六样式 XSSFCellStyle setBorder6 = work.createCellStyle(); setBorder6.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);// 设置背景色 setBorder6.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder6.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder6.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder6.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder6.setAlignment(setBorder.ALIGN_CENTER); // 水平居中 setBorder6.setVerticalAlignment(setBorder.VERTICAL_CENTER);//垂直居中 XSSFRow row1 = sheetAt.createRow(1); row1.createCell(1).setCellValue(""); row1.getCell(1).setCellStyle(setBorder3); row1.createCell(2).setCellValue("已录系统"); row1.getCell(2).setCellStyle(setBorder3); CellRangeAddress region = new CellRangeAddress(1,3, (short) 3, (short) 14); sheetAt.addMergedRegion(region); row1.createCell(3).setCellValue("安顺公司运输业务统计一览表("+year+"."+month+"."+day+""+week+")"); row1.getCell(3).setCellStyle(setBorder); row1 = sheetAt.createRow(2); row1.createCell(1).setCellValue(""); row1.getCell(1).setCellStyle(setBorder4); row1.createCell(2).setCellValue("新增"); row1.getCell(2).setCellStyle(setBorder4); row1 = sheetAt.createRow(3); row1.createCell(1).setCellValue(""); row1.getCell(1).setCellStyle(setBorder5); row1.createCell(2).setCellValue("备注"); row1.getCell(2).setCellStyle(setBorder5); row1 = sheetAt.createRow(4); 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("客户"); row1.createCell(11).setCellValue("箱号"); row1.createCell(12).setCellValue("收箱"); row1.createCell(13).setCellValue("备注"); row1.createCell(14).setCellValue("调度备注"); for(int z=1;z<=14;z++){ row1.getCell(z).setCellStyle(setBorder6); } RegionUtil.setBorderTop(1, region, sheetAt, work); RegionUtil.setBorderBottom(1, region, sheetAt, work); RegionUtil.setBorderLeft(1, region, sheetAt, work); RegionUtil.setBorderRight(1, region, sheetAt, work); Boolean flag = false; Boolean flag_old = false; for(int i=0;i<list.size();i++){ Map<String, Object> map = new HashMap<>(); if(i<list.size()){ map = list.get(i); } /* * 从第六行开始赋值 */ XSSFRow row6 = sheetAt.createRow(i+5); String car_name = String.valueOf(map.get("car_name")); //车牌号 if(map.get("car_name")!=null&&!"".equals(car_name)) { if(car_name.equals(cr_From_Carno_last)){ cnt++; if(i==list.size()-1){ //当最后一行时看情况合并 CellRangeAddress region1 = new CellRangeAddress(i-cnt+5, i+5, (short) 1, (short) 1); sheetAt.addMergedRegion(region1); } flag = flag_old; }else{ if(cnt>0){ //合并单元格 CellRangeAddress region1 = new CellRangeAddress(i+5-cnt-1, i-1+5, (short) 1, (short) 1); sheetAt.addMergedRegion(region1); } if(flag){ flag=false; }else{ flag=true; } cnt=0; } row6.createCell(1).setCellValue(car_name); }else { row6.createCell(1).setCellValue(""); } //将现在的提货编号赋值 cr_From_Carno_last=car_name; //提货司机 String cr_from_driver_name = String.valueOf(map.get("cr_from_driver_name")); if(map.get("cr_from_driver_name")!=null&&!"".equals(cr_from_driver_name)) { row6.createCell(2).setCellValue(cr_from_driver_name); }else { row6.createCell(2).setCellValue(""); } //货主单位 String cr_company = String.valueOf(map.get("cr_company")); if(map.get("cr_company") !=null && !"".equals(cr_company)) { row6.createCell(3).setCellValue(cr_company); }else { row6.createCell(3).setCellValue(""); } //车次 String cr_cnt = String.valueOf(map.get("cr_cnt")); if(map.get("cr_cnt") !=null && !"".equals(cr_cnt)) { row6.createCell(4).setCellValue(cr_cnt); }else { row6.createCell(4).setCellValue(""); } //箱量 String cr_Boxtype = String.valueOf(map.get("cr_Boxtype")); if(map.get("cr_Boxtype") !=null&&!"".equals(cr_Boxtype)) { row6.createCell(5).setCellValue(cr_Boxtype); }else { row6.createCell(5).setCellValue(""); } //类型 String order_busname = String.valueOf(map.get("order_busname")); if(map.get("order_busname")!=null&&!"".equals(order_busname)) { row6.createCell(6).setCellValue(order_busname); }else { row6.createCell(6).setCellValue(""); } //时间 if(map.get("cr_Gettime") !=null) { row6.createCell(7).setCellValue( String.valueOf(map.get("cr_Gettime"))); }else { row6.createCell(7).setCellValue(""); } //委托号 String cr_Sumitstate = String.valueOf(map.get("cr_Sumitstate")); if(map.get("cr_Sumitstate") !=null) { row6.createCell(8).setCellValue(cr_Sumitstate); }else { row6.createCell(8).setCellValue(""); } //业务编号 String cr_orderno = String.valueOf(map.get("cr_orderno")); if(map.get("cr_orderno") !=null) { row6.createCell(9).setCellValue(cr_orderno); }else { row6.createCell(9).setCellValue(""); } //客户 String cr_order_company_value = String.valueOf(map.get("cr_order_company_value")); if(map.get("cr_order_company_value") !=null) { row6.createCell(10).setCellValue(cr_order_company_value); }else { row6.createCell(10).setCellValue(""); } //箱号 String cr_Boxno = String.valueOf(map.get("cr_Boxno")); if(map.get("cr_Boxno") !=null) { row6.createCell(11).setCellValue(cr_Boxno); }else { row6.createCell(11).setCellValue(""); } //是否收箱 String cr_sbox = String.valueOf(map.get("cr_sbox")); if(map.get("cr_sbox") !=null) { row6.createCell(12).setCellValue(cr_sbox); }else { row6.createCell(12).setCellValue(""); } //备注 String cr_from_comment = String.valueOf(map.get("cr_from_comment")); if(map.get("cr_from_comment") !=null) { row6.createCell(13).setCellValue(cr_from_comment); }else { row6.createCell(13).setCellValue(""); } //调度备注 String cr_remmark = String.valueOf(map.get("cr_remmark")); if(map.get("cr_remmark") !=null) { row6.createCell(14).setCellValue(cr_remmark); }else { row6.createCell(14).setCellValue(""); } for(int j=1;j<=14;j++){ if(flag){ row6.getCell(j).setCellStyle(setBorder1); }else{ row6.getCell(j).setCellStyle(setBorder2); } } flag_old = flag; } }
二、
/** * 导出 托运单 */ @RequestMapping(params = "ExportHjyTyd") @ResponseBody public void ExportHjyTyd(HttpServletRequest request, ModelMap modelMap, HttpServletResponse response, OutputStream output) throws UnsupportedEncodingException { Workbook tempWorkBook = null; String id = request.getParameter("id");//业务编号 String ids = request.getParameter("ids");//选择框 String wheresql = " "; if(id!=null && !id.equals("")) { wheresql += " and a.id='" + id + "'"; } if(ids !=null && !ids.equals("")) { wheresql += " and b.id='" + ids + "'"; } String sql = " select a.* ,b.*,sysdate() sysdate " + " from ldc_order a,car_route b" + " where a.order_code = b.cr_orderno " + wheresql + " order by b.create_date desc "; String lujing = request.getSession().getServletContext().getRealPath("/"); String lujing1 = lujing + "export\\template\\exporthjytyd.xlsx"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); InputStream in; try { in = new FileInputStream(new File(lujing1)); XSSFWorkbook work = null; work = new XSSFWorkbook(in); this.ExportHjyTydOut(request, response, work, maps); } catch (Exception e) { e.printStackTrace(); } } /** * 导出托运单动作 */ public void ExportHjyTydOut(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook work, List<Map<String, Object>> list) { XSSFSheet sheetAt = work.getSheetAt(0); XSSFRow row1 = sheetAt.getRow(1); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); String dateString =""; Map<String, Object> map = new HashMap<>(); map = list.get(0); /* * 从第三行开始赋值 */ XSSFRow row3 = sheetAt.getRow(2); //发货日期 if(map.get("create_date") !=null) { Date date = (Date) map.get("create_date"); dateString = formatter.format(date); row3.getCell(2).setCellValue(dateString); }else { row3.getCell(2).setCellValue(""); } //导出日期 String qq = formatter.format(map.get("sysdate")); row3.getCell(7).setCellValue(formatter.format(map.get("sysdate"))); //业务编号 if(map.get("order_code") !=null) { row3.getCell(12).setCellValue(String.valueOf(map.get("order_code"))); }else { row3.getCell(12).setCellValue(""); } /* * 从第四行开始赋值 */ XSSFRow row4 = sheetAt.getRow(3); //提货联系人 if(map.get("cr_Fromperson") !=null) { row4.getCell(2).setCellValue(String.valueOf(map.get("cr_Fromperson"))); }else { row4.getCell(2).setCellValue(""); } //提货联系人电话 if(map.get("cr_Frompersontel") !=null) { row4.getCell(5).setCellValue(String.valueOf(map.get("cr_Frompersontel"))); }else { row4.getCell(5).setCellValue(""); } //提货地址 if(map.get("cr_Fromplace_Value") !=null) { row4.getCell(8).setCellValue(String.valueOf(map.get("cr_Fromplace_Value"))); }else { row4.getCell(8).setCellValue(""); } /* * 从第5行开始赋值 */ XSSFRow row5 = sheetAt.getRow(4); //送货联系人 if(map.get("cr_Toperson") !=null) { row5.getCell(2).setCellValue(String.valueOf(map.get("cr_Toperson"))); }else { row5.getCell(2).setCellValue(""); } //送货联系人电话 if(map.get("cr_Topersontel") !=null) { row5.getCell(5).setCellValue(String.valueOf(map.get("cr_Topersontel"))); }else { row5.getCell(5).setCellValue(""); } //送货地址 if(map.get("cr_Toplace_Value") !=null) { row5.getCell(8).setCellValue(String.valueOf(map.get("cr_Toplace_Value"))); }else { row5.getCell(8).setCellValue(""); } /* * 从第6行开始赋值 orderFk */ XSSFRow row6 = sheetAt.getRow(5); //付款方式 if(map.get("cr_Proteacty") !=null) { row6.getCell(10).setCellValue(String.valueOf(map.get("cr_Proteacty"))); }else { row6.getCell(10).setCellValue(""); } //交货方式 if(map.get("order_Licenceno") !=null) { row6.getCell(13).setCellValue(String.valueOf(map.get("order_Licenceno"))); }else { row6.getCell(13).setCellValue(""); } /* * 从第7行开始赋值 */ XSSFRow row7 = sheetAt.getRow(6); //货物 if(map.get("order_Goods") !=null) { row7.getCell(1).setCellValue(String.valueOf(map.get("order_Goods"))); }else { row7.getCell(1).setCellValue(""); } //件数 if(map.get("order_Number") !=null) { row7.getCell(2).setCellValue(String.valueOf(map.get("order_Number"))); }else { row7.getCell(2).setCellValue(""); } //包装 if(map.get("order_Bz") !=null && !"".equals(map.get("order_Bz"))) { String sql = " select typename from t_s_type " + " where typegroupid=(select id from t_s_typegroup where typegroupcode='orderbz')" + " and typecode = '"+(String)map.get("order_Bz")+"'"; row7.getCell(3).setCellValue(String.valueOf(map.get("order_Bz"))); }else { row7.getCell(3).setCellValue(""); } //重量（KG） if(map.get("order_Gross_Weight") !=null) { row7.getCell(4).setCellValue(String.valueOf(map.get("order_Gross_Weight"))); }else { row7.getCell(4).setCellValue(""); } //尺寸 if(map.get("order_Size") !=null) { row7.getCell(5).setCellValue(String.valueOf(map.get("order_Size"))); }else { row7.getCell(5).setCellValue(""); } /* * 从第9行开始赋值 */ XSSFRow row9 = sheetAt.getRow(8); //备注 if(map.get("cr_From_Comment") !=null) { row9.getCell(12).setCellValue(String.valueOf(map.get("cr_From_Comment"))); }else { row9.getCell(12).setCellValue(""); } //前台反应 response.reset(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); //保证不乱码 try { Date date=new Date(); SimpleDateFormat format=new SimpleDateFormat("MMddHHmmss"); String time="bb"+format.format(date)+".xlsx"; response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(time.getBytes("utf-8"), "ISO-8859-1")); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } try { ByteArrayOutputStream oss =new ByteArrayOutputStream(); OutputStream os = response.getOutputStream(); work.write(oss); byte temp[] = oss.toByteArray(); ByteArrayInputStream in1 = new ByteArrayInputStream(temp); int n = 0; while ((n = in1.read(temp)) >0) { os.write(temp, 0, n); } os.flush(); os.close(); } catch(Exception e){ e.printStackTrace(); } }