springboot整合阿里easyexcel2.x实现海量数据excel导入导出demo

springboot整合阿里easyexcel实现海量数据excel导入导出。实现demo如下：

1.pom.xml导入依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.6</version>
</dependency>

<dependency>
   <groupId>cn.hutool</groupId>
   <artifactId>hutool-core</artifactId>
   <version>5.2.0</version>
</dependency>
2.导出实体类

package com.zwj.easyexcel.data;

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

import java.util.Date;

/**
 * @Author: zhengwj
 * @Description:  特殊号码 导出实体
 * @Date: 2020/4/1 11:20
 * @Version: 1.0
 */
@Data
public class ConfigFilterExport {

    /**
     * 特殊号码主键
     */
    @ExcelIgnore
    private String filterPk;
    /**
     * 用户号码
     */
    @ExcelProperty("用户号码")
    private String filterNumber;
    /**
     * 用户姓名
     */
    @ExcelProperty("用户姓名")
    private String filterName;
    /**
     * 归属地
     */
    @ExcelProperty("归属地")
    private String filterLocation;
    /**
     * 号码类型
     */
    @ExcelProperty("号码类型")
    private String filterType;
    /**
     * 申请人
     */
    @ExcelIgnore
    private String filterApplicant;
    /**
     * 申请时间
     */
    @ExcelIgnore
    private Date filterApptime;
    /**
     * 申请原因
     */
    @ExcelIgnore
    private String filterReason;
    /**
     * 审核状态
     */
    @ExcelIgnore
    private String filterStatus;
    /**
     * 是否有效
     */
    @ExcelIgnore
    private String filterIsenabled;
    /**
     * 创建人
     */
    @ExcelIgnore
    private String filterCreater;
    /**
     * 创建时间
     */
    @ExcelIgnore
    private Date filterCtime;
    /**
     * 更新人
     */
    @ExcelIgnore
    private String filterUpdater;
    /**
     * 更新时间
     */
    @ExcelIgnore
    private Date filterUtime;
}
3.导入实体类

package com.zwj.easyexcel.data;

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

import java.util.Date;

/**
 * @Author: zhengwj
 * @Description:  特殊号码 导入实体(自定义解析列)
 *
 * @Date: 2020/4/1 11:20
 * @Version: 1.0
 */
@Data
public class ConfigFilterImport {

    /**
     * 特殊号码主键
     */
    @ExcelIgnore
    private String filterPk;
    /**
     * 用户号码
     */
    @ExcelProperty("用户号码")
    private String filterNumber;
    /**
     * 用户姓名
     */
    @ExcelProperty("用户姓名")
    private String filterName;
    /**
     * 归属地
     */
    @ExcelProperty("归属地")
    private String filterLocation;
    /**
     * 号码类型
     */
    @ExcelProperty("号码类型")
    private String filterType;
    /**
     * 申请人
     */
    @ExcelIgnore
    private String filterApplicant;
    /**
     * 申请时间
     */
    @ExcelIgnore
    private Date filterApptime;
    /**
     * 申请原因
     */
    @ExcelIgnore
    private String filterReason;
    /**
     * 审核状态
     */
    @ExcelIgnore
    private String filterStatus;
    /**
     * 是否有效
     */
    @ExcelIgnore
    private String filterIsenabled;

}
3.导入监听类

package com.zwj.easyexcel.listener;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;
import com.zwj.easyexcel.dao.ConfigFilterDao;
import com.zwj.easyexcel.data.ConfigFilterImport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @Author: zhengwj
 * @Description:    特殊号码导入监听类
 * @Date: 2020/4/1 16:30
 * @Version: 1.0
 */
// 不能被spring管理
public class ConfigFilterListener  extends AnalysisEventListener<ConfigFilterImport> {

    private static final Logger logger = LoggerFactory.getLogger(ConfigFilterListener.class);

    private static final int BATCH_COUNT = 10000;

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

    private ConfigFilterDao configFilterDao;
    public ConfigFilterListener(ConfigFilterDao configFilterDao){
        this.configFilterDao = configFilterDao;
    }

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

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        logger.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        logger.info("{}条数据，开始存储数据库！", list.size());
        configFilterDao.save(list);
        logger.info("存储数据库成功！");
    }
}
4.导入导出实体的分页查询和批量新增方法

package com.zwj.easyexcel.dao;


import com.zwj.easyexcel.data.ConfigFilterExport;
import com.zwj.easyexcel.data.ConfigFilterImport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * @Author: zhengwj
 * @Description:    导入导出实体的分页查询和批量新增方法
 *
 * @Date: 2020/4/1 16:43
 * @Version: 1.0
 */
