使用EasyExcel实现多种类型多sheet页的Excel导入

一、前言

在工作中，我们或多或少的都会用到数据导入功能。
一般情况下我们都是一个sheet的数据导入，这样我们只需要简单的调用EasyExcel的EasyExcel.read(String pathName, Class head, ReadListener readListener)方法即可
但是，很多情况下，我们都会有比较复杂的导入需求，比如说，我需要导入不同类型的Excel，并且Excel中又有多个sheet页，并且其中的sheet页是有关联的。这种用上面的方法就行不通了。
因此，我们可以用策略模式来获取不同的Excel类型，又因为每种类型的Excel的数据读取方式和存储方式是差不多的，所以我们可以用模板方法模式来实现不同类型不同sheet页的数据的读取和存储。

tips：以下为部分为部分伪代码，具体需要根据自己业务实现

二、准备工作

2.1 准备一个导入的基类DemoImportBaseDTO.java，后面需要导入的类都继承这个基类

如果想需要传递一些数据的话，可以在基类中定义一些属性，加上@ExcelIgnore注解即可
一些公共的方法也可以定义在基类里面，例如amountCheck()和numberCheck()方法都是来做参数校验的

@Data
@AllArgsConstructor
@NoArgsConstructor
public class DemoImportBaseDTO extends BaseRowModel implements Serializable {
    private static final Long serialVersionUID = -1L;

    @ExcelIgnore
    private RoleEnum role;

    @ExcelIgnore
    private TypeEnum type;

    @ExcelProperty
    private String errorMessage;

    @ExcelIgnore
    private Long userId;

    public void inputCheck(StringBuffer errMsg) {
        this.setErrorMessage(errMsg.toString());
    }

    public void amountCheck(StringBuffer errMsg, String amount, String name) {
        int index = amount.indexOf(".");
        if (index != -1) {
            String[] split = amount.split("\\.");
            if (split[1].length() > 2) {
                errMsg.append(name);
                errMsg.append("小数点不要超过2位");
            }
        }
    }

    public void numberCheck(StringBuffer errMsg, String amount, String name){
        boolean strResult = amount.matches("-?[0-9]+.?[0-9]*");
        if (!strResult) {
            errMsg.append(name);
            errMsg.append("不是数字");
        }
    }
}

2.2 准备Excel表导入的类，继承DemoImportBaseDTO.java基类

表1有3个sheet页,对应的类为：
Demo1Sheet0ImportDTO.java、Demo1Sheet1ImportDTO.java和Demo1Sheet2ImportDTO.java
表2有2个sheet页，对应的类为：
Demo1Sheet0ImportDTO.java和Demo2Sheet1ImportDTO.java
表3有1个sheet页，对应的类为：
Demo1Sheet0ImportDTO.java
另外：在每个类中都可以写自己的校验规则，如inputCheck()方法

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Demo1Sheet0ImportDTO extends DemoImportBaseDTO {

    @ExcelProperty(index = 0, value = "序号")
    private Integer index;

    @ExcelProperty(index = 1, value = "字段1")
    private String field1;

    @ExcelProperty(index = 2, value = "字段2")
    private String field2;
    
    // .....

    public void inputCheck(StringBuffer errMsg) {
        if (getIndex() == null) {
            errMsg.append("序号不可为空");
        }
        super.inputCheck(errMsg);
    }
}

2.3 准备一个类DemoExcelReadListener.java，用来处理读取出来的Excel数据

@Slf4j
@Data
public class DemoExcelReadListener {

    private static final int BATCH_COUNT = 500;

    private List<DemoImportBaseDTO> cacheData = new ArrayList<>();

    private DemoImportBaseDTO demoImportDTO;

    private List<DemoImportBaseDTO> failedData = new ArrayList<>();

    private List<Integer> successData = new ArrayList<>();

    private Map<Integer, Map<String, String>> relIdMap;

    private DemoImportService demoImportService;
    // 读取数据
    public void invoke(DemoImportBaseDTO baseDTO, AnalysisContext analysisContext) {
        if (baseDTO != null) {
            BeanUtil.copyProperties(demoImportDTO, baseDTO, CopyOptions.create().setIgnoreNullValue(true).setIgnoreError(true));
            cacheData.add(baseDTO);
        }

        if (cacheData.size() > BATCH_COUNT) {
            // 保存数据
            demoImportService.save(cacheData, failedData, successData);
            cacheData.clear();
        }
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        demoImportService.save(cacheData, failedData, successData);
    }
}

2.4 准备一个抽象类DemoImportService.java，用来定义一些导入的框架，后面具体导入的实现类都继承这个类

