使用EasyExcel将Excel表格中二级分类导入对应数据库

后端接口实现

1 需求

• 从Excel表格中导入数据库中
• 

2 用代码生成器生成对应实体类serviceController

• strategy.setInclude("edu_subject");   //使用这张表生成
  

• Controller上加跨域注解 @CrossOrigin @CrossOrigin

• /*
  Navicat MySQL Data Transfer

  Source Server : localhost_3306
  Source Server Version : 80024
  Source Host : localhost:3306
  Source Database : guli

  Target Server Type : MYSQL
  Target Server Version : 80024
  File Encoding : 65001

  Date: 2021-07-16 23:12:48
  */

  SET FOREIGN_KEY_CHECKS=0;

  ---

  – Table structure for edu_subject

  ---

  DROP TABLE IF EXISTS edu_subject;
  CREATE TABLE edu_subject (
  id char(19) NOT NULL COMMENT ‘课程类别ID’,
  title varchar(10) NOT NULL COMMENT ‘类别名称’,
  parent_id char(19) NOT NULL DEFAULT ‘0’ COMMENT ‘父ID’,
  sort int unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘排序字段’,
  gmt_create datetime NOT NULL COMMENT ‘创建时间’,
  gmt_modified datetime NOT NULL COMMENT ‘更新时间’,
  PRIMARY KEY (id),
  KEY idx_parent_id (parent_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT=‘课程科目’;

添加依赖

 <dependencies>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--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>
    </dependencies>

3 创建Excel实体类对象 (对应起Excel表格)

一级分类	二级分类
前端开发	vue
前端开发	js
前端开发	jquery
后端开发	Java
后端开发	Java
数据库开发	MySQL

package com.atguigu.eduservice.entity.Excel;

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

@Data
public class SubjectData {

    @ExcelProperty(index=0)
    private String oneSubjectName;  //对应一级课程名称  一级分类

    @ExcelProperty(index=1)
    private String twoSubjectName;  //对应二级课程名称   二级分类
}

4 创建读Excel数据所需要的监听器

package com.atguigu.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.Excel.SubjectData;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.servicebase.exceptionhandler.GuliException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public  class SubjectExcelListener extends AnalysisEventListener {


    //注入service因为要查询数据库
    @Autowired
    private EduSubjectService eduSubjectService;


    //读取excel内容.一行一行进行读取   每次读取一行  多次调用直至读完

   //一级分类	二级分类

   //前端开发	vue
   //前端开发	js
   //前端开发	jquey
   //后端开发	Java
   //后端开发	Java
   //数据库开发	MySQL
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        //强转类型
        SubjectData subjectData = (SubjectData) o;
        System.out.println(subjectData);  //SubjectData(oneSubjectName=前端开发, twoSubjectName=vue)

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

        //一行一行读取的  每次读取有两值  第一个值是一级分类  第二个值是二级分类
        //要做一个判断  一级 二级分类如果相同了  就不在添加了
        EduSubject oneSubject = existOneSubject(subjectData.getOneSubjectName());

        //添加一级分类
        if(oneSubject==null){
            //空说明不存在  添加
            EduSubject subject = new EduSubject();
            subject.setParentId("0");
            subject.setTitle(subjectData.getOneSubjectName());
            eduSubjectService.save(subject);
        }

        //添加二级分类
        //又调用了一次查一级分类   解决getId()报的空指针异常
        EduSubject oneSubject2 = existOneSubject(subjectData.getOneSubjectName());
        String pid=oneSubject2.getId();
        EduSubject twoSubject = existTwoSubject(subjectData.getTwoSubjectName(), pid);
        if(twoSubject==null){
            //添加
           // System.out.println("进来了");
            EduSubject subject1 = new EduSubject();
            subject1.setParentId(pid);
            subject1.setTitle(subjectData.getTwoSubjectName());
            eduSubjectService.save(subject1);
        }
    }

    //自定义方法  判断一级分类不能重复添加
    private EduSubject existOneSubject(String name){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);       //查是否有对应的课程名字
        wrapper.eq("parent_id",0);  //一级分类父id默认0
        EduSubject edu1 = eduSubjectService.getOne(wrapper);
        return edu1;
    }

    //自定义方法   判断二级分类不能重复添加
    private EduSubject existTwoSubject(String name,String pid){

        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);  //查是否有对应的课程名字
        wrapper.eq("parent_id",pid); //二级分类的父id就是一级分类的id
        EduSubject edu2 = eduSubjectService.getOne(wrapper);
        return edu2;

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

5. Service层

• 接口 代码生成器直接生成的 方法自己定义的

• package com.atguigu.eduservice.service;
  
  import com.atguigu.eduservice.entity.EduSubject;
  import com.baomidou.mybatisplus.extension.service.IService;
  import org.springframework.web.multipart.MultipartFile;
  
  /**
   * <p>
   * 课程科目 服务类
   * </p>
   *
   * @author niu
   * @since 2021-07-16
   */
  public interface EduSubjectService extends IService<EduSubject> {
  
      //添加课程分类
      void saveSubject(MultipartFile file);
  }
  
  

• 接口实现类

  package com.atguigu.eduservice.service.impl;
  
  import com.alibaba.excel.EasyExcel;
  import com.atguigu.eduservice.entity.EduSubject;
  import com.atguigu.eduservice.entity.Excel.SubjectData;
  import com.atguigu.eduservice.listener.SubjectExcelListener;
  import com.atguigu.eduservice.mapper.EduSubjectMapper;
  import com.atguigu.eduservice.service.EduSubjectService;
  import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.stereotype.Service;
  import org.springframework.web.multipart.MultipartFile;
  
  import java.io.IOException;
  import java.io.InputStream;
  
  /**
   * <p>
   * 课程科目 服务实现类
   * </p>
   *
   * @author testjava
   * @since 2021-07-16
   */
  @Service
  public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
  
      @Autowired
      private SubjectExcelListener subjectExcelListener;
  
      @Override
      public void saveSubject(MultipartFile file) {
  
  
          try {
              //文件输入流
              InputStream  inputStream = file.getInputStream();
              //调用方法进行读取
              EasyExcel.read(inputStream,
                      SubjectData.class,
                      subjectExcelListener).sheet().doRead();
  
          } catch (IOException e) {
              e.printStackTrace();
          }
      }
  }
  
  

  接口实现类里面调用方法进行读取 ,

  //调用方法进行读取
  EasyExcel.read(inputStream,
  SubjectData.class,
  subjectExcelListener).sheet().doRead();

  视频里用的 new subjectExcelListener() 来传入监听器 导致监听器无法交给spring容器进行管理,也就无法用spring容器里的方法(无法注入service层,调用service的方法) 所以就使用传值的方法 将service一步一步传值到监听器中 很麻烦 不过思路值得借鉴

  

  所以我在这直接传一个监听器对象,没有直接new 在监听器类那加注解交给spring管理 解决麻烦

6. controller层

package com.atguigu.eduservice.controller;


import com.atguigu.commonutils.R;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.eduservice.service.EduTeacherService;
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>
 *
 * @author testjava
 * @since 2021-07-16
 */
@CrossOrigin
@RestController
@RequestMapping("/eduservice/subject")
public class EduSubjectController {
    @Autowired
    private EduSubjectService eduSubjectService;
    //添加课程分类
    //获取上传过来的文件  ,将文件中的内容读取出来
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file){
        //上传
        try {
            eduSubjectService.saveSubject(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return R.ok();
    }
}

7 项目结构

8 swagger测试

• 数据库中成功添加