Springboot集成EasyExcel实现导入导出

前言：

之前写过一篇springboot集成poi导出表格的文章，但是当数据量变大后发现，有jvm内存溢出的现象，导致服务宕机；就此现象，必定要做出相应的解决方案才行，这时发现了阿里巴巴提供的easyexcel工具类，github地址：https://github.com/alibaba/easyexcel

本篇文章只讲解easyexecl的使用，apache poi和jxl，excelPOI优缺点请自行百度，这里也不一一描述了，咱们直接进入正题。

实现效果：

导出接口：5w条数据花费6秒钟左右成功导出！

导入数据：
准备要导入的数据如下

调用接口执行导入

查看数据库，导入成功

实现过程:

1.环境搭建（引入相关依赖）：
我这里使用的是Springboot版本为 2.3.2.RELEASE
数据库使用的mysql

     	<!--导出导入easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>

2.控制层代码
本次演示共计两个接口，分别为导出/导入

import com.king.science.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author xf
 * @version 1.0.0
 * @ClassName TestController
 * @Description TODO
 * @createTime 2020.09.06 13:49
 */
@RequestMapping("/excel")
@RestController
public class ExcelController {

    @Autowired
    private ExcelService excelService;
    /**
     * 导出用户信息
     * @param response
     * @throws IOException
     */
    @GetMapping("/user/excelExport")
    public void excelExport(HttpServletResponse response) throws IOException {
        excelService.excelExport(response);
    }

    /**
     * 导入用户信息
     * @param file
     * @return
     * @throws IOException
     */
    @PostMapping("/user/excelImport")
    public String excelImport(@RequestParam("file") MultipartFile file) throws IOException {
        excelService.excelImport(file);
        return "success";
    }

}

3.表格监听工具类
负责监听表格的导出/导入的数据

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

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

/**
 * @author xf
 * @version 1.0.0
 * @ClassName ExcelListener 表格监听工具类
 * @Description TODO
 * @createTime 2020.09.02 13:51
 */
@Component
public class ExcelListener  extends AnalysisEventListener {
    private static Logger logger = LoggerFactory.getLogger(ExcelListener.class);
   
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();
    public void invoke(Object o, AnalysisContext analysisContext) {
        logger.info("导入数据{}", JSON.toJSONString(o));
        datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
        // doSomething(o);//根据自己业务做处理
    }

    private void doSomething(Object object) {
        //1、入库调用接口，可在这里写，也可在业务层写
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//         datas.clear();//解析结束销毁不用的资源
    }
}

4.具体实现过程，接口实现类

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.king.science.dao.SysUserDao;
import com.king.science.dto.User;
import com.king.science.entity.SysUser;
import com.king.science.service.ExcelService;
import com.king.science.util.ExcelListener;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.TreeSet;
import java.util.stream.Collectors;

/**
 * @author xf
 * @version 1.0.0
 * @ClassName ExcelServiceImpl
 * @Description TODO
 * @createTime 2020.09.06 13:50
 */
@Service
public class ExcelServiceImpl  implements ExcelService {
    private static final Logger logger = LoggerFactory.getLogger(ExcelServiceImpl.class);


