[开发测试]app10-EasyExecl读写，后端上传execl服务请求接口

pom.xml

        <!--lombok用来简化实体类：需要安装lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
        </dependency>
        <!--xls-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>

读

读比较复杂，需要创建监听器EasyListener

实体类 DemoData

package com.shengun.osss;

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

@Data
public class DemoData {

	// index表示行
    @ExcelProperty(value = "学生序号",index = 0)
    private Integer sno;

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

监听类 EasyListener

package com.shengun.osss.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.shengun.osss.DemoData;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class EasyListener extends AnalysisEventListener<DemoData> {

    /**
     * 存储所有读取到的数据
     */
    private List<DemoData> list = new ArrayList<>();

    public List<DemoData> getList() {
        return list;
    }

    // 一行一行读取
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("***" + data);
        //这里就可以存储数据到mysql中
        list.add(data);
    }

    //读取表头
    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        System.out.println("表头信息：" + headMap);
    }

    //读取完成之后做到事情
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}

读数据

package com.shengun.osss;

import com.alibaba.excel.EasyExcel;
import com.shengun.osss.listener.EasyListener;

import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {
    public static void main(String[] args) {
        String fileName = "D:\\pdf\\write.xls";
        //读
        EasyListener easyListener = new EasyListener();
        EasyExcel.read(fileName, DemoData.class,easyListener).sheet().doRead();
        List<DemoData> list = easyListener.getList();
        System.out.println("###############" + list);
    }
}

写

package com.shengun.osss;

import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {
    public static void main(String[] args) {
        String fileName = "D:\\pdf\\write.xls";
        //写
        EasyExcel.write(fileName, DemoData.class)
                .sheet("学生列表")
                .doWrite(getDataList());
    }

    /**
     * 构造要写的数据
     * @return
     */
    private static List<DemoData> getDataList() {

        List<DemoData> list = new ArrayList<>();

        for (int i = 0; i < 10; i++) {
            DemoData demoData = new DemoData();
            demoData.setSno(i);
            demoData.setSname("lucy" + i);
            list.add(demoData);
        }

        return list;
    }
}

后端接口编写实战

上传这种合适execl数据，后台把数据保存在mysql中

实体类

package com.shengun.eduservice.entity.execl;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import lombok.Data;

/**
 * excel两列参数
 */
@ApiModel(value="excel对象",description="excel对象封装")
@Data
public class SubjectData {

    @ExcelProperty(index = 0)
    private String oneSubjectName;

    @ExcelProperty(index = 1)
    private String twoSubjectName;

}

controller

在监听程序中，我们需要把数据保存在mysql中，所有我们要把eduSubjectService当成参数传递给service

eduSubjectService.saveSubject(file,eduSubjectService);

代码：

package com.shengun.eduservice.controller;


import com.shengun.commonutils.R;
import com.shengun.eduservice.entity.subject.OneSubject;
import com.shengun.eduservice.service.EduSubjectService;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import javax.security.auth.Subject;
import java.util.List;

/**
 * <p>
 * 课程科目 前端控制器
 * </p>
 *
 * @author shengun
 * @since 2022-04-17
 */
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
@ApiModel(value="课程管理",description = "课程管理")
public class EduSubjectController {

    @Autowired
    private EduSubjectService eduSubjectService;


    // 添加课程分类
    @PostMapping("/addSubject")
    @ApiOperation(value = "添加课程分类")
    public R addSubject(MultipartFile file) {
        eduSubjectService.saveSubject(file,eduSubjectService);
        return R.ok();
    }
}

service + serviceImpl

需要把eduSubjectService 当成参数传递给监听程序

 EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead();

package com.shengun.eduservice.service;

import com.shengun.eduservice.entity.EduSubject;
import com.baomidou.mybatisplus.extension.service.IService;
import com.shengun.eduservice.entity.subject.OneSubject;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * <p>
 * 课程科目 服务类
 * </p>
 *
 * @author shengun
 * @since 2022-04-17
 */
public interface EduSubjectService extends IService<EduSubject> {

    //添加课程分类
    void saveSubject(MultipartFile file,EduSubjectService eduSubjectService);
}

package com.shengun.eduservice.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.shengun.eduservice.entity.EduSubject;
import com.shengun.eduservice.entity.execl.SubjectData;
import com.shengun.eduservice.entity.subject.OneSubject;
import com.shengun.eduservice.entity.subject.TwoSubject;
import com.shengun.eduservice.listener.SubjectExcelListener;
import com.shengun.eduservice.mapper.EduSubjectMapper;
import com.shengun.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shengun.servicebase.exceptionHandler.ZkcExecption;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * 课程科目 服务实现类
 * </p>
 *
 * @author shengun
 * @since 2022-04-17
 */
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

    @Autowired
    private EduSubjectMapper eduSubjectMapper;

    @Override
    public void saveSubject(MultipartFile file, EduSubjectService eduSubjectService) {

        try {
            //获取流
            InputStream inputStream = file.getInputStream();
            // 读取流中信息，保存在数据库中
            EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead();
        }catch (Exception e) {
            e.printStackTrace();
            // 失败，抛出异常
            throw new ZkcExecption(20002, "添加课程分类失败");
        }

    }
}

监听类

由于一级分类和二级分类在一张表中，很多人说可以连表查询
我这里使用的是

判断一级分类可不可以添加：

name = ? and parent_id = 0

判断二级分类可不可以添加：

name = ? and parent_id = ?

package com.shengun.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.shengun.eduservice.entity.EduSubject;
import com.shengun.eduservice.entity.execl.SubjectData;
import com.shengun.eduservice.service.EduSubjectService;
import com.shengun.servicebase.exceptionHandler.ZkcExecption;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    /**
     * 方便注入，实现添加课程
     */
    private EduSubjectService eduSubjectService;
    public SubjectExcelListener(){}
    public SubjectExcelListener(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }

    @Override
    public void invoke(SubjectData subjectData, AnalysisContext context) {
      log.info("######################################添加表中信息：" + subjectData);

      if(subjectData == null){
          throw new ZkcExecption(20001, "添加失败");
      }

      //一级分类可不可以添加
      EduSubject oneEduSubject = this.existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
      if(oneEduSubject == null){
          // 等于空，重新new个EduSubject对象
          oneEduSubject = new EduSubject();
          oneEduSubject.setTitle(subjectData.getOneSubjectName());
          oneEduSubject.setParentId("0");
          eduSubjectService.save(oneEduSubject); //添加成功之后会返回ID
      }

      //获取父类ID
      String parentId = oneEduSubject.getId();

      EduSubject twoEduSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), parentId);
        if (twoEduSubject == null) {
            twoEduSubject = new EduSubject();
            twoEduSubject.setTitle(subjectData.getTwoSubjectName());
            twoEduSubject.setParentId(parentId);
            eduSubjectService.save(twoEduSubject);  //添加二级分类
        }
    }


    /**
     * 判断一级分类可不可以添加
     * @return
     */
    private EduSubject existOneSubject(EduSubjectService eduSubjectService,String name) {
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title", name);
        queryWrapper.eq("parent_id", "0");
        EduSubject eduSubject = eduSubjectService.getOne(queryWrapper);
        return eduSubject;
    }

    /**
     * 判断二级分类可不可以添加
     * @return
     */
    private EduSubject existTwoSubject(EduSubjectService eduSubjectService,String name,String parentId) {
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title", name);
        queryWrapper.eq("parent_id", parentId);
        EduSubject eduSubject = eduSubjectService.getOne(queryWrapper);
        return eduSubject;
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("##############所有数据解析完成############");
    }
}

阅读世界，共赴山海

423全民读书节，邀你共读