SpringBoot 解析excel 使用easyexcel依赖 2.1.1版本

1. 引入easyexcel 依赖

<dependencies>
        <dependency>
<!--            解析excel 依赖  使用 easyexcel 这个-->
<!--           使用他之前需要引入org.apache.poi 这个依赖才可以使用  这个依赖已经在父模块引入过 这里就不需要引入了-->
<!--            使用easyexcel 2.1.1 版本 必须使用 poi的版本为3.17 否在会报错-->
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

2. 测试

1.控制层

接受上传的文件

实现层

以下是代码

package com.school.eduservice.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.school.eduservice.entity.EduSubject;
import com.school.eduservice.entity.excel.SubjectData;
import com.school.eduservice.listener.SubjectExcelListener;
import com.school.eduservice.mapper.EduSubjectMapper;
import com.school.eduservice.service.EduSubjectService;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;

/**
 * <p>
 * 课程科目 服务实现类
 * </p>
 *
 * @author Json
 * @since 2021-12-05
 */
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

    @Override
    public void addFiles(MultipartFile file,EduSubjectService eduSubjectService) {
        try {
            //文件输入流
            InputStream inputStream = file.getInputStream();
            //调用方法读取
            EasyExcel.read(inputStream,SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead();
        }catch (Exception e){
              e.printStackTrace();
        }


    }
}

3.设置监听器 类

以下是监听器的代码

package com.school.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.school.eduservice.entity.EduSubject;
import com.school.eduservice.entity.excel.SubjectData;
import com.school.eduservice.service.EduSubjectService;
import com.school.service_base.exceptionhandler.schoolException;

/**
 * @User: Json
 * @Date: 2021/12/5
 * excel 监听器  
 **/

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    //因为 SubjectExcelListener 这个类 不能交给 Spring进行管理
    // 比如 controller @RestController 、Service @Service  这些注解 就是让当前类交给Spring管理 可以在类中相互注入
    // 需要自己new  不能注入其他对象
    // 不能进行数据库操作

    //解决方案 把需要的 Service 通过构造函数 传进来
    public EduSubjectService subjectService;

    public SubjectExcelListener() {
    }

    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }


    //读取excel 数据 一行一行的读
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if(subjectData==null){
            throw new schoolException(400,"文件数据为空");
        }

        //一行一行的读取
        // 先判断一级分类是否重复
        EduSubject oneEduSubject = isOneEduSubject(subjectService, subjectData.getOneSubjectName());
        if(oneEduSubject==null){
            oneEduSubject = new EduSubject();
            oneEduSubject.setParentId("0");
            oneEduSubject.setTitle(subjectData.getOneSubjectName());  //设置一级分类名称
            subjectService.save(oneEduSubject);
        }

        //获取一级分类的id
        String pid=oneEduSubject.getId();
        //判断二级分类是否重复
        EduSubject twoEduSubject = isTwoEduSubject(subjectService, subjectData.getTwoSubjectName(),pid);
        if(twoEduSubject==null){
            EduSubject eduSubject = new EduSubject();
            eduSubject.setParentId(pid);
            eduSubject.setTitle(subjectData.getTwoSubjectName());  //设置一级分类名称
            subjectService.save(eduSubject);
        }

    }

    //判断一级分类不能重复添加
    public EduSubject isOneEduSubject(EduSubjectService subjectService,String name){
        QueryWrapper<EduSubject> wrapper=new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id","0");
        EduSubject one = subjectService.getOne(wrapper);
        return one;
    }

    //判断二级分类不能重复添加
    public EduSubject isTwoEduSubject(EduSubjectService subjectService,String name,String pid){
        QueryWrapper<EduSubject> wrapper=new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",pid);
        EduSubject two = subjectService.getOne(wrapper);
        return two;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

具体需求 在监听器里 开发