@Slf4j
public abstract class DemoImportService {

    public DemoExcelReadListener listener;

    public AnalysisEventListener analysisEventListener;

    public Class<? extends DemoImportBaseDTO> clazz;

    public ApiResult<Map<Integer, Map<String, String>>> demoSheet0Import(String fileName, DemoImportBaseDTO importDTO) {
        return read(fileName, importDTO, null, 0, 3);
    }

    public ApiResult<Map<Integer, Map<String, String>>> debtImport(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
        return read(fileName, importDTO, relMap, sheetNo, headNum);
    }

    private ApiResult<Map<Integer, Map<String, String>>> read(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
        this.listener = new DemoExcelReadListener();
        listener.setDemoImportDTO(importDTO);
        listener.setDemoImportService(this);
        listener.setRelIdMap(relMap == null ? new HashMap<>() : relMap);
        EasyExcel.read(fileName, clazz, analysisEventListener)
                .sheet(sheetNo)
                .headRowNumber(headNum)
                .doRead();
        log.info("未导入数量：{}", listener.getFailedData().size() + "条");
        String message = "导入完成，成功" + listener.getSuccessData().size() + "条，失败" + listener.getFailedData().size() + "条";
        ApiResult<Map<Integer, Map<String, String>>> resultDto = new ApiResult<>();
        resultDto.setMessage(message);
        resultDto.setData(listener.getRelIdMap());
        return resultDto;
    }

    public abstract void save(List<DemoImportBaseDTO> cacheData, List<DemoImportBaseDTO> failedData, List<Integer> successData);
}

2.5 准备一个工具类EasyExcelUtil.java，用来将数据以Excel的形式存在指定位置

@Slf4j
public class EasyExcelUtil {
    public static void writeAndSaveExcel(String saveFilePath, Map<String, List<? extends BaseRowModel>> dataListMap,
                                         List<String> sheetNames, List<Class<? extends BaseRowModel>> classes) {
        if (!CollectionUtils.isEmpty(sheetNames) && !MapUtils.isEmpty(dataListMap)) {
            try {
                OutputStream out = new FileOutputStream(saveFilePath);
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
                int sheetNo = 1;
                int headlineMun = 0;
                for (int i = 0; i < sheetNames.size(); ++i) {
                    String sheetName = sheetNames.get(i);
                    Class<? extends BaseRowModel> clas = classes.get(i);
                    Sheet sheet = new Sheet(sheetNo, headlineMun, clas);
                    sheet.setSheetName(sheetName);
                    sheet.setAutoWidth(true);
                    writer.write(dataListMap.get(sheetName), sheet);
                    ++sheetNo;
                }
                writer.finish();
            } catch (FileNotFoundException var7) {
                log.error(var7.getMessage() == null ? "" : var7.getMessage(), var7);
                // throw new Exception(new Object[0].toString());
            } catch (Exception var8) {
                log.error(var8.getMessage() == null ? "" : var8.getMessage(), var8);
                // throw new Exception(new Object[0].toString());
            }
        }
    }
}

2.6 准备一个工具类DemoImportAndExportResponse.java，用来放导入校验失败的数据

@Data
public class DemoImportAndExportResponse implements Serializable {
    private static final Long serialVersionUID = -1L;

    private List<? extends BaseRowModel> data;

    private Class<? extends BaseRowModel> clazz;
}

2.7 准备一个工具类DemoImportRespDto.java，导入返回实体

@Data
public class DemoImportRespDto implements Serializable {
    @ApiModelProperty("异常提示信息列表")
    String errMessage;

    @ApiModelProperty("下载地址")
    private String filePath;
}

三、具体代码实现

准备一个类DemoImportProviderServiceImpl.java，导入的时候调用importDemo()方法即可

3.1 定义一个mapserviceStrategies，并且在Bean初始化的时候赋值，相当于是一个策略模式，减少使用if-else带来的代码臃肿问题

该map的作用是将每个sheet页对应的实现类的名称按key为sheet页的编号，value为bean的名称存储
在取的时候根据type类型和sheet页的编号，获取对应的实现类的Bean的名称

private Map<TypeEnum, Map<Integer, String>> serviceStrategies = new HashMap<>();

