api导出Excel(2003版本) 包含样式

前端: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();
    }