[EasyExcel]: 使用AnalysisEventListener回调监听器读取Excel

Pom.xml

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

Controller:

@PostMapping("/readExcel")
    public R<String> readExcelToDB(@RequestParam(value = "multipartFile") MultipartFile multipartFile) {
        // 非管理员禁止调用
        if (!tokenService.getLoginUser(ServletUtils.getRequest()).getUser().getNickName().equals("admin")) {
            log.info(tokenService.getLoginUser(ServletUtils.getRequest()).getUser().getNickName() + "非法调用此接口");
            throw new ServiceException("参数异常");
        }
        try (InputStream inputStream = multipartFile.getInputStream()) {
            return skywingUserService.readExcelToDB(multipartFile.getName(), inputStream);
        } catch (Exception e) {
            log.error("读取 " + multipartFile.getName() + " 文件失败, 原因: {}", e.getMessage());
            throw new ServiceException("读取 " + multipartFile.getName() + " 文件失败");
        }

    }

Service:

import com.alibaba.excel.EasyExcel;

@Override
    public R<String> readExcelToDB(String fileName, InputStream inputStream) {
        try {
            // 读取文件内容
            EasyExcel.read(inputStream, RegisterInfoExcel.class, new RegisterInfoExcelListener(sysUserMapper, meetZoomuserinfoTabMapper)).sheet().doRead();
            return R.ok("读取Excel文件成功");
        } catch (Exception e) {
            log.error("读取 " + fileName + " 文件失败, 原因: {}", e.getMessage());
        }

        return R.ok();
    }

Listener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * 读取注册用户信息Excel表并写入DB
 *
 * @author 毛毛
 */
public class RegisterInfoExcelListener extends AnalysisEventListener<RegisterInfoExcel> {

    private static final Logger log = LoggerFactory.getLogger(RegisterInfoExcelListener.class);
    private static final int BATCH_COUNT = 100;
    // 由于@Autowired注解无法注入, 所以使用参数的方式注入
    private final SysUserMapper sysUserMapper;
    // 由于@Autowired注解无法注入, 所以使用参数的方式注入
    private final MeetZoomuserinfoTabMapper meetZoomuserinfoTabMapper;
    private List<RegisterInfoExcel> cacheDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    public RegisterInfoExcelListener(SysUserMapper sysUserMapper, MeetZoomuserinfoTabMapper meetZoomuserinfoTabMapper) {
        this.sysUserMapper = sysUserMapper;
        this.meetZoomuserinfoTabMapper = meetZoomuserinfoTabMapper;
    }

    /**
     * 批量读取Excel写入DB
     *
     * @param registerInfoExcel 全局监听内容
     * @param analysisContext   读取到的Excel内容
     */
    @Override
    public void invoke(RegisterInfoExcel registerInfoExcel, AnalysisContext analysisContext) {
        // 将监听到的数据存入缓存集合中
        cacheDataList.add(registerInfoExcel);

        // 批量处理缓存的数据
        if (cacheDataList.size() >= BATCH_COUNT) {
            saveToDB();
            cacheDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 处理结尾, 不足100条
     *
     * @param analysisContext 全局监听内容
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveToDB();
        log.info(analysisContext.getCustom().toString() + "所有数据解析完成");
    }

    /**
     * 将读取到的内容写入DB
     */
    private void saveToDB() {
        for (RegisterInfoExcel userInfoExcel : cacheDataList) {
            // 若该用户已存在, 则更新
            MeetZoomuserinfoTab registeredUser = meetZoomuserinfoTabMapper.selectByPhone(userInfoExcel.getPhone());
            if (ObjectUtils.isNotEmpty(registeredUser)) {
                try {
                    // 更新nid
                    if (!registeredUser.getZoomcode().equals(userInfoExcel.getAccount())) {
                        registeredUser.setZoomcode(userInfoExcel.getAccount());
                        // 更新DB
                        meetZoomuserinfoTabMapper.updateMeetZoomuserinfoTab(registeredUser);
                        log.info(userInfoExcel.getUserName() + " 更新数据库成功\n");
                    }
                } catch (Exception e) {
                    log.error(userInfoExcel.getUserName() + " 更新DB失败, 原因: \n" + e.getMessage());
                }
            } else {    // 该用户不存在
                // 根据手机号 和 用户姓名查询用户信息
                SysUser userInfo = sysUserMapper.getByUserNameAndPhone(userInfoExcel.getUserName(), userInfoExcel.getPhone());

                if (ObjectUtils.isNotEmpty(userInfo)) {
                    // 构建用户注册信息
                    MeetZoomuserinfoTab registerInfo = MeetZoomuserinfoTab.builder()
                            .userid(String.valueOf(userInfo.getUserId()))
                            .hostid(userInfo.getNickName())
                            .zoomcode(userInfoExcel.getAccount())
                            .email(userInfo.getEmail())
                            .role("普通角色")
                            .dept("XXX分公司")
                            .type(1)
                            .phone(userInfo.getPhonenumber())
                            .build();

                    // 写入注册信息表
                    try {
                        meetZoomuserinfoTabMapper.insertMeetZoomuserinfoTab(registerInfo);
                        log.info(userInfo.getUserName() + " 写入数据库成功\n");
                    } catch (Exception e) {
                        log.error(userInfo.getUserName() + " 写入DB失败, 原因: \n" + e.getMessage());
                    }
                } else {
                    log.error("\n" + userInfoExcel.getUserName() + " 用户不存在");
                }
            }
        }
    }
}

Domain

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * 导入用户信息-Excel表对象
 * @author 毛毛
 */
@Data
@EqualsAndHashCode
public class RegisterInfoExcel {

    /**
     * 部门或组织全路径
     */
    @ExcelProperty("部门或组织全路径")
    private String deptPath;

    /**
     * 用户名
     */
    @ExcelProperty("用户名")
    private String userName;

    /**
     * 手机号
     */
    @ExcelProperty("手机号")
    private String phone;

    /**
     * 邮箱
     */
    @ExcelProperty("邮箱")
    private String email;

    /**
     * 账号
     */
    @ExcelProperty("账号")
    private String account;

    /**
     * 加入企业时间
     */
    @ExcelProperty("加入企业时间")
    private Date timeOfFirm;
}