利用EasyExcel进行对表格数据的写入

<dependency>

   <groupId>com.alibaba</groupId>

   <artifactId>easyexcel</artifactId>

   <version>2.1.1</version>

</dependency>

package com.noob.eduservice.entity.vo;

import com.alibaba.excel.annotation.ExcelProperty;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import org.springframework.stereotype.Component;

/**

 * Author: noob

 * DATE: 2020/7/13 0013

 * PROJECT: jiaoyu

 * DESCRIPTION: excel中对应的实体类

 **/

@Component

@Data

@AllArgsConstructor

@NoArgsConstructor

public class ExcelSubjectDate {

    @ExcelProperty(index = 0)

    private String OneSubjectName;

    @ExcelProperty(index = 1)

    private String TwoSubjectName;

}

package com.noob.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.noob.eduservice.entity.EduSubject;

import com.noob.eduservice.entity.vo.ExcelSubjectDate;

import com.noob.eduservice.service.EduSubjectService;

import com.noob.exceptionHander.zdyException;

import lombok.AllArgsConstructor;

import lombok.NoArgsConstructor;

/**

 * Author: noob

 * DATE: 2020/7/13 0013

 * PROJECT: jiaoyu

 * DESCRIPTION: 读取Excel文件时的监听器,SubjectExcelListener这个类不能被spring管理，需要用到无参构造

 **/

@NoArgsConstructor

@AllArgsConstructor

public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectDate>{

    private EduSubjectService eduSubjectService;

    /**

     * @Author noob

     * @Description //读取excel中的内容，一行一行读取加入数据库中

     * @Param

     * @return

     **/

    @Override

    public void invoke(ExcelSubjectDate excelSubjectDate, AnalysisContext analysisContext) {

        if (excelSubjectDate == null){

            throw new zdyException(20001,"文件异常");

        }

        //一行一行读取，每次读取两个值

        //判断一级分类是否相同

        EduSubject exitOneSubject = this.exitOneSubject(eduSubjectService, excelSubjectDate.getOneSubjectName());

        if (exitOneSubject == null){ //没有这个一级分类进行添加

            exitOneSubject = new EduSubject();

            exitOneSubject.setParentId("0");

            exitOneSubject.setTitle(excelSubjectDate.getOneSubjectName());

            eduSubjectService.save(exitOneSubject);

        }

        //判断二级分类

        String pid = exitOneSubject.getId();//获取一级分类的id值

        EduSubject exitTwoSubject = this.exitTwoSubject(eduSubjectService, excelSubjectDate.getTwoSubjectName(), pid);

        if (exitTwoSubject == null){

            exitTwoSubject = new EduSubject();

            exitTwoSubject.setParentId(pid);

            exitTwoSubject.setTitle(excelSubjectDate.getTwoSubjectName());

            eduSubjectService.save(exitOneSubject);

        }

    }

    //判断二级分类不能重复

    private EduSubject exitTwoSubject(EduSubjectService eduSubjectService,String name,String pid){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();

        wrapper.eq("title",name);

        wrapper.eq("parent_id",pid);

        EduSubject onesubject = eduSubjectService.getOne(wrapper);

        return onesubject;

    }

    //判断一级分类不能重复

    private EduSubject exitOneSubject(EduSubjectService eduSubjectService,String name){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();

        wrapper.eq("title",name);

        wrapper.eq("parent_id",0);

        EduSubject twosubject = eduSubjectService.getOne(wrapper);

        return twosubject;

    }

    @Override

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

package com.noob.eduservice.controller;

import com.noob.commomutils.R;

import com.noob.eduservice.service.EduSubjectService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.CrossOrigin;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.multipart.MultipartFile;

/**

 * <p>

 * 课程科目 前端控制器

 * </p>

 *@Description

 * @author testjava

 * @since 2020-07-13

 */

@RestController

@RequestMapping("/edu/subject")

@CrossOrigin

public class EduSubjectController {

    @Autowired

    private EduSubjectService eduSubjectService;

    /**

     * @Author noob

     * @Description //添加课程分类,获取上传过来的文件进行读取

     * @Param file:上传的文件

     * @return

     **/

    @PostMapping("addsubject")

    public R  addSubject(MultipartFile file){

        eduSubjectService.saveSubject(file);

        return R.ok();

    }

}