EasyExcel导入

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

转换器

package com.zs.ac.modules.excel.convert;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.zs.ac.modules.excel.enumtype.KnowledgeEntryType;
import lombok.extern.slf4j.Slf4j;

/**
 *
 */
@Slf4j
public class KnowledgeEntryTypeConverter implements Converter<KnowledgeEntryType> {

    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return null;
    }

    @Override
    public KnowledgeEntryType convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 导入的时候直接导入数字即可 不用操作
        if (cellData.toString().equals("1")) {
            return KnowledgeEntryType.TEXT;
        } else if (cellData.toString().equals("2")) {
            return KnowledgeEntryType.STRUCTURE;
        } else if (cellData.toString().equals("3")) {
            return KnowledgeEntryType.RICH_TEXT;
        } else {
            log.error("导入的数据有误");
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(KnowledgeEntryType value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return Converter.super.convertToExcelData(value, contentProperty, globalConfiguration);
    }

}

监听器

package com.finshine.finaps.web.listener;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.finshine.finaps.web.dto.equipment.EquipmentCreateDTO;
import com.finshine.finaps.web.enums.ProcessTypeEnum;
import com.finshine.finaps.web.service.EquipmentService;
import com.finshine.finaps.web.vo.EquipmentExcelVO;
import lombok.extern.slf4j.Slf4j;

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

@Slf4j
public class EasyExcelListener extends AnalysisEventListener<EquipmentExcelVO> {
    private final EquipmentService equipmentService;

    public EasyExcelListener(EquipmentService equipmentService) {
        this.equipmentService = equipmentService;
    }


    /**
     * 每隔100条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    List<EquipmentExcelVO> list = new ArrayList<EquipmentExcelVO>();

    @Override
    public void invoke(EquipmentExcelVO data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSONObject.toJSON(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        for (EquipmentExcelVO equipmentExcelVO : list) {
            //存放转换后的工序数字
            List<String> process = new ArrayList<>();
            String status = equipmentExcelVO.getStatus();
            if (StringUtils.equals("正常", status)) {
                // 0正常 1-故障 2-维修
                equipmentExcelVO.setStatus("0");
            } else if (StringUtils.equals("故障", status)) {
                equipmentExcelVO.setStatus("1");
            } else if (StringUtils.equals("维修", status)) {
                equipmentExcelVO.setStatus("2");
            }
            String processType = StrUtil.removeAll(equipmentExcelVO.getProcessType(), " ");
            //逗号-不区分中英文逗号
            String regex = ",|，|\\s+";
            //存放工序列表
            List<String> processTypeList = List.of(processType.split(regex));
            processTypeList.forEach(p -> {
                Integer type = getType(p);
                process.add(String.valueOf(type));
            });
            EquipmentCreateDTO equipmentEntityDto = BeanUtil.copyProperties(equipmentExcelVO, EquipmentCreateDTO.class);
            equipmentEntityDto.setProcessType(process);
            equipmentService.create(equipmentEntityDto);
            //每次插入后清空工序数字
            process.clear();
        }
        log.info("存储数据库成功！");
    }
    
    /**
     * 根据工序中文 得到对应数字
     *
     * @param process 工序
     * @return {@code Integer}
     */
    private Integer getType(String process) {
        if (StringUtils.equals(ProcessTypeEnum.TURNING.getProcessName(), process)) {
            return ProcessTypeEnum.TURNING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.MILLING.getProcessName(), process)) {
            return ProcessTypeEnum.MILLING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.DRILLING.getProcessName(), process)) {
            return ProcessTypeEnum.DRILLING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.GRINDING.getProcessName(), process)) {
            return ProcessTypeEnum.GRINDING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.FORGING.getProcessName(), process)) {
            return ProcessTypeEnum.FORGING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.STAMPING.getProcessName(), process)) {
            return ProcessTypeEnum.STAMPING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.WELDING.getProcessName(), process)) {
            return ProcessTypeEnum.WELDING.getProcessCode();
        } else if (StringUtils.equals(ProcessTypeEnum.ASSEMBLY.getProcessName(), process)) {
            return ProcessTypeEnum.ASSEMBLY.getProcessCode();
        } else {
            return 0;
        }
    }
}

实体类

package com.finshine.finaps.web.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @description
 * @since 2023-01-10 16:50
 */
@Data
public class EquipmentExcelVO {
    @ApiModelProperty(value = "设备编号")
    @ExcelProperty(value = "设备编号", index = 1)
    private String equipmentNum;

    @ApiModelProperty("设备类型")
    @ExcelProperty(value = "设备类型", index = 2)
    private String equipmentType;

    @ApiModelProperty("设备型号")
    @ExcelProperty(value = "设备型号", index = 3)
    private String spec;

    @ApiModelProperty(value = "工序类型")
    @ExcelProperty(value = "可用工序类型", index = 4)
    private String processType;

    @ExcelProperty(value = "设备状态", index = 5, converter = KnowledgeEntryTypeConverter.class)
    private String status;
}

controller

    @ApiOperation("导入设备excel")
    @PostMapping("/importDeviceExcel")
    public RespWrapper testImport(@RequestParam("file") MultipartFile multipartFile) {
        try {
            EasyExcel.read(multipartFile.getInputStream(), EquipmentExcelVO.class, new EasyExcelListener(equipmentService)).sheet().doRead();
        } catch (Exception e) {
            throw new BusinessException("数据导入失败:" + e);
        }
        return RespWrapper.buildSuccessResp("导入成功");
    }