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

前言

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

源码

链接：https://pan.baidu.com/s/1Ixp1o-fjyO2zioB8efb9LQ?pwd=1234
提取码：1234

springboot 整合 easy-excel

主要技术：springboot、mybatis-plus和easy-excel。贴源码出来，是因为第一次做easy-excel的时候是摸索阶段，有很多思考不全的地方，还有后期使用版本和插件升级等原因。建议看源码，或者看我往后写的几篇，有不正确的地方，我趁周末时间勘误。

·创建一个工程，太简单了省略。注意IDEA 有没有 lombok 插件，2021年之后的版本是自带有的。

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.com.ztn.excel</groupId>
    <artifactId>ztn-easy-excel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>ztn-easy-excel</name>
    <description>ztn-easy-excel</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

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

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

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/*.xml
  type-aliases-package: cn.com.ztn.excel.pojo
  global-config:
    db-config:
      id-type: auto
      logic-delete-value: -1
      logic-not-delete-value: 0
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    call-setters-on-nulls: true
    jdbc-type-for-null: null
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

实现

mybatis-plus 配置分页插件

package cn.com.ztn.excel.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("cn.com.ztn.excel.mapper")
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

easy-excel 导入、导出实现

写一个通用的导入监听，这样不用每个类都写一个。官网上有示例，不过自己封装一下会更好。

package cn.com.ztn.excel;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;

@SpringBootApplication
@EnableAsync
@MapperScan({"cn.com.ztn.excel.mapper"})
public class ZtnEasyExcelApplication {

    public static void main(String[] args) {
        SpringApplication.run(ZtnEasyExcelApplication.class, args);
    }
}

导入监听

这里导入监听还是不建议用泛型，当时第一次做 easy-excel 程序员第一敏感神经就是能简则简。

package cn.com.ztn.excel.listener;

import cn.com.ztn.excel.mapper.BaseDaoMapper;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.log4j.Log4j2;
import org.springframework.scheduling.annotation.Async;

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

@Slf4j
public class ImportExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 每隔1000条存储数据库，然后清理list，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 临时存储
     */
    private List<T> cachedData = new ArrayList<>(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private BaseDaoMapper baseDaoMapper;

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

    /**
     * 这个每一条数据解析都会来调用
     * @param t
     * @param analysisContext
     */
    @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("存储数据库成功!");
    }
}

导出模拟监听

这里做了一个调整，一是：优化了查询，分页不用去count，数据多了，mySql 的count 查询非常慢。还有 URLEncoder.encode 附件名称加码，前段需要用 decodeURI 函数解码，下载的附件就会显示中文。

package cn.com.easyExcel.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.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class ExportListener<T> {

    private BaseMapper<T> baseMapper;

    public ExportListener(BaseMapper<T> baseMapper) {
        this.baseMapper = baseMapper;
    }

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

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

    private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

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

    private static final int PAGE_SIZE = 10000;

    public void exportExcel(HttpServletResponse response, String sheetName, Class<T> pojoClass,
                            LambdaQueryWrapper<T> queryWrapper) throws IOException {
        ServletOutputStream out = getServletOutputStream(response, sheetName);
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = EasyExcel.write(out, pojoClass).build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();

        int totalCount = Math.toIntExact(baseMapper.selectCount(queryWrapper));

        int pageNumber = (int) Math.ceil((double) totalCount / (double) PAGE_SIZE);    //分页条数看情况

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

    /**
     * 查询优化的方法
     */
    public void exportNoQueryCount(HttpServletResponse response, String sheetName, Class<T> pojoClass,
                                   LambdaQueryWrapper<T> queryWrapper) throws IOException {
        ServletOutputStream out = getServletOutputStream(response, sheetName);
        ExcelWriter excelWriter = EasyExcel.write(out, pojoClass).build();
        
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
        int startIndex = 1;
        while (true){
            int startParam =(startIndex - 1) * PAGE_SIZE;
            int pageNumber = (int) Math.ceil((double) startParam / (double) PAGE_SIZE+1);
            Page<T> page = new Page<>(pageNumber, PAGE_SIZE, false);
            Page<T> pojoIPage = baseMapper.selectPage(page, queryWrapper);
            List<T> recordList = pojoIPage.getRecords();
            if (CollectionUtils.isEmpty(recordList)) {
                break;
            }
            startIndex++;
            excelWriter.write(recordList , writeSheet);
        }
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }

    public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String sheetName) throws IOException {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DATA_FORMAT);
        String nowTime = formatter.format(LocalDateTime.now());
        String fileName = sheetName.concat("_").concat(nowTime).concat(".xlsx");
        response.setContentType(CONTENT_TYPE);
        //设置字符集为utf-8
        response.setCharacterEncoding(CHARACTER_UTF_8);
        
        //用postman测正常，浏览器多了filename_=utf-8等字样
//        response.setHeader(CONTENT_DISPOSITION,
//                "attachment;filename=" + URLEncoder.encode(fileName, CHARACTER_UTF_8)
//                        + ";filename*=utf-8''" + URLEncoder.encode(fileName, CHARACTER_UTF_8));
        
        //postman测会乱码，但浏览器下载就正常,真实开发是用这个，前端需要解码，文件名才能看到中文
        response.setHeader(CONTENT_DISPOSITION,
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        return response.getOutputStream();
    }

}

Controller 层

实体对象

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);
    }
}

service 层

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.slf4j.Slf4j;
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;

@Slf4j
@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<City> queryWrapper = new QueryWrapper<>();

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

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

mapper 层

package cn.com.ztn.excel.mapper;

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

import java.util.List;

public interface BaseDaoMapper<T> extends BaseMapper<T> {

    void batchInsertData(List<T> 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<City> {
    //批量插入
    @Async
    void batchInsertData(List<City> list);
}

mapper.xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.com.ztn.excel.mapper.CityMapper">
    <!--批量插入-->
    <insert id="batchInsertData">
        insert into city_cn(`name`, country_code, district, population)
        values
        <foreach collection="list" item="city" separator=",">
            (#{city.name}, #{city.countryCode},#{city.district}, #{city.population})
        </foreach>
    </insert>

    <select id="selectAllByPage" resultType="cn.com.ztn.excel.pojo.City">
        SELECT
            `name`,
            country_code,
            district,
            population
        FROM
            city_cn
        WHERE 1 = 1
        <if test="isDelete != null">
            and is_delete = #{isDelete}
        </if>
    </select>
</mapper>

结语

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