EasyExcel导入、导出(百万数据量,生产环境已测)

EasyExcel导入、导出(百万数据量,生产环境已测)

需求:测试EasyExcel的导入导出使用、性能,测试数据量十个字段100万条数据;


前言

这个是实战优化,数据层用的是es+mongdb,不过其他框架的同学可以直接使用,只需要稍微改一下就行,需要改动的地方我已经通过注释标明

一、导出实现

1、pom.xml文件

主要依赖是:

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

2、yml文件(导入、导出共用代码)

spring:
  devtools:
    restart:
      enabled: true  #设置开启热部署
      additional-paths: src/main/java #重启目录
      exclude: WEB-INF/**
  freemarker:
    cache: false    #页面不加载缓存,修改即时生效
  servlet:
    multipart: #这里必须要加这配置,不然一百万条数据文件太大会拒绝导入
      max-request-size: 100MB
      max-file-size: 100MB

3、共用的代码文件

创建一个类对象类名与字段名根据自身业务需要定义,用来接受接收需要导出的字段,通过@ExcelProperty(value = "列名", index = 排列顺序)注解来反射接收需要写出的值

/**
 * @className: UserAddressBookExportExcel
 * @description: 用户通讯录导出类
 * @author: 路过人间的姜先生
 * @date: 2022/7/25
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class UserAddressBookExportExcel {

  @ExcelProperty(value = "用户姓名", index = 0)
  private String userName;

  @ExcelProperty(value = "用户手机号", index = 1)
  private String userPhoneNumber;

  @ExcelProperty(value = "好友备注", index = 2)
  private String friendRemarks;

  @ExcelProperty(value = "好友手机号", index = 3)
  private String friendPhoneNumber;

  /** 好友是否注册 0=>没有注册 1=>有注册 */
  @ExcelProperty(value = "好友注册状态", index = 4)
  private String isRegister;

  /** 是否是好友 0=>不是好友 1=>是好友 */
  @ExcelProperty(value = "双方关系", index = 5)
  private String isFriend;

  /** 邀请通讯录好友的时间 */
  @ExcelProperty(value = "邀请时间", index = 6)
  private String sendnInvitationTime;
}

4、excel导出

/**
   * 我们公司的框架用的是es+mongdb做的数据层,
   * 分页也许和使用mybatis的同学们不同,不过本质上的逻辑都是一样,
   * 核心都是循环分页查询,然后拼接写入到excel中
   * 
   * @description: 导出用户Excel
   * @return: void
   * @author: 路过人间的姜先生
   * @date: 2022/5/19
   */
  @ApiOperation(value = "导出用户通讯录", httpMethod = "GET")
  @GetMapping(value = "/userAddressBook/exportExcel")
  @AvoidRepeatableCommit
  public void exportExcel(UserAddressBookQueryRequest queryRequest) {

    // 分页查询,每次查询量1000
    int pageNum = 1000;

    ExcelWriter excelWriter = null;
    try {
      // 下方使用了用户自己选择文件保存路径的方式,所以需要配请求参数,如果使用固定路径可忽略此代码
      String filename = URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "UTF-8");
      response.setCharacterEncoding("UTF-8");
      // 设定输出文件头
      response.setHeader("Content-disposition", "attachment; filename=" + filename);
      // 定义输出类型
      response.setContentType("application/x-xls");

      // 这里采用用户自己选择文件保存路径的方式
      OutputStream out = response.getOutputStream();
      // 这里其实就是把上面的方法分开写,写入同一个sheet
      excelWriter = EasyExcelFactory.write(out,UserAddressBookExportExcel.class).build();
      WriteSheet writeSheet = EasyExcelFactory.writerSheet("用户通讯录").build();

      // 重点是这里的循环调用---es的分页查询,先查询出需要导出的总数
      //(用mybatis的同学这个方法的实现也是一样的,只需要定义一个条件查询所有的方法即可)
      Integer count = userAddressBookService.count(queryRequest);
      int num = (count / pageNum) + 1;
      for (int i = 0; i < num; i++) {
        // Elasticsearch分页查询,from+size默认分页方式;
        Pageable pageable = new PageRequest(i, pageNum);
        // 从数据库中查询到我要的通讯录实体,然后组装为我定义的UserAddressBookExportExcel的集合模型
        List<UserAddressBookExportExcel> userAddressBookExportExcels =
            userAddressBookService.exportExcel(queryRequest, pageable);
        if (userAddressBookExportExcels.size() > 0) {
          // 导出
          excelWriter.write(userAddressBookExportExcels, writeSheet);
        }
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (excelWriter != null) {
        excelWriter.finish();
      }
    }
  }

