@RequestMapping(value="/doZbExp", produces = "text/html;charset=UTF-8")
@ResponseBody
@Transactional
public String doZbExp(HttpServletRequest request){
PageData pd = this.getPageData();
String gid = Validate.isNullToDefaultString(pd.getString("gid"), "");
String pcbh = Validate.isNullToDefaultString(pd.getString("pcbh"), "");
String colum =Validate.isNullToDefaultString(pd.getString("colum"), "");//模糊查询前面下拉框的值
String nr=Validate.isNullToDefaultString(pd.getString("nr"), "");//模糊查询input框输入的内容
String lx=Validate.isNullToDefaultString(pd.getString("lx"), "");//lx全部
//查询列表数据
List<Map<String, String>> datas = (List<Map<String, String>>)xxhphzService.getZbList(gid,pcbh,colum,nr,lx);
//临时文件名
String file = System.currentTimeMillis()+"";
//下载时文件名
String filedisplay = pd.getString("xlsname");
//文件绝对路径
//String realPath=PropertiesUtil.getGlobalValueByKey("FileDataPhysicalPath");
String realPath=this.uploadFolder;
String dayFold = new SimpleDateFormat("yyyyMMdd").format(new Date());
String fileurl = realPath+"excel\\"+dayFold+"\\"+file+".xls";
try {
File filedir = new File(fileurl.substring(0,fileurl.lastIndexOf(System.getProperties().getProperty("file.separator"))));
if(!filedir.isDirectory()){
filedir.mkdirs();
}
} catch (Exception e) {
e.printStackTrace();
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("table");
HSSFRow row;
HSSFCell cell;
short colorIndex = 10;
HSSFPalette palette = wb.getCustomPalette();
Color rgb = Color.GREEN;
short bgIndex = colorIndex++;
palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
short bdIndex = colorIndex++;
rgb = Color.BLACK;
palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());
// 创建表格之后设置行高与列宽
HSSFCellStyle setBorder = wb.createCellStyle();
// 五、设置列宽:
// 第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 15 * 256);
sheet.setColumnWidth(2, 15 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 30 * 256);
sheet.setColumnWidth(5, 15 * 256);
sheet.setColumnWidth(6, 15 * 256);
sheet.setColumnWidth(7, 15 * 256);
sheet.setColumnWidth(8, 15 * 256);
sheet.setColumnWidth(9, 15 * 256);
sheet.setColumnWidth(10, 15 * 256);
HSSFFont font=wb.createFont();
//设置字体大小
//font.setFontHeightInPoints((short) 12);
//字体加粗
font.setBold(true);
setBorder.setFont(font);
// 六、设置自动换行:
setBorder.setWrapText(true);
// 设置水平居中
setBorder.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直居中
setBorder.setVerticalAlignment(VerticalAlignment.CENTER);
//给专家意见设置样式(垂直居中,自动换行)
HSSFCellStyle setBorder1 = wb.createCellStyle();
setBorder1.setWrapText(true);
setBorder1.setVerticalAlignment(VerticalAlignment.CENTER);
setBorder1.setAlignment(HorizontalAlignment.LEFT);
HSSFCellStyle setBorder2 = wb.createCellStyle();
setBorder2.setWrapText(true);
setBorder2.setVerticalAlignment(VerticalAlignment.CENTER);
setBorder2.setAlignment(HorizontalAlignment.RIGHT);
String[] ttable = {"组名","被评单位名称","一级指标","二级指标","评价单位","评分","平均分","标准差","二级指标得分率","一级指标得分率","学校得分率"};
//设置表头
row = sheet.createRow(0);// 创建表格行
row.setHeightInPoints(30);//设置行高
for (int j = 0; j < 11; j++) {
cell = row.createCell(j);// 创建单元格
cell.setCellValue(ttable[j]);//c
cell.setCellStyle(setBorder);//设置单元格中数据居中显示
}
ArrayList<String> rowspans = new ArrayList<>();
ArrayList<String> rowspans2 = new ArrayList<>();
ArrayList<String> rowspans3 = new ArrayList<>();
for(int i = 1;i<datas.size()+1;i++){
row = sheet.createRow(i);
//设置行高3行字
row.setHeightInPoints(30);
Map<String, String> map = datas.get(i-1);
String guid1 = Validate.isNullToDefaultString(map.get("fmbid"), "");//封面id
String yjzbid = Validate.isNullToDefaultString(map.get("yjzbid"), "");//一级指标id
String zbid = Validate.isNullToDefaultString(map.get("zbid"), "");//一级指标id
//按封面合并
String rowspan= Validate.isNullToDefaultString(xxhphzService.getZbRow(gid,pcbh, colum, nr, guid1), "");
//按一级指标合并
String rowspan2= Validate.isNullToDefaultString(xxhphzService.getYjzbRow(gid,pcbh, colum, nr, guid1,yjzbid), "");
//按二级指标合并
String rowspan3= Validate.isNullToDefaultString(xxhphzService.getEjzbRow(gid,pcbh, colum, nr, guid1,zbid), "");
rowspans.add(rowspan);
rowspans2.add(rowspan2);
rowspans3.add(rowspan3);
HSSFCell cell0 = row.createCell(0);
HSSFCell cell1 = row.createCell(1);
HSSFCell cell2 = row.createCell(2);
HSSFCell cell3 = row.createCell(3);
HSSFCell cell4 = row.createCell(4);
HSSFCell cell5 = row.createCell(5);
HSSFCell cell6 = row.createCell(6);
HSSFCell cell7 = row.createCell(7);
HSSFCell cell8 = row.createCell(8);
HSSFCell cell9 = row.createCell(9);
HSSFCell cell10 = row.createCell(10);
//非必定执行
String zm = map.get("ZM");
String bpdwmc = map.get("BPDWMC");
String yjzb = map.get("YJZB");
String ejzb = map.get("EJZB");
String pjdwmc = map.get("PJDWMC");
String pf = Validate.isNullToDefaultString(map.get("PF"),"0.00");
String pjf = Validate.isNullToDefaultString(map.get("PJF"),"0.00");
String bzc = Validate.isNullToDefaultString(map.get("BZC"),"0.00");
String ejdf = Validate.isNullToDefaultString(map.get("EJDF")+"%","0.00%");
String yjdf = Validate.isNullToDefaultString(map.get("YJDF")+"%","0.00%");
String xxdf = Validate.isNullToDefaultString(map.get("XXDF")+"%","0.00%");
cell0.setCellValue(zm);
cell0.setCellStyle(setBorder1);
cell1.setCellValue(bpdwmc);
cell1.setCellStyle(setBorder1);
cell2.setCellValue(yjzb);
cell2.setCellStyle(setBorder1);
cell3.setCellValue(ejzb);
cell3.setCellStyle(setBorder1);
cell4.setCellValue(pjdwmc);
cell4.setCellStyle(setBorder1);
//分数类居右
cell5.setCellValue(pf);
cell5.setCellStyle(setBorder2);
cell6.setCellValue(pjf);
cell6.setCellStyle(setBorder2);
cell7.setCellValue(bzc);
cell7.setCellStyle(setBorder2);
cell8.setCellValue(ejdf);
cell8.setCellStyle(setBorder2);
cell9.setCellValue(yjdf);
cell9.setCellStyle(setBorder2);
cell10.setCellValue(xxdf);
cell10.setCellStyle(setBorder2);
}
//开始合并单元格
//第一组合并按封面id合并
for(int i=0;i<rowspans.size();){
//所占行数
int rowNum = Integer.parseInt(rowspans.get(i));
//起始行
int startRow = i + 1;
//终止行
int endRow = i + rowNum;
if(rowNum > 1){
//合并第一列(zm)
CellRangeAddress region1 = new CellRangeAddress(startRow, // first row
endRow, // last row
0, // first column
0 // last column
);
//合并第二列(bpdwmc)
CellRangeAddress region2 = new CellRangeAddress(startRow, // first row
endRow, // last row
1, // first column
1 // last column
);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
//合并第三列(学校得分率)
CellRangeAddress region3 = new CellRangeAddress(startRow, // first row
endRow, // last row
10, // first column
10 // last column
);
sheet.addMergedRegion(region3);
}
i=i+rowNum;//
}
//第二种合并按照一级指标合并
for(int i=0;i<rowspans2.size();){
//所占行数
int rowNum = Integer.parseInt(rowspans2.get(i));
//起始行
int startRow = i + 1;
//终止行
int endRow = i + rowNum;
if(rowNum > 1){
//合并第2列(一级指标名称)
CellRangeAddress region4 = new CellRangeAddress(startRow, // first row
endRow, // last row
2, // first column
2 // last column
);
//合并一级指标得分率
CellRangeAddress region5 = new CellRangeAddress(startRow, // first row
endRow, // last row
9, // first column
9 // last column
);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
}
i=i+rowNum;//
}
//第三层合并按照二级指标合并
for(int i=0;i<rowspans3.size();){
//所占行数
int rowNum = Integer.parseInt(rowspans3.get(i));
//起始行
int startRow = i + 1;
//终止行
int endRow = i + rowNum;
if(rowNum > 1){
//合并二级指标名称
CellRangeAddress region6 = new CellRangeAddress(startRow, // first row
endRow, // last row
3, // first column
3 // last column
);
//合并平均分
CellRangeAddress region7 = new CellRangeAddress(startRow, // first row
endRow, // last row
6, // first column
6 // last column
);
//合并标准差
CellRangeAddress region8 = new CellRangeAddress(startRow, // first row
endRow, // last row
7, // first column
7 // last column
);
//合并二级得分率
CellRangeAddress region9 = new CellRangeAddress(startRow, // first row
endRow, // last row
8, // first column
8 // last column
);
sheet.addMergedRegion(region6);
sheet.addMergedRegion(region7);
sheet.addMergedRegion(region8);
sheet.addMergedRegion(region9);
}
i=i+rowNum;
}
try {
wb.write(new FileOutputStream(fileurl));
System.out.println("文件以创建完成");
} catch (Exception e1) {
e1.printStackTrace();
}
return "{\"url\":\"excel\\\\"+dayFold+"\\\\" + file + ".xls\"}"; //文件创建完成
}
效果图