谷粒学院（九）EasyExcel | 课程分类模块

文章目录

一、EasyExcel 简介

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

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

2、EasyExcel特点

Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。

3、案例1:EasyExcel进行Excel写操作

1、pom中引入xml相关依赖

<dependencies><!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.1</version></dependency><!-- 因为EasyExcel底层是Poi所以需要引入poi的依赖 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency></dependencies>

2、创建实体类

@DatapublicclassWriteData{//设置excel表头名称@ExcelProperty("学生编号")privateInteger sno;@ExcelProperty("学生姓名")privateString sname;}

3、实现写操作

publicstaticvoidtestWrite(){//实现excel的写操作//1.设置写入文件夹地址和excel名称String filename ="F:\\write.xlsx";//2.调用easyexcel里面的方法实现写操作EasyExcel.write(filename,WriteData.class).sheet("学生列表").doWrite(getData());}publicstaticList<WriteData>getData(){List<WriteData> list =newArrayList<>();for(int i =0;i <10;i++){String sname ="lucy"+i;
        list.add(newWriteData(i, sname));}return list;}

4、案例2:EasyExcel进行Excel读操作

1、创建实体类并标记对应列关系

@DatapublicclassReadData{//设置excel表头名称@ExcelProperty(index =0)privateInteger sno;@ExcelProperty(index =1)privateString sname;}

2、创建监听器进行excel文件读取

publicclassExcelListnerextendsAnalysisEventListener<ReadData>{List<ReadData> list =newArrayList<ReadData>();//一行一行的去读取@Overridepublicvoidinvoke(ReadData readData,AnalysisContext analysisContext){System.out.println("****"+readData);
        list.add(readData);}//读取Excel表头信息@OverridepublicvoidinvokeHeadMap(Map<Integer,String> headMap,AnalysisContext context){System.out.println("表头信息:"+headMap);}//读取完成之后执行@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext analysisContext){}}

3、进行读操作

publicstaticvoidtestRead(){String filename ="F:\\write.xlsx";EasyExcel.read(filename,ReadData.class,newExcelListner()).sheet().doRead();}

二、课程分类添加功能(后端)

核心:EasyExcel读取excel内容实现

1、引入easyexcel依赖

2、使用代码生成器把课程分类代码生成

3、创建实体类和excel对应关系

@DatapublicclassSubjectData{@ExcelProperty(index =0)privateString oneSujectName;@ExcelProperty(index =1)privateString twoSujectName;}

4、编写 EduSubjectController 类

@Api(description ="课程分类")@RestController@RequestMapping("/eduservice/subject")@CrossOriginpublicclassEduSubjectController{@AutowiredprivateEduSubjectService eduSubjectService;//添加课程分类@ApiOperation(value ="添加课程分类")@PostMapping("addSubjects")publicRaddSubjects(MultipartFile file){boolean flag = eduSubjectService.saveSubjects(file);if(flag){returnR.ok().message("文件导入成功!");}else{returnR.error().message("文件导入失败!");}}}

5、编写EduSubjectServiceImpl 类

@ServicepublicclassEduSubjectServiceImplextendsServiceImpl<EduSubjectMapper,EduSubject>implementsEduSubjectService{@AutowiredprivateEduSubjectService eduSubjectService;//添加课程分类@OverridepublicbooleansaveSubjects(MultipartFile file){try{InputStream inputStream = file.getInputStream();//进行excel文件读取EasyExcel.read(inputStream,SubjectData.class,newSubjectExcelListner(eduSubjectService)).sheet().doRead();returntrue;}catch(Exception e){
            e.printStackTrace();returnfalse;}}}

6、创建读取Excel监听器

