SpringBoot，EasyExcel导入xlsx文件

实体类

@Data
@TableName("")
//一定要实现序列化，防止内存溢出
public class EquipmentExcel implements Serializable {

    
    @ExcelProperty(value = {"所属部门"})
    @TableField("dept_guid")
    private String deptGuid;
}

控制层

   @ControllerEndpoint(operation = "上传设备数据", exceptionMessage = "导入Excel失败")
   
    @PostMapping(value = "/uploadExcel", consumes = "multipart
    // 读取三百条数据，批量插入一次。
    private static final int BATCH_COUNT = 300;
    // 记录Excel读取的行数
    private int COUNT = 0;

    // 记录Excel重复的行数
    private int duplicateCOUNT = 0;

    //保存读取成功的Excel数据
    private List list = new ArrayList<>();

    //保存读取失败的Excel消息
    private List errorMessage = new ArrayList<>();

    //DAO
    private EquipmentMapper baseMapper;

    // 设备类别（有线设备，NB设备，消防主机，用户传输装置）
    private List deviceCategory = new ArrayList<>();
    

    //初始化监听器
    public ExcelModelListener(EquipmentMapper baseMapper, List deviceCategory, List deviceType, List status, CurrentUser user,List Tdept,List Tguiddata) {
        this.baseMapper = baseMapper;
        this.deviceCategory = deviceCategory;
    }


    //解析到一条数据,对每条数据进行操作
    @Override
    public void invoke(EquipmentExcel data, AnalysisContext context) {

        this.COUNT = this.COUNT + 1;

        //     判断字段长度和字段为空
        checkLengthAndNull(data);
        // 检查自定义字符串格式,将字符串转化成字典id,绑定默认值
        checkDiy(data);
        // 映射字段
        //添加到集合
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();

        StringBuilder sb = new StringBuilder();
        String countMessage = MessageFormat.format(
                "一共{0}条数据，成功导入{1}条数据，重复{2}条数据，失败{3}条数据:",
                this.COUNT, this.COUNT - this.duplicateCOUNT - this.errorMessage.size(),
                duplicateCOUNT, this.errorMessage.size());
        sb.append(countMessage);
        //将错误信息用字符串返回
        if (this.errorMessage.size() > 0) {
            errorMessage.forEach((it) -> {
                sb.append("|");
                sb.append(it);
            });
        }
        throw new ExcelAnalysisException(sb.toString());
    }

    
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        this.headMap = headMap;
    }


    
    @Override
    public void onException(Exception exception, AnalysisContext context) {

        if (exception instanceof ExcelDataConvertException) {
            //转换格式发生的异常,不会进入invoke解析,这里要++ 把解析失败数据也加上
            this.COUNT=this.COUNT+1;
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            // 获取列名
            String columnName = (String) this.headMap.get(excelDataConvertException.getColumnIndex());
            // 获取行
            int row = excelDataConvertException.getRowIndex();
            // 获取出错的单元块
            String cellData = excelDataConvertException.getCellData().toString();
            String mes = MessageFormat.format("错误发生在{0}行，{1}列，数据为:{2}", (row+1), columnName, cellData);
            errorMessage.add(mes);
        }
        // 自定义抛出的异常
        else {
            errorMessage.add(exception.getMessage());
        }
    }

    

    private void saveData() {
        //一次性插入多条，自定义sql语句
        if (!CollectionUtils.isEmpty(list)) {
            //去重前数据条数
            int originSize = list.size();
            //  调用Excel内部数据去重方法
            list = duplicateRemovalExcel(list);
            //调用数据库内部数据去重方法
            duplicateRemoval(list);
            //去重后数据条数
            int duplicateSize = list.size();
            this.duplicateCOUNT = this.duplicateCOUNT + (originSize - duplicateSize);

        }
        //再判断一次，防止去重完毕List为空,插入数据库报错
        if(!CollectionUtils.isEmpty(list)) {
            //最后插入数据
            //插入数据库要不要捕捉异常
            baseMapper.insertexcel(list);
        }

    }

    //    自定义异常
    private void throwException(String message) {
        String mes = MessageFormat.format("错误发生在{0}行:{1}", this.COUNT+1,message);
        throw new ExcelAnalysisException(mes);
    }



    //     检查字段长度和字段为空
    private void checkLengthAndNull(EquipmentExcel data) {

        // 如果整行数据为空
        if (data == null) {
            throwException("整行数据为空");
        }
     
    //数据库中的数据去重
    private List duplicateRemoval(List data) {
        List dbDeviceSource = baseMapper.getAllDeviceSource();

//        不能用foreach 迭代删除  java.util.ConcurrentModificationException
        for (Iterator i = data.iterator(); i.hasNext(); ) {
            EquipmentExcel equipmentExcel = i.next();
            for (Iterator j = dbDeviceSource.iterator(); j.hasNext(); ) {
                String deviceSource = j.next();
                if (equipmentExcel.getDeviceSource().equals(deviceSource)) {
                    i.remove();
                }
            }
        }
        return data;
    }

    //    先把Excel中重复的数据去除
    private List duplicateRemovalExcel(List list) {
        Set EquipmentExcelSet = new TreeSet<>((o1, o2) -> o1.getDeviceSource().compareTo(o2.getDeviceSource()));
        EquipmentExcelSet.addAll(list);
        return new ArrayList<>(EquipmentExcelSet);
    }
}