/**
* 一级权限数据导出
* @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("\r\n", "");
// 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
