easypoi读取多sheet的excel文件

引入高版本的jar包

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.4</version>
        </dependency>

同时引入easyPoi和poi是因为我这边还需要处理动态导出excel功能,同时引入他们需要高版本,如果poi引入3.x版本,读取excel会出现错误

java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/FileMagic报错

poi3.7以后请使用ooxml-schemas-1.1.jar或以上的版本,否则报以下错误

POI异常：java.lang.NoClassDefFoundError: org.openxmlformats.schemas.spreadsheetml.x2

主要实现

public R<Object> excelSheetImport(MultipartFile multipartFile) {
        List<SourceCategory> sourceCategories = sourceCategoryMapper.selectList(new LambdaQueryWrapper<>());
        Map<String, String> sourceMap = sourceCategories.stream().collect(Collectors.toMap(SourceCategory::getName, SourceCategory::getCode));
        try {
            List<Object> objList = new ArrayList<>();
            objList.add(new ImportIndex1DataDTO());
            objList.add(new ImportIndex2DataDTO());
            objList.add(new ImportIndex2DataDTO());
            List<List<Object>> resultList = excelHandler.importExcels(multipartFile, 3, objList);
            for (int i = 0; i < resultList.size(); i++) {
                List<Object> objects = resultList.get(i);
                if (i == 0) {
                    List<ImportIndex1DataDTO> voList = JSON.parseArray(JSON.toJSONString(objects), ImportIndex1DataDTO.class);
                    for (ImportIndex1DataDTO importIndex1DataDTO : voList) {
                        List<ImportIndex1DataDTO.FactorContentDTO> factorContentDTOS = new ArrayList<>();
                        factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
                            .builder().code(importIndex1DataDTO.getCode1())
                            .name(importIndex1DataDTO.getName1())
                            .value(importIndex1DataDTO.getValue1())
                            .unit(importIndex1DataDTO.getUnit1())
                            .build());
                        factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
                            .builder().code(importIndex1DataDTO.getCode2())
                            .name(importIndex1DataDTO.getName2())
                            .value(importIndex1DataDTO.getValue2())
                            .unit(importIndex1DataDTO.getUnit2())
                            .build());
                        factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
                            .builder().code(importIndex1DataDTO.getCode3())
                            .name(importIndex1DataDTO.getName3())
                            .value(importIndex1DataDTO.getValue3())
                            .unit(importIndex1DataDTO.getUnit3())
                            .build());
                        EmissionFactor emissionFactor = new EmissionFactor();
                        emissionFactor.setFuelType(importIndex1DataDTO.getFuelType());
                        emissionFactor.setAlias(importIndex1DataDTO.getAlias());
                        emissionFactor.setMeasUnit(importIndex1DataDTO.getMeasUnit());
                        emissionFactor.setEnergyForm(importIndex1DataDTO.getEnergyForm());
                        emissionFactor.setEnergyClass(importIndex1DataDTO.getEnergyClass());
                        emissionFactor.setEnergyType(importIndex1DataDTO.getEnergyType());
                        emissionFactor.setEnergyDesc(importIndex1DataDTO.getEnergyDesc());
                        emissionFactor.setDataContent(JSONObject.toJSONString(factorContentDTOS));
                        emissionFactor.setDataSources(importIndex1DataDTO.getDataSource());
                        emissionFactor.setSourceCategoryCode(sourceMap.get(importIndex1DataDTO.getSourceCategoryName()));
                        emissionFactor.setGasType(importIndex1DataDTO.getGasType());
                        emissionFactor.setCreateTime(LocalDateTime.now());
                        emissionFactor.setIsDeleted(0);
                        EmissionFactor factor = emissionFactorMapper.selectOne(new LambdaQueryWrapper<>(EmissionFactor.class)
                            .eq(EmissionFactor::getFuelType, emissionFactor.getFuelType())
                            .eq(EmissionFactor::getGasType, emissionFactor.getGasType())
                            .eq(EmissionFactor::getSourceCategoryCode, emissionFactor.getSourceCategoryCode())
                        );
                        if (Func.isEmpty(factor)) {
                            emissionFactorMapper.insert(emissionFactor);
                        } else {
                            emissionFactor.setId(factor.getId());
                            emissionFactorMapper.updateById(emissionFactor);
                        }
                    }
                } else {
                    List<ImportIndex2DataDTO> voList = JSON.parseArray(JSON.toJSONString(objects), ImportIndex2DataDTO.class);
                    for (ImportIndex2DataDTO importIndex2DataDTO : voList) {
                        List<ImportIndex1DataDTO.FactorContentDTO> factorContentDTOS = new ArrayList<>();
                        factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
                            .builder().code(importIndex2DataDTO.getCode())
                            .name(importIndex2DataDTO.getName())
                            .value(importIndex2DataDTO.getValue())
                            .unit(importIndex2DataDTO.getUnit())
                            .build());
                        EmissionFactor emissionFactor = new EmissionFactor();
                        emissionFactor.setFuelType(importIndex2DataDTO.getFuelType());
                        emissionFactor.setAlias(importIndex2DataDTO.getAlias());
                        emissionFactor.setMeasUnit(importIndex2DataDTO.getMeasUnit());
                        emissionFactor.setEnergyForm(importIndex2DataDTO.getEnergyForm());
                        emissionFactor.setEnergyClass(importIndex2DataDTO.getEnergyClass());
                        emissionFactor.setEnergyType(importIndex2DataDTO.getEnergyType());
                        emissionFactor.setEnergyDesc(importIndex2DataDTO.getEnergyDesc());
                        emissionFactor.setDataContent(JSONObject.toJSONString(factorContentDTOS));
                        emissionFactor.setDataSources(importIndex2DataDTO.getDataSource());
                        emissionFactor.setSourceCategoryCode(sourceMap.get(importIndex2DataDTO.getSourceCategoryName()));
                        emissionFactor.setGasType(importIndex2DataDTO.getGasType());
                        emissionFactor.setCreateTime(LocalDateTime.now());
                        emissionFactor.setIsDeleted(0);
                        EmissionFactor factor = emissionFactorMapper.selectOne(new LambdaQueryWrapper<>(EmissionFactor.class)
                            .eq(EmissionFactor::getFuelType, emissionFactor.getFuelType())
                            .eq(EmissionFactor::getGasType, emissionFactor.getGasType())
                            .eq(EmissionFactor::getSourceCategoryCode, emissionFactor.getSourceCategoryCode())
                        );
                        if (Func.isEmpty(factor)) {
                            emissionFactorMapper.insert(emissionFactor);
                        } else {
                            emissionFactor.setId(factor.getId());
                            emissionFactorMapper.updateById(emissionFactor);
                        }
                    }
                }
            }
            return R.success("导入excel成功");
        } catch (Exception e) {
            log.error("导入失败", e);
            return R.fail("导入excel失败");
        }
    }