@PostConstruct
private void init() {
    if (serviceStrategies.isEmpty()) {
        HashMap<Integer, String> Demo1SubMap = new HashMap<>();
        Demo1SubMap.put(0, "Demo1Sheet0ImportServiceImpl");
        Demo1SubMap.put(1, "Demo1Sheet1ImportServiceImpl");
        Demo1SubMap.put(2, "Demo1Sheet2ImportServiceImpl");
        serviceStrategies.put(TypeEnum.TYPE1, Demo1SubMap);
        HashMap<Integer, String> Demo2Map = new HashMap<>();
        Demo2Map.put(0, "Demo1Sheet0ImportServiceImpl");
        Demo2Map.put(1, "Demo2Sheet1ServiceImpl");
        serviceStrategies.put(TypeEnum.TYPE2, Demo2Map);
        HashMap<Integer, String> Demo3Map = new HashMap<>();
        Demo3Map.put(0, "Demo1Sheet0ImportServiceImpl");
        serviceStrategies.put(TypeEnum.TYPE3, Demo3Map);
    }
}

3.2 importDemo()方法

1. 通过读取Excel获取sheet页，然后遍历这个sheet页列表，通过sheet页的编号去map中获取对应的bean的名称
2. 根据Bean的名称通过applicationContext.getBean(beanName)方法获取对应的Service类
3. 根据sheet页的编号调用对应的导入的方法
4. 将导入失败的数据通过exportErrorData()方法写入Excel，保存到指定位置，然后返回错误信息和Excel的下载地址

@Override
public DemoImportRespDto importDemo(String fileName, String importFileName, TypeEnum type, RoleEnum roleEnum, Long userId) {
    DemoImportBaseDTO importDTO = new DemoImportBaseDTO();
    importDTO.setType(type);
    importDTO.setRole(roleEnum);
    importDTO.setUserId(userId);
    List<ReadSheet> readSheets = EasyExcel.read(fileName).build().excelExecutor().sheetList();
    int sheetSize = readSheets.size();
    DemoImportService demoImportService;
    ApiResult<Map<Integer, Map<String, String>>> resultDto = null;
    ArrayList<String> list = new ArrayList<>();
    LinkedHashMap<String, DemoImportAndExportResponse> map = new LinkedHashMap<>();
    for (ReadSheet sheet : readSheets) {
        int sheetNo = sheet.getSheetNo();
        String sheetName = sheet.getSheetName();
        String beanName = serviceStrategies.get(type).get(sheetNo);
        if (null == beanName) {
            continue;
        }
        demoImportService = (DemoImportService) applicationContext.getBean(beanName);
        if (sheetNo == 0) {
            resultDto = demoImportService.demoSheet0Import(fileName, importDTO);
        } else {
            resultDto = demoImportService.debtImport(fileName, importDTO, resultDto.getData(), sheetNo, 3);
        }
        String message = "sheet" + sheetName + resultDto.getMessage();
        list.add(message);
        if (!demoImportService.listener.getFailedData().isEmpty()) {
            DemoImportAndExportResponse response = new DemoImportAndExportResponse();
            response.setClazz(demoImportService.clazz);
            response.setData(demoImportService.listener.getFailedData());
            map.put(sheetName, response);
        }
    }
    return exportErrorData(importFileName, map, list.toString());
}

3.3 DemoImportProviderServiceImpl.java完整代码

@Component
@Slf4j
public class DemoImportProviderServiceImpl implements DemoImportProviderService {

    @Resource
    private ApplicationContext applicationContext;

    private Map<TypeEnum, Map<Integer, String>> serviceStrategies = new HashMap<>();

    @PostConstruct
    private void init() {
        if (serviceStrategies.isEmpty()) {
            HashMap<Integer, String> Demo1SubMap = new HashMap<>();
            Demo1SubMap.put(0, "Demo1Sheet0ImportServiceImpl");
            Demo1SubMap.put(1, "Demo1Sheet1ImportServiceImpl");
            Demo1SubMap.put(2, "Demo1Sheet2ImportServiceImpl");
            serviceStrategies.put(TypeEnum.TYPE1, Demo1SubMap);
            HashMap<Integer, String> Demo2Map = new HashMap<>();
            Demo2Map.put(0, "Demo1Sheet0ImportServiceImpl");
            Demo2Map.put(1, "Demo2Sheet1ServiceImpl");
            serviceStrategies.put(TypeEnum.TYPE2, Demo2Map);
            HashMap<Integer, String> Demo3Map = new HashMap<>();
            Demo3Map.put(0, "Demo1Sheet0ImportServiceImpl");
            serviceStrategies.put(TypeEnum.TYPE3, Demo3Map);
        }
    }

