SpringBoot整合EasyExcel二级分类

一、Excel导入导出的应用场景

1、数据导入：减轻录入工作量
2、数据导出：统计信息归档
3、数据传输：异构系统之间数据传输

二、EasyExcel简介

1、EasyExcel特点

• Java领域解析、生成Excel比较有名的框架有Apache
  poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的fullgc。
• EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
• EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。

三、EasyExcel的使用

1、引入pom文件

	<dependencies>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

2、创建实体类

@Data
public class ExcelSubjectData{
	//设置列对应的属性
    @ExcelProperty(index = 0)
    private String oneSubjectName;

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

3、创建读取操作的监听器

package com.atguigu.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.excel.ExcelSubjectData;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.servicebase.handler.GuliException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectData> {

    //因为SubjectExcelListener不能交给spring进行管理，需要自己new，不能注入其他对象
    public EduSubjectService subjectService;

    public void setSubjectService(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

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

    //一行一行去读取excle内容,第一个值是一级分类,第二个值是二级分类
    @Override
    public void invoke(ExcelSubjectData excelSubjectData, AnalysisContext analysisContext) {
        if(excelSubjectData==null){
            throw new GuliException(20001, "文件数据为空");
        }

        // //判断一级分类是否重复
        EduSubject existOneSubject = this.existOneSubject(excelSubjectData.getOneSubjectName(), subjectService);
        //判断一级分类
        if(existOneSubject==null){
            existOneSubject=new EduSubject();
            existOneSubject.setTitle(excelSubjectData.getOneSubjectName());
            existOneSubject.setParentId("0");
            subjectService.save(existOneSubject);
        }

        String pid=existOneSubject.getId();
        EduSubject existTwoSubject = this.existTwoSubject(excelSubjectData.getTwoSubjectName(), subjectService, pid);

        //判断二级分类
        if(existTwoSubject==null){
            existTwoSubject=new EduSubject();
            existTwoSubject.setTitle(excelSubjectData.getTwoSubjectName());
            existTwoSubject.setParentId(pid);
            subjectService.save(existTwoSubject);
        }

    }

    //判断一级分类是否重复
    private EduSubject existOneSubject(String name,EduSubjectService subjectService){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title", name);
        wrapper.eq("parent_id", "0");
        EduSubject subject = subjectService.getOne(wrapper);
        return subject;
    }

    //判断二级分类是否重复
    private EduSubject existTwoSubject(String name,EduSubjectService subjectService,String pid){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title", name);
        wrapper.eq("parent_id",pid);
        EduSubject subject = subjectService.getOne(wrapper);
        return subject;
    }

    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

5、接口

public interface EduSubjectService extends IService<EduSubject> {
    void saveSubject(MultipartFile file,EduSubjectService subjectService);
}

6、实现类

    public void saveSubject(MultipartFile file,EduSubjectService subjectService){
        InputStream in= null;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        EasyExcel.read(in, ExcelSubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
    }

7、Controller层

@Autowired
    EduSubjectService subjectService;

    //添加课程
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file){
        subjectService.saveSubject(file,subjectService);
        return R.ok();
    }