使用EasyExcel对Excel进行读写操作

操作Excel的文档 地址：Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 首页

前置：Excel中的数据和数据库中最终实现的效果如下

1.导Maven坐标

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
  </dependency>
<!--xls-->
   <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
   </dependency>

   <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
   </dependency>

   <dependency>
      <groupId>commons-fileupload</groupId>
     <artifactId>commons-fileupload</artifactId>
   </dependency>

 2.Controller层代码：

package com.atguigu.eduservice.controller;
import com.atguigu.commonutils.R;
import com.atguigu.eduservice.service.EduSubjectService;
import org.springframework.beans.factory.annotation.Autowired;
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;

@RestController
@RequestMapping("/eduservice/edusubject")
public class EduSubjectController {
    @Autowired
    private  EduSubjectService  eduSubjectService;//Service层的接口
    @PostMapping("/addsubject")
    public R addSubject(MultipartFile file){
        //将Excel文件中的数据保存到数据库
        eduSubjectService.saveSubject(file, eduSubjectService);
        return R.ok();
    }
}

3.Service层代码:

import com.alibaba.excel.EasyExcel;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.excel.ExcelSubjectData;
import com.atguigu.eduservice.listener.readListenerImpl;
import com.atguigu.eduservice.mapper.EduSubjectMapper;
import com.atguigu.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

@Service/*这是业务层的的代码*/
//EduSubjectMapper是Dao层的接口，EduSubject对应数据库表的实体类，
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    @Override
    public void saveSubject(MultipartFile file,EduSubjectService eduSubjectService) {
        try {
            InputStream inputStream = file.getInputStream();
            //文件流，文件实体类的class，监听器的实现类
            EasyExcel.read(inputStream, ExcelSubjectData.class,new readListenerImpl(eduSubjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

4.那个监听Excel的监听器的实现类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.excel.ExcelSubjectData;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.service.exceptionhandler.MyException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

//ExcelSubjectData是Excel表格的实体封装类
//EduSubject 是数据库中的实体封装对象
public class readListenerImpl extends AnalysisEventListener<ExcelSubjectData> {

    public EduSubjectService eduSubjectService;//Service层的接口
    public readListenerImpl() {
    }
    public readListenerImpl(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }

    //一行一行执行，Excel的每一行数据会被封装到ExcelSubjectData的对象中
    @Override
    public void invoke(ExcelSubjectData excelSubjectData, AnalysisContext analysisContext) {
        //如果文件是空，那么抛出异常
        if(excelSubjectData==null){
            throw new MyException(20001,"数据是空的");
        }
        //要是第一分类不重复，那么添加一条消息；如果重复的话再看第二分类重不重复
        EduSubject eduSubject = this.existOneSubject(eduSubjectService, excelSubjectData.getOneSubjectName());
        if(eduSubject==null){
            eduSubject = new EduSubject();//新建一个记录,添加到数据库
            eduSubject.setParentId("0");
            eduSubject.setTitle(excelSubjectData.getOneSubjectName());
            eduSubjectService.save(eduSubject);
        }

        String pid=eduSubject.getId();
        EduSubject eduSubject1 = this.existTwoSubject(eduSubjectService, excelSubjectData.getTwoSubjectName(), pid);
        if(eduSubject1==null){
            eduSubject1 = new EduSubject();
            eduSubject1.setParentId(pid);
            eduSubject1.setTitle(excelSubjectData.getTwoSubjectName());
            eduSubjectService.save(eduSubject1);
        }
    }
    //Excel解析结束后会执行该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
    //判断一级分类不能重复
    private EduSubject existOneSubject(EduSubjectService eduSubjectService,String name){
        QueryWrapper<EduSubject> eduSubjectQueryWrapper = new QueryWrapper<>();//设置查询的条件
        eduSubjectQueryWrapper.eq("title",name);
        eduSubjectQueryWrapper.eq("parent_id","0");
        EduSubject oneSubject = eduSubjectService.getOne(eduSubjectQueryWrapper);
        return oneSubject;
    }
    //判断二级分类不能重复
    private EduSubject existTwoSubject(EduSubjectService eduSubjectService,String name,String pid){
        QueryWrapper<EduSubject> eduSubjectQueryWrapper = new QueryWrapper<>();//设置查询的条件
        eduSubjectQueryWrapper.eq("title",name);
        eduSubjectQueryWrapper.eq("parent_id",pid);
        EduSubject twoSubject = eduSubjectService.getOne(eduSubjectQueryWrapper);
        return twoSubject;
    }
}