导入导出功能实现

easypoi 导入导出execl 工具类

/**
     * 下载导入成绩数据模板
     *
     * @param response
     * @return
     */
    //@PreAuthorize ("hasAnyAuthority('sys:user:import')")
    @GetMapping("import/template")
    @ApiOperation(value = "下载模板")
    public void importFileTemplate(HttpServletResponse response) {
        String fileName = "学生成绩导入模板.xlsx";
        List<StudentGradeExcelDTO> list = Lists.newArrayList();
        //list.add(UserUtils.getCurrentUserDTO ());
        EasyPoiUtil.exportExcel(list, "学生成绩", "学生成绩", StudentGradeExcelDTO.class, fileName, response);
    }

    /**
     * 下载导入信息数据模板
     *
     * @param response
     * @return
     */
    //@PreAuthorize ("hasAnyAuthority('sys:user:import')")
    @GetMapping("import/infoTemplate")
    @ApiOperation(value = "下载模板")
    public void importInfoFileTemplate(HttpServletResponse response) {
        String fileName = "学生信息导入模板.xlsx";
        List<StudentInfoExcelDTO> list = Lists.newArrayList();
        EasyPoiUtil.exportExcel(list, "学生信息", "学生信息", StudentInfoExcelDTO.class, fileName, response);
    }

    /**
     * 导入用户数据
     *
     * @return
     */
    @DemoMode
    //@PreAuthorize("hasAnyAuthority('sys:user:import')")
    @PostMapping("importStudentInfo")
    @ApiOperation(value = "导入学生信息excel")
    public ResponseEntity importInfoFile(MultipartFile file) {
        try {
            int successNum = 0;
            int failureNum = 0;
            StringBuilder failureMsg = new StringBuilder();
            List<StudentInfoExcelDTO> list = EasyPoiUtil.importExcel(file, 1, 1, StudentInfoExcelDTO.class);
            for (StudentInfoExcelDTO studentInfoExcelDTO : list) {
                try {
                    StudentGradeDTO studentGradeDTO = studentGradeService.selectOneByKsh(studentInfoExcelDTO.getKsh());
                    if (null == studentGradeDTO) {
                        StudentGradeDTO studentGradeDTO1 = new StudentGradeDTO();
                        BeanUtil.copyProperties(studentInfoExcelDTO, studentGradeDTO1);
                        studentGradeService.saveOrUpdate(studentGradeWrapper.INSTANCE.toEntity(studentGradeDTO1));
                        successNum++;
                    } else {
                        failureMsg.append("<br/>该学生 " + studentInfoExcelDTO.getXm() + " 已存在; ");
                        failureNum++;
                    }
                } catch (ConstraintViolationException ex) {
                    failureMsg.append("<br/>该学生 " + studentInfoExcelDTO.getXm() + " 导入失败：");
                    List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
                    for (String message : messageList) {
                        failureMsg.append(message + "; ");
                        failureNum++;
                    }
                } catch (Exception ex) {
                    failureNum++;
                    failureMsg.append("<br/>该学生 " + studentInfoExcelDTO.getXm() + " 导入失败：" + ex.getMessage());
                }
            }
            if (failureNum > 0) {
                failureMsg.insert(0, "，失败 " + failureNum + " 条用户，导入信息如下：");
            }
            return ResponseEntity.ok("已成功导入 " + successNum + " 条用户" + failureMsg);
        } catch (Exception e) {
            return ResponseEntity.badRequest().body("导入用户失败！失败信息：" + e.getMessage());
        }
    }


    /**
     * 导入用户数据
     *
     * @return
     */
    @DemoMode
    //@PreAuthorize("hasAnyAuthority('sys:user:import')")
    @PostMapping("importPicture")
    @ApiOperation(value = "导入学生图片")
    public ResponseEntity<Map> importPicture(MultipartFile file) {
        //控件要求返回的字段为id,value，type
        Map map = new HashMap<>();
        map.put("id", "");
        map.put("value", "");
        map.put("type", "");
        try {
            Calendar cal = Calendar.getInstance();
            int year = cal.get(Calendar.YEAR);
            //获取上传的最绝对路径，相对于盘符的路径
            String fileDir = FileKit.getStdImageDir(year) + "/zips/";
            String destDirPath = FileKit.getStdImageDir(year) + "/images/";
            if (!file.isEmpty()) {
                String name = file.getOriginalFilename();
                if (fileProperties.isAvailable(name)) {
                    // 文件保存路径
                    // 转存文件
                    FileUtils.createDirectory(fileDir);
                    String filePath = fileDir + name;
                    File newFile = FileUtils.getAvailableFile(filePath, 0);
                    file.transferTo(newFile);
                    //针对同名文件，操作系统会追加序号，导致原来的filePath获取错误，需要拿到最新的文件夹
                    filePath = fileDir + newFile.getName();
                    //FileUtils.copyInputStreamToFile(file.getInputStream(), fileUrl + newFile.getName ());
                    // 将原压缩文件解压到临时目录
                    FileUtils.createDirectory(destDirPath);
                    ZipUtil.unzipFile(filePath, destDirPath);
                    map.put("id", FileKit.transDirToUrl(newFile.getAbsolutePath()));
                    map.put("value", newFile.getName());
                    map.put("type", FileKit.getFileType(newFile.getName()));
                    return ResponseEntity.ok(map);
                } else {
                    return ResponseEntity.ok(map);
                }
            } else {
                return ResponseEntity.ok(map);
            }
        } catch (Exception e) {
            return ResponseEntity.badRequest().eTag("上传失败").build();
        }


    }

    /**
     * 导入用户数据
     *
     * @return
     */
    @DemoMode
    //@PreAuthorize("hasAnyAuthority('sys:user:import')")
    @PostMapping("importGrade")
    @ApiOperation(value = "导入学生成绩excel")
    public ResponseEntity importGradeFile(MultipartFile file) {
        try {
            int successNum = 0;
            int failureNum = 0;
            StringBuilder failureMsg = new StringBuilder();
            List<StudentGradeExcelDTO> list = EasyPoiUtil.importExcel(file, 1, 1, StudentGradeExcelDTO.class);
            for (StudentGradeExcelDTO studentGradeExcelDTO : list) {
                try {
                    StudentGradeDTO studentGradeDTO = studentGradeService.selectOneByKsh(studentGradeExcelDTO.getKsh());
                    if (null != studentGradeDTO) {
                        //BeanUtil.copyProperties(studentGradeExcelDTO,studentGradeDTO);
                        studentGradeDTO.setCj1(studentGradeExcelDTO.getCj1());
                        studentGradeDTO.setCj2(studentGradeExcelDTO.getCj2());
                        studentGradeDTO.setCj3(studentGradeExcelDTO.getCj3());
                        studentGradeDTO.setCj4(studentGradeExcelDTO.getCj4());
                        studentGradeDTO.setZf(studentGradeExcelDTO.getZf());
                        studentGradeService.saveOrUpdate(studentGradeWrapper.INSTANCE.toEntity(studentGradeDTO));
                        successNum++;
                    } else {
                        failureMsg.append("<br/>该学生 " + studentGradeExcelDTO.getKsh() + " 不存在; ");
                        failureNum++;
                    }
                } catch (ConstraintViolationException ex) {
                    failureMsg.append("<br/>该学生 " + studentGradeExcelDTO.getKsh() + " 导入失败：");
                    List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
                    for (String message : messageList) {
                        failureMsg.append(message + "; ");
                        failureNum++;
                    }
                } catch (Exception ex) {
                    failureNum++;
                    failureMsg.append("<br/>该学生 " + studentGradeExcelDTO.getKsh() + " 导入失败：" + ex.getMessage());
                }
            }
            if (failureNum > 0) {
                failureMsg.insert(0, "，失败 " + failureNum + " 条用户，导入信息如下：");
            }
            return ResponseEntity.ok("已成功导入 " + successNum + " 条用户" + failureMsg);
        } catch (Exception e) {
            return ResponseEntity.badRequest().body("导入用户失败！失败信息：" + e.getMessage());
        }
    }


    /**
     * 初始化图片
     *
     * @param examDetailDTO
     * @return
     */
    @DemoMode
    @ApiLog("修改考试须知")
    @ApiOperation(value = "修改考试须知")
    @GetMapping("initPhoto")
    public ResponseEntity initPhoto() throws Exception {
        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        String destDirPath = FileKit.getStdImageDir(year) + "/images/";
        File file = new File(destDirPath);
        if (file.exists() && file.isDirectory()) {
            File files[] = file.listFiles();
            for (File f : files) {
                String filess[] = f.getName().split("[.]");
                String fileName = filess[0];
                StudentGrade studentGrade = studentGradeService.selectImgStd(fileName, fileName, fileName);
                if (null != studentGrade) {
                    studentGrade.setPhoto(FileKit.transDirToUrl(f.getAbsolutePath()));
                    studentGradeService.saveOrUpdate(studentGrade);
                }
            }
        }
        return ResponseEntity.ok("图片初始化成功!");
    }


}