04-EasyExcel使用与阿里云整合EasyExcel

EasyExcel使用与谷粒学院整合EasyExcel

一.EasyExcel的使用场景

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

二.EasyExcel特点

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

三.使用介绍

1.引入依赖

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

2.创建实体类，设置表头和添加的数据字段

package com.sunset.demo.excel;

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

@Data
public class DemoData {
//    设置表头名称
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer sno;
    @ExcelProperty(value = "学生姓名",index = 1)
    private String name;
}

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

package com.sunset.demo.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;

import java.util.Map;

public class ExcelListener extends AnalysisEventListener<DemoData> {
//    一行一行读取数据
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println("***"+demoData);
    }
//读取完成后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

//    读取表头

    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        System.out.println("表头"+headMap);
    }
}

4.测试读操作

//    读操作
    @Test
    public void test1(){
//        读路径
        String filename = "E:\\test\\write.xlsx";
        EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
    }

5.测试写操作

 
//模拟数据
	private static List<DemoData> getData(){
        List<DemoData> list = new ArrayList<>();
        for (int i = 0 ;i< 10; i++){
            DemoData demoData = new DemoData();
            demoData.setSno(i);
            demoData.setName("gjl"+i);
            list.add(demoData);
        }
        return list;
    }

//写操作

//    写操作
    @Test
    public void test(){
        //        实现写操作
//        1.设置写入文件夹的位置和 excel名称
        String filename = "E:\\test\\write.xlsx";
//        调用方法,第一个参数文件路径名称，，第二个参数实体类class
        EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());
    }

四.谷粒学院整合EasyExcel

1.创建实体类

package com.sunset.eduService.entity.excel;

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;
}

2.创建listen监听器

package com.sunset.eduService.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sunset.eduService.entity.EduSubject;
import com.sunset.eduService.entity.excel.SubjectData;
import com.sunset.eduService.service.EduSubjectService;
import com.sunset.servicebase.exceptionHanlder.SunsetException;

public class SubjectExcelListen extends AnalysisEventListener<SubjectData> {
    private EduSubjectService eduSubjectService;

    public SubjectExcelListen() {
    }

    public SubjectExcelListen(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }

    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if(subjectData == null){
            throw new SunsetException(20001,"文件数据为空");
        }
//        判断一级分类是否重复
        EduSubject eduSubject = this.existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
        if(eduSubject==null){
            eduSubject = new EduSubject();
            eduSubject.setParentId("0");
            eduSubject.setTitle(subjectData.getOneSubjectName());
            eduSubjectService.save(eduSubject);
        }
//        获取一级分类id值
        String pid = eduSubject.getId();
        EduSubject twoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
        if(twoSubject == null){
            twoSubject = new EduSubject();
            twoSubject.setParentId(pid);
            twoSubject.setTitle(subjectData.getTwoSubjectName());
            eduSubjectService.save(twoSubject);
        }

    }

//    一级分类不能重复添加
    private EduSubject existOneSubject(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;
    }

    //    二级分类不能重复添加
    private EduSubject existTwoSubject(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) {

    }
}

3.创建service层

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

    }

4.创建controller层

//    添加课程分类
//    获取上传过来的文件，把文件读取出来、
    @PostMapping("/addSubject")
    public Result addSubject(MultipartFile file){
//        上传过来的excel文件
        subjectService.saveSubject(file,subjectService);
        return Result.ok();
    }

注意：这里选择传入subjectService是因为在listen里面要用到这个对象，但是listen监听器并没有交给spring容器管理，如果选择注入的话，注入不了，所以选择在controller里传过去，然后在构造方法里面将subjectService初始化，防止空指针异常。

service层

五.谷粒学院课程展示

1.展示模型

树状图模型

2.实现步骤

1. 创建一级分类，二级分类vo对象

一级分类

package com.sunset.eduService.entity.subject;

import lombok.Data;

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

//一级分类
@Data
public class OneSubject {
    private String id;
    private String title;
//    一个一级分类里面有多个二级分类
    private List<TwoSubject> children = new ArrayList<>();
}

二级分类

package com.sunset.eduService.entity.subject;

import lombok.Data;

//二级分类
@Data
public class TwoSubject {
    private String id;
    private String title;
}

2. 创建service层

//返回指定数据
    @Override
    public List<OneSubject> getAllOneTwoSubject() {
//        1.查询出所有的一级分类 parent_id = 0
        QueryWrapper<EduSubject> wrapper1 = new QueryWrapper<>();
        wrapper1.eq("parent_id",0);
        List<EduSubject> oneList = baseMapper.selectList(wrapper1);
//        2.查询出所有的二级分类 parent_id !=0
        QueryWrapper<EduSubject> wrapper2 = new QueryWrapper<>();
        wrapper2.ne("parent_id",0);
        List<EduSubject> twoList = baseMapper.selectList(wrapper2);
//        创建list集合，用于最后封装的数据
        List<OneSubject> finalList = new ArrayList<>();
//        3.封装一级分类
        for (int i = 0; i < oneList.size(); i++) {//遍历集合，得到每个对象
            EduSubject eduSubject = oneList.get(i);
//            String id = eduSubject.getId();
//            String title = eduSubject.getTitle();
            OneSubject oneSubject = new OneSubject();
//            oneSubject.setId(id);
//            oneSubject.setTitle(title);
//            使用工具类封装
            BeanUtils.copyProperties(eduSubject, oneSubject);
//            在一级分类里面封装二级分类
            List<TwoSubject> twoSubjectList = new ArrayList<>();
            for (int j = 0; j < twoList.size(); j++) {
                EduSubject eduSubject1 = twoList.get(j);
                if (eduSubject1.getParentId().equals(eduSubject.getId())) {
                    TwoSubject twoSubject = new TwoSubject();
                    BeanUtils.copyProperties(eduSubject1, twoSubject);
                    twoSubjectList.add(twoSubject);
                }
            }
            oneSubject.setChildren(twoSubjectList);
            finalList.add(oneSubject);
        }

//        4.封装二级分类
        return finalList;
    }

3. controller调用

//    课程类别添加（树型）
    @GetMapping("/getAllSubject")
    public Result getAllSubject(){
//        list集合的泛型是一级分类
        List<OneSubject> list = subjectService.getAllOneTwoSubject();
        return Result.ok().data("list",list);
    }