5、涉及到的 Service层代码

/**
   * 通讯录导出
   *  该方法的入参可以根据你们自己框架的设计来定义,本博主由于用的是es,所以是这么写的,
   *  还是那句话,代码不同,核心实现逻辑一致
   * @param: queryRequest
   * @param: pageable
   * @return: void
   * @author: 路过人间的姜先生
   * @date: 2022/7/25
   */
  @Override
  public List<UserAddressBookExportExcel> exportExcel(
      UserAddressBookQueryRequest queryRequest, Pageable pageable) {
      //调用条件分页查询的方法,返回一个模型集合(此处用你们项目中自己的分页查询方法)
    List<UserAddressBookDTO> dtoList = findAll(queryRequest, pageable).getContent();
    // 下面的操作就是定义一个UserAddressBookExportExcel集合对象,遍历数据库中返回的集合对它赋值
    List<UserAddressBookExportExcel> userAddressBookDTOS = new ArrayList<>();
    dtoList.stream()
        .forEach(
            (userBoot -> {
              UserAddressBookExportExcel build =
                  UserAddressBookExportExcel.builder()
                      .userName(
                          Objects.nonNull(userBoot.getUserDTO())
                              ? userBoot.getUserDTO().getNickName()
                              : "用户已注销")
                      .userPhoneNumber(
                          Objects.nonNull(userBoot.getUserDTO())
                              ? userBoot.getUserDTO().getPhoneNumber()
                              : "用户已注销")
                      .friendPhoneNumber(userBoot.getFriendPhoneNumber())
                      .friendRemarks(userBoot.getFriendRemarks())
                      .sendnInvitationTime(
                          Objects.nonNull(userBoot.getSendnInvitationTime())
                              ? userBoot.getSendnInvitationTime().toString()
                              : LocalDate.now().toString())
                      .isRegister(
                          (Objects.nonNull(userBoot.getIsFriend())
                                  && Objects.equals(userBoot.getIsRegister(), 1))
                              ? "该好友已注册"
                              : "未注册")
                      .isFriend(
                          (Objects.nonNull(userBoot.getIsFriend())
                                  && Objects.equals(userBoot.getIsFriend(), 1))
                              ? "好友"
                              : "非好友")
                      .build();

              userAddressBookDTOS.add(build);
            }));

    return userAddressBookDTOS;
  }

二、导如实现

1、EasyExcel的工具类(最主要的文件,如果使用的其它框架,可以忽略上面的代码,注释会尽量写的详细一些)

package com.bug.util.excel;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.bug.entity.TestExcel;
import com.bug.service.excel.TestExcelService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
/**
 * 小bug

 */
 /**
   * 通讯录导出
   * excel导入的工具类
   * 是不是很奇怪这里是实现的ReadListener接口而不是继承的AnalysisEventListener类
   * 因为源码里AnalysisEventListener类也是继承的ReadListener接口,何必多此一举呢
   * @author: 路过人间的姜先生
   * @date: 2022/7/25
   */
public class ExcelUpload implements ReadListener<UserAddressBookExportExcel> {
 
    private UserAddressBookService userAddressBookService;
    /**
     * 注意这里是不能交给spring管理的,就是不能使用@Resource,或者@Autowired注入
     * 可以使用构造方法的方式获取你想要的对象
     * @param testExcelService testExcelService
     */
    public ExcelUpload(UserAddressBookService userAddressBookService){
        this.userAddressBookService = userAddressBookService;
    }
 
    /**
     * 无参构造方法,不能省略
     */
    public ExcelUpload(){}
 
    public static final Logger log = LoggerFactory.getLogger(ExcelUpload.class);
 
