使用 EasyExcel 操作exsel文件

导入easyExcel依赖，而且easyExcel依赖于 阿帕奇poi，因为是对阿帕奇poi的再次封装

    <dependencies>
        <!--EasyExcel依赖-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

excel文件，我们做的就是把文件的信息及分类添加到数据库

excel对应的实体类，后面读取excel文件时会用到，和 excel 文件一一对应 @ExcelProperty（index= 0）表示第一列

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;
}

数据库对应的实体类

@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;


}

EduSubjectController层在controller中调用service的saveSubject，把文件传到service

@RestController
@RequestMapping("/eduservice/edu-subject")
@CrossOrigin
public class EduSubjectController {

    //注入service
    @Autowired
    private EduSubjectService subjectService;

    //添加课程分类
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file){
        //调用service读取文件
        subjectService.saveSubject(file,subjectService);
        return R.ok();
    }
}

service层

public interface EduSubjectService extends IService<EduSubject> {

    void saveSubject(MultipartFile file,EduSubjectService subjectService);

}

service实现层，在serviceImlp中调用EasyExcel对象进行文件数据的读取

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

    @Override
    public void saveSubject(MultipartFile file,EduSubjectService subjectService) {
        try{
            //获取文件输入流
            InputStream inputStream = file.getInputStream();
            //调用EasyExcel
            EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

创建listener包 SubjectExcelListener类实现 AnalysisEventListener<SubjectData这里放与excel文件对应的实体类>接口的 easyExcel监听器

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.service.EduSubjectService;
import com.atguigu.servicebase.exceptionhandler.GuliException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {


    //监听器没有被spring容器管理，不能直接操作数据库，通过new监听器时，传入subjectService调用有参构造注入service
    public EduSubjectService subjectService;

    public SubjectExcelListener() {
    }

    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

    /**
     * 读取 Excel 文件内容，一行一行的读取
     * */
    @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){
            //为空，new 一个 EduSubject 插入数据库
            existOneSubject = new EduSubject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName());  //一级分类名称
            subjectService.save(existOneSubject);
        }

        //插入二级分类
        String pid = existOneSubject.getId();  //如果一级分类不为空，获取一级分类ID，如果一级分类不存在，插入数据库成功一定不为空，获取ID
        EduSubject existTwoSubject = this.existTwoSubject(subjectService,subjectData.getTwoSubjectName(),pid);
        //如果二级分类不存在插入数据库
        if(existTwoSubject == null){
            //为空，new 一个 EduSubject 插入数据库
            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 oneEduSubject = subjectService.getOne(wrapper);
        return oneEduSubject;
    }
    //判断二级分类不能重复添加
    private EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",pid);
        EduSubject twoEduSubject = subjectService.getOne(wrapper);
        return twoEduSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

在丝袜哥中测试接口

数据库刷新