easy-excel 解决百万数据导入导出，性能很强

• 前言
• springboot 整合 easy-excel
• • pom.xml
  • application.yml
  • 实现
  • • easy-excel 导入、导出实现
    • • 导入监听
      • 导出模拟监听
      • Controller 层
      • service 层
      • mapper 层
      • mapper.xml
• 结语

上个月公司有个老系统反馈，导入导出数据量一大就出现卡顿，半天不响应。刚来领导要求我改造一下，在 gitee 和 度娘搜索比较了几个，easy-excel 性能是非常非常高的，具体还要结合自身的网络环境和代码环境。

主要技术：springboot、mybatis-plus和easy-excel。

·创建一个工程，太简单了省略。

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.5
         
    
    cn.com.ztn.excel
    ztn-easy-excel
    0.0.1-SNAPSHOT
    ztn-easy-excel
    ztn-easy-excel
    
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter-web
        

        
            org.projectlombok
            lombok
            true
        

        
            org.springframework.boot
            spring-boot-starter-test
            test
        

        
            com.alibaba
            easyexcel
            2.2.10
        

        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.0
        

        
            mysql
            mysql-connector-java
            runtime
        

        
            com.alibaba
            druid-spring-boot-starter
            1.2.2
        

        
            com.alibaba
            fastjson
            1.2.70
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
                
                    
                        
                            org.projectlombok
                            lombok
                        
                    
                
            
        
    

server:
  port: 8888
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/world?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      initial-size: 10
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
  servlet:
    multipart:
      max-file-size: 200MB
      max-request-size: 200MB

mybatis-plus:
  mapper-locations: classpath*:/mapper
    private static final int BATCH_COUNT = 1000;
    
    private List cachedData = new ArrayList<>(BATCH_COUNT);
    
    private baseDaoMapper baseDaoMapper;

    
    public importExcelListener(baseDaoMapper baseDaoMapper) {
        this.baseDaoMapper = baseDaoMapper;
    }

    
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(t));
        cachedData.add(t);
        if(cachedData .size() >= BATCH_COUNT){
            batchSaveData();
            cachedData = new ArrayList<>(BATCH_COUNT);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //这里也要保存数据，确保最后遗留的数据也存储到数据库
        batchSaveData();
    }

    @Async
    public void batchSaveData() {
        log.info("{}条数据，开始存储数据库!", cachedData.size());
        baseDaoMapper.batchInsertData(cachedData);
        log.info("存储数据库成功!");
    }
}

package cn.com.ztn.excel.listener;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.baseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExportExcelListener {

    private baseMapper baseMapper;

    public ExportExcelListener(baseMapper baseMapper) {
        this.baseMapper = baseMapper;
    }

    private static final String DATA_FORMAT = "yyyy-MM-dd-HH-mm-ss";

    private static final String CHARACTER = "UTF-8";

    private static final String CONTENT_TYPE = "application/vnd.ms-excel;charset=utf-8";

    private static final String CONTENT_DISPOSITION = "Content-Disposition";

    private static final String CACHE_ConTROL = "Cache-Control";

    private static final String NO_STORE = "no-store";

    private static final String MAX_AGE = "max-age=0";

    private static final Integer PAGE_SIZE = 10000;

    public void exportExcel(HttpServletResponse response, String sheetName, Class pojoClass,
                            QueryWrapper queryWrapper) throws IOException {
        SimpleDateFormat format = new SimpleDateFormat(DATA_FORMAT);
        String nowTime = format.format(new Date());

        StringBuffer bf = new StringBuffer();
        String fileName = bf.append(sheetName)
                .append(nowTime)
                .append(".xlsx")
                .toString();

        ExcelWriter excelWriter = null;
        ServletOutputStream out = response.getOutputStream();
        //设置字符集为utf-8
        response.setCharacterEncoding(CHARACTER);
        response.setContentType(CONTENT_TYPE);
        //通知浏览器服务器发送的数据格式
        response.setHeader(CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode(fileName, CHARACTER));

        //发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据
        response.setHeader(CACHE_CONTROL, NO_STORE);
        response.addHeader(CACHE_CONTROL, MAX_AGE);
        // 这里 需要指定写用哪个class去写
        excelWriter = EasyExcel.write(out, pojoClass).build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
        Integer number = baseMapper.selectCount(queryWrapper);
        int pageNumber = (int) Math.ceil((double) number / (double) PAGE_SIZE);    //分页条数看情况

        // 去调用写入,根据数据库分页的总的页数来
        for (int i = 1; i <= pageNumber; i++) {
            //先定义一个空集合每次循环使他变成null减少内存的占用
            List pageCity = null;
            Page page = new Page<>(i, PAGE_SIZE);
            Page cityIPage = (Page) baseMapper.selectPage(page, queryWrapper);
            pageCity = cityIPage.getRecords();
            excelWriter.write(pageCity , writeSheet);
            pageCity.clear();
        }
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
        out.flush();
    }

}

