dao
service
//导出印尼目标 public XSSFWorkbook exportTargetExcel(@Param("searchStr")String searchStr,@Param("conditions")String conditions,String[] excelHeader,String excelHeader1,String excelHeader2,String title) throws Exception; service.impl
@Override public XSSFWorkbook exportTargetExcel(String searchStr, String conditions, String[] excelHeader, String excelHeader1,String excelHeader2, String title) throws Exception { //查询门店以及门店整体目标 List list = indontargetDao.selectShopList(searchStr,conditions); //分公司的所有关键机型 List ModelKeyList = indontargetDao.selectKeyModel(WebPageUtil.getLoginedUser().getPartyId()); //查询门店关键机型目标 List ModelList = indontargetDao.selectTargetModel(searchStr, conditions); // SimpleDateFormat format = new SimpleDateFormat("MM/yyyy");// Date date =new Date();// // String d = format.format(date);// System.out.println(d+"-------------9999----------------");// Date dt = format.parse(d);// System.out.println(d+"-------------9999----------------"); int[] excelWidth = { 120,120,120,120}; 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.setFont(font);// style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框// style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框// style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框// style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style1.setFillForegroundColor(HSSFColor.PALE_BLUE.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); sheet.createFreezePane(2,0,2,0);//锁定参数1为起始列，参数2为起始行，3结整列，4为结束行 sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));// 起始行号，终止行号， 起始列号，终止列号 sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));// 起始行号，终止行号， 起始列号，终止列号 sheet.addMergedRegion(new CellRangeAddress(0,0,3,ModelKeyList.size()+2));// 起始行号，终止行号， 起始列号，终止列号 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); } XSSFRow row1 = sheet.createRow(1); XSSFCell cell1 = row1.createCell(2); cell1.setCellValue(excelHeader1); cell1.setCellStyle(style1); XSSFCell cell10 = row.createCell(3); cell10.setCellValue(excelHeader2); cell10.setCellStyle(style1); //门店整体目标 动态左边门店 for (int a = 0; a < list.size(); a++) { row = sheet.createRow(a+2); IndonTarget indTarget = list.get(a); //获得 当前的门店ID String shop=indTarget.getShopId()+""; if(indTarget.getShopName()!=null && indTarget.getShopName()!=""){ XSSFCell cell = row.createCell(0); cell.setCellValue(indTarget.getShopName()); cell.setCellStyle(style); } XSSFCell cell = row.createCell(1); cell.setCellValue(indTarget.getDatadate()); cell.setCellStyle(style); XSSFCell cell2 = row.createCell(2); cell2.setCellValue(indTarget.getQuantity()); cell2.setCellStyle(style); //所有分公司关键机型 动态头部机型 for (int i = 0; i < ModelKeyList.size(); i++) { IndonTarget keyModel = ModelKeyList.get(i); //获得当前型号 String key=keyModel.getModel(); XSSFCell cell0 = row1.createCell(i+3); cell0.setCellValue(key); cell0.setCellStyle(style1); XSSFCell cell3 = row.createCell(i+3); //分公司某个关键机型目标 for (int j = 0; j < ModelList.size(); j++) { IndonTarget indTargetData = ModelList.get(j); String thisShop=indTargetData.getShopId()+""; if(thisShop.equals(shop) && indTargetData.getModel().equals(key) ){ cell3.setCellValue(indTargetData.getQuantity()); } } cell3.setCellStyle(style); } } return workbook; } action
public void exportTargetExcel(){ try { String title="Indonesia TV Target"; String fileName = title+".xlsx"; 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 datadate = request.getParameter("datadate"); String shop = request.getParameter("shopName"); SimpleDateFormat dfd = new SimpleDateFormat("yyyy-MM");// 设置日期格式 Date d = new Date(); String dt = dfd.format(d); String searchStr = "1 = 1"; if(shop!=null && !shop.equals("")){ shop=shop.replace("\'", "\\'"); searchStr += " and si.shop_name like ('%"+shop+"%')"; } if(datadate!=null && !datadate.equals("")){ searchStr += " and date_format(t.datadate,'%Y-%m') ='"+datadate+"'"; }else{ searchStr += " and date_format(t.datadate,'%Y-%m') ='"+dt+"'"; } //权限 String userPartyIds = WebPageUtil.loadPartyIdsByUserId(); String conditions = ""; if(!WebPageUtil.isHAdmin()){ if(null!=userPartyIds && !"".equals(userPartyIds)){ conditions +=" si.COUNTRY_ID in ("+userPartyIds+")"; }else { conditions += " 1=2 "; } }else{ conditions += " 1=1 "; } String[] excelHeader={ "*Shop Name","*Month","Total Target" }; String excelHeader1= "*Quantity" ; String excelHeader2= "*Model Target" ; XSSFWorkbook workbook =indonTargetServce.exportTargetExcel(conditions,searchStr, excelHeader,excelHeader1,excelHeader2, 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(); } }