easyExcel实现导入数据库

需求

我们需要将excel的数据导入至数据库中。

easyExcel Maven 依赖

	<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

过程

对应entity

package com.zlf.edu.entity.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * Created with IntelliJ IDEA.
 *
 * @Auther: zlf
 * @Date: 2021/03/25/17:12
 * @Description:
 */
@Data
public class SubjectData {


    @ExcelProperty(index = 0) //第一列
    private String oneSubjectName;

    @ExcelProperty(index = 1) // 第二列
    private String twoSubjectName;
}

由于读取excel需要实现监听器，并且该监听器不能被spring容器管理，所以我们spring的注解不能在里面用，所以我们需要将service层的接口传入该监听器，实现将excel的内容存储至数据库中。

package com.zlf.edu.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zlf.edu.entity.Subject;
import com.zlf.edu.entity.excel.SubjectData;
import com.zlf.edu.service.SubjectService;
import com.zlf.servicebase.exceptionHandler.GlobalException;

import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Auther: zlf
 * @Date: 2021/03/25/17:19
 * @Description:
 */
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    // 由于 SubjectExcelListener 不能交给Spring管理 所以我们只能手动传入 subjectService


    public SubjectService subjectService;

    public SubjectExcelListener(){

    }

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

    // 读取excel数据，一行一行读取的数据
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if(subjectData == null){
            throw  new GlobalException(20001,"文件数据为空");
        }
        // 一行一行读取，每次读取有两个值，第一个是一级分类，第二个是二级分类

        Subject existOneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
        if(existOneSubject == null){//表示没有相同的一级分类
            existOneSubject  = new Subject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName()); //设置一级分类名称
            subjectService.save(existOneSubject);
        }

        String parentId = existOneSubject.getId();

        Subject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), parentId);
        if(existTwoSubject == null){//表示没有相同的二级分类
            existTwoSubject  = new Subject();
            existTwoSubject.setParentId(parentId);
            existTwoSubject.setTitle(subjectData.getTwoSubjectName()); //设置二级分类名称
            subjectService.save(existTwoSubject);
        }


    }

    // 判断一级分类不能重复添加
    private Subject existOneSubject(SubjectService subjectService,String name){
        QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title",name);
        queryWrapper.eq("parent_id","0");
        Subject oneSubject = subjectService.getOne(queryWrapper);
        return oneSubject;
    }

    // 判断二级分类不能重复添加
    private Subject existTwoSubject(SubjectService subjectService,String name,String pid){
        QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title",name);
        queryWrapper.eq("parent_id",pid);
        Subject twoSubject = subjectService.getOne(queryWrapper);
        return twoSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

controller 层接口

/**
 * <p>
 * 课程科目 前端控制器
 * </p>
 *
 * @author zlf
 * @since 2021-03-25
 */
@RestController
@RequestMapping("/edu/subject")
@CrossOrigin
@Api(description = "课程管理")
public class SubjectController {

    @Autowired
    SubjectService subjectService;

    /**
    * @Description: 通过上传excel，添加课程分类
    * @Param: [file]
    * @return: com.zlf.commonutils.Response.R
    * @Author: zlf
    * @Date: 2021/3/25
    */
    @PostMapping("addSubject")
    public R addSubject(@ApiParam(name = "file",value = "上传excel") MultipartFile file){

        subjectService.saveSubject(file,subjectService);
        return R.ok();
    }

}

课程分类实体类

/**
 * <p>
 * 课程科目
 * </p>
 *
 * @author zlf
 * @since 2021-03-25
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("edu_subject")
@ApiModel(value="Subject对象", description="课程科目")
public class Subject 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;


}

service 层

package com.zlf.edu.service.impl;

import com.alibaba.excel.EasyExcel;
import com.zlf.edu.entity.Subject;
import com.zlf.edu.entity.excel.SubjectData;
import com.zlf.edu.listener.SubjectExcelListener;
import com.zlf.edu.mapper.SubjectMapper;
import com.zlf.edu.service.SubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;

/**
 * <p>
 * 课程科目 服务实现类
 * </p>
 *
 * @author zlf
 * @since 2021-03-25
 */
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {

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

    }
}

效果