相关util方法

public class ExcelHandler {

    /**
     * 读取多个sheet
     *
     * @param file：文件流
     * @param index：需要读取的sheet个数                              [默认0开始，如果传入3，则读取0 1 2]
     * @param params：每个sheet里面需要封装的对象[如果index为3，则需要传入对应的3个对象]
     * @param <T>
     * @return
     */
    public <T> List<List<T>> importExcels(MultipartFile file, int index, List<Object> params) throws Exception {
        this.checkFile(file);
        List<List<T>> resultList = new LinkedList<>();
        for (int i = 0; i < index; i++) {
            UploadDataListener<T> uploadDataListener = new UploadDataListener<>();
            ExcelReaderBuilder builder = EasyExcelFactory.read(file.getInputStream(), params.get(i).getClass(), uploadDataListener);
            // 从第5行开始读取数据
            builder.sheet(i).headRowNumber(4).doRead();
            List<T> list = uploadDataListener.getList();
            resultList.add(list);
        }
        return resultList;
    }

    /**
     * 文件格式校验
     *
     * @param file：
     */
    private void checkFile(MultipartFile file) {
        if (file == null) {
            throw new RuntimeException("文件不能为空");
        }
        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            throw new RuntimeException("文件不能为空");
        }
        if (!fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc())
            && !fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX_XLS.getDesc())) {
            throw new RuntimeException("请上传.xlsx或.xls文件");
        }
    }

    @Getter
    static enum ExcelTemplateEnum {
        /**
         * 单sheet导出
         */
        TEMPLATE_1("1", "complex"),

        /**
         * 模板格式
         */
        TEMPLATE_SUFFIX("xlsx", ".xlsx"),
        TEMPLATE_SUFFIX_XLS("xls", ".xls"),
        TEMPLATE_SUFFIX_DOCX("docx", ".docx"),
        /**
         * 模板路径
         */
        TEMPLATE_PATH("path", "excel"),
        ;

        private final String code;
        private final String desc;

        ExcelTemplateEnum(String code, String desc) {
            this.code = code;
            this.desc = desc;
        }

        /**
         * 通过code获取msg
         *
         * @param code 枚举值
         * @return
         */
        public static String getMsgByCode(String code) {
            if (code == null) {
                return null;
            }
            ExcelTemplateEnum enumList = getByCode(code);
            if (enumList == null) {
                return null;
            }
            return enumList.getDesc();
        }

        public static String getCode(ExcelTemplateEnum enumList) {
            if (enumList == null) {
                return null;
            }
            return enumList.getCode();
        }

        public static ExcelTemplateEnum getByCode(String code) {
            for (ExcelTemplateEnum enumList : values()) {
                if (enumList.getCode().equals(code)) {
                    return enumList;
                }
            }
            return null;
        }

    }
}

public class UploadDataListener<T> extends AnalysisEventListener<T> {

    /**数据集*/
    private final List<T> list = new ArrayList<>();

    public List<T> getList(){
        return this.list;
    }

    /**
     * 每条数据都会进入
     * @param object：
     * @param analysisContext：
     */
    @Override
    public void invoke(T object, AnalysisContext analysisContext) {
        this.list.add(object);
    }

    /**
     * 数据解析完调用
     * @param analysisContext：
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    /**
     * 异常时调用
     * @param exception：
     * @param context：
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        // 数据解析异常
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            throw new RuntimeException("第" + excelDataConvertException.getRowIndex() + "行" + excelDataConvertException.getColumnIndex() + "列" + "数据解析异常");
        }
    }
}