EasyExcel的使用

1、导入依赖

String fileName = "D:\\test\\easyExcel\\"+ UUID.randomUUID()+".xlsx";
createNewFile(fileName);
QueryWrapper<StudentInfo> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("status",1);
List<StudentInfo> studentInfos = studentInfoService.list(queryWrapper);
ArrayList<StudentExcelDate> list = new ArrayList<>();
for (StudentInfo info : studentInfos) {
    String classname ="null";
    String schoolName = "null";
    String groupName = "暂未分组";

    if (!StringUtils.isEmpty(info.getRelationClassId())){
            classname = classInfoService.getById(info.getRelationClassId()).getName();
    }
    if (!StringUtils.isEmpty(info.getRelationSchoolId())){
        schoolName = schoolService.getById(info.getRelationSchoolId()).getName();
    }
    if (!StringUtils.isEmpty(info.getGroupId())){
        groupName = groupInfoService.getById(info.getGroupId()).getName();
    }

    StudentExcelDate date = new StudentExcelDate();
    date.setId(info.getId());
    date.setGender(info.getGender().equals(1) ? "男" : "女");
    date.setName(info.getName());
    date.setIdcardNumber(info.getIdcardNumber());
    date.setStudentNum(info.getStudentNum());
    date.setTelephone(info.getTelephone());
    date.setGroup(groupName);
    date.setClassInfo(classname);
    date.setSchool(schoolName);
    list.add(date);
}

EasyExcel.write(fileName, StudentExcelDate.class).sheet("学生信息表").doWrite(list);
return CommonResult.success("ok",null);

2、导出列表

/**
     * @Description: 编码判断修改为UTF-8
     * @Author: LZH
     * @Date: 2021/10/29 10:23
     */
    private static final String ENCODE_1 ="MSIE";
    private static final String ENCODE_2 ="Trident";

    private static final String ENCODE_UTF ="UTF-8";

    private static final String CHAR_SET_NAME ="ISO-8859-1";

    /**
     * @Description: 导出列表（教师端、学生端）
     * @Author: LZH
     * @Date: 2021/10/25 14:06
     */
    @ApiOperation("导出列表（教师端、学生端）")
    @GetMapping("export")
    public CommonResult<Object> exportPracticeExcel(HttpServletResponse response,HttpServletRequest request,ManagerPageVo vo){

        try {
            //设置文件名
            String filenames = "实训安排表"+System.currentTimeMillis();
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains(ENCODE_1) || userAgent.contains(ENCODE_2)) {
                filenames = URLEncoder.encode(filenames,ENCODE_UTF);
            } else {
                filenames = new String(filenames.getBytes(ENCODE_UTF), CHAR_SET_NAME);
            }

            response.setContentType("application/json.ms-exce");
            response.setCharacterEncoding("utf-8");
            response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");

            QueryWrapper<Practice> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("status",1);

            if (!StringUtils.isEmpty(vo)){
                if (!StringUtils.isEmpty(vo.getClassId())){
                    queryWrapper.eq("relation_class_id",vo.getClassId());
                }
            }

            List<Practice> list = practiceService.list(queryWrapper);

            List<PracticeExcelData> data = practiceService.exportFormat(list);

            EasyExcel.write(response.getOutputStream(),PracticeExcelData.class).sheet("实训安排").doWrite(data);

            return null;

        } catch (Exception e) {

            e.printStackTrace();
            return CommonResult.error(500,"导出异常");

        }
    }