SpringBoot+Mybatis-Plus+EasyExcel

首先建立一个springboot项目，导入依赖

<!-- MyBatisPlus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
 <!-- mysql驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- 数据连接池 druid -->
        <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.20</version>
        </dependency>
 <!--   阿里出的easyexcel     -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

application.yml配置mybatis-plus

#mybatis-plus配置
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

mapper中创建userInfoMapper

package com.haoyang.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.haoyang.enity.userinfo;
import org.apache.ibatis.annotations.Mapper;


@Mapper
public interface userInfoMapper extends BaseMapper<userinfo>{

}

在启动类配置@MapperScan("com.haoyang.Mapper")

在创建一个util包建UploadDataListener类

package com.haoyang.util;

import java.util.List;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.haoyang.Mapper.userInfoMapper;
import com.haoyang.enity.userinfo;
import lombok.extern.slf4j.Slf4j;

/**
 * 模板的读取类
 *
 * @author Jiaju Zhuang
 */
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class UploadDataListener implements ReadListener<userinfo> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    private List<userinfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private userInfoMapper uploadDAO;

    public UploadDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        //uploadDAO = new UploadDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param uploadDAO
     */
    public UploadDataListener(userInfoMapper uploadDAO) {
        this.uploadDAO = uploadDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(userinfo data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
//        获取数据
            if (cachedDataList.size() >= 1) {
                for (int i = 0; i < cachedDataList.size(); i++) {
                    userinfo user = (userinfo) cachedDataList.get(i);
                    System.out.println(user.toString());
                    //使用mybatis-plus添加到数据库
                    uploadDAO.insert(user);
                }
            }
//        uploadDAO.insert(cachedDataList);//来自mapper里面的批量新增方法（自己去建一个）
        log.info("存储数据库成功！");
    }
}

在Controller中创建一个userinfoController类

package com.haoyang.Controller;

import com.alibaba.excel.EasyExcel;
import com.haoyang.Mapper.userInfoMapper;
import com.haoyang.enity.userinfo;
import com.haoyang.util.UploadDataListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@RestController
public class userinfoController {
    @Autowired
    userInfoMapper userinfos;

    @GetMapping("/user")
    public List find() {
        List<userinfo> list = userinfos.selectList(null);
        return list;
    }
//    //excel导出功能 控制器代码
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        //先去数据库里查询出所有数据，然后把这些数据导出为excel
        List<userinfo> list = userinfos.selectList(null);
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("Test", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), userinfo.class).sheet("模板").doWrite(list);
    }
    /**
     * excel文件上传
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>
     * 3. 直接读即可
     */
    @PostMapping("excelupload")
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), userinfo.class, new UploadDataListener(userinfos)).sheet().doRead();
        return "成功";
    }
}

实体类enity中

package com.haoyang.enity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;

@Data
@ColumnWidth(30)//注释在具体属性上,设置单独列。注释在类上,统一设置列宽
@HeadRowHeight(30)//设置表头行高
@ContentRowHeight(20)//统一设置数据行行高
@ApiModel(value = "User对象", description = "")
public class userinfo implements Serializable {

    private static final long serialVersionUID =1L;

    @ExcelProperty(value = "主键ID",index = 0)
    @ApiModelProperty(value = "主键ID")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ExcelProperty(value = "姓名", index = 1)
    @ApiModelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "创建时间", index = 2)
    @ApiModelProperty(value = "创建时间")
    @TableField(value = "signtime",fill = FieldFill.INSERT) //数据库名是驼峰命名，mybaits-plus不认识
    private String signTime;

    @ExcelProperty(value = "电话号码", index = 3)
    @ApiModelProperty(value = "电话号码")
    private String phone;

    @ExcelProperty(value = "邮箱", index = 4)
    @ApiModelProperty(value = "邮箱")
    private String email;

    @ExcelProperty(value = "QQ", index = 5)
    @ApiModelProperty(value = "QQ")
    private String qq;

    @ExcelProperty(value = "微信", index = 6)
    @ApiModelProperty(value = "微信")
    private String weixin;

    @ExcelProperty(value = "头像", index = 7)
    @ApiModelProperty(value = "头像")
    private String avatar_url;

    @Override
    public String toString() {
        return "userinfo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", signTime='" + signTime + '\'' +
                ", phone='" + phone + '\'' +
                ", email='" + email + '\'' +
                ", qq='" + qq + '\'' +
                ", weixin='" + weixin + '\'' +
                ", avatar_url='" + avatar_url + '\'' +
                '}';
    }
public userinfo(){}
    public userinfo(Integer id, String name, String signTime, String phone, String email, String qq, String weixin, String avatar_url) {
        this.id = id;
        this.name = name;
        this.signTime = signTime;
        this.phone = phone;
        this.email = email;
        this.qq = qq;
        this.weixin = weixin;
        this.avatar_url = avatar_url;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setSignTime(String signTime) {
        this.signTime = signTime;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    public void setWeixin(String weixin) {
        this.weixin = weixin;
    }

    public void setAvatar_url(String avatar_url) {
        this.avatar_url = avatar_url;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getSignTime() {
        return signTime;
    }

    public String getPhone() {
        return phone;
    }

    public String getEmail() {
        return email;
    }

    public String getQq() {
        return qq;
    }

    public String getWeixin() {
        return weixin;
    }

    public String getAvatar_url() {
        return avatar_url;
    }
}