使用EasyExcel添加分类数据到数据库

1、测试EasyExcel的Demo

（1） 目录结构

引入依赖：

   <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

FakeData（定义Excel表格对应的类文件）：

@Data
public class FakeData {

    @ExcelProperty(value = "学号",index = 0)
    private Integer sno;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;
}

TestEasyExcel:

public class TestEasyExcel {
    //写操作
    @Test
    public void writeToExcel(){
        //实现Excel的读写操作
        //1.设置写入文件夹的地址和excel文件名称
        String fileName = "F:\\write.xlsx";
        //2.调用easyexcel里面的方法实现写操作
        //write方法两个参数，第一参数文件路径名称，第二个参数实体类class
        EasyExcel.write(fileName,FakeData.class).sheet("学生列表").doWrite(getData());
    }

    //读操作
    @Test
    public void readFromExcel(){
        //1.设置读取文件夹的地址和excel文件名称
        String fileName = "F:\\write.xlsx";
        EasyExcel.read(fileName,FakeData.class,new ExcelListener()).sheet().doRead();

    }

    //创建返回list集合的方法
    public static List<FakeData> getData(){
        List<FakeData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            FakeData fakeData = new FakeData();
            fakeData.setSno(i);
            fakeData.setName("kun"+i);
            list.add(fakeData);
        }
        return list;
    }
}

实现EasyExcel的读操作需要编写监听器继承AnalysisEventListener并实现里面的方法：

/*使用EasyExcel读取文件*/
public class ExcelListener extends AnalysisEventListener<FakeData> {

    //一行一行读取Excel内容
    @Override
    public void invoke(FakeData fakeData, AnalysisContext analysisContext) {

        System.out.println("****"+fakeData);
    }
    //读取表头的内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头"+headMap);
    }

    //读取完成之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

2、读取Excel中一级分类和二级分类并写入数据库

创建Excel表对应的类信息SubjectData

Excel分类数据：

@Data
public class SubjectData {

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

    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

EduSubject

：

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

    @TableField(fill = FieldFill.INSERT_UPDATE)
    @ApiModelProperty(value = "更新时间")
    private Date gmtModified;
}

EduSubjectController：

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

    @Autowired
    private EduSubjectService eduSubjectService;

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

EduSubjectService：

public interface EduSubjectService extends IService<EduSubject> {

    public void saveSubject(MultipartFile file,EduSubjectService eduSubjectService);
}

EduSubjectServiceImpl：

@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()).sheet().doRead();
            //将eduSubjectService携带到SubjectExcelListener()的有参构造中
            EasyExcel.read(in, SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

监听器SubjectExcelListener：

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    /*因为SubjectExcelListener不能交给Spring管理，需要自己new，不能直接注入其它对象，所以不能进行数据库操作*/
    /*解决方法：传参的时候携带subjectService，并在SubjectExcelListener中定义有参构造函数用于初始化subjectService*/

    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 subject = existOneSubject(subjectService, subjectData.getOneSubjectName());
        if(subject == null){  //一级分类无重复，进行添加
            subject = new EduSubject();
            subject.setParentId("0");
            subject.setTitle(subjectData.getOneSubjectName());
            subjectService.save(subject);
        }
        //判断二级分类名称是否重复
        String pid = subject.getId();
        EduSubject subject2 = existTwoSubject(subjectService, subjectData.getTwoSubjectName(),pid);
        if(subject == null){  //二级分类无重复，进行添加
            subject = new EduSubject();
            subject.setParentId(pid);
            subject.setTitle(subjectData.getTwoSubjectName());
            subjectService.save(subject2);
        }

    }

    //判断一级分类名称不能重复添加
    public EduSubject existOneSubject(EduSubjectService subjectService,String name){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",0);
        EduSubject subject = subjectService.getOne(wrapper);
        return subject;
    }


    //判断二级分类名称不能重复添加
    public EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",pid);
        EduSubject subject = subjectService.getOne(wrapper);
        return subject;
    }


    //读取表头的内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头"+headMap);
    }

    //读取完成之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

---

欢迎关注公众号Java技术大本营，会不定期分享BAT面试资料等福利。

---