EasyExcel读取课程分类存入数据库

1.表中数据如下

 

 

 2.创建对应的实体类来封装读取到的数据

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SubjectData {
    @ExcelProperty(index = 0)
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;



}

3.在Controller中读取表格数据接口

 @Autowired
    private EduSubjectService eduSubjectService;
@PostMapping("addSubject")
    public R addSubject(MultipartFile file){
        eduSubjectService.saveSubject(file,eduSubjectService);//监听器不能交给spring管理，要把eduSubjectService传入

return R.ok(); }

4.编写EduSubjectService 

public interface EduSubjectService extends IService<EduSubject> {

    void saveSubject(MultipartFile file,EduSubjectService eduSubjectService);

    List<OneSubject> getOneTwoSubject();
}

5.实现接口EduSubjectService 读取excel

@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 (Exception e){
            e.printStackTrace();

        }
}


    }

6.实现监听器

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    public EduSubjectService eduSubjectService;

    public SubjectExcelListener() {
    }

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

    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {//每次读取一行数据到subjectData中

        if (subjectData==null)
        {
            throw new WangException(20001,"文件数据为空");
        }

        EduSubject existOneSubject = this.existOneSubject(eduSubjectService, subjectData.getOneSubjectName());//根据封装好的对象读取第一列
        if (existOneSubject==null)
        {
            existOneSubject = new EduSubject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName());
            eduSubjectService.save(existOneSubject);

        }


        String pid = existOneSubject.getId();

        EduSubject existTwoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);

        if (existTwoSubject==null)
        {
            existTwoSubject = new EduSubject();
            existTwoSubject.setParentId(pid);
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());
            eduSubjectService.save(existTwoSubject);

        }



    }


    private EduSubject existOneSubject(EduSubjectService eduSubjectService,String name){//根据一级标题和父类id从数据库查数据
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id","0");
        EduSubject eduOneSubject = eduSubjectService.getOne(wrapper);


        return eduOneSubject;
    }


    private EduSubject existTwoSubject(EduSubjectService eduSubjectService,String name,String pid){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",pid);
        EduSubject eduTwoSubject = eduSubjectService.getOne(wrapper);


        return eduTwoSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}