    private static final int count = 10000;//设置读取的条数,每次达到指定条数时就保存入数据库
    private List<UserAddressBookExportExcel> exportExcelTrue = new ArrayList<>();//校验正确的数据集合,数量达到设定值后插入数据库,然后再清空一直循环
    private List<UserAddressBookExportExcel> exportExcelFalse = new ArrayList<>();//校验失败、保存数据库失败的数据集合,可以插入到一个失败数据表,或者显示在前端提醒用户哪些数据导入失败
    /**
     * 很明显,onException这个就是用来处理异常的,当其它侦听器出现异常时会触发此方法
     * @param e Exception
     * @param analysisContext analysisContext
     * Exception 默认是直接抛出异常的,要注意处理异常
     */
    @Override
    public void onException(Exception e, AnalysisContext analysisContext) {
        log.info("兄嘚,你的代码出现异常了!");
        e.printStackTrace();
    }
 
    /**
     * 获取excel的第一行head数据
     * @param map 数据map
     * @param analysisContext analysisContext
     */
    @Override
    public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
        log.info("第一列:{} 第二列:{} 第三列:{}",map.get(0).getStringValue(),map.get(1).getStringValue(),map.get(2).getStringValue());
 
    }
 
    /**
     * 读取正文数据,一次只读取一行
     * @param testExcel 实体类对象
     * @param analysisContext analysisContext
     */
    @Override
    public void invoke(UserAddressBookExportExcel exportExcel, AnalysisContext analysisContext) {
        log.info("读取到一条数据:{}", JSON.toJSONString(exportExcel));
        //因为是测试,这里只做一些简单的为空判断,正式的可以根据业务需求自己写校验条件
        if(testExcel == null){//对象为空直接跳出
            return;
        }else if(StringUtils.isBlank(exportExcel.getUserName())){//判断名字是否为空
            exportExcelFalse.add(exportExcel);//放入错误集合列表
            return;
        }else if(StringUtils.isBlank(testExcel.getUserPhoneNumber())){//判断电话是否为空
            exportExcelFalse.add(testExcel);//放入错误集合列表
            return;
        }
        exportExcelTrue.add(exportExcel);//校验通过的方法正确集合列表
        if(count <= exportExcelTrue.size()){//集合数据大于设定的数量时,提交数据库保存
            userAddressBookService.saveBatch(exportExcelTrue);//批量存入数据库
            exportExcelTrue = new ArrayList<>();//清空正确列表数据,再次循环
        }
 
    }
 
    /**
     * 额外单元格返回的数据,这个方法还没详细了解过,一直没用到过
     * @param cellExtra cellExtra对象
     * @param analysisContext analysisContext
     */
    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
        log.info("extra:{}",JSON.toJSONString(cellExtra));
    }
 
    /**
     * 当读取完所有数据后就会执行次方法
     * @param analysisContext analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("兄嘚,所有数据读取完了哦!");
        //读取完excel后再次判断是否还要未存入数据库的数据
        if(exportExcelTrue.size() > 0){
            userAddressBookService.saveBatch(exportExcelTrue);//不为空,则存入数据库
        }
        //这里也可以处理错误列表,保存入错误列表数据库,或者显示到前端给用户查看
    }
 
    /**
     * 这个方法最坑,默认返回的是false,一定要记得改成true,为false时会只返回一条数据,
     * 意思是只会执行invokeHead方法
     * @param analysisContext analysisContext
     * @return 是否读取下一行
     */
    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        return true;
    }
}

2、excel导入

/**
     * 2、EasyExcel excel导入(百万级数据测试)
     * @return
     */
    @PostMapping("/submitExcel")
    public void submitExcel(MultipartFile file){
        log.info("开始导入excel。。。");

        //创建reader
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(file.getInputStream(), UserAddressBookExportExcel.class, new ExcelUpload(UserAddressBookService)).build();
            // 构建sheet,可以指定是第几个sheet
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            // 读取sheet
            excelReader.read(readSheet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (excelReader != null) {
                //这里不能省略
                excelReader.finish();
            }
        }
    }

三、总结

EasyExcel本身的读写速度是非常快的,再加上先生我用的是es,速度翻倍,如上:导入100万条数据7个字段,只需要3-4分钟即可完成,for循环导出100万条只需要2分钟。

真正影响速度的其实是:你的批量插入方法和你的分页查询的速度,打个比方,就最上面的分页查询导出,循环对100万条分页查询,

一次只查500条,整个的导出需要15分钟左右,查询就用了12-13分钟左右。最后看看效果吧!

  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

路过人间的姜先生

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值