/** * 一级权限数据导出 * @return */ @RequestMapping(value = "/getExportData", method = RequestMethod.GET) @ResponseBody //@RequestParam("name") String name, public void getExportData(HttpServletRequest request,HttpServletResponse response){ String filename2 = ""; Sheet sheet = null; Sheet SecondSheet = null; String fileName = "xxxx统计报表" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date().getTime()) + ".xlsx"; SXSSFWorkbook sxworkbook = this.createWookBook(); /** * 权限辨别： * 1.如果数据权限为3 仅导出中层权限报表。 * 2.其他情况直接导出等级权限一二级报表。 * 说明： 网点用户目前无报表导出功能。 */ if("3".equals(String.valueOf(ShiroUtils.getUser().getInsLev()))){ /** * 中层报表。 */ SecondSheet = sxworkbook.createSheet("中层权限报表"); /** * 表头标题行模板设定,写入数据到报表。 */ this.setModelA(SecondSheet.createRow(0), SecondSheet.createRow(1),SecondSheet,aaaaa(serviceName).getExportDataSecond(value), this.setExcelCellStyle(sxworkbook)); }else if("2".equals(String.valueOf(ShiroUtils.getUser().getInsLev()))){ /** * 顶层权限报表1。 */ sheet = sxworkbook.createSheet("顶层权限报表1"); /** * 表头标题行模板设定,写入数据到报表。 */ this.setModelB(sheet.createRow((short)0), sheet.createRow((short)1), sheet, aaaaa(serviceName).getExportData(), this.setExcelCellStyle(sxworkbook)); /** * 顶层权限报表2。 */ SecondSheet = sxworkbook.createSheet("顶层权限报表2"); /** * 表头标题行模板设定,写入数据到报表。 */ this.setModelA(SecondSheet.createRow(0), SecondSheet.createRow(1),SecondSheet,aaaaa(serviceName).getExportDataSecond(null), this.setExcelCellStyle(sxworkbook)); } // 报表单元格合并模板。 this.setMergeCells(sheet, SecondSheet); // 窗格冻结设定 this.setFreezePanes(sheet, SecondSheet); //获取User-agent 当前是哪个浏览器 String header = request.getHeader("User-Agent"); try { filename2 = this.encodeDownloadFilename(fileName,header); response.setHeader("content-disposition", "filename="+filename2); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); sxworkbook.write(response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); }finally{ try { response.getOutputStream().close(); } catch (IOException e) { e.printStackTrace(); } } } /** * 下载文件时，针对不同浏览器，进行附件名的编码 * * @param filename * 下载文件名 * @param agent * 客户端浏览器 * @return 编码后的下载附件名 * @throws IOException */ public static String encodeDownloadFilename(String filename, String agent) throws IOException { // 如果是火狐浏览器 if (agent.contains("Firefox")) { filename = "=?UTF-8?B?" + Base64.getEncoder().encodeToString(filename.getBytes("utf-8")) + "?="; filename = filename.replaceAll(" ", ""); // IE及其他浏览器 } else { filename = URLEncoder.encode(filename, "utf-8"); filename = filename.replace("+"," "); } return filename; } /** * 创建工作簿。 * @return */ public SXSSFWorkbook createWookBook(){ XSSFWorkbook xworkbook = new XSSFWorkbook(); SXSSFWorkbook sxworkbook = new SXSSFWorkbook(xworkbook,100); return sxworkbook; } /** * * 标题行设定,报表数据写入. * @param rowA 标题行（第一行）。 * @param rowB 标题行（第二行）。 * @param sheet 报表页Sheet对象。 * @param data 数据集。 * @param TitleBarstyle 标题行样式对象。 * @param dataStyle 数据行样式对象。 */ public void setModelA(Row rowA,Row rowB,Sheet sheet,List<ExportData> data,CellStyle style){ /** * 创建标题行。 */ Cell cellA1 = rowA.createCell(0); Cell cellA2 = rowA.createCell(3); Cell cellA3 = rowA.createCell(12); Cell cellB0 = rowB.createCell(0); Cell cellB1 = rowB.createCell(1); Cell cellB2 = rowB.createCell(2); Cell cellB3 = rowB.createCell(3); Cell cellB4 = rowB.createCell(4); Cell cellB5 = rowB.createCell(5); Cell cellB6 = rowB.createCell(6); Cell cellB7 = rowB.createCell(7); Cell cellB8 = rowB.createCell(8); Cell cellB9 = rowB.createCell(9); Cell cellB10 = rowB.createCell(10); Cell cellB11 = rowB.createCell(11); Cell cellB12 = rowB.createCell(12); Cell cellB13 = rowB.createCell(13); Cell cellB14 = rowB.createCell(14); Cell cellB15 = rowB.createCell(15); Cell cellB16 = rowB.createCell(16); Cell cellB17 = rowB.createCell(17); Cell cellB18 = rowB.createCell(18); Cell cellB19 = rowB.createCell(19); Cell cellB20 = rowB.createCell(20); /** * 标题行样式设定。 */ cellA1.setCellStyle(style); cellA2.setCellStyle(style); cellA3.setCellStyle(style); cellB0.setCellStyle(style); cellB1.setCellStyle(style); cellB2.setCellStyle(style); cellB3.setCellStyle(style); cellB4.setCellStyle(style); cellB5.setCellStyle(style); cellB6.setCellStyle(style); cellB7.setCellStyle(style); cellB8.setCellStyle(style); cellB9.setCellStyle(style); cellB10.setCellStyle(style); cellB11.setCellStyle(style); cellB12.setCellStyle(style); cellB13.setCellStyle(style); cellB14.setCellStyle(style); cellB15.setCellStyle(style); cellB16.setCellStyle(style); cellB17.setCellStyle(style); cellB18.setCellStyle(style); cellB19.setCellStyle(style); cellB20.setCellStyle(style); /** * 设定标题行内容。 */ cellA1.setCellValue("name"); cellA2.setCellValue("name"); cellA3.setCellValue("name"); cellB0.setCellValue("name"); cellB1.setCellValue("name"); cellB2.setCellValue("name"); cellB3.setCellValue("name"); cellB4.setCellValue("name"); cellB5.setCellValue("name"); cellB6.setCellValue("name"); cellB7.setCellValue("name"); cellB8.setCellValue("name"); cellB9.setCellValue("name"); cellB10.setCellValue("name"); cellB11.setCellValue("name"); cellB12.setCellValue("name"); cellB13.setCellValue("name"); cellB14.setCellValue("name"); cellB15.setCellValue("name"); cellB16.setCellValue("name"); cellB17.setCellValue("name"); cellB18.setCellValue("name"); cellB19.setCellValue("name"); cellB20.setCellValue("name"); // 循环数据进入报表。 if(data != null && data.size() > 0){ for (int h=0; h<data.size(); h++) { /** * 创建数据行。 */ Row row = sheet.createRow((short)h+2); Cell cell0 = row.createCell(0); Cell cell1 = row.createCell(1); Cell cell2 = row.createCell(2); Cell cell3 = row.createCell(3); Cell cell4 = row.createCell(4); Cell cell5 = row.createCell(5); Cell cell6 = row.createCell(6); Cell cell7 = row.createCell(7); Cell cell8 = row.createCell(8); Cell cell9 = row.createCell(9); Cell cell10 = row.createCell(10); Cell cell11 = row.createCell(11); Cell cell12 = row.createCell(12); Cell cell13 = row.createCell(13); Cell cell14 = row.createCell(14); Cell cell15 = row.createCell(15); Cell cell16 = row.createCell(16); Cell cell17 = row.createCell(17); Cell cell18 = row.createCell(18); Cell cell19 = row.createCell(19); Cell cell20 = row.createCell(20); /** * 数据行样式设定。 */ cell0.setCellStyle(style); cell1.setCellStyle(style); cell2.setCellStyle(style); cell3.setCellStyle(style); cell4.setCellStyle(style); cell5.setCellStyle(style); cell6.setCellStyle(style); cell7.setCellStyle(style); cell8.setCellStyle(style); cell9.setCellStyle(style); cell10.setCellStyle(style); cell11.setCellStyle(style); cell12.setCellStyle(style); cell13.setCellStyle(style); cell14.setCellStyle(style); cell15.setCellStyle(style); cell16.setCellStyle(style); cell17.setCellStyle(style); cell18.setCellStyle(style); cell19.setCellStyle(style); cell20.setCellStyle(style); // 设定数据行内容。 cell0.setCellValue(data.get(h).getxname()); cell1.setCellValue(data.get(h).getxname()); cell2.setCellValue(data.get(h).getxname()); cell3.setCellValue(data.get(h).getxname()); cell4.setCellValue(data.get(h).getxname()); cell5.setCellValue(data.get(h).getxname()); cell6.setCellValue(data.get(h).getxname()); cell7.setCellValue(data.get(h).getxname()); cell8.setCellValue(data.get(h).getxname()+"%"); cell9.setCellValue(data.get(h).getxname()+"%"); cell10.setCellValue(data.get(h).getxname()+"%"); cell11.setCellValue(data.get(h).getxname()+"%"); cell12.setCellValue(data.get(h).getxname()); cell13.setCellValue(data.get(h).getxname()); cell14.setCellValue(data.get(h).getxname()); cell15.setCellValue(data.get(h).getxname()); cell16.setCellValue(data.get(h).getxname()); cell17.setCellValue(data.get(h).getxname()+"%"); cell18.setCellValue(data.get(h).getxname()==null?"0%":data.get(h).getxname()+"%"); cell19.setCellValue(data.get(h).getxname()+"%"); cell20.setCellValue(data.get(h).getxname()==""?"0%":data.get(h).getxname()+"%"); } } } /** * 标题行设定,报表数据写入. * @param rowA 标题行（第一行）。 * @param rowB 标题行（第二行）。 * @param sheet 报表页Sheet对象。 * @param data 数据集。 */ public void setModelB(Row rowA,Row rowB,Sheet sheet,List<ExportData> data, CellStyle style) { /** * 标题行创建。 */ Cell cellA1 = rowA.createCell(0); Cell cellA2 = rowA.createCell(1); Cell cellA3 = rowA.createCell(10); Cell cellB1 = rowB.createCell(1); Cell cellB2 = rowB.createCell(2); Cell cellB3 = rowB.createCell(3); Cell cellB4 = rowB.createCell(4); Cell cellB5 = rowB.createCell(5); Cell cellB6 = rowB.createCell(6); Cell cellB7 = rowB.createCell(7); Cell cellB8 = rowB.createCell(8); Cell cellB9 = rowB.createCell(9); Cell cellB10 = rowB.createCell(10); Cell cellB11 = rowB.createCell(11); Cell cellB12 = rowB.createCell(12); Cell cellB13 = rowB.createCell(13); Cell cellB14 = rowB.createCell(14); Cell cellB15 = rowB.createCell(15); Cell cellB16 = rowB.createCell(16); Cell cellB17 = rowB.createCell(17); Cell cellB18 = rowB.createCell(18); /** * 标题行样式设定。 */ cellA1.setCellStyle(style); cellA2.setCellStyle(style); cellA3.setCellStyle(style); cellB1.setCellStyle(style); cellB2.setCellStyle(style); cellB3.setCellStyle(style); cellB4.setCellStyle(style); cellB5.setCellStyle(style); cellB6.setCellStyle(style); cellB7.setCellStyle(style); cellB8.setCellStyle(style); cellB9.setCellStyle(style); cellB10.setCellStyle(style); cellB11.setCellStyle(style); cellB12.setCellStyle(style); cellB13.setCellStyle(style); cellB14.setCellStyle(style); cellB15.setCellStyle(style); cellB16.setCellStyle(style); cellB17.setCellStyle(style); cellB18.setCellStyle(style); // 赋值第一行标题行内容。 cellA1.setCellValue("排名"); cellA2.setCellValue("客户情况"); cellA3.setCellValue("收付汇情况"); // 赋值第二行标题行内容。 cellB1.setCellValue("客户总数"); cellB2.setCellValue("建行收支客户数"); cellB3.setCellValue("工行收支客户数"); cellB4.setCellValue("农行收支客户数"); cellB5.setCellValue("中行收支客户数"); cellB6.setCellValue("我行收支客户占比"); cellB7.setCellValue("工行收支客户占比"); cellB8.setCellValue("农行收支客户占比"); cellB9.setCellValue("中行收支客户占比"); cellB10.setCellValue("总量"); cellB11.setCellValue("建行"); cellB12.setCellValue("工行"); cellB13.setCellValue("农行"); cellB14.setCellValue("中行"); cellB15.setCellValue("我行占比"); cellB16.setCellValue("工行占比"); cellB17.setCellValue("农行占比"); cellB18.setCellValue("中行占比"); // 报表数据写入。 if(data != null && data.size() > 0){ for (int x=0; x<data.size(); x++) { // 创建数据行。 Row row = sheet.createRow((short)x+2); Cell cell0 = row.createCell(0); Cell cell1 = row.createCell(1); Cell cell2 = row.createCell(2); Cell cell3 = row.createCell(3); Cell cell4 = row.createCell(4); Cell cell5 = row.createCell(5); Cell cell6 = row.createCell(6); Cell cell7 = row.createCell(7); Cell cell8 = row.createCell(8); Cell cell9 = row.createCell(9); Cell cell10 = row.createCell(10); Cell cell11 = row.createCell(11); Cell cell12 = row.createCell(12); Cell cell13 = row.createCell(13); Cell cell14 = row.createCell(14); Cell cell15 = row.createCell(15); Cell cell16 = row.createCell(16); Cell cell17 = row.createCell(17); Cell cell18 = row.createCell(18); cell0.setCellStyle(style); cell1.setCellStyle(style); cell2.setCellStyle(style); cell3.setCellStyle(style); cell4.setCellStyle(style); cell5.setCellStyle(style); cell6.setCellStyle(style); cell7.setCellStyle(style); cell8.setCellStyle(style); cell9.setCellStyle(style); cell10.setCellStyle(style); cell11.setCellStyle(style); cell12.setCellStyle(style); cell13.setCellStyle(style); cell14.setCellStyle(style); cell15.setCellStyle(style); cell16.setCellStyle(style); cell17.setCellStyle(style); cell18.setCellStyle(style); /** * 数据行样式设定。 */ cell0.setCellValue(data.get(x).getxname()); cell1.setCellValue(data.get(x).getxname()); cell2.setCellValue(data.get(x).getxname()); cell3.setCellValue(data.get(x).getxname()); cell4.setCellValue(data.get(x).getxname()); cell5.setCellValue(data.get(x).getxname()); cell6.setCellValue(data.get(x).getxname()+"%"); cell7.setCellValue(data.get(x).getxname()+"%"); cell8.setCellValue(data.get(x).getxname()+"%"); cell9.setCellValue(data.get(x).getxname()+"%"); cell10.setCellValue(data.get(x).getxname()); cell11.setCellValue(data.get(x).getxname()); cell12.setCellValue(data.get(x).getxname()); cell13.setCellValue(data.get(x).getxname()); cell14.setCellValue(data.get(x).getxname()); cell15.setCellValue(data.get(x).getxname()+"%"); cell16.setCellValue(data.get(x).getxname()==null?"0%":data.get(x).getxname()+"%"); cell17.setCellValue(data.get(x).getxname()+"%"); cell18.setCellValue(data.get(x).getxname()==""?"0%":data.get(x).getxname()+"%"); } } } /** * 窗格冻结模板设定 * @param sheet1 报表模板A. * @param sheet2 报表模板B. */ public void setFreezePanes(Sheet sheet1 , Sheet sheet2){ // 冻结最左边两列和最上面一行。 if(sheet1 != null){ sheet1.createFreezePane(0,2); } if(sheet2 != null){ sheet2.createFreezePane(0,2); } } /** * 报表单元格合并模板。 * @param sheet1 报表模板A. * @param sheet2 报表模板B. * CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 */ public void setMergeCells(Sheet sheet1 , Sheet sheet2){ if(sheet1 != null){ sheet1.addMergedRegion(new CellRangeAddress(0,1,0,0)); sheet1.addMergedRegion(new CellRangeAddress(0,0,1,9)); sheet1.addMergedRegion(new CellRangeAddress(0,0,10,18)); } if(sheet2 != null){ sheet2.addMergedRegion(new CellRangeAddress(0,0,0,2)); sheet2.addMergedRegion(new CellRangeAddress(0,0,3,11)); sheet2.addMergedRegion(new CellRangeAddress(0,0,12,20)); } } /** * 样式设定. * @param sxworkbook workbook工作簿对象。 * @return */ public CellStyle setExcelCellStyle(SXSSFWorkbook sxworkbook){ // workbook工作簿样式操作对象。 CellStyle style = sxworkbook.createCellStyle(); // 文本样式设定对象。 Font font = sxworkbook.createFont(); // 设定字体样式进入行级。 style.setFont(font); // 字体样式设定：粗体显示。 // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置背景色。 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index); // 设置外边框 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 字体垂直居中设定。 return style; }
-- SQL SELECT COUNT(*)AS TOTALCOUNT , COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)AS XXXCOUNT, COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)AS XXXCOUNT, COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)AS XXXCOUNT, COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)AS XXXCOUNT, CAST(COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)/ COUNT(*) AS DECIMAL(38,4))*100 AS XXXZB, CAST(COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)/ COUNT(*) AS DECIMAL(38,4))*100 AS XXXZB, CAST(COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)/ COUNT(*) AS DECIMAL(38,4))*100 AS XXXZB, CAST(COUNT(CASE WHEN F.XXX>0 THEN F.XXX ELSE NULL END)/ COUNT(*) AS DECIMAL(38,4))*100 AS XXXZB, SUM(F.TOTAL) AS TOTAL, SUM(F.XXX) AS XXX, SUM(F.XXX) AS XXX, SUM(F.XXX) AS XXX, SUM(F.XXX) AS XXX, CASE WHEN SUM(F.TOTAL)>0 THEN CAST((SUM(F.XXX)/SUM(F.TOTAL))*100 AS DECIMAL(38,4)) END AS XXXHLZB, CASE WHEN SUM(F.TOTAL)>0 THEN CAST((SUM(F.XXX)/SUM(F.TOTAL))*100 AS DECIMAL(38,4)) END AS XXXHLZB, CASE WHEN SUM(F.TOTAL)>0 THEN CAST((SUM(F.XXX)/SUM(F.TOTAL))*100 AS DECIMAL(38,4)) END AS XXXHLZB, CASE WHEN SUM(F.TOTAL)>0 THEN CAST((SUM(F.XXX)/SUM(F.TOTAL))*100 AS DECIMAL(38,4)) END AS XXXHLZB FROM XXX F GROUP BY F.XXX