为什么使用EasyExcel:
(1)Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
(2)EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
(3)EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
需求要求:
excel导入到数据库表中,一级分类和二级分类,一级分类的parent_id 为0,二级分类的parent_id 为一级分类的id
excel文中的信息
后端开发:
第一步:引入easyexcel依赖
第二步:创建实体类和excel对应关系
第三步:监听器实现
实体类
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}
controller层
public class EduSubjectController {
@Autowired
private EduSubjectService subjectService;
@PostMapping("/addSubject")
public R addSubject(MultipartFile file){
subjectService.saveSubject(file,subjectService);
return R.ok();
}
}
service层
@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 (IOException e) {
e.printStackTrace();
}
}
}
监听器实现(重点)
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
//因为SubjectExcelListener不能交给spring进行管理,需要自己new,不能注入其他对象
//所以构造subject无参和有参构造器
public EduSubjectService subjectService;
public SubjectExcelListener(){}
public SubjectExcelListener(EduSubjectService subjectService) {
this.subjectService = subjectService;
}
//一行一行去读取excle内容
@Override
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.setTitle(subjectData.getOneSubjectName());
existOneSubject.setParentId("0");
subjectService.save(existOneSubject);
}
//获取一级分类id值
String pid = existOneSubject.getId();
//添加二级分类
EduSubject existTwoSubject =
this.existTwoSubject(subjectService,subjectData.getTwoSubjectName(), pid);
if(existTwoSubject == null) {//没有相同的二级分类,进行添加
existTwoSubject = new EduSubject();
existTwoSubject.setTitle(subjectData.getTwoSubjectName());
existTwoSubject.setParentId(pid);
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) {
}
}
插入表中对应的实体类
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;
}