文章目录

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

前言

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

springboot 整合 easy-excel

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

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

pom.xml

    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
                        
                    
                
            
        
    


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

实现

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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

导入监听

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 extends AnalysisEventListener {

    /**
     * 每隔1000条存储数据库，然后清理list，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 临时存储
     */
    private List 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("存储数据库成功!");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

导出模拟监听

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

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

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

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

        long afterTime = System.currentTimeMillis();
        log.info("耗时:{}", afterTime - beforeTime);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

mapper 层

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);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mapper.xml

    
    
        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}
        
    

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

结语

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

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