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

龚振濂

2023-12-01

文章目录

前言

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

springboot 整合 easy-excel

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

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

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

导入监听

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;

@Log4j2
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("存储数据库成功!");
    }
}

导出模拟监听

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<T> {

    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<T> pojoClass,
                            QueryWrapper<T> 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<T> pageCity = null;
            Page<T> page = new Page<>(i, PAGE_SIZE);
            Page<T> cityIPage = (Page<T>) baseMapper.selectPage(page, queryWrapper);
            pageCity = cityIPage.getRecords();
            excelWriter.write(pageCity , writeSheet);
            pageCity.clear();
        }
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
        out.flush();
    }

}

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.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<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 性能高了不少。

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