    @Override
    public DemoImportRespDto importDemo(String fileName, String importFileName, TypeEnum type, RoleEnum roleEnum, Long userId) {
        DemoImportBaseDTO importDTO = new DemoImportBaseDTO();
        importDTO.setType(type);
        importDTO.setRole(roleEnum);
        importDTO.setUserId(userId);
        List<ReadSheet> readSheets = EasyExcel.read(fileName).build().excelExecutor().sheetList();
        int sheetSize = readSheets.size();
        if (type.equals(TypeEnum.TYPE2)) {
            if (sheetSize != 2) {
                log.error("请导入对应的模板");
            }
        } else if (type.equals(TypeEnum.TYPE1)) {
            if (sheetSize != 4) {
                log.error("请导入对应的模板");
            }
        } else {
            if (sheetSize != 1) {
                log.error("请导入对应的模板");
            }
        }
        DemoImportService demoImportService;
        ApiResult<Map<Integer, Map<String, String>>> resultDto = null;
        ArrayList<String> list = new ArrayList<>();
        LinkedHashMap<String, DemoImportAndExportResponse> map = new LinkedHashMap<>();
        for (ReadSheet sheet : readSheets) {
            int sheetNo = sheet.getSheetNo();
            String sheetName = sheet.getSheetName();
            String beanName = serviceStrategies.get(type).get(sheetNo);
            if (null == beanName) {
                continue;
            }
            demoImportService = (DemoImportService) applicationContext.getBean(beanName);
            if (sheetNo == 0) {
                resultDto = demoImportService.demoSheet0Import(fileName, importDTO);
            } else {
                resultDto = demoImportService.debtImport(fileName, importDTO, resultDto.getData(), sheetNo, 3);
            }
            String message = "sheet" + sheetName + resultDto.getMessage();
            list.add(message);
            if (!demoImportService.listener.getFailedData().isEmpty()) {
                DemoImportAndExportResponse response = new DemoImportAndExportResponse();
                response.setClazz(demoImportService.clazz);
                response.setData(demoImportService.listener.getFailedData());
                map.put(sheetName, response);
            }
        }
        return exportErrorData(importFileName, map, list.toString());
    }

    private DemoImportRespDto exportErrorData(String importFileName, LinkedHashMap<String, DemoImportAndExportResponse> data, String message) {
        DemoImportRespDto demoImportRespDto = new DemoImportRespDto();
        LinkedHashMap<String, List<? extends BaseRowModel>> map = new LinkedHashMap<>();
        List<Class<? extends BaseRowModel>> classes = new ArrayList<>();
        List<String> sheetNames = new ArrayList<>();
        String tempFilePath = "D:\\doc";
        String destFileName = importFileName + "_" + new SimpleDateFormat("yyyyMMdd").format(new Date()) + "导入错误数据" + ".xlsx";
        if (!data.isEmpty()) {
            data.forEach((k, v) -> {
                sheetNames.add(k);
                classes.add(v.getClazz());
                map.put(k, v.getData());
            });
            try {
                log.info("保存文件失败");
                EasyExcelUtil.writeAndSaveExcel(tempFilePath + destFileName, map, sheetNames, classes);
            } catch (Exception e) {
                log.warn("保存导入失败文件失败");
            }
        }
        demoImportRespDto.setErrMessage(message);
        demoImportRespDto.setFilePath(tempFilePath + destFileName);
        return demoImportRespDto;
    }
}

四、具体实现类

4.1 Demo1Sheet0ImportServiceImpl.java具体代码实现

@Service
@Slf4j
public class Demo1Sheet0ImportServiceImpl extends DemoImportService {

    @Resource
    private ApplicationContext applicationContext;

    @Override
    public ApiResult<Map<Integer, Map<String, String>>> demoSheet0Import(String fileName, DemoImportBaseDTO demoImportDTO) {
        this.analysisEventListener = new AnalysisEventListener<Demo1Sheet0ImportDTO>() {
            @Override
            public void invoke(Demo1Sheet0ImportDTO demoImport, AnalysisContext analysisContext) {
                log.info("数据校验");
                StringBuffer check = new StringBuffer();
                demoImport.inputCheck(check);
                if (StringUtils.isNotEmpty(check)) {
                    export4CheckError(demoImport, analysisContext, check);
                    return;
                }
                log.info("继续校验");
                export4CheckError(demoImport, analysisContext, check);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                listener.doAfterAllAnalysed(analysisContext);
            }
        };
        this.clazz = DemoImportDTO.class;
        return super.DemoImport(fileName, demoImportDTO);
    }

    private void export4CheckError(Demo1Sheet0ImportDTO demoImport, AnalysisContext analysisContext, StringBuffer check) {
        demoImport.setErrorMessage(check.toString());
        listener.invoke(demoImport, analysisContext);
    }

