写操作:
读操作:
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
//创建读取excel监听器
public class ExcelListener extends AnalysisEventListener<ReadData> {
//创建list集合封装最终的数据
List<ReadData> list = new ArrayList<ReadData>();
//一行一行去读取excle内容
@Override
public void invoke(ReadData user, AnalysisContext analysisContext) {
System.out.println("***"+user);
list.add(user);
}
//读取excel表头信息
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext
context) {
System.out.println("表头信息:"+headMap);
}
//读取完成后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
public static void main(String[] args) throws Exception {
// 写法1:
String fileName = "F:\\01.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, ReadData.class, new
ExcelListener()).sheet().doRead();
// 写法2:
InputStream in = new BufferedInputStream(new
FileInputStream("F:\\01.xlsx"));
ExcelReader excelReader = EasyExcel.read(in, ReadData.class, new
ExcelListener()).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
以上为示例
学院项目中使用:
package com.atguigu.eduservice.controller;
import com.atguigu.commonutils.R;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.subject.OneSubject;
import com.atguigu.eduservice.service.EduSubjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
/**
* <p>
* 课程科目 前端控制器
* </p>
*
* @author testjava
* @since 2020-02-29
*/
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService subjectService;
//添加课程分类
//获取上传过来文件,把文件内容读取出来
@PostMapping("addSubject")
public R addSubject(MultipartFile file) {
//上传过来excel文件
subjectService.saveSubject(file,subjectService);
return R.ok();
}
//课程分类列表(树形)
@GetMapping("getAllSubject")
public R getAllSubject() {
//list集合泛型是一级分类
List<OneSubject> list = subjectService.getAllOneTwoSubject();
return R.ok().data("list",list);
}
}
package com.atguigu.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;
}
package com.atguigu.eduservice.service.impl;
import com.alibaba.excel.EasyExcel;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.excel.SubjectData;
import com.atguigu.eduservice.entity.subject.OneSubject;
import com.atguigu.eduservice.entity.subject.TwoSubject;
import com.atguigu.eduservice.listener.SubjectExcelListener;
import com.atguigu.eduservice.mapper.EduSubjectMapper;
import com.atguigu.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* <p>
* 课程科目 服务实现类
* </p>
*
* @author testjava
* @since 2020-02-29
*/
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
//添加课程分类
@Override
public void saveSubject(MultipartFile file,EduSubjectService subjectService) {
try {
//文件输入流
InputStream in = file.getInputStream();
//调用方法进行读取
EasyExcel.read(in, SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
}catch(Exception e){
e.printStackTrace();
}
}
//课程分类列表(树形)
@Override
public List<OneSubject> getAllOneTwoSubject() {
//1 查询所有一级分类 parentid = 0
QueryWrapper<EduSubject> wrapperOne = new QueryWrapper<>();
wrapperOne.eq("parent_id","0");
List<EduSubject> oneSubjectList = baseMapper.selectList(wrapperOne);
//2 查询所有二级分类 parentid != 0
QueryWrapper<EduSubject> wrapperTwo = new QueryWrapper<>();
wrapperTwo.ne("parent_id","0");
List<EduSubject> twoSubjectList = baseMapper.selectList(wrapperTwo);
//创建list集合,用于存储最终封装数据
List<OneSubject> finalSubjectList = new ArrayList<>();
//3 封装一级分类
//查询出来所有的一级分类list集合遍历,得到每个一级分类对象,获取每个一级分类对象值,
//封装到要求的list集合里面 List<OneSubject> finalSubjectList
for (int i = 0; i < oneSubjectList.size(); i++) { //遍历oneSubjectList集合
//得到oneSubjectList每个eduSubject对象
EduSubject eduSubject = oneSubjectList.get(i);
//把eduSubject里面值获取出来,放到OneSubject对象里面
OneSubject oneSubject = new OneSubject();
// oneSubject.setId(eduSubject.getId());
// oneSubject.setTitle(eduSubject.getTitle());
//eduSubject值复制到对应oneSubject对象里面
BeanUtils.copyProperties(eduSubject,oneSubject);
//多个OneSubject放到finalSubjectList里面
finalSubjectList.add(oneSubject);
//在一级分类循环遍历查询所有的二级分类
//创建list集合封装每个一级分类的二级分类
List<TwoSubject> twoFinalSubjectList = new ArrayList<>();
//遍历二级分类list集合
for (int m = 0; m < twoSubjectList.size(); m++) {
//获取每个二级分类
EduSubject tSubject = twoSubjectList.get(m);
//判断二级分类parentid和一级分类id是否一样
if(tSubject.getParentId().equals(eduSubject.getId())) {
//把tSubject值复制到TwoSubject里面,放到twoFinalSubjectList里面
TwoSubject twoSubject = new TwoSubject();
BeanUtils.copyProperties(tSubject,twoSubject);
twoFinalSubjectList.add(twoSubject);
}
}
//把一级下面所有二级分类放到一级分类里面
oneSubject.setChildren(twoFinalSubjectList);
}
return finalSubjectList;
}
}
监听器就是当excel 数据上传后,进行解析,然后 判断一级分类,二级分类在数据库中是否存在,然后将其存入数据库相应表中。
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.SubjectData;
import com.atguigu.eduservice.mapper.EduSubjectMapper;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.servicebase.exceptionhandler.GuliException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
//因为SubjectExcelListener不交给spring进行管理,需要自己new,不能注入其他对象
//不能实现数据库操作
public EduSubjectService subjectService;
public SubjectExcelListener() {}
public SubjectExcelListener(EduSubjectService subjectService) {
this.subjectService = subjectService;
}
//读取excel内容,一行一行进行读取
@Override
// 调用;祈求;引起;恳求: invoke
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if(subjectData == null) {
throw new GuliException(20001,"文件数据为空");
}
//一行一行读取,每次读取有两个值,第一个值一级分类,第二个值二级分类
//判断一级分类是否重复
EduSubject existOneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
if(existOneSubject == null) { //没有相同一级分类,进行添加
existOneSubject = new EduSubject();
existOneSubject.setParentId("0");
existOneSubject.setTitle(subjectData.getOneSubjectName());//一级分类名称
subjectService.save(existOneSubject);
}
//获取一级分类id值
String pid = existOneSubject.getId();
//添加二级分类
//判断二级分类是否重复
EduSubject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
if(existTwoSubject == null) {
existTwoSubject = new EduSubject();
existTwoSubject.setParentId(pid);
existTwoSubject.setTitle(subjectData.getTwoSubjectName());//二级分类名称
subjectService.save(existTwoSubject);
}
}
//判断一级分类不能重复添加
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) {
}
}
这是 一级分类,二级分类对应的:
package com.atguigu.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.atguigu.eduservice.entity.subject;
import lombok.Data;
//二级分类
@Data
public class TwoSubject {
private String id;
private String title;
}
package com.atguigu.eduservice.entity;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
/**
* <p>
* 课程科目
* </p>
*
* @author testjava
* @since 2020-02-29
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="EduSubject对象", description="课程科目")
public class EduSubject implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "课程类别ID")
@TableId(value = "id", type = IdType.ID_WORKER_STR)
private String id;
@ApiModelProperty(value = "类别名称")
private String title;
@ApiModelProperty(value = "父ID")
private String parentId;
@ApiModelProperty(value = "排序字段")
private Integer sort;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
private Date gmtCreate;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date gmtModified;
}
以上是与数据库对应的实体类,另外两个用于展示。