前端:layui $("#excel").click(function () { var name =sessionStorage.getItem("name"); sessionStorage.setItem("name",""); var cid =sessionStorage.getItem("id"); var id =sessionStorage.getItem("fenid"); download("/api/exam/ExportExcel?id="+id+"&name="+name+"&cid="+cid); }) function download(url) { var $form = $("<form></form>").attr("action", url).attr("method", "post").attr("target", "_blank"); $form.appendTo('body').submit().remove(); }
接口:
@ApiResponses({ @ApiResponse(code = 200,message = "导出票数统计excel含有样式"), @ApiResponse(code = 3012,message = "失败"), }) @RequestMapping("/ExportExcel") public void wxpoiExcel(HttpServletRequest request, HttpServletResponse response,String name, Integer id,Integer cid) throws IOException { List<Poy> poyList = new ArrayList<>(); List<Personnel> list = new ArrayList<>(); List<Swer> swersList = new ArrayList<>(); PersonCouts per= new PersonCouts(); if (StringUtils.isBlank(name)){ //参加总人数 poyList = poyService.selectPoy(cid); //完成投票人数 swersList = swerService.selectSwer(id); Integer unfinished=poyList.size()-swersList.size(); per.setHeadcount(poyList.size()); per.setCompleted(swersList.size()); per.setUnfinished(unfinished); }else{ //参加总人数 poyList = poyService.selectPoy(cid); //完成投票人数 swersList = swerService.selectSwerName(id,name); Integer unfinished=poyList.size()-swersList.size(); per.setHeadcount(poyList.size()); per.setCompleted(swersList.size()); per.setUnfinished(unfinished); } //根据id查询测评名称 Personnel personnel = personService.selectPerson(id); //根据项目id查询出被测评人的数组 // List<Porect> porectList = porectService.selectLists(id); //根据id查询出题目和答案个数 List<Contro> controList = controService.selectControList(id); //创建Excel工作薄对象 HSSFWorkbook workbook=new HSSFWorkbook(); //创建Excel工作表对象 HSSFSheet sheet = workbook.createSheet("wj"); HSSFCellStyle headStyle = workbook.createCellStyle(); //表头 headStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中 //创建行的单元格,从0开始 HSSFRow row = sheet.createRow(0); //创建标题行单元格 HSSFCell cell = row.createCell(0); //设置一共有多少行的值 和title cell.setCellStyle(headStyle); int index=controList.size()*4; cell.setCellValue(personnel.getName()); CellRangeAddress region=new CellRangeAddress(0, 0, 0, index); sheet.addMergedRegion(region); //设置第2行开始左侧合并单元格姓名 HSSFRow row1 = sheet.createRow(1); //创建标题行单元格 HSSFCell cell1 = row1.createCell(0); cell1.setCellStyle(headStyle); cell1.setCellValue("姓名"); CellRangeAddress region1=new CellRangeAddress(1, 2, 0, 0); sheet.addMergedRegion(region1); //设置第3行开始左侧合并单元格选项 HSSFRow row2 = sheet.createRow(2); //创建右侧选项和答案并合并单元格 int index1=0; for(int i=0;i<controList.size();i++){ String[] st1r =controList.get(i).getContro().split(","); int t=st1r.length; int count = i*t+1; int counts = i*t+t; HSSFCell cell2 = row1.createCell(count); cell2.setCellStyle(headStyle); cell2.setCellValue(controList.get(i).getName()); CellRangeAddress region2 = new CellRangeAddress(1, 1, count, counts); sheet.addMergedRegion(region2); String[] str =controList.get(i).getContro().split(","); for (String s: str) { index1++; HSSFCell cell3 = row2.createCell(index1); cell3.setCellStyle(headStyle); cell3.setCellValue(s); } } Personnel p = personService.selectPerson(id); //查询答案 List<SwerVo> s=new ArrayList<>(); List<SwerVo> swList=new ArrayList<>(); if (StringUtils.isBlank(name)){ s = swerService.selectSwerVoList(id); swList=swerService.selectSwerListName(id); }else{ s = swerService.selectSwerVoListName(id,name); swList=swerService.selectSwerList(id,name); } //第一部查询出所有人的姓名 List<SwerVo> listName = new ArrayList<>(); for (int i=0;i<swList.size();i++) { List<SwerVo> listNamesv = new ArrayList<>(); SwerVo sw = new SwerVo(); //名字和名称 List<SwerVo> svNameList=swerService.selectSwerNList(id,swList.get(i).getPorname()); //查询出每个人的题目加以封装 for (int j =0;j<svNameList.size();j++){ List<SwerVo> listda= new ArrayList<>(); if (swList.get(i).getPorname().equals(svNameList.get(j).getPorname())){ SwerVo sv = new SwerVo(); //查询出每个题目的多个答案加以封装 for (int k=0;k<s.size();k++){ if (s.get(k).getKname().equals(svNameList.get(j).getKname()) && s.get(k).getPorname().equals(svNameList.get(j).getPorname())){ SwerVo so = new SwerVo(); so.setCounts(s.get(k).getCounts()); so.setAnswer(s.get(k).getAnswer()); listda.add(so); } } sv.setKname(svNameList.get(j).getKname()); sv.setList(listda); listNamesv.add(sv); } } sw.setPorname(swList.get(i).getPorname()); //名字 sw.setList(listNamesv); listName.add(sw); } int index2=3; for (int i=0;i<listName.size();i++){ int index3=0; //设置第4行开始左侧合并单元格姓名 HSSFRow row3 = sheet.createRow(index2); //创建标题行单元格 HSSFCell cell3 = row3.createCell(index3); cell3.setCellStyle(headStyle); cell3.setCellValue(listName.get(i).getPorname());//姓名 index2++; List<SwerVo> listPorName = listName.get(i).getList();//题目 for (int j=0;j<listPorName.size();j++){ List<SwerVo> listAnswer = listPorName.get(j).getList();//答案 for(int k=0;k<controList.size();k++){ if (controList.get(k).getName().equals(listPorName.get(j).getKname())){ String[] ar=controList.get(k).getContro().split(","); //合并重复的选项 Map<String, SwerVo> stringtestMap = listAnswer.stream().collect(Collectors.toMap(SwerVo::getAnswer, ts -> ts)); for (int o=0;o<ar.length;o++) { index3++; //给选项重新赋值 SwerVo temp = stringtestMap.getOrDefault(ar[o] , new SwerVo(ar[o],0) ); System.out.println( String.format("%s:%s",temp.getAnswer(),temp.getCounts()) ); HSSFCell cell4 = row3.createCell(index3); cell4.setCellStyle(headStyle); cell4.setCellValue(temp.getCounts());//个数 } } } } } //设置最后一行行开始左侧单元格人数 HSSFRow row4 = sheet.createRow(listName.size()+3); //创建标题行单元格 HSSFCell cell4 = row4.createCell(0); cell4.setCellValue("参加人数:"+per.getHeadcount()); cell4.setCellStyle(headStyle); HSSFCell cell5 = row4.createCell(1); cell5.setCellValue("已投票人数:"+per.getCompleted()); cell5.setCellStyle(headStyle); HSSFCell cell6 = row4.createCell(2); cell6.setCellValue("未投票人数:"+per.getUnfinished()); cell6.setCellStyle(headStyle); //文档输出 // 第六步,将文件存到浏览器设置的下载位置 String fileName=new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString(); //八进制输出流 response.setContentType("application/octet-stream"); //这后面可以设置导出Excel的名称,此例中名为student.xls response.setHeader("Content-disposition",String.format("attachment;filename=%s.xls",URLEncoder.encode(fileName,"UTF-8"))); //刷新缓冲 response.flushBuffer(); //workbook将Excel写入到response的输出流中,供页面下载 workbook.write(response.getOutputStream()); workbook.close(); }