一导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
二创建excel的实体类
package com.noob.eduservice.entity.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;
/**
* Author: noob
* DATE: 2020/7/13 0013
* PROJECT: jiaoyu
* DESCRIPTION: excel中对应的实体类
**/
@Component
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelSubjectDate {
@ExcelProperty(index = 0)
private String OneSubjectName;
@ExcelProperty(index = 1)
private String TwoSubjectName;
}
三创建excel的监听器
package com.noob.eduservice.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.noob.eduservice.entity.EduSubject;
import com.noob.eduservice.entity.vo.ExcelSubjectDate;
import com.noob.eduservice.service.EduSubjectService;
import com.noob.exceptionHander.zdyException;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
/**
* Author: noob
* DATE: 2020/7/13 0013
* PROJECT: jiaoyu
* DESCRIPTION: 读取Excel文件时的监听器,SubjectExcelListener这个类不能被spring管理,需要用到无参构造
**/
@NoArgsConstructor
@AllArgsConstructor
public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectDate>{
private EduSubjectService eduSubjectService;
/**
* @Author noob
* @Description //读取excel中的内容,一行一行读取加入数据库中
* @Param
* @return
**/
@Override
public void invoke(ExcelSubjectDate excelSubjectDate, AnalysisContext analysisContext) {
if (excelSubjectDate == null){
throw new zdyException(20001,"文件异常");
}
//一行一行读取,每次读取两个值
//判断一级分类是否相同
EduSubject exitOneSubject = this.exitOneSubject(eduSubjectService, excelSubjectDate.getOneSubjectName());
if (exitOneSubject == null){ //没有这个一级分类进行添加
exitOneSubject = new EduSubject();
exitOneSubject.setParentId("0");
exitOneSubject.setTitle(excelSubjectDate.getOneSubjectName());
eduSubjectService.save(exitOneSubject);
}
//判断二级分类
String pid = exitOneSubject.getId();//获取一级分类的id值
EduSubject exitTwoSubject = this.exitTwoSubject(eduSubjectService, excelSubjectDate.getTwoSubjectName(), pid);
if (exitTwoSubject == null){
exitTwoSubject = new EduSubject();
exitTwoSubject.setParentId(pid);
exitTwoSubject.setTitle(excelSubjectDate.getTwoSubjectName());
eduSubjectService.save(exitOneSubject);
}
}
//判断二级分类不能重复
private EduSubject exitTwoSubject(EduSubjectService eduSubjectService,String name,String pid){
QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id",pid);
EduSubject onesubject = eduSubjectService.getOne(wrapper);
return onesubject;
}
//判断一级分类不能重复
private EduSubject exitOneSubject(EduSubjectService eduSubjectService,String name){
QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id",0);
EduSubject twosubject = eduSubjectService.getOne(wrapper);
return twosubject;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
四进行测试
package com.noob.eduservice.controller;
import com.noob.commomutils.R;
import com.noob.eduservice.service.EduSubjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* <p>
* 课程科目 前端控制器
* </p>
*@Description
* @author testjava
* @since 2020-07-13
*/
@RestController
@RequestMapping("/edu/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService eduSubjectService;
/**
* @Author noob
* @Description //添加课程分类,获取上传过来的文件进行读取
* @Param file:上传的文件
* @return
**/
@PostMapping("addsubject")
public R addSubject(MultipartFile file){
eduSubjectService.saveSubject(file);
return R.ok();
}
}