EasyExcel+MySQL定制化实现数据导入，导出功能

添加依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

导入功能

导入的实体类

@Data
@ApiModel
public class UserImport {
    
    /**
     * 用户账号
     */
    @ApiModelProperty(value = "用户账号")
    @ExcelProperty("用户账号")
    private String userAccount;

    /**
     * 用户密码
     */
    @ExcelProperty("用户密码")
    @ApiModelProperty(value = "用户密码")
    private String userPassword;

    /**
     * 编号
     */
    @ExcelProperty("编号")
    @ApiModelProperty(value = "编号")
    private String plantCode;

}

controller层

    @PostMapping("import_excel")
    @ApiOperation(value = "导入用户", notes = "根据Excel导入用户")
    public BaseResponse<Boolean> importExcel(HttpServletRequest request,@RequestParam("file") MultipartFile file) {
        boolean result = userService.importExcel(request, file);
        return ResultUtils.success(result);
    }

service层

/**
     * 导入Excel
     * @param request
     * @param file
     * @return
     */
    boolean importExcel(HttpServletRequest request,@RequestParam("file") MultipartFile file);

impl实现类

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean importExcel(HttpServletRequest request, MultipartFile file) {
        File excelFile = null;
        String suffix = FilesUtils.suffix(file.getOriginalFilename());
        try {
            excelFile = createTempFile("temp", suffix);
            excelFile.deleteOnExit();
            file.transferTo(excelFile);
            String fileName = excelFile.getPath();
            List<UserImport> userList = EasyExcel.read(fileName).head(UserImport.class).sheet().doReadSync();
            List<User> users = new ArrayList<>();
            userList.forEach(e -> {
                //1.效验
                if (StringUtils.isAnyBlank(e.getUserAccount(), e.getUserPassword())) {
                    throw new BusinessException(ErrorCode.PARAMS_ERROR, "参数为空");
                }
                //2.对密码加密
                String encryptPassword = DigestUtils.md5DigestAsHex((SALT + e.getUserPassword()).getBytes());
                e.setUserPassword(encryptPassword);
                User user = new User();
                BeanUtils.copyProperties(e, user);
                user.setCreateTime(new Date());
                user.setUpdateTime(new Date());
                user.setUserRole(0);
                users.add(user);
            });
            userService.saveBatch(users);
            return true;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

FilesUtils工具类

public class FilesUtils {
    /**
     * 获取文件后缀名
     * @param s
     * @return
     */
    public static String suffix(String s) {
        String suffixType = "";
        for (int i = s.length() - 1;i>0;i--) {
            if (s.charAt(i) == '.') {
                suffixType = s.substring(i + 1);
                break;
            }
        }
        return suffixType;
    }
}

创建一个xlxs文件，如图所示

导出功能

导出类

@Data
public class UserExport {

    /**
     * 用户账号
     */
    @ApiModelProperty(value = "用户账号")
    @ExcelProperty(value = "用户账号", index = 0)
    private String userAccount;

    /**
     * 用户密码
     */
    @ApiModelProperty(value = "用户密码")
    @ExcelProperty(value = "用户密码", index = 1)
    private String userPassword;

    /**
     * 编号
     */
    @ApiModelProperty(value = "编号")
    @ExcelProperty(value = "编号", index = 2)
    private String plantCode;

    /**
     * 创建时间
     */
    @ApiModelProperty(value = "创建时间")
    @ExcelProperty(value = "创建时间", index = 3)
    private Date createTime;

    public UserExport(String userAccount, String userPassword, String plantCode, Date createTime) {
        this.userAccount = userAccount;
        this.userPassword = userPassword;
        this.plantCode = plantCode;
        this.createTime = createTime;
    }

    public UserExport() {}

    public static UserExport map(User user) {
        return new UserExport(user.getUserAccount(),user.getUserPassword(),user.getPlantCode(),user.getCreateTime());
    }
}

controller层

    @GetMapping("export_excel")
    @ApiOperation(value = "导出用户", notes = "根据Excel导出用户")
    public void exportExcel(HttpServletResponse response) throws IOException {
        userService.exportExcel(response);
    }

service层

    /**
     * 导出Excel
     * @param response
     * @return
     */
    void exportExcel(HttpServletResponse response) throws IOException;

impl实现类

    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        String fileName = "user";
        List<User> list = userService.list();
        List<UserExport> exportList = list.stream().map(user -> UserExport.map(user)).collect(Collectors.toList());
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExport.class).sheet("模板").doWrite(exportList);
    }

小结

这里就完成数据的导入及导出功能,采用mysql作为数据库进行持久化，通过接口的方式实现。