EasyExcel
步骤
1.导入maven坐标
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
2.创建实体类,加入表头注解(对应excel的数据)
package com.gyb.eduservice.entity.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @Author: 郜宇博
* @Date: 2021/10/2 16:57
*/
@Data
public class SubjectData {
/**
* 一级分类
* 注解代表在excel的第一列
*/
@ExcelProperty(index = 0)
private String oneSubjectName;
/**
* 二级分类
*/
@ExcelProperty(index = 1)
private String twoSubjectName;
}
3.创建要添加到数据库的实体类
package com.gyb.eduservice.entity;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;
/**
* <p>
* 课程科目
* </p>
*
* @author 郜宇博
* @since 2021-10-02
*/
@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;
}
4.创建一个监听器,继承AnalysisEventListener
package com.gyb.eduservice.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.gyb.eduservice.entity.EduSubject;
import com.gyb.eduservice.entity.excel.SubjectData;
import com.gyb.eduservice.service.EduSubjectService;
import com.gyb.servicebase.exceptionhandler.MyException;
/**
* @Author: 郜宇博
* @Date: 2021/10/2 17:03
*/
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
/**
* 因为SubjectExcelListener,不能交给Spring容器管理,
* 所以不能使用@Autowire自动导入eduSubjectService,
* 因此需要使用构造函数获取到eduSubjectService
*/
public EduSubjectService eduSubjectService;
public SubjectExcelListener(EduSubjectService eduSubjectService) {
this.eduSubjectService = eduSubjectService;
}
public SubjectExcelListener() {
}
/**
* 判断一级分类是否重复
* @param oneName 一级分类的名称
* @return
*/
private EduSubject judgeExistOneSubject(String oneName){
//获取该名称的一级分类,一级分类的parent_id为0
EduSubject oneSubject = eduSubjectService.getOne(
new QueryWrapper<EduSubject>()
.eq("title", oneName)
.eq("parent_id", "0"));
return oneSubject;
}
/**
* 判断二级分类是否重复
* @param twoName 一级分类的名称
* @return
*/
private EduSubject judgeExistTwoSubject(String twoName,String pid){
//获取该名称的一级分类,一级分类的parent_id为0
EduSubject twoSubject = eduSubjectService.getOne(
new QueryWrapper<EduSubject>()
.eq("title", twoName)
.eq("parent_id", pid));
return twoSubject;
}
/**
* 读取方法
* 一行一行读,没有一级或二级分类就创建
* @param subjectData 传入的文件数据
* @param analysisContext
*/
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if (subjectData == null){
throw new MyException(20001,"文件数据为空");
}
//一行一行读,每行有两个值,第一个是一级分类,第二个是二级分类
EduSubject one = this.judgeExistOneSubject(subjectData.getOneSubjectName());
//一级分类
//不重复。添加
if (one == null){
//创建对象
one = new EduSubject();
one.setParentId("0");
one.setTitle(subjectData.getOneSubjectName());
//添加到数据库
eduSubjectService.save(one);
}
//获取该二级分类对应一级分类的id值
String pid = one.getId();
//二级分类
EduSubject two = this.judgeExistTwoSubject(subjectData.getTwoSubjectName(), pid);
if (two == null){
//创建对象
two = new EduSubject();
two.setParentId(pid);
two.setTitle(subjectData.getTwoSubjectName());
//添加到数据库
eduSubjectService.save(two);
}
}
/**
* 读取之后表头操作
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
5.在servie层编写添加文件的方法
package com.gyb.eduservice.service.impl;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.gyb.eduservice.entity.EduSubject;
import com.gyb.eduservice.entity.excel.SubjectData;
import com.gyb.eduservice.listener.SubjectExcelListener;
import com.gyb.eduservice.mapper.EduSubjectMapper;
import com.gyb.eduservice.service.EduSubjectService;
import com.gyb.servicebase.exceptionhandler.MyException;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
/**
* <p>
* 课程科目 服务实现类
* </p>
*
* @author 郜宇博
* @since 2021-10-02
*/
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
/**
* 添加课程分类
* @param file
*/
@Override
public void addSubject(MultipartFile file,EduSubjectService eduSubjectService) {
try {
InputStream inputStream = file.getInputStream();
//读取文件内的内容,因为是一行行读,因此第三个参数需要传递一个监听器
EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(eduSubjectService))
.sheet()
.doRead();
} catch (IOException e) {
throw new MyException(20001,e.getMessage());
}
}
}
6.在controller层调用
package com.gyb.eduservice.controller;
import com.gyb.commonutils.Result;
import com.gyb.eduservice.service.EduSubjectService;
import io.swagger.annotations.ApiOperation;
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>
*
* @author 郜宇博
* @since 2021-10-02
*/
@RestController
@RequestMapping("/eduservice/edu-subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService eduSubjectService;
/**
* 添加课程分类
* @param file 上传的excel文件
* @return
*/
@PostMapping("addSubject")
@ApiOperation("添加文件")
public Result addSubject(MultipartFile file){
try {
eduSubjectService.addSubject(file,eduSubjectService);
} catch (Exception e) {
return Result.error().message(e.getMessage());
}
return Result.ok();
}
}
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/115164.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...