通过表格批量导入

最新推荐文章于 2024-08-11 20:00:32 发布

weixin_57052296

最新推荐文章于 2024-08-11 20:00:32 发布

阅读量97

点赞数

文章标签： java

本文链接：https://blog.csdn.net/weixin_57052296/article/details/131982175

版权

该文章描述了一个使用EasyExcel库处理Excel文件的过程，包括头检查监听器（EasyExcelHeaderCheck）用于验证Excel表头格式，解析监听器（EasyExcelHeaderListener）在解析数据后执行回调，以及业务层方法（importPhoneByExcel）进行电话号码的正则验证并处理导入数据。在控制层，文件通过POST请求上传并由服务端处理。

摘要由CSDN通过智能技术生成

1.检查头

public class EasyExcelHeaderCheck<T> implements ReadListener<T> {
    /**
     * excel要验证的头
     */
    private final List<String> header;

    /**
     * 是否检查excel头
     */
    private final Boolean checkHeader;

    /**
     * 需要检查的excel头数量
     */
    private final Integer realHeaderCheckCount;

    /**
     * 已经检查的数量
     */
    private Integer checkedCount = 0;

    /**
     * 原始文件对象
     */
    private final MultipartFile file;


    public EasyExcelHeaderCheck(List<String> header, Boolean checkHeader, Integer realHeaderCheckCount, MultipartFile file) {
        this.header = ObjectUtils.isNotEmpty(header) ? header : Collections.emptyList();
        this.checkHeader = ObjectUtils.isNotEmpty(checkHeader) ? checkHeader : false;
        this.realHeaderCheckCount = realHeaderCheckCount == null ? 0 : realHeaderCheckCount;
        this.file = file;
    }

    /**
     * When analysis one head row trigger invoke function.
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        if (ObjectUtils.isNotEmpty(checkHeader) && checkHeader && checkedCount < realHeaderCheckCount) {
            List<String> collect = headMap.values().stream().map(CellData::getStringValue).collect(Collectors.toList());
            if(collect.size() > header.size()){
                throw new BussinessException("excel表头格式不正确");
            }
            for (int i = 0; i < collect.size(); i++) {
                String s = collect.get(i);
                if (ObjectUtils.isNotEmpty(s) && !collect.get(i).equals(header.get(i))) {
                    throw new BussinessException("excel内容格式不正确");
                }
            }
            checkedCount++;
        }

    }

    /**
     * When analysis one row trigger invoke function.
     *
     * @param data    one row value. is same as {@link AnalysisContext#readRowHolder()}
     * @param context analysis context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {

    }

    /**
     * if have something to do after all analysis
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }


}

2.解析监听

public class EasyExcelHeaderListener<T> extends EasyExcelHeaderCheck<T> implements ReadListener<T> {

    private final Consumer<List<T>> consumer;

    private final List<T> cachedDataList = new ArrayList<>();

    /**
     * @param consumer             解析完对应数量之后执行的方法
     * @param header               需要验证的excel头的
     * @param checkHeader          是否需要验证excel头的
     * @param realHeaderCheckCount 需要验证的excel头的数量,easyExcel读取可以配置headRowNumber(2)这个参数,
     *                             数据会从这一行后面开始解析,但是我们可能只需要验证第一行的头部,其余行是注释可以配置realHeaderCheckCount为1
     */
    public EasyExcelHeaderListener(Consumer<List<T>> consumer, List<String> header, Boolean checkHeader,
                                   Integer realHeaderCheckCount, MultipartFile file) {
        super(header, checkHeader, realHeaderCheckCount, file);
        this.consumer = consumer;
    }

    /**
     * When analysis one row trigger invoke function.
     *
     * @param data    one row value.is same as {@link AnalysisContext#readRowHolder()}
     * @param context analysis context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        cachedDataList.add(data);
    }

    /**
     * if have something to do after all analysis
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (CollectionUtils.isNotEmpty(cachedDataList)) {
            consumer.accept(cachedDataList);
        }
    }
}

3.业务层

public String importPhoneByExcel(MultipartFile file) throws IOException {
        List<String> phoneList = new ArrayList<>();
        //验证电话号码的正则表达式
        Pattern p = Pattern.compile("^((13[0-9])|(14[0-9])|(15[0-9])|(16[0-9])|(17[0-9])|(18[0-9])|(19[0-9]))\\d{8}$");
        EasyExcel.read(file.getInputStream(), ImportPhoneByExcelBO.class, new EasyExcelHeaderListener<ImportPhoneByExcelBO>(dataList -> {
                    List<String> errArr = new ArrayList<>();
                    for (int i = 0; i < dataList.size(); i++) {
                        if (p.matcher(dataList.get(i).getPhone()).matches()) {
                            phoneList.add(dataList.get(i).getPhone());
                        } else {
                            errArr.add(String.valueOf(i + 1 + 1));
                        }
                    }
                    if (ObjectUtils.isNotEmpty(errArr)) {
                        String errMsg = String.join(",", errArr);
                        throw new BussinessException("第[" + errMsg + "]行数据不正确,请检查!");
                    }
                }, Collections.singletonList("列名的集合"), true, 1, file))
                .sheet().autoTrim(true).headRowNumber(1).doRead();
        return String.join(",", phoneList);
    }

4.控制层

    @PostMapping("/import/excel")
    public Response<String> importPhoneByExcel(@RequestBody @Valid MultipartFile file) throws IOException {
        return OkResponse.IMPORT.toResponse(announcementService.importPhoneByExcel(file));
    }