publicclassSubjectExcelListnerextendsAnalysisEventListener<SubjectData>{//因为SubjectExcelListner在EduSubjectServiceImpl每次会被new的形式使用,所以SubjectExcelListner不能交给//Spring进行管理,也就不能使用@Autowird或@Resource注解注入对象.//但是该类需要调用service中的方法进行数据库操作,该如何使用呢?//可以通过构造方法传递参数的形式,使用service对象.privateEduSubjectService eduSubjectService;publicSubjectExcelListner(EduSubjectService eduSubjectService){this.eduSubjectService = eduSubjectService;}publicSubjectExcelListner(){}//一行一行的去读取@Overridepublicvoidinvoke(SubjectData subjectData,AnalysisContext analysisContext){if(subjectData==null){thrownewGuLiException(20001,"数据为空!");}//添加一级分类EduSubject oneSubject =this.existOneSubject(subjectData.getOneSubjectName());if(oneSubject==null){//没有相同的一级分类
            oneSubject =newEduSubject();
            oneSubject.setTitle(subjectData.getOneSubjectName());
            oneSubject.setParentId("0");
            eduSubjectService.save(oneSubject);}//获取一级分类idString pid = oneSubject.getId();//添加二级分类EduSubject twoSubject =this.existTwoSubject(subjectData.getTwoSubjectName(), pid);if(twoSubject==null){
            twoSubject =newEduSubject();
            twoSubject.setTitle(subjectData.getTwoSubjectName());
            twoSubject.setParentId(pid);
            eduSubjectService.save(twoSubject);}}//读取Excel表头@OverridepublicvoidinvokeHeadMap(Map<Integer,String> headMap,AnalysisContext context){System.out.println("表头信息:"+headMap);}//读取完成后的操作@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext analysisContext){}//判断一级分类是否重复privateEduSubjectexistOneSubject(String name){QueryWrapper<EduSubject> wrapper =newQueryWrapper<>();
        wrapper.eq("title", name).eq("parent_id",0);EduSubject subject = eduSubjectService.getOne(wrapper);return subject;}//判断二级分类是否重复privateEduSubjectexistTwoSubject(String name,String pid){QueryWrapper<EduSubject> wrapper =newQueryWrapper<>();
        wrapper.eq("title", name).eq("parent_id", pid);EduSubject subject = eduSubjectService.getOne(wrapper);return subject;}}

7、重启oss服务，Swagger中测试文件上传

http://localhost:8001/swagger-ui.html

三、课程分类添加功能(前端)

1、添加课程分类路由

/src/router/index.js:

{
        path:'/subject',
        component: Layout,//布局
        redirect:'/subject/table',
        name:'SubjectSort',
        meta:{ title:'课程分类管理', icon:'nested'},
        children:[{
                path:'list',
                name:'EduSubjectList',component:()=>import('@/views/edu/subject/list'),
                meta:{ title:'课程分类列表', icon:'table'}},{
                path:'import',
                name:'EduSubjectImport',component:()=>import('@/views/edu/subject/import'),
                meta:{ title:'导入课程分类', icon:'tree'}}]}

2、创建课程分类页面，修改路由对应的页面路径

3、在添加课程分类页面实现效果

编写页面:

<template><div class="app-container"><el-form label-width="120px"><el-form-item label="信息描述"><el-tag type="info">excel模版说明</el-tag><el-tag><i class="el-icon-download"/><a :href="OSS_PATH + '/excel/template.xlsx'">点击下载模版</a></el-tag></el-form-item><el-form-item label="选择Excel"><el-upload
          ref="upload":auto-upload="false":on-success="fileUploadSuccess":on-error="fileUploadError":disabled="importBtnDisabled":limit="1":action="BASE_API+'/eduservice/subject/addSubjects'"
          name="file"
          accept="application/vnd.ms-excel"><el-button slot="trigger" size="small" type="primary">选取文件</el-button><el-button
            :loading="false"
            style="margin-left: 10px;"
            size="small"
            type="success"
            @click="submitUpload">{{ fileUploadBtnText }}</el-button></el-upload></el-form-item></el-form></div></template>

编写js方法:

<script>exportdefault{data(){return{BASE_API: process.env.BASE_API,// 接口API地址OSS_PATH: process.env.OSS_PATH,// 阿里云OSS地址
      fileUploadBtnText:'上传到服务器',// 按钮文字
      importBtnDisabled:false,// 按钮是否禁用,
      loading:false}},created(){},
  methods:{//上传成功调用的方法fileUploadSuccess(response){if(response.success ===true){this.fileUploadBtnText ='导入成功'this.loading =falsethis.$message({
            type:'success',
            message: response.message
        })}//跳转到列表页面this.$router.push({path:'/subject/list'})},//上传失败调用的方法fileUploadError(response){if(response.success ===false){this.fileUploadBtnText ='导入失败'this.loading =falsethis.$message({
            type:'error',
            message: response.message
        })}//跳转到列表页面this.$router.push({path:'/subject/list'})},//点击按钮上传文件到接口submitUpload(){this.importBtnDisabled =true//上传按钮禁用this.loading =truethis.$refs.upload.submit()}}}</script>

