Controller:
public void exportMemberOrderInfo(HttpServletResponse response, @RequestParam(value = "storeName") String storeName, @RequestParam(value = "startTime") String startTime, @RequestParam(value = "endTime") String endTime){ doexport(response,(workbook, out, resp) -> { String fileName = "会员维修记录表" + new String(("-" + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(),"UTF-8"); String encodedFileName = java.net.URLEncoder.encode(fileName,"UTF-8"); response.setHeader("Content-disposition","attachment;fileName=" + encodedFileName + ".xls"); baseService.exportMemberOrderInfo(out, workbook,storeName,startTime,endTime); }); } public void doexport(HttpServletResponse response,Exporter exporter){ ServletOutputStream out; try { HSSFWorkbook workbook = new HSSFWorkbook(); response.setCharacterEncoding("utf-8"); //输出Excel response.setContentType("application/vnd.ms-excel"); out = response.getOutputStream(); exporter.export(workbook, out, response); //将文件输出到客户端浏览器 workbook.write(out); // 释放资源 // Workbook.close(); } catch (Exception e) { e.printStackTrace(); logger.error("error while export", e); } }
service:
/** * 导出会员信息 */ public void exportMemberOrderInfo(ServletOutputStream out, HSSFWorkbook workbook, String storeName,String startTime,String endTime) { try { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(true); HSSFCell cell; //会员列表页 String[] titles = new String[]{"会员ID","会员姓名", "电话号码","会员积分","车牌号", "隶属门店","订单编号","产品名称", "基础服务名称", "产品金额", "工时费", "下单日期", }; //在workbook中添加一个sheet，对应Excel文件中的会员列表页 HSSFSheet hssfSheet = workbook.createSheet("会员维修信息页"); //设置行宽 for (int i = 0; i < titles.length; i++) { //会员维修信息页的行宽 hssfSheet.setColumnWidth(i, 13 * 256); } //在会员维修信息页中添加表头第0行，注意老版本poi对Excel的行数列数有限制short HSSFRow hssfRow = hssfSheet.createRow(0); //创建单元格，并设置值表头，设置表头居中 HSSFCellStyle style = workbook.createCellStyle(); //居中样式 style.setAlignment(HorizontalAlignment.CENTER); //会员订单 HSSFCell hssfCell = null; for (int i = 0; i < titles.length; i++) { //会员维修信息页列索引从0开始 hssfCell = hssfRow.createCell(i); //会员维修信息页列名 hssfCell.setCellValue(titles[i]); //会员维修信息页列居中显示 hssfCell.setCellStyle(style); } //获取会员维修记录数 List<OrderAllInfoVO> list = orderService.MemberOrderInfoByStoreNameAndOrderTime(storeName,startTime,endTime); List<OrderAllInfoVO> listNew=new ArrayList<>(); //获取会员积分 for(OrderAllInfoVO orderAllInfoVO:list){ BaseInfo baseInfo=baseInfoService.selectById(orderAllInfoVO.getMemberId()); if(baseInfo!=null){ orderAllInfoVO.setMemberPoints(baseInfo.getMemberPoints()); } listNew.add(orderAllInfoVO); } //写入实体数据 //大于0表示有订单信息 if (listNew.size() > 0) { int index = 1; int col = 0; //Excel行（+1表示下一行） //循环每个会员信息 int i = 0; for (OrderAllInfoVO orderAllInfoVO : listNew) { //每次每个会员基础信息写入数据完毕则加一行 hssfRow = hssfSheet.createRow(index); //创建单元格，并设置值 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); //会员ID hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getId() == null ? "" : orderAllInfoVO.getId().toString()); //获取会员姓名并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberName() == null ? "" : orderAllInfoVO.getMemberName()); //获取电话号码并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberPhone() == null ? "" : orderAllInfoVO.getMemberPhone()); //获取会员积分并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberPoints() == null ? "" : orderAllInfoVO.getMemberPoints().toString()); //获取车牌号并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getCarNum() == null ? "" : orderAllInfoVO.getCarNum()); //获取隶属门店并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getStoreName() == null ? "" : orderAllInfoVO.getStoreName()); //获取订单编号并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getOrderNum() == null ? "" : orderAllInfoVO.getOrderNum()); //获取产品名称并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getProductName() == null ? "" : orderAllInfoVO.getProductName()); //获取服务名称并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getServicesName() == null ? "" : orderAllInfoVO.getServicesName()); //获取产品金额并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getProductPrice() == null ? "" : orderAllInfoVO.getProductPrice().toString()); //获取工时费并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getHourlyWage() == null ? "" : orderAllInfoVO.getHourlyWage().toString()); //获取下单日期并赋值 hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getOrderTime() == null ? "" : sdf.format(orderAllInfoVO.getOrderTime())); col = 0; index++; } } } catch (Exception e) { e.printStackTrace(); } }
js
open(url)