    @Override
    public void save(List<DemoImportBaseDTO> demoImportDTOS, List<DemoImportBaseDTO> failedData, List<Integer> successData) {
        if (CollectionUtils.isEmpty(DemoImportDTOS)) {
            return;
        }
        DemoImportService demoImportService = (DemoImportService) applicationContext.getBean("Demo1Sheet0ImportServiceImpl");
        demoImportService.clazz = this.clazz;
        demoImportService.listener = this.listener;
        DemoImportBaseDTO demoImportBaseDTO = demoImportDTOS.get(0);
        RoleEnum role = demoImportBaseDTO.getRole();
        Long userId = demoImportBaseDTO.getUserId();
        List<Demo1Sheet0ImportDTO> demoDTOS = new ArrayList(demoImportDTOS);
        List<Demo1Sheet0ImportDTO> demo1Sheet0ImportDTOS = new ArrayList<>();
        for (Demo1Sheet0ImportDTO item : demoDTOS) {
            if (StringUtils.isNoneBlank(item.getErrorMessage())) {
                failedData.add(item);
                continue;
            }
            Demo1Sheet0ImportDTOS.add(item);
            successData.add(item.getIndex());
        }
        // 执行插入数据库操作...
        // 执行插入数据库操作...
        // 执行插入数据库操作...
        log.info("执行插入数据库操作");
        demo1Sheet0ImportDTOS.forEach(demo1Sheet0ImportDTO -> {
            HashMap<String, String> param = new HashMap<>();
            param.put("relId", demo1Sheet0ImportDTO.getBuyerName());
            listener.getRelIdMap().put(demo1Sheet0ImportDTO.getIndex(), param);
        });
    }

}

4.2 Demo2Sheet1ImportServiceImpl.java具体代码实现

@Service
@Slf4j
public class Demo2Sheet1ImportServiceImpl extends DemoImportService {
    
    @Resource
    private ApplicationContext applicationContext;

    @Override
    public ApiResult<Map<Integer, Map<String, String>>> debtImport(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
        this.analysisEventListener = new AnalysisEventListener<Demo2Sheet1ImportDTO>() {
            @Override
            public void invoke(Demo2Sheet1ImportDTO demo2Sheet1ImportDTO, AnalysisContext analysisContext) {
                if (listener.getRelIdMap().get(demo2Sheet1ImportDTO.getIndex()) == null){
                    demo2Sheet1ImportDTO.setErrorMessage("关联数据没有导入成功");
                    listener.invoke(demo2Sheet1ImportDTO, analysisContext);
                }else {
                    log.info("一些赋值可以在这");
                    String billNo = listener.getRelIdMap().get(demo2Sheet1ImportDTO.getIndex()).get("no");
                    demo2Sheet1ImportDTO.setBillNo(billNo);
                }
                demo2Sheet1ImportDTO.inputCheck(new StringBuffer());
                listener.invoke(demo2Sheet1ImportDTO, analysisContext);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                listener.doAfterAllAnalysed(analysisContext);
            }
        };
        this.clazz = demo2Sheet1ImportDTO.class;
        return super.debtImport(fileName, importDTO, relMap, sheetNo, 4);
    }

    @Override
    public void save(List<DemoImportBaseDTO> demoImportDTOS, List<DemoImportBaseDTO> failedData, List<Integer> successData) {
        if (CollectionUtils.isEmpty(demoImportDTOS)) {
            return;
        }
        DemoImportService demoImportService = (DemoImportService) applicationContext.getBean("Demo2Sheet1ServiceImpl");
        demoImportService.clazz = this.clazz;
        demoImportService.listener = this.listener;
        List<Demo2Sheet1ImportDTO> demo2Sheet1DTOS = new ArrayList(demoImportDTOS);
        List<Demo2Sheet1ImportDTO> demo2Sheet1ImportDTOS = new ArrayList<>();
        for (Demo2Sheet1ImportDTO item : demo2Sheet1DTOS) {
            if (listener.getRelIdMap().get(item.getIndex()) == null) {
                item.setErrorMessage("关联数据没有导入成功");
                failedData.add(item);
                continue;
            }
            if (StringUtils.isNoneBlank(item.getErrorMessage())) {
                failedData.add(item);
                continue;
            }
            log.info("构造对象");
            log.info("对象插入数据库");
            demo2Sheet1ImportDTOS.add(item);
            successData.add(item.getIndex());
        }
        log.info("执行插入数据库操作");
        // xxxservice.saveBath(demo2Sheet1ImportDTOS)
    }
}

五、总结

1. 通过策略模式来消除if-else带来的代码臃肿问题
2. 通过模板方法模式，创建一个抽象类，然后子类去重写一些方法，来实现各自不同的功能