4、启动服务测试

四、课程分类列表(后端）

1、根据返回数据创建对应实体类

//一级分类@DatapublicclassOneSubject{privateString id;privateString title;//一个一级分类里面有多个二级分类privateList<TwoSubject> children =newArrayList<>();}

//二级分类@DatapublicclassTwoSubject{privateString id;privateString title;}

返回数据格式为：

2、编写Controller类

@ApiOperation(value ="查询所有课程分类")@GetMapping("getAllSubject")publicRgetAllSubject(){List<OneSubject> oneSubjectList =  eduSubjectService.getAllSubject();returnR.ok().data("list",oneSubjectList);}

3、编写Service类

//查询所有的课程分类==>树形@OverridepublicList<OneSubject>getAllSubject(){//1.查询所有一级分类  parent_id=0QueryWrapper<EduSubject> wrapper =newQueryWrapper<EduSubject>();
        wrapper.eq("parent_id",0);List<EduSubject> oneSubjectList =this.baseMapper.selectList(wrapper);//2.查询所有二级分类 parent_id!=0
        wrapper =newQueryWrapper<EduSubject>();
        wrapper.ne("parent_id",0);List<EduSubject> twoSubjectList =this.baseMapper.selectList(wrapper);//定义最终的返回类型List<OneSubject> finalSubjectList =newArrayList<OneSubject>();///3 封装一级分类//查询出来所有一级分类list集合集合，得到每一个一级分类对象，回去每一个一级分类对象值，//封装到要求的list集合里面  List<OneSubject> findSubjectList//List<EduSubject> ==> List <OneSubject> finalSubjectListfor(EduSubject subject : oneSubjectList){//将oneSubjectList封装到finalSubjectListOneSubject oneSubject =newOneSubject();//以下两种方法都可BeanUtils.copyProperties(subject,oneSubject);// oneSubject.setId(subject.getId());// oneSubject.setTitle(subject.getTitle());//在一级分类循环遍历查询所有的二级分类//创建list集合封装每一个一级分类的二级分类List<TwoSubject> children =newArrayList<>();for(EduSubject eduSubject : twoSubjectList){TwoSubject twoSubject =newTwoSubject();if(eduSubject.getParentId().equals(subject.getId())){BeanUtils.copyProperties(eduSubject,twoSubject);
                    children.add(twoSubject);}}
            oneSubject.setChildren(children);

            finalSubjectList.add(oneSubject);//加入结果集  该语句放在76行之后也可以想想看为啥???}return finalSubjectList;}

4、使用swagger进行测试

…

五、课程分类列表(前端)

1、编写前端接口

import request from'@/utils/request'exportdefault{//查询所有的课程分类getAllSubject(){returnrequest({
            url:`/eduservice/subject/getAllSubject`,
            method:'get'})}}

2、参考tree模块把前端整合出来

<template><div class="app-container"><el-input v-model="filterText" placeholder="Filter keyword" style="margin-bottom:30px;"/><el-tree
      ref="tree2":data="data2":props="defaultProps":filter-node-method="filterNode"class="filter-tree"default-expand-all
    /></div></template>

3、前端接口调用

<script>import subject from'@/api/edu/subject'exportdefault{data(){return{
      filterText:'',
      data2:[],
      defaultProps:{
        children:'children',
        label:'title'}}},
  watch:{filterText(val){this.$refs.tree2.filter(val)}},created(){//页面加载完成后调用this.getSubjectList()},

  methods:{getSubjectList(){
        subject.getAllSubject().then(response=>{this.data2 = response.data.list;})},filterNode(value, data){if(!value)returntruereturn data.title.toLowerCase().indexOf(value)!==-1//搜索不区分大小写}}}</script>

4、启动项目服务测试