1、@RequestMapping(value = "/exportScale", method = RequestMethod.GET) public String exportScale(String ids,String startTime,String endTime, String shipperName, String cargoName, String carNo, String planNo, String getowner,String phzName, String mtCode,String kq, HttpSession session,HttpServletRequest request,HttpServletResponse response){ String[] cellname={"编号","车牌号","是否超载","皮重","皮重时间","毛重","毛重时间","净重", "货物名称","计划号","发货人","收货人","车队","作业区域","库区"}; String[] keyList={"car_no","is_overload","tare_weight","tare_time","gross_weight","gross_time", "net_weight","cargo_name","plan_no","shipper_name","getowner","phz_name","name","depot_name"}; List<Integer> depotList = new ArrayList<Integer>(); //如果库区为空,则选择为全部,取该用户所属部门下拥有的库区权限 if(StringUtils.isEmpty(kq)){ Map<String, Object> map = getBackUserDepot(); depotList = (List<Integer>) map.get("idList"); }else{ depotList.add(Integer.valueOf(kq)); } List<Map<String, Object>> list = backBillPcService.exportScale(ids,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); try { String exportFileName = "过衡数据信息"; response.setContentType("application/vnd.ms-excel"); //根据浏览器类型处理文件名称 String agent = request.getHeader("USER-AGENT").toLowerCase(); if (agent.indexOf("firefox") > -1){//若是火狐 exportFileName = new String(exportFileName.getBytes("UTF-8"), "ISO8859-1"); } else {//其他浏览器 exportFileName = java.net.URLEncoder.encode(exportFileName, "UTF-8"); } OutputStream out = response.getOutputStream(); response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName + ".xls"); if (list != null) { ExportExcelUtil.exportExcel("过衡数据信息", cellname, list, keyList, out); } out.flush(); out.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }
public static void exportExcel(String title, String[] headers, List<Map<String, Object>> dataset, String[] keyList, OutputStream out) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((int) 16); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 // HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 // HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 // comment.setString(new HSSFRichTextString("可以在POI中添加注释!")); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. // comment.setAuthor("leno"); //产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } //循环放置表格中的值 for(int i = 0; i<dataset.size(); i++){ row = sheet.createRow(i + 1); //产生编号,1,2,3,4,5...的递增编号,不需要,header去掉编号,这里注释掉就可以 row.createCell(0).setCellValue(i+1+""); Map<String, Object> obj=dataset.get(i); for(int j = 0; j < keyList.length; j ++){ if(obj.get(keyList[j]) != null){ row.createCell(j+1).setCellValue(obj.get(keyList[j])+""); } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } }
@Override public List<Map<String, Object>> exportScale(String ids, String startTime, String endTime, String shipperName, String cargoName, String carNo, String planNo, String getowner, String phzName, String mtCode, List<Integer> depotList) { String[] idArray = null; if(ids != null){ idArray = ids.split(","); } List<Map<String, Object>> list = dbcOilBillMapper.exportScale(idArray,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); return list; }
<select id="exportScale" resultType="java.util.HashMap"> select a.id,a.plan_no,a.tare_weight,a.tare_time,a.gross_weight,a.gross_time,a.net_weight, CASE a.is_overload WHEN '0' THEN '未超载' WHEN '1' then '已超载' end as is_overload, b.shipper_name,b.getowner,dd.`name`,b.cargo_name, c.car_no, d.phz_name , e.depot_name from dbc_oil_bill a left join dbc_oil_plan b on a.plan_id = b.id left join dbc_car c on a.head_car_id = c.id left join dbc_oil_plan_split d on a.split_id = d.id left join dbc_district dd on b.mt_code = dd.code left join dbc_oil_depot e on b.depot_code=e.code <where> a.if_del='0' <if test="idArray != null and idArray.length > 0"> and a.id IN <foreach collection="idArray" item="id" index="index" open="(" separator="," close=")"> #{id} </foreach> </if> and e.id in <foreach collection="depotList" item="depotId" index="index" open="(" separator="," close=")"> ${depotId} </foreach> <if test="planNo != null"> and a.plan_no like concat ('%',#{planNo},'%') </if> <if test="shipperName != null"> and b.shipper_name like concat ('%',#{shipperName},'%') </if> <if test="phzName != null"> and d.phz_name like concat ('%',#{phzName},'%') </if> <if test="cargoName != null"> and b.cargo_name like concat ('%',#{cargoName},'%') </if> <if test="carNo != null"> and c.car_no like concat ('%',#{carNo},'%') </if> <if test="getowner != null"> and b.getowner like concat ('%',#{getowner},'%') </if> <if test="mtCode != null"> and b.mt_code =#{mtCode} </if> <if test="startTime != null"> and a.gross_time >= #{startTime} </if> <if test="endTime != null"> and a.gross_time <= #{endTime} </if> </where> order by a.plan_no desc </select>
2、带统计的Excel@RequestMapping(value = "/exportScale", method = RequestMethod.GET) public String exportScale(String ids,String startTime,String endTime, String shipperName, String cargoName, String carNo, String planNo, String getowner,String phzName, String mtCode,String kq, HttpSession session,HttpServletRequest request,HttpServletResponse response){ String[] cellname={"编号","车牌号","是否超载","皮重","皮重时间","毛重","毛重时间","净重", "货物名称","计划号","发货人","收货人","车队","作业区域","库区"}; HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet(new Date().getTime()+"report"); HSSFCell cell; HSSFCellStyle stycle = hwb.createCellStyle(); stycle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stycle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stycle.setBorderBottom(HSSFCellStyle.BORDER_THIN); stycle.setBorderLeft(HSSFCellStyle.BORDER_THIN); stycle.setBorderRight(HSSFCellStyle.BORDER_THIN); stycle.setBorderTop(HSSFCellStyle.BORDER_THIN); stycle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = hwb.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 stycle.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = hwb.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = hwb.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); HSSFRow row = sheet.createRow(1); for (int i = 0; i < cellname.length; i++) { cell = row.createCell(i); cell.setCellValue(cellname[i]); cell.setCellStyle(stycle); sheet.setColumnWidth((short) i, cellname[i].getBytes().length * 400); } List<Integer> depotList = new ArrayList<Integer>(); //如果库区为空,则选择为全部,取该用户所属部门下拥有的库区权限 if(StringUtils.isEmpty(kq)){ Map<String, Object> map = getBackUserDepot(); depotList = (List<Integer>) map.get("idList"); }else{ depotList.add(Integer.valueOf(kq)); } Map<String, Object> map = backBillPcService.getTotal(ids,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); List<Map<String, Object>> list = backBillPcService.exportScale(ids,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); if (list != null) { for (int j = 1; j < list.size(); j++) { row = sheet.createRow(j + 1); row.createCell(0).setCellValue(j+1+""); Map<String, Object> obj=list.get(j); if(obj.get("car_no") != null){ row.createCell(1).setCellValue(obj.get("car_no")+""); } if(obj.get("is_overload") != null){ row.createCell(2).setCellValue(obj.get("is_overload")+""); } if(obj.get("tare_weight") != null){ row.createCell(3).setCellValue(obj.get("tare_weight")+""); } if(obj.get("tare_time") != null){ row.createCell(4).setCellValue(obj.get("tare_time")+""); } if(obj.get("gross_weight") != null){ row.createCell(5).setCellValue(obj.get("gross_weight")+""); } if(obj.get("gross_time") != null){ row.createCell(6).setCellValue(obj.get("gross_time")+""); } if(obj.get("net_weight") != null){ row.createCell(7).setCellValue(obj.get("net_weight")+""); } if(obj.get("cargo_name") != null){ row.createCell(8).setCellValue(obj.get("cargo_name")+""); } if(obj.get("plan_no") != null){ row.createCell(9).setCellValue(obj.get("plan_no")+""); } if(obj.get("shipper_name") != null){ row.createCell(10).setCellValue(obj.get("shipper_name")+""); } if(obj.get("getowner") != null){ row.createCell(11).setCellValue(obj.get("getowner")+""); } if(obj.get("phz_name") != null){ row.createCell(12).setCellValue(obj.get("phz_name")+""); } if(obj.get("name") != null){ row.createCell(13).setCellValue(obj.get("name")+""); } if(obj.get("depot_name") != null){ row.createCell(14).setCellValue(obj.get("depot_name")+""); } } row = sheet.createRow(0); row.createCell(0).setCellValue("总计"); row.createCell(1).setCellValue("车数:"+map.get("carNum")+"(辆)"); row.createCell(3).setCellValue("皮重:"+map.get("tareWeight")+"(吨)"); row.createCell(5).setCellValue("毛重:"+map.get("grossWeight")+"(吨)"); row.createCell(7).setCellValue("净重:"+map.get("netWeight")+"(吨)"); } try { String exportFileName = "过衡数据查询统计"; response.setContentType("application/vnd.ms-excel"); //根据浏览器类型处理文件名称 String agent = request.getHeader("USER-AGENT").toLowerCase(); if (agent.indexOf("firefox") > -1){//若是火狐 exportFileName = new String(exportFileName.getBytes("UTF-8"), "ISO8859-1"); } else {//其他浏览器 exportFileName = java.net.URLEncoder.encode(exportFileName, "UTF-8"); } response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName + ".xls"); OutputStream out = response.getOutputStream(); hwb.write(out); out.flush(); out.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }
@Override public Map<String, Object> getTotal(String ids, String startTime, String endTime, String shipperName, String cargoName, String carNo, String planNo, String getowner, String phzName, String mtCode, List<Integer> depotList) { String[] idArray = null; if(ids != null){ idArray = ids.split(","); } Map<String, Object> map = dbcOilBillMapper.getTotal(idArray,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); return map; }
@Override public List<Map<String, Object>> exportScale(String ids, String startTime, String endTime, String shipperName, String cargoName, String carNo, String planNo, String getowner, String phzName, String mtCode, List<Integer> depotList) { String[] idArray = null; if(ids != null){ idArray = ids.split(","); } List<Map<String, Object>> list = dbcOilBillMapper.exportScale(idArray,startTime,endTime,shipperName,cargoName,carNo,planNo,getowner,phzName,mtCode,depotList); return list; }
<select id="getTotal" resultType="java.util.HashMap"> select count(a.id) carNum, SUM(a.tare_weight) tareWeight, SUM(a.gross_weight) grossWeight,SUM(a.net_weight) netWeight from dbc_oil_bill a left join dbc_oil_plan b on a.plan_id = b.id left join dbc_car c on a.head_car_id = c.id left join dbc_oil_plan_split d on a.split_id = d.id left join dbc_district dd on b.mt_code = dd.code left join dbc_oil_depot e on b.depot_code=e.code <where> a.if_del='0' <if test="idArray != null and idArray.length > 0"> and a.id IN <foreach collection="idArray" item="id" index="index" open="(" separator="," close=")"> #{id} </foreach> </if> and e.id in <foreach collection="depotList" item="depotId" index="index" open="(" separator="," close=")"> ${depotId} </foreach> <if test="planNo != null"> and a.plan_no like concat ('%',#{planNo},'%') </if> <if test="shipperName != null"> and b.shipper_name like concat ('%',#{shipperName},'%') </if> <if test="phzName != null"> and d.phz_name like concat ('%',#{phzName},'%') </if> <if test="cargoName != null"> and b.cargo_name like concat ('%',#{cargoName},'%') </if> <if test="carNo != null"> and c.car_no like concat ('%',#{carNo},'%') </if> <if test="getowner != null"> and b.getowner like concat ('%',#{getowner},'%') </if> <if test="mtCode != null"> and b.mt_code =#{mtCode} </if> <if test="startTime != null"> and a.gross_time >= #{startTime} </if> <if test="endTime != null"> and a.gross_time <= #{endTime} </if> </where> </select>
Excel表格的项目demo
最新推荐文章于 2022-11-24 11:28:49 发布