springboot使用easyExcel读取excel表格中的数据到数据库

由于easyExcel是在poi基础上进行的封装，所以需要引入poi依赖

<!--xls-->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>${poi.version}</version>
 </dependency>
 <!--xlsx-->
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>${poi.version}</version>
 </dependency>
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.1.1</version>
 </dependency>

项目结构

创建一个实体类，用于接收上传的excel表格中的数据

@Data
public class SubjectData {

    @ExcelProperty(index=0)//index=0指定对应excel表中的第一列的字段
    private String oneSubjectName;

    @ExcelProperty(index=1)//index=0指定对应excel表中的第二列的字段
    private String twoSubjectName;
}

创建一个监听类，用于执行读取到的excel中的每一行数据，由于SubjectExcelListener 监听器不能被spring容器管理，所以需要添加SubjectExcelListener 的有参构造方法，且将service属性作为构造的属性赋值，方便在service中new SubjectExcelListener()的时候将service对象传递过来。

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    //因为SubjectExcelListener不能交给spring穷奇进行管理，需要自己new，不能呼入其他对象
    //所一这里写构造方法，外面在new对象的时候传递进来。
    private EduSubjectService eduSubjectService;

    SubjectExcelListener() {
    }

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

    //读取excel中的内容，一行一行的读取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData == null) {
            throw new GuliException(2001, "文件数据为空");
        }
        EduSubject existOneSubject = existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
        if (existOneSubject == null) {//没有相同以及分类。进行添加new
            existOneSubject = new EduSubject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName()); //一级分类
            eduSubjectService.save(existOneSubject);
        }

        //获取一级分类id值
        String pid = existOneSubject.getId();
        //添加二级分类，判断二级分类是否重复
        EduSubject twoSubject = existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
        if(twoSubject==null){
            twoSubject =  new EduSubject();
            twoSubject.setParentId(pid);
            twoSubject.setTitle(subjectData.getTwoSubjectName());
            eduSubjectService.save(twoSubject);
        }
    }

    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) {

    }
}

编写Controller类，在这里将servicer里面 对象EduSubjectService 传递过去。

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

    @Autowired
    private EduSubjectService eduSubjectService;


    @PostMapping("addSubject")
    public R saveSubject(MultipartFile file) {
        eduSubjectService.saveSubject(file, eduSubjectService);
        return R.ok();
    }
}

编写service，在new SubjectExcelListener(eduSubjectService))传递eduSubjectService对象过去。

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    @Override
    public void saveSubject(MultipartFile file, EduSubjectService eduSubjectService) {
        try {
            //文件输入流
            InputStream in = file.getInputStream();
            EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(eduSubjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

测试：
准备要上传的excel文件

上传完毕之后的结果