EasyExcel 的使用方法

1.引入依赖

  <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.1.1</version>
            </dependency>
            
  <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-extension -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.4.1</version>
        </dependency>

2.前端调用后端接口

3.后端创建接口

@Autowired
    private SubjectService subjectService;

    @ApiOperation(value = "excel批量导入")
    @PostMapping("/addSubject")
    public Msg addSubject(MultipartFile file){
        subjectService.saveSubject(file,subjectService);
        return Msg.success();
    }

3.1创建实体类

@Data
public class ExcelSubjectData {
    @ExcelProperty(index = 0)
    private String oneSubjectName;

    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

3.2调用service层

public interface SubjectService extends IService<Subject> {

    void saveSubject(MultipartFile file,SubjectService subjectService);
    }

3.3实现service层

//添加课程分类
//poi读取excel内容
@Override
public void importSubjectData(MultipartFile file,EduSubjectService subjectService) {
   try {
        //1 获取文件输入流
        InputStream inputStream = file.getInputStream();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(inputStream, ExcelSubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
    }catch(Exception e) {
        e.printStackTrace();
        throw new GuliException(20002,"添加课程分类失败");
    }
}

4.创建Excel监听器

public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectData> {

    public SubjectService subjectService;

    public SubjectExcelListener() {
    }

    //创建有参数构造，传递subjectService用于操作数据库
    public SubjectExcelListener(SubjectService subjectService) {
        this.subjectService = subjectService;
    }

    //一行一行去读取excle内容
    @Override
    public void invoke(ExcelSubjectData excelSubjectData, AnalysisContext analysisContext) {
        if (excelSubjectData == null){
            throw new GuliException(20001,"文件数据为空");
        }
        //添加一级分类
        Subject subject = this.existOneSubject(subjectService, excelSubjectData.getOneSubjectName());
        if (subject == null){//没有相同的
             subject =new Subject();
            subject.setParentId("0");
            subject.setTitle(excelSubjectData.getOneSubjectName());
            subjectService.save(subject);
        }
        //获取一级分类id值
        String pid =subject.getId();
        //添加二级分类
        Subject subject2 = this.existTwoSubject(subjectService, excelSubjectData.getTwoSubjectName(), pid);
        if(subject2 == null){
            subject2 =new Subject();
            subject2.setParentId(pid);
            subject2.setTitle(excelSubjectData.getTwoSubjectName());
            subjectService.save(subject2);
        }
    }
    //判断一级分类是否重复
    private Subject existOneSubject(SubjectService subjectService,String name){
        QueryWrapper<Subject> wrapper =new QueryWrapper<>();
        wrapper.eq("title", name);
        wrapper.eq("parent_id", "0");
        Subject one = subjectService.getOne(wrapper);
        return one;
    }
    //判断二级分类是否重复
    private Subject existTwoSubject(SubjectService subjectService,String name,String pid){
        QueryWrapper<Subject> wrapper =new QueryWrapper<>();
        wrapper.eq("title", name);
        wrapper.eq("parent_id", pid);
        Subject one = subjectService.getOne(wrapper);
        return one;
    }
    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}