EasyExcel导出作答详情

    /**
     * 导出详分表-第二版-wzy
     * 只能按年级为单位导出
     */
    @GetMapping("/exportSummary/grade")
    public void exportSummaryGrade2(@RequestParam(value = "schId") Integer schId,
                                   @RequestParam(value = "gradeId") Integer gradeId,
                                   @RequestParam(value = "homeworkId") Integer homeworkId,
                                   @RequestParam(value = "name") String name,
                                   HttpServletResponse response) {
        try {
            //1.组装表头
            Homework homework = homeworkService.getById(homeworkId);
            //1.1 固定表头
            String[] colTableHeader = {"班级", "姓名", "学号", "正确率", "全对的数量", "错的数量"};
            //1.2 动态表头
            List<ScoreDetail> scoreDetailVos = teacherService.getTopicNo(homeworkId);
            //1.3 拿到作业的班级范围
            List<Clazz> classIds =
                    homework.getType() == 0 ? getClazz(gradeId, null): getClazz(null, homework.getClazzIds());
            //1.4 没有作答
            if (classIds.isEmpty()){
                return;
            }
            //2.合并表头
            List<String> tableHeader = new ArrayList<>(colTableHeader.length);
            Collections.addAll(tableHeader, colTableHeader);
            for (ScoreDetail scoreDetailVo : scoreDetailVos) {
                tableHeader.add(scoreDetailVo.getShowContent());
            }
            log.info("[excel导出一次作业详细分数表头]: {},{}", tableHeader, tableHeader.size());
            //3 创建excel
            String excelName = "【"+name+"】"+ "-作答详情.xlsx";
            //3.1 循环sheet 按班级
            Workbook workbook = new XSSFWorkbook();
            for (Clazz clazz : classIds) {
                Sheet sheet = workbook.createSheet(clazz.getName()+"-作答详情");
                //4 样式
                //4.1 表头
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, tableHeader.size() - 1);
                sheet.addMergedRegion(region);
                Row header = sheet.createRow(0);
                Cell headerCell = header.createCell(0);
                headerCell.setCellValue("【"+ name+"】");
                headerCell.setCellStyle(getTitleStyle(workbook));
                Row headerRow = sheet.createRow(1);
                for (int i = 0; i < tableHeader.size(); i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(tableHeader.get(i));
                    cell.setCellStyle(getHeaderStyle(workbook));
                }
                //5 查询学生 不用关联 要计算出未交卷的
                List<ScoreVo> scoreVos = new ArrayList<>();
                List<Student> students = teacherService.getStudents(schId, clazz.getId());
                if (students.isEmpty()){
                    break;
                }
                //5.1 查询学生作答
                for (Student student : students){
                    ScoreVo scoreVo = new ScoreVo();
                    //写入学生的姓名 id 和学号
                    scoreVo.setStudentName(student.getName()).setStudentId(student.getId()).setStudentNo(student.getStuNo());
                    //5.2 查询作答
                    Score score = scoreService.getOne(
                            new LambdaQueryWrapper<Score>().eq(Score::getHomeworkId, homeworkId).
                                    eq(Score::getStudentId, student.getId())
                                    .orderByDesc(Score::getCreateTime).last("limit 1"));
                    //没提交
                    if (score == null){
                        scoreVo.setSubmit(0);
                    }else {
                        scoreVo.setSubmit(1).setRightNum(score.getRightNum()).setErrorNum(homework.getTmCount()-score.getRightNum())
                                .setPer(String.format("%.3f", Double.valueOf(score.getRightNum()) / homework.getTmCount()));
                        List<ScoreDetail> scoreDetails = scoreDetailService.list(
                                new LambdaQueryWrapper<ScoreDetail>().eq(ScoreDetail::getScoreCode, score.getCode())
                        );
                        log.info("scoreDetails before:{}", scoreDetails);
                        scoreVo.setScoreDetailList(scoreDetails);
                    }
                    scoreVos.add(scoreVo);
                }
                //5.2 塞数据 {"班级", "姓名", "学号", "正确率", "全对的数量", "错的数量"};
                for (int i = 0; i < scoreVos.size(); i++) {
                    ScoreVo scoreVo = scoreVos.get(i);
                    Row row = sheet.createRow(i + 2);
                    row.createCell(0).setCellValue(clazz.getName());
                    row.createCell(1).setCellValue(scoreVo.getStudentName());
                    row.createCell(2).setCellValue(scoreVo.getStudentNo());
                    if (scoreVo.getSubmit() == 1) {
                        List<ScoreDetail> scoreDetails = scoreVo.getScoreDetailList();
                        log.info("scoreDetails:{}", scoreDetails);
                        row.createCell(3).setCellValue(scoreVo.getPer());
                        row.createCell(4).setCellValue(scoreVo.getRightNum());
                        row.createCell(5).setCellValue(homework.getTmCount()-scoreVo.getRightNum());
                        for (int j = 6; j < tableHeader.size(); j++) {
                            if ((j - 6) < scoreDetails.size()) {
                                Integer answerStatus = scoreDetails.get(j - 6).getAnswerStatus();
                                String answer = "异常";
                                if(answerStatus == 0){
                                    answer = "错";
                                }
                                if(answerStatus == 1){
                                    answer = "对";
                                }
                                if(answerStatus == 2){
                                    answer = "半对";
                                }
                                row.createCell(j).setCellValue(answer);
                            }
                        }
                    }else {
                        row.createCell(3).setCellValue("未提交");
                    }
                }
            }
            //6.导出
            response.reset();
//            response.setContentType("application/vnd.ms-excel");
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-disposition", "attachment;filename=" +
                    new String(excelName.getBytes("UTF-8"), "ISO-8859-1"));
            //输出流
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            response.getOutputStream().close();
            workbook.close();
        } catch (CustomException e){
            e.printStackTrace();
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 表头
     */
    public static CellStyle getHeaderStyle(Workbook workbook) throws IOException {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        Font font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 标题
     */
    public static CellStyle getTitleStyle(Workbook workbook) throws IOException {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setFillBackgroundColor((short) 22);
        Font font = workbook.createFont();
        font.setFontHeight((short)400);
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }