EasyExcel技术读写excel表格数据，以树形式数据返回前端（springboot）

导入依赖

	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
    <!--xls-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
        </dependency>

easyexcel技术是对POI技术的一个增强简化

写操作

1.创建一个实体类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SubjectData {
    @ExcelProperty(index = 0)
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

@ExcelProperty()注解中index属性是表示读写excel时的索引位置，除表头外，从0开始记
2.在主程序实现写操作

import com.alibaba.excel.EasyExcel;

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

/**
 * 测试EasyExcel使用读写
 */
public class testExcel {
    public static void main(String[] args) {
        //读写的文件路径
        String filename="D:\\";
        //写入操作
        EasyExcel.write(filename,ExcelData.class).sheet("联系人").doWrite(getList());
        //读取操作
        //(filename,,new ExcelListener()).sheet().doRead();
    }
    //设置要写入的对象list
    private static List<ExcelData> getList(){
        List<ExcelData> list=new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            ExcelData excelData=new ExcelData();
            excelData.setId(i);
            excelData.setAge(i);
            excelData.setName("kk"+i);
            list.add(excelData);
        }
        return list;
    }
}

(filename,).sheet(“联系人”).doWrite(getList());方法中，sheet是于写excel文件时表单地下的sheet对应，doWrite（）传要写的内容，也也自己帮做了关流

读取excel文件添加到数据库（重点）

1.在上面创建好实体类后创建controller

 //获取到上传的文件，把文件内容读取
    @ApiOperation("上传excel文件读取添加课程分类")
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file) {
        //上传过来的excel文件
        boolean flag=subjectService.saveSubject(file, subjectService);
        if(flag){
            return R.ok().message("文件上传成功");
        }else {
            return R.error().message("文件上传失败");
        }
    }

2.创建service

public interface EduSubjectService extends IService<EduSubject> {

    //添加课程分类
    Boolean saveSubject(MultipartFile file, EduSubjectService subjectService);

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

   
    @Override
    public Boolean saveSubject(MultipartFile file, EduSubjectService subjectService) {
        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }

3.创建监听器，增强数据结构，以树的形式返回前端

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.lmh.eduService.entity.EduSubject;
import com.lmh.eduService.entity.excel.SubjectData;
import com.lmh.eduService.service.EduSubjectService;
import com.lmh.servicebase.exceptionhandler.LmhException;

//因为该类无法交给spring管理，就无法注入和被其他注入，只能new出来，则不能实现操作数据库
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
    //解决不注入的问题，自己new一个，用构造函数赋值
    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 LmhException(20001, "文件数据为空");
        }
        //一行一行的读，第一个值为一级分类，第二个值为二级分类
        //先判断一级是否重复
        EduSubject oneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
        if(oneSubject==null){//则表示不重复
            oneSubject=new EduSubject();
            oneSubject.setSort(0);
            oneSubject.setParentId("0");
            oneSubject.setTitle(subjectData.getOneSubjectName());

            subjectService.save(oneSubject);
        }
        //获取一级分类id值
        String pid=oneSubject.getId();
        //判断二级
        EduSubject twoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
        if(twoSubject==null){
            twoSubject=new EduSubject();
            twoSubject.setSort(0);
            twoSubject.setTitle(subjectData.getTwoSubjectName());
            twoSubject.setParentId(pid);

            subjectService.save(twoSubject);
        }
    }

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

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

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

4.其中问题：
（1）R 为自定义的json返回数据类型类，便于前后端数据传递

package com.lmh.commonutils;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.HashMap;
import java.util.Map;

/**
 * 返回数据统一类型，设置类
 */
@Data
public class R {
    @ApiModelProperty(value = "是否成功")
    private Boolean success;

    @ApiModelProperty(value = "返回码")
    private Integer code;

    @ApiModelProperty(value = "返回消息")
    private String message;

    @ApiModelProperty(value = "返回数据")
    private Map<String,Object> data=new HashMap<String, Object>();

    //构造私有，只能使用静态方法的数据结构
    private R(){};

    //成功 返回数据结构
    public static R ok(){
        R r=new R();
        r.setSuccess(true);
        r.setCode(ResultCode.SUCCESS);
        r.setMessage("成功");
        return r;
    }

    //失败 返回数据结构
    public static R error(){
        R r=new R();
        r.setSuccess(false);
        r.setCode(ResultCode.ERROR);
        r.setMessage("失败");
        return r;
    }

    //可以链式编程
    public R success(Boolean success){
        this.setSuccess(success);
        return this;
    }

    public R message(String message){
        this.setMessage(message);
        return this;
    }

    public R code(Integer code){
        this.setCode(code);
        return this;
    }

    public R data(String key, Object value){
        this.data.put(key, value);
        return this;
    }

    public R data(Map<String, Object> map){
        this.setData(map);
        return this;
    }
}

/**
 * 状态码常量
 */
public interface ResultCode {
    public static Integer SUCCESS=20000;//表示成功的状态码
    public static Integer ERROR=20001;//表示失败的状态码
}

（2）在监听器中，在invoke方法中对数据进行自定义格式封装，其中的类是自定义类型

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

//一级分类
@Data
public class OneSubject {

    private String id;

    private String title;

    //一个一级分类有多个二级分类
    private List<TwoSubject> children=new ArrayList<>();
}

import lombok.Data;

//二级分类
@Data
public class TwoSubject {

    private String id;

    private String title;
}

其他一级分类与二级分类是一对多关系，所有在一级分类中有list集合
，树状显示数据一般为一对多，则需要建立两个实体类，把前端需要的数据类型写入，并将连着关系表达，也就是在一的实体类加一个集合存多的实体类，在监听器中做数据结构构造