实体对象

package cn.com.ztn.excel.pojo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("city_cn")
public class City {
    
    @ExcelIgnore
    private Long id;
    @ColumnWidth(30)
    @ExcelProperty("城市名称")
    private String name;
    @ColumnWidth(15)
    @ExcelProperty("国家编码")
    private String countryCode;
    @ColumnWidth(15)
    @ExcelProperty("地区")
    private String district;
    @ColumnWidth(15)
    @ExcelProperty("人口")
    private Long population;
}

package cn.com.ztn.excel.controller;

import cn.com.ztn.excel.service.CityService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

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

@RestController
@Slf4j
@RequestMapping("/city")
public class CityController {

    @Autowired
    private CityService cityService;

    @PostMapping(value="/excel/import")
    public String importExcel(@RequestParam(name = "file") MultipartFile file) throws IOException {
        cityService.importExcel(file);
        return "导入成功";
    }

    @PostMapping(value = "/excel/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        cityService.exportExcel(response);
    }
}

package cn.com.ztn.excel.service;

import org.springframework.web.multipart.MultipartFile;

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

public interface CityService {
    void importExcel(MultipartFile file) throws IOException;

    void exportExcel(HttpServletResponse response) throws IOException;
}

package cn.com.ztn.excel.service.impl;

import cn.com.ztn.excel.listener.ExportExcelListener;
import cn.com.ztn.excel.listener.importExcelListener;
import cn.com.ztn.excel.mapper.CityMapper;
import cn.com.ztn.excel.pojo.City;
import cn.com.ztn.excel.service.CityService;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

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

@Log4j2
@Service
public class CityServiceImpl implements CityService {

    @Autowired
    private CityMapper cityMapper;

    @Override
    public void importExcel(MultipartFile file) throws IOException {
        long beforeTime = System.currentTimeMillis();
        EasyExcel.read(file.getInputStream(),
                City.class,
                new importExcelListener(cityMapper)).sheet().headRowNumber(1).doRead();
        long afterTime = System.currentTimeMillis();
        log.info("耗时:{}", afterTime - beforeTime);
    }

    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        long beforeTime = System.currentTimeMillis();
        QueryWrapper queryWrapper = new QueryWrapper<>();

        new ExportExcelListener(cityMapper).
                exportExcel(response, "城市列表", City.class,
                        queryWrapper);

        long afterTime = System.currentTimeMillis();
        log.info("耗时:{}", afterTime - beforeTime);
    }
}

package cn.com.ztn.excel.mapper;

import com.baomidou.mybatisplus.core.mapper.baseMapper;

import java.util.List;

public interface baseDaoMapper extends baseMapper {

    void batchInsertData(List list);
}

package cn.com.ztn.excel.mapper;

import cn.com.ztn.excel.pojo.City;
import org.springframework.scheduling.annotation.Async;

import java.util.List;


public interface CityMapper extends baseDaoMapper {
    //批量插入
    @Async
    void batchInsertData(List list);
}

    
    
        insert into city_cn(`name`, country_code, district, population)
        values
        
            (#{city.name}, #{city.countryCode},#{city.district}, #{city.population})
        
    

    
        SELECT
            `name`,
            country_code,
            district,
            population
        FROM
            city_cn
        WHERe 1 = 1
        
            and is_delete = #{isDelete}
        
    

测试过代码，八九十万数据毫无压力，平均一万一秒还要少。当然还是要看网络环境和代码环境。总体easy-excel 比起 easy-poi 性能高了不少。