eazyexcel 导入带异常捕捉

//UserInfo pojo


@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_user_info")
public class UserInfo implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "user_id", type = IdType.AUTO)
    @ExcelIgnore
    private Long userId;

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

    /**
     * 密码
     */
    @ExcelProperty("密码")
    private String password;

    /**
     * 真实姓名
     */
    @ExcelProperty("真实姓名")
    private String realName;


    /**
     * 所属角色
     */
    @ApiModelProperty(value = "角色",example = "1",dataType = "Integer")
    @ExcelProperty("角色")
    private Integer roleId;
}

  
  //controller

  
   @Autowired
   private UserInfoService userInfoService;

   @PostMapping(value="/test1/import")
    public String importExcel(@RequestParam(name = "file") MultipartFile file) throws IOException {
        userInfoService.importExcel(file);
        return "导入成功";
    }

//Service
public interface UserInfoService extends IService<UserInfo> {
    void importExcel(MultipartFile file) throws IOException;
}

//ServiceImpl
@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {
    @Resource
    private UserInfoMapper userInfoMapper;

    @Override
    public void importExcel(MultipartFile file) throws IOException {
        long beforeTime = System.currentTimeMillis();
        EasyExcel.read(file.getInputStream(),
                UserInfo.class,
                new ImportExcelListener(userInfoMapper)).sheet().headRowNumber(1).doRead();
        long afterTime = System.currentTimeMillis();
        log.info("耗时:{}", afterTime - beforeTime);
    }
}


//mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {   

	@Async
    void insertBatch(List<UserInfo> list);
}

//xml
<!--批量插入-->
<insert id="insertBatch">
    insert into sys_user_info(`username`, password, real_name, role_id)
    values
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.password},#{user.realName}, #{user.roleId})
    </foreach>
</insert>

@Log4j2
public class  ImportExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 每隔1000条存储数据库，然后清理list，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 临时存储
     */
    private List<UserInfo> cachedData = new ArrayList<>(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private UserInfoMapper baseDaoMapper;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public ImportExcelListener(UserInfoMapper baseDaoMapper) {
        this.baseDaoMapper = baseDaoMapper;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param t
     * @param analysisContext
     */
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(t));
        cachedData.add((UserInfo) t);
        if(cachedData .size() >= BATCH_COUNT){
            batchSaveData();
            cachedData = new ArrayList<>(BATCH_COUNT);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //这里也要保存数据，确保最后遗留的数据也存储到数据库
        batchSaveData();
    }

    @Async
    public void batchSaveData() {
        log.info("{}条数据，开始存储数据库!", cachedData.size());
        baseDaoMapper.insertBatch(cachedData);
        log.info("存储数据库成功!");
    }


    /**
     *  收集表头
     * @param t
     * @param analysisContext
     */
    private List<List<String>> head = new ArrayList<>();
    /**
     *  收集错误数据行内容
     * @param t
     * @param analysisContext
     */
    private List<List<String>> data = new ArrayList<>();
    public List<List<String>> getHead() {
        return this.head;
    }

    public List<List<String>> getData() {
        return this.data;
    }
    /**
     * 捕捉错误信息头
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 工具类见下
        EasyExcelErrorFixUtil.setExcelErrorHead(headMap, head);
    }
    /**
     * 捕捉错误信息内容数据
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 调用以下封装的错误数据收集工具
        EasyExcelErrorFixUtil.setErrorData(exception, context, data, head.size());
        if(!CollectionUtils.isEmpty(data)){
            //遍历头
            for (List<String> list : head) {
                for (String column : list) {
                    System.out.print(column+ "  ");
                }
            }
            System.out.println();
            //遍历每一行
            for (List<String> datum : data) {
                for (int i = 0; i < datum.size(); i++) {
                    if(i == 0){
                        //行号+1
                        System.out.print((Integer.parseInt(datum.get(i))+1)+" ");
                        continue;
                    }
                    System.out.print(datum.get(i)+" ");
                }
                System.out.println();
            }
            //此处如果不抛异常则导入正确的数据，错误的不导入
            throw new RuntimeException("有错误数据!");
        }
    }
}

class EasyExcelErrorFixUtil {

    /**
     * 收集导入时的错误数据
     * @param exception
     * @param context 出现异常的内容
     * @param data 将错误数据转换格式后赋值给空集合
     * @param size title的长度
     */
    public static void setErrorData(Exception exception, AnalysisContext context, List<List<String>> data, int size) {
        if (exception instanceof ExcelDataConvertException) {

            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;

            int row = convertException.getRowIndex();
            int column = convertException.getColumnIndex();

            Map<Integer, Cell> cellMapResult = context.readRowHolder().getCellMap();

            List<String> dataList = new ArrayList<>();
            dataList.add(Integer.toString(row));
            for (int i = 0; i < size; i ++) {
                if(cellMapResult.get(i) == null) {
                    dataList.add(null);
                } else if(column == i) {
                    dataList.add("{格式错误}--" + cellMapResult.get(i).toString());
                } else {
                    dataList.add(cellMapResult.get(i).toString());
                }
            }
            data.add(dataList);
        }
    }

    /**
     * 收集导入时Excel的title
     * @param headMap 传入需要处理的title map集合
     * @param head 赋值给空集合
     */
    public static void setExcelErrorHead(Map<Integer, String> headMap, List<List<String>> head) {
        List<String> errorTips = new ArrayList<>();
        errorTips.add("错误数据行");
        head.add(errorTips);
        for(Map.Entry<Integer, String>  entry: headMap.entrySet()) {
            List<String> errorHead = new ArrayList<>();
            errorHead.add(entry.getValue());
            head.add(errorHead);
        }
    }

}