    @Autowired
    private SysUserDao sysUserDao;
    /**
     * 导出用户信息
     * @param response
     * @throws IOException
     */
    @Override
    public void excelExport(HttpServletResponse response) throws IOException {
        //准备导出的数据
        List<SysUser> list = sysUserDao.getListByType();
        logger.info("记录导出数据行数：{}",list.size());
        String fileName = "用户名单";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
        ServletOutputStream out = response.getOutputStream();
        //xls 一个 Sheet 最多支持 65535 行，如果数据要在一个 Sheet 中可以通过指定 ExcelFormat.Xlsx 来导出
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
        Sheet sheet = new Sheet(1,0,SysUser.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        sheet.setSheetName("用户名单");
        writer.write(list,sheet);
        writer.finish();
        out.flush();
        response.getOutputStream().close();
        out.close();
    }

    /**
     * 导入用户信息
     * @param file
     * @throws IOException
     */
    @Override
    public void excelImport(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        //实例化实现了AnalysisEventListener接口的类
            ExcelListener listener = new ExcelListener();
        //传入参数
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
        //读取信息
        excelReader.read(new Sheet(1, 1, User.class));
        //获取数据
        List<Object> list = listener.getDatas();
        List<User> originalList = new ArrayList<User>();
        logger.info("记录导入数据行数：{}",originalList.size());
        User catagory = new User();
        //转换数据类型
        for (int i = 0; i < list.size(); i++) {
            catagory = (User) list.get(i);
            originalList.add(catagory);
        }
        //对list进行去重并拿到新的list
        List<User> lists = originalList.stream()
                .filter(s -> StringUtils.isNotBlank(s.getPhoneNumber()))
                .collect(Collectors.collectingAndThen(Collectors.toCollection(
                        () -> new TreeSet<User>(Comparator.comparing(User::getPhoneNumber))), ArrayList::new));
        //执行批量插入
        if (lists.size() > 0){
            logger.info("执行批量入库");
            sysUserDao.addLists(lists);
           return;
        }

        logger.info("解析数据为空");
    }

}

5.用户表实体类
注意：注解 @ExcelProperty

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * 用户信息表(SysUser)表实体类
 *
 * @author makejava
 * @since 2020-08-30 16:53:53
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("sys_user")
@Getter
@Setter
public class SysUser extends BaseRowModel {

    /**
     *  注解 @ExcelProperty(value = String[], index = int):
     * 设置表头信息
     * value: 表名称
     * index: 列号
     */

    //用户ID
   // @TableId(type = IdType.ID_WORKER_STR)
    @TableId("user_id")
    @ExcelProperty(value = "ID", index = 0)
    private Integer userId;
    //部门ID
    @TableField("dept_id")
    @ExcelProperty(value = "部门id", index = 1)
    private Integer deptId;
    //登录账号
    @TableField("login_name")
    @ExcelProperty(value = "登录账号", index = 2)
    private String loginName;
    //用户昵称
    @TableField("user_name")
    @ExcelProperty(value = "用户昵称", index = 3)
    private String userName;
    //用户邮箱
    @TableField("email")
    @ExcelProperty(value = "用户邮箱", index = 4)
    private String email;
    //手机号码
    @TableField("phone_number")
    @ExcelProperty(value = "用户手机号", index = 5)
    private String phoneNumber;
    //用户性别:0男,1女
    @TableField("sex")
    @ExcelProperty(value = "用户性别", index = 6)
    private String sex;
    //头像路径
    @ExcelProperty(value = "头像地址", index = 8)
    @TableField("avatar")
    private String avatar;
    //密码
    @TableField("password")
    @ExcelProperty(value = "密码", index = 9)
    private String password;
    //盐加密
    @TableField("salt")
    @ExcelProperty(value = "盐", index = 10)
    private String salt;
    //类型:Y默认用户,N非默认用户
    @TableField("user_type")
    @ExcelProperty(value = "用户类型", index = 11)
    private String userType;
    //帐号状态:0正常,1禁用
    @ExcelProperty(value = "账户状态", index = 7)
    @TableField("status")
    private Integer status;
    //拒绝登录描述
    @TableField("refuse_des")
    @ExcelProperty(value = "拒绝登录描述", index = 12)
    private String refuseDes;
    //创建者
    @TableField("create_by")
    private String createBy;
    //创建时间
    @TableField("create_time")
    private Date createTime;
    //更新者
    @TableField("update_by")
    private String updateBy;
    //更新时间
    @TableField("update_time")
    private Date updateTime;


  
}

如上就是 本次使用springboot集成easyExcel的核心流程！

昨天已逝，明日是谜，面对今朝，尽力而为！