xml
<select id="selectShopList" resultType="java.util.HashMap"> SELECT p.PARTY_NAME partyName, c.CUSTOMER_NAME customerName, s.SHOP_ID shopId, s.SHOP_NAME shopName FROM shop_info s,customer_info c,party p WHERE s.CUSTOMER_ID=c.CUSTOMER_ID AND s.PARTY_ID=p.PARTY_ID AND p.COUNTRY_ID=#{countryId} </select> <select id="selectShopRole" resultType="java.util.HashMap"> SELECT u.USER_NAME userName, m.salertype salerType FROM shop_saler_mapping m,user_login u WHERE m.`user_login_id`=u.`USER_LOGIN_ID` AND m.shop_id=#{shopId}
</select>
dao
//获取门店信息 public List<HashMap<String, Object>> selectShopList(@Param("countryId")String countryId) throws Exception; //获取门店中的角色信息 public List<HashMap<String,Object>> selectShopRole(@Param("shopId")String shopId) throws Exception;
service
//导出门店人员信息 public XSSFWorkbook exportShopRole( String[] excelHeader, String title) throws Exception;
service.impl
public XSSFWorkbook exportShopInfo(String searchStr,String conditions, String[] excelHeader, String title) throws Exception { //门店信息集合 List<Shop> list = shopDao.exportShopInfo(searchStr,conditions); int[] excelWidth = {120,120,120,120,120,120,250,120,120,120,120,120,180}; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(title); XSSFRow row = sheet.createRow(0); //导出字体样式 XSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); // 字体大小 //导出样式 XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setFont(font); for (int i = 0; i < excelWidth.length; i++) { sheet.setColumnWidth(i, 32 * excelWidth[i]); } //表头数据 for (int i = 0; i < excelHeader.length; i++) { XSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(style); } //表体数据 for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i+1); Shop shop = list.get(i); //门店名 XSSFCell cell0 = row.createCell(0); cell0.setCellValue(shop.getShopName()); cell0.setCellStyle(style); //客户编码 XSSFCell cell1 = row.createCell(1); cell1.setCellValue(shop.getCustomerCode()); cell1.setCellStyle(style); //门店等级 XSSFCell cell2 = row.createCell(2); if(shop.getLevel()!=null){ if(shop.getLevel().equals("1")){ cell2.setCellValue("A"); }else if(shop.getLevel().equals("2")){ cell2.setCellValue("B"); }else if(shop.getLevel().equals("3")){ cell2.setCellValue("C"); }else if(shop.getLevel().equals("4")){ cell2.setCellValue("D"); }else if(shop.getLevel().equals("5")){ cell2.setCellValue("S"); }else if(shop.getLevel().equals("6")){ cell2.setCellValue("AA"); }else{ cell2.setCellValue(shop.getLevel()); } }else{ cell2.setCellValue(shop.getLevel()); } cell2.setCellStyle(style); //进驻时间 XSSFCell cell3 = row.createCell(3); if(shop.getEnterDate()==null || shop.getEnterDate().equals("")){ cell3.setCellValue(""); }else{ cell3.setCellValue(DateFormatUtils.format(shop.getEnterDate(), "yyyy-MM-dd")); } cell3.setCellStyle(style); //区域 XSSFCell cell4 = row.createCell(4); cell4.setCellValue(shop.getPartyName()); cell4.setCellStyle(style); //省份 XSSFCell cell5 = row.createCell(5); cell5.setCellValue(shop.getProvinceName()); cell5.setCellStyle(style); //地址 XSSFCell cell6 = row.createCell(6); cell6.setCellValue(shop.getDetailAddress()); cell6.setCellStyle(style); //联系人 XSSFCell cell7 = row.createCell(7); cell7.setCellValue(shop.getContactName()); cell7.setCellStyle(style); //联系人电话 XSSFCell cell8 = row.createCell(8); cell8.setCellValue(shop.getPhone()); cell8.setCellStyle(style); //邮箱 XSSFCell cell9 = row.createCell(9); cell9.setCellValue(shop.getEmail()); cell9.setCellStyle(style); //经度 XSSFCell cell10 = row.createCell(10); if( shop.getLng()==null){ cell10.setCellValue(0); }else{ cell10.setCellValue(shop.getLng()); } cell10.setCellStyle(style); //纬度 XSSFCell cell11 = row.createCell(11); if( shop.getLat()==null){ cell11.setCellValue(0); }else{ cell11.setCellValue(shop.getLat()); } cell11.setCellStyle(style); //Location XSSFCell cell12 = row.createCell(12); cell12.setCellValue(shop.getLocation()); cell12.setCellStyle(style); } return workbook; } @Override public XSSFWorkbook exportShopRole( String[] excelHeader, String title) throws Exception { //门店信息 List<HashMap<String, Object>> shopList = shopDao.selectShopList(WebPageUtil.getLoginedUser().getPartyId()); int[] excelWidth = {120,180,180,250,250,250}; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(title); //导出字体样式 XSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); // 字体大小 //导出样式 XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setFont(font); XSSFCellStyle style1 = workbook.createCellStyle(); style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style1.setFillForegroundColor(HSSFColor.GOLD.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style1.setFont(font); for (int i = 0; i < excelWidth.length; i++) { sheet.setColumnWidth(i, 32 * excelWidth[i]); } XSSFRow row = sheet.createRow(0); //表头数据 for (int i = 0; i < excelHeader.length; i++) { XSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(style1); } //左边区域，渠道，门店名 for (int i = 0; i < shopList.size(); i++) { //返回map,每一个shopId对应一个map HashMap<String, Object> shopMap=shopList.get(i); String shopId = shopMap.get("shopId").toString(); //门店id中对应的用户名 List<HashMap<String,Object>> listRole = shopDao.selectShopRole(shopId); String promoter=""; String ywy=""; String dd=""; for (int j = 0; j < listRole.size(); j++) { HashMap<String,Object> shop2 = listRole.get(j); if(shop2.get("salerType").toString().equals("0")) { ywy+=shop2.get("userName")+"/"; } else if(shop2.get("salerType").toString().equals("1")) { promoter+=shop2.get("userName")+"/"; } else if(shop2.get("salerType").toString().equals("2")) { dd+=shop2.get("userName")+"/"; } } //结束循环，结果放在循环外 shopMap.put("promoter", promoter); shopMap.put("ywy", ywy); shopMap.put("dd", dd); } ////////////end/////////// //excel export for (int j = 0; j < shopList.size(); j++) { row = sheet.createRow(j+1); //区域 XSSFCell cell0 = row.createCell(0); cell0.setCellValue(shopList.get(j).get("partyName").toString()); cell0.setCellStyle(style); //渠道 XSSFCell cell1 = row.createCell(1); cell1.setCellValue(shopList.get(j).get("customerName").toString()); cell1.setCellStyle(style); //门店 XSSFCell cell2 = row.createCell(2); cell2.setCellValue(shopList.get(j).get("shopName").toString()); cell2.setCellStyle(style); XSSFCell cell3 = row.createCell(0+3); cell3.setCellValue(shopList.get(j).get("promoter").toString()); cell3.setCellStyle(style); XSSFCell cell4 = row.createCell(0+4); cell4.setCellValue(shopList.get(j).get("ywy").toString()); cell4.setCellStyle(style); XSSFCell cell5 = row.createCell(0+5); cell5.setCellValue(shopList.get(j).get("dd").toString()); cell5.setCellStyle(style); } return workbook; } }
action
//导出门店人员信息 public void exportShopRole(){ try { String title ="Export All Info"; String fileName = getExportExcelName(title); final String userAgent = request.getHeader("USER-AGENT"); if (null != userAgent){ if (-1 != userAgent.indexOf("Firefox")) {//Firefox fileName = new String(fileName.getBytes(), "ISO8859-1"); }else if (-1 != userAgent.indexOf("Chrome")) {//Chrome fileName = new String(fileName.getBytes(), "ISO8859-1"); } else {//IE7+ fileName = URLEncoder.encode(fileName, "UTF-8"); fileName = StringUtils.replace(fileName, "+", "%20");//替换空格 } } else { fileName = fileName; } //国家化列表头 String[] excelHeader = {"Party","Dealer Name","Store Name", "Promoter Name","Sales Man Name","MD Name"}; XSSFWorkbook workbook = shopService.exportShopRole(excelHeader, title); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { e.printStackTrace(); log.error(e.getMessage(),e); } }