@Repository
public class ConfigFilterDao {

    private static final Logger logger = LoggerFactory.getLogger(ConfigFilterDao.class);

    @Value("${spring.datasource.url}")
    private String url ;
    @Value("${spring.datasource.username}")
    private String user;
    @Value("${spring.datasource.password}")
    private String password ;

    /**
     * 批量增加
     * @param list     大批量数据使用原生jdbc
     */
    //TODO
    public void save(List<ConfigFilterImport> list) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            String sql = "INSERT INTO TBL_CONFIG_FILTER............";   //插入sql
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            for(ConfigFilterImport dr : list){
                pstm.setString(1, UUID.randomUUID().toString().replace("-", ""));
                pstm.setString(2,"");
                pstm.setString(2,"");
                //.........
                pstm.addBatch();
            }
            pstm.executeBatch();
            conn.commit();
            Long endTime = System.currentTimeMillis();
            logger.info("用时：" + (endTime - startTime));
        } catch (Exception e) {
            logger.error("执行出错{}",e.getMessage());
            throw new RuntimeException(e);
        } finally {
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    logger.error("执行出错{}",e.getMessage());
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    logger.error("执行出错{}",e.getMessage());
                    throw new RuntimeException(e);
                }
            }
        }
    }

    //数据导出使用的分页查询方法  直接调用mybatis相应实体方法（sql优化）
    public List<ConfigFilterExport> selectConfigFilterPage(Map<String, Object> param) {

        //TODO
        return null;
    }
}
5.接口

package com.zwj.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.zwj.easyexcel.dao.ConfigFilterDao;
import com.zwj.easyexcel.data.ConfigFilterExport;
import com.zwj.easyexcel.data.ConfigFilterImport;
import com.zwj.easyexcel.listener.ConfigFilterListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 数据量: 多
 读: 多
 写: 多
 *            注：对于大批量数据前端使用ajax异步请求
 * @author zwj
 * @since 2020-03-31 14:57:45
 */
@RestController
@RequestMapping("/excel")
public class EasyExcelController  {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelController.class);

    @Resource
    private ConfigFilterDao configFilterDao;
    /**
     * 获取特殊号码导入模板
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping(value = "/getConfigFilterTemplate")
    public void getConfigFilterTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException{
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode防止中文乱码
        String fileName = URLEncoder.encode("特殊号码导入模板", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        List<ConfigFilterImport> list = new ArrayList<>();
        EasyExcel.write(response.getOutputStream(), ConfigFilterImport.class).sheet("模板").doWrite(list);
    }

    /**
     * 特殊号码excel导入
     * @param file
     * @return
     * @throws IOException
     */
    @RequestMapping(value = { "/uploadConfigFilterExcel" }, method = { RequestMethod.POST })
    public String uploadConfigFilterExcel(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), ConfigFilterImport.class, new ConfigFilterListener(configFilterDao)).sheet().doRead();
        return "success";
    }

    /**
     * 特殊号码excel导出
     * @param param
     * @param response
     * @throws IOException
     */
    @RequestMapping(value = { "/downloadConfigFilter" }, method = { RequestMethod.GET })
    public void download(@RequestParam Map<String, Object> param, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");

        String fileName = "ConfigFilter" + System.currentTimeMillis() + ".xlsx";
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ConfigFilterExport.class).build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("特殊号码").build();

        int pageNumber = 1;
        int pageSize = 10000;
        int dataLength = pageSize;
        List<ConfigFilterExport> data = null;
        while (dataLength == pageSize){
     //       int startIndex = (pageNumber - 1) * pageSize;
            param.put("pageNo", pageNumber);
            param.put("pageSize", pageSize);
            data=configFilterDao.selectConfigFilterPage(param);   //分页查询
            excelWriter.write(data, writeSheet);
            if(null == data || data.isEmpty()){
                break;
            }
            dataLength = data.size();
            pageNumber++;
            //写数据
            excelWriter.write(data, writeSheet);
        }
        excelWriter.finish();
    }

}

支持多版本excel，实现方便，直接替换demo中的实体类即可。
框架导出导出效率非常之快，且不会内存溢出，功能优化在于sql，耗时主要在于sql执行，所以大批量数据时一定要使用原生jdbc操作。
完整代码可在github上下载：
GitHub - wojozer/easyexcel-demo: 阿里easyexcel 2.x实现百万级海量数据效率导入导出demo