使用EasyExcel对Excel进行操作

2020-09-15  本文已影响0人  c_gentle
课程分类存储结构.png EasyExcel写操作.png EasyExcel读操作.png EasyExcel读操作分类.png

一、引入pom依赖

  <dependencies>
        <!--阿里开源操作excel表格的工具类,引入的前提是已经引入poi因为他是对poi的封装-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

二、创建实体类

设置表头和添加的数据字段

package com.caiweiwei.demo.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
    //设置表头名称
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer sno;
    @ExcelProperty(value = "学生姓名",index = 1)
    private String name;
}

三、实现写操作

       //实现对excel写的操作
       //1.设置写入文件夹的地址和文件的名称
       String filename = "F:\\write.xlsx";
      //调用easyExcel里面的方法实现写的操作
     //write方法的两个参数:第一个参数文件路径名称,第二个参数实体类class
      EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());

创建方法循环设置要添加到Excel的数据

//循环设置要添加的数据，最终封装到list集合中
private static List<DemoData> data() {
    List<DemoData> list = new ArrayList<DemoData>();
    for (int i = 0; i < 10; i++) {
        DemoData data = new DemoData();
        data.setSno(i);
        data.setSname("张三"+i);
        list.add(data);
    }
    return list;
}

四、实现读操作

创建对应实体类

package com.caiweiwei.demo.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
    //设置表头名称
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer sno;
    @ExcelProperty(value = "学生姓名",index = 1)
    private String name;
}

创建读取操作的监听器

package com.caiweiwei.demo.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;
public class ExcelListener extends AnalysisEventListener<DemoData> {

    //一行一行读取excel内容
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println("*****"+demoData);
    }
   //读取表头内容
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头"+headMap);
    }
    //读取完成之后做什么事情
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

调用实现最终的读取

   public static void main(String[] args) throws Exception {
        // 写法1：
        String fileName = "F:\\01.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ReadData.class, new ExcelListener()).sheet().doRead();
}

五、案例代码

功能描述 表格写有一级分类和对应二级分类,读取表格内容将分类存储到数据库中,并把重复的去掉
读取表格实体类

package com.caiweiwei.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;
}

controller层

package com.caiweiwei.eduservice.controller;
import com.caiweiwei.commonutils.R;
import com.caiweiwei.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>
 *
 * @author testjava
 * @since 2020-09-15
 */
@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();
    }
}

service实现类

package com.caiweiwei.eduservice.service.impl;

import com.alibaba.excel.EasyExcel;
import com.caiweiwei.eduservice.entity.EduSubject;
import com.caiweiwei.eduservice.entity.excel.SubjectData;
import com.caiweiwei.eduservice.listener.SubjectExcelListener;
import com.caiweiwei.eduservice.mapper.EduSubjectMapper;
import com.caiweiwei.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.InputStream;

/**
 * <p>
 * 课程科目 服务实现类
 * </p>
 *
 * @author testjava
 * @since 2020-09-15
 */
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    /**
     * 功能描述:添加课程分类
     *
     * @Author: 蔡威威
     * @Date: 2020/9/15 21:36
     */
    @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();
        }
    }
}

编写的监听器

package com.caiweiwei.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.caiweiwei.eduservice.entity.EduSubject;
import com.caiweiwei.eduservice.entity.excel.SubjectData;
import com.caiweiwei.eduservice.service.EduSubjectService;
import com.caiweiwei.servicebase.exceptionhandler.GuliException;

import java.util.Map;

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    //因为SubjectExcelListener不能交给spring管理,需要自己new,不能注入其他对象
    //不能实现数据库操作
    public EduSubjectService eduSubjectService;

    public SubjectExcelListener() {
    }

    public SubjectExcelListener(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }

    //读取excel内容,一行一行进行读取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData == null) {
            throw new GuliException(20001, "文件数据为空");
        }
        //一行一行读取,每次读取有两个值,第一个值为一级分类,第二个值为对应的二级分类
        EduSubject eduSubject = existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
        //添加一级分类
        if (eduSubject == null) {
            eduSubject = new EduSubject();
            eduSubject.setParentId("0");
            eduSubject.setTitle(subjectData.getOneSubjectName());
            eduSubjectService.save(eduSubject);
        }
        //添加二级分类
        //获取一级分类的id值
        String pid = eduSubject.getId();
        EduSubject existTwoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
        if (existTwoSubject == null) {
            existTwoSubject = new EduSubject();
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());
            existTwoSubject.setParentId(pid);
            eduSubjectService.save(existTwoSubject);
        }
    }

    //判断一级分类是否重复
    private EduSubject existOneSubject(EduSubjectService eduSubjectService, String name) {
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title", name);
        queryWrapper.eq("parent_id", "0");
        return eduSubjectService.getOne(queryWrapper);
    }
    //判断二级分类是否重复
    private EduSubject existTwoSubject(EduSubjectService eduSubjectService, String name, String pid) {
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("title", name);
        queryWrapper.eq("parent_id", pid);
        return eduSubjectService.getOne(queryWrapper);
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

上传表格示意图


课程分类
上一篇下一篇

猜你喜欢

热点阅读