EasyExcel实现导入导出

导入Pom.xml

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.3.3</version>
</dependency>

导入功能：

1.准备好一个导入模板（也就是excel表格）

2.导入的数据对应的实体类：

import lombok.Data;

/**
 * @author: cxx
 * @Date: 2024-04-19 11:08
 **/
@Data
public class UserMobileItem {
    private String mobileNo;
}

3.Controller：

  @PostMapping("/upload")
    public BooleanResult upload(@RequestParam("file") MultipartFile file,@RequestParam("userId") Integer userId){
        return userService.upload(file,userId);
    }

4.Service:

     /**
     * 上传用户手机号
     * @param file
     * @return
     */
    BooleanResult upload(MultipartFile file,Integer userId);

5.ServiceImpl (解析文件并把数据存储到数据库)

    @Override
    public BooleanResult upload(MultipartFile file,Integer userId) {
        if (file == null) {
            throw new BusinessException("上传文件为空");
        }
        List<UserMobileItem> res = new ArrayList<>();
        try {
            EasyExcel.read(file.getInputStream(), UserMobileItem.class, new AnalysisEventListener<UserMobileItem>() {
                @Override
                public void invoke(UserMobileItem o, AnalysisContext analysisContext) {
                    res.add(o);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    saveUserMobileToDb(res,userId);
                }
            }).sheet().doRead();
        } catch (Exception e) {
            log.error("上传异常", e);
            if (e instanceof BusinessException) {
                throw new BusinessException(e.getMessage());
            }
            return new BooleanResult(false);
        }
        return new BooleanResult(res.size() > 0);
    }



    private void saveUserMobileToDb(List<UserMobileItem> res,Integer userId) {
        if (res.size() == 0) {
            throw new BusinessException("上传文件内容为空");
        }
        List<PromotionUserMobile> UserMobileList = new ArrayList<>();
        StringBuilder errorMsg = new StringBuilder();
        LambdaQueryWrapper<PromotionUserMobile> userMobileQuery = new LambdaQueryWrapper<>();
        userMobileQuery.eq(PromotionUserMobile::getUserId, userId);
        List<PromotionUserMobile> userMobiles = userMobileMapper.selectList(userMobileQuery);

        for (int i = 1; i <= res.size(); i++) {
            UserMobileItem userMobileItem = res.get(i - 1);
            //验证手机号
            boolean mobileNumber = MobileUtil.isMobileNumber(userMobileItem.getMobileNo());
            if (mobileNumber) {
                boolean flag = userMobiles.stream().anyMatch(s -> s.getMobileNo().equals(userMobileItem.getMobileNo()));
                //不存在添加
                if (!flag) {
                    PromotionUserMobile promotionUserMobile = new PromotionUserMobile();
                    promotionUserMobile.setUserId(userId)
                            .setMobileNo(userMobileItem.getMobileNo())
                            .setCreateTime(LocalDateTime.now());
                    UserMobileList.add(promotionUserMobile);
                }
            }else {
                errorMsg.append("第" + (i+1) + "行手机号格式错误");
            }
        }
        if (errorMsg.length() != 0) {
            throw new BusinessException(errorMsg.toString());
        }
        if (!CollectionUtils.isEmpty(UserMobileList)) {
            UserMobileList.forEach(userMobile -> {
                userMobileMapper.insert(userMobile);
            });
        }
    }

导出功能：

1.导出的实体类：

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.experimental.Accessors;

import java.time.LocalDateTime;
import java.util.List;

@Data
@ExcelIgnoreUnannotated
@ContentStyle(borderLeft = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class UserResult {
    private Integer id;

    @ExcelProperty("账户ID")
    @ColumnWidth(20)
    private String accountNo;

    @ExcelProperty("注册时间")
    @ColumnWidth(20)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime registerTime;

    @ExcelProperty("昵称")
    @ColumnWidth(20)
    private String nickName;

    @ExcelProperty("姓名")
    @ColumnWidth(20)
    private String userName;

    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String mobileNo;
    @ExcelProperty("身份证号")
    @ColumnWidth(20)
    private String idNo;


    @ExcelProperty("银行名称")
    @ColumnWidth(20)
    private String bankName;

    @ExcelProperty("银行账号")
    @ColumnWidth(20)
    private String bankAccountNo;

}

2.Controller：

    @GetMapping("/exportUser")
    public void exportUser(Integer userId, HttpServletResponse response) {
        userService.exportUser(userId, response);
    }

3.Service:

     /**
     * 导出用户信息
     * @param userId
     * @param response
     */
    void exportUser(Integer userId,HttpServletResponse response);

4.ServiceImpl:

    @Override
    public void exportUser(Integer userId, HttpServletResponse response) {
        List<UserResult> userListByPage = userMapper.getUserInfo(userId);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            String fileName = URLEncoder.encode("用户信息", "UTF-8").replaceAll("\\+", "%20") + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-ddHHmmssSSS"));
            response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), UserResult.class).sheet("台账").doWrite(userListByPage);
        } catch (Exception e) {
            log.error(e.toString());
        }
    }

导出多个sheet页表格 

 1.导出的实体类：

@Data
@ExcelIgnoreUnannotated
@ContentStyle(borderLeft = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class TreeUserResult {
    @ExcelProperty("姓名")
    @ColumnWidth(20)
    private String userName;

    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String mobileNo;

    @ExcelProperty("角色")
    @ColumnWidth(20)
    private String roleName;
}

2.Controller:

@GetMapping("/handleDownload")
    public void handleDownload(HttpServletResponse response){
        userService.handleDownload(response);
    }

3.Service:

  /**
     * 导出树状表格
     * @param response
     */
    void handleDownload(HttpServletResponse response);

4.ServiceImpl: 

    @Override
    public void handleDownload(HttpServletResponse response) {
        List<ParentUser> userTopLevel = userMapper.getUserTopLevel();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            String fileName = URLEncoder.encode("用户树状表格", "UTF-8").replaceAll("\\+", "%20") + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-ddHHmmssSSS"));
            response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), TreeUserResult.class).build();
            if (userTopLevel != null && userTopLevel.size() > 0) {
                for (int i = 0; i < userTopLevel.size(); i++) {
                    ParentUser parentUser = userTopLevel.get(i);
                    List<TreeUserResult> treeUser = userMapper.getTreeUser(parentUser.getId());
                    WriteSheet sheet = EasyExcel.writerSheet(i, parentUser.getUserName()).build();
                    excelWriter.write(treeUser, sheet);
                }
                excelWriter.finish();
            }
        } catch (Exception e) {
            log.error(e.toString());
        }
    }

EasyExcecl的导入导出就到这了~~