EasyExcel读取多个sheet表数据，自定义监听器-526互联

接口

 /**
     * 导入
     *
     * @param file
     * @return
     */
    @PostMapping("/waitimport")
    public Result waitImport(@RequestParam("file") MultipartFile file) {
        try {

            ExcelReader read = EasyExcel.read(file.getInputStream()).build();

            ReadSheet sheet1 = EasyExcel.readSheet("流水列表").head(BankSerialWaitCheckImportDto.class).registerReadListener(bankSerialWaitCheckImportListener).build();
            ReadSheet sheet2 = EasyExcel.readSheet("明细列表").head(BusinessBillsBankSerialImportDto.class).registerReadListener(businessBillsBankSerialImportListener).build();

            read.read(sheet1, sheet2);
            read.finish();

            ThreadLocal<List<BankSerialWaitCheckImportDto>> dataListLocal1 = bankSerialWaitCheckImportListener.getDataList();
            List<BankSerialWaitCheckImportDto> bsList = (List<BankSerialWaitCheckImportDto>) dataListLocal1.get();

            ThreadLocal<List<BusinessBillsBankSerialImportDto>> dataListLocal2 = businessBillsBankSerialImportListener.getDataList();
            List<BusinessBillsBankSerialImportDto> billsRecv = (List<BusinessBillsBankSerialImportDto>) dataListLocal2.get();

            Result result = bankSerialService.handleWaitCheckPageImport(bsList, billsRecv);
            return Result.ok();
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("导入失败：" + e.getMessage());
        }
    }

监听器

package com.chinaums.chcp.biz.importXls.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.chinaums.chcp.biz.importXls.vo.BankSerialWaitCheckImportDto;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
@Scope("prototype")
public class BankSerialWaitCheckImportListener extends AnalysisEventListener<BankSerialWaitCheckImportDto> {

    // 保存结果
    List<BankSerialWaitCheckImportDto> saveList = new ArrayList<>();

    ThreadLocal<List<BankSerialWaitCheckImportDto>> dataList = new ThreadLocal<>();



    /**
     * 每读一行内容，都会调用一次该对象的invoke,在invoke可以操作使用读取到的数据
     *
     * @param vo
     * @param analysisContext
     */
    @Override
    public void invoke(BankSerialWaitCheckImportDto vo, AnalysisContext analysisContext) {
        vo.setNum(saveList.size() + 1);
        saveList.add(vo);
    }

    /**
     * 读取完整个文档之后，调用的方法
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        List<BankSerialWaitCheckImportDto> newList = new ArrayList<>();
        newList.addAll(saveList);
        saveList.clear();
        dataList.set(newList);

    }

    public ThreadLocal<List<BankSerialWaitCheckImportDto>> getDataList() {
        return dataList;
    }
}

package com.chinaums.chcp.biz.importXls.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.chinaums.chcp.biz.importXls.vo.BusinessBillsBankSerialImportDto;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
@Scope("prototype")
public class BusinessBillsBankSerialImportListener extends AnalysisEventListener<BusinessBillsBankSerialImportDto> {

    // 保存结果
    List<BusinessBillsBankSerialImportDto> saveList = new ArrayList<>();

    ThreadLocal<List<BusinessBillsBankSerialImportDto>> dataList = new ThreadLocal<>();



    /**
     * 每读一行内容，都会调用一次该对象的invoke,在invoke可以操作使用读取到的数据
     *
     * @param vo
     * @param analysisContext
     */
    @Override
    public void invoke(BusinessBillsBankSerialImportDto vo, AnalysisContext analysisContext) {
        vo.setNum(saveList.size() + 1);
        saveList.add(vo);
    }

    /**
     * 读取完整个文档之后，调用的方法
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        List<BusinessBillsBankSerialImportDto> newList = new ArrayList<>();
        newList.addAll(saveList);
        saveList.clear();
        dataList.set(newList);

    }

    public ThreadLocal<List<BusinessBillsBankSerialImportDto>> getDataList() {
        return dataList;
    }
}

实体类

package com.xxx.chcp.biz.importXls.vo;

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

import java.io.Serializable;

/**
    * 流水列表
    */
@Data
//导出时忽略没有@ExcelProperty的字段
@ExcelIgnoreUnannotated
public class BankSerialWaitCheckImportDto implements Serializable {
    private Integer num;

    @ExcelProperty(value = "唯一标识")
    private String id;


    /**
     * 银行名称-关联
     */
    @ExcelProperty(value = "银行名称")
    private String bankName;

    /**
     * 项目名称
     */
    @ExcelProperty(value = "项目名称")
    private String projectName;

    /**
     * 备注
     */
    @ExcelProperty(value = "备注")
    private String remark;

}

package com.xxx.chcp.biz.importXls.vo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.chinaums.chcp.base.annotation.Dict;
import com.chinaums.chcp.base.constant.SysConstant;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import lombok.Data;

import java.io.Serializable;
import java.time.LocalDateTime;


/**
    * 明细列表
    */
@Data
//导出时忽略没有@ExcelProperty的字段
@ExcelIgnoreUnannotated
public class BusinessBillsBankSerialImportDto implements Serializable {
    private Integer num;

    /**
     * 关联银行流水入账ID
     */
    @ExcelProperty(value = "关联的唯一标识")
    private String bankSerialId;

    /**
     * 合同号
     */
    @ExcelProperty(value = "合同编号")
    private String contractNo;

    /**
     * 合同金额
     */
    @ExcelProperty(value = "合同金额")
    private String contractAmount;

}