胖虎的工具箱-编程导航 胖虎的工具箱-编程导航

EasyExcel3.0.5 解决大数据导入导出，防止OOM

2年前 (2022) 程序员胖胖胖虎阿
374 0 0

文章目录

  • 前言
  • 源码
  • 代码实现
    • POM 依赖
    • application.yaml
    • Application 启动类
    • Config 相关配置类
    • 创建员工信息表
    • 导入和导出实体
    • Controller 层
    • Service 层
    • easyExcel 导入导出监听器和转换器（重中之重）
      • 导入监听器
      • 导出监听器
      • 转换器
    • Mapper 层
    • mapper.xml

前言

之前分享过一篇 easyExcel 实现导入导出的问题，最近对语雀社区多阅读了一下，想着用最新升级的版本重新做一个。框架和细节我都做了一点改造，希望各位使用这个框架的时候能收获更多。

同时温馨提示：我只会认真分享知识。作为IT这一行，不要去轻信什么P7、P8，什么架构师培训，不要被网络培训机构整的噱头骗了钱财，给你上课的人，可能自己终身都没有进过大厂，讲解的东西都是官网社区水分过来的，不要钱财骗了，时间浪费了。

我们自己自学，踏实跟着官网社区做，好好找一些实用的资料，他不香吗？

好了，话不多说，上干货。如果觉得好，请不要吝啬你的评论。

源码

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

代码实现

创建 springboot 项目的过程再此省略。
采用技术：
数据库：mysql
框架：springboot、mybatis-plus、easyExcel
数据连接池：durid
接口文档：knife4j，swagger的升级版

POM 依赖

<?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.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>springBoot-easyExcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springBoot-easyExcel</name>
    <description>springBoot-easyExcel</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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </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.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

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

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.79</version>
        </dependency>

        <!--文档框架 -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>2.0.9</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.22</version>
        </dependency>

    </dependencies>

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

</project>

EasyExcel3.0.5 解决大数据导入导出，防止OOM

application.yaml

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/test?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
  # SpringBoot2.6.0和 swagger冲突问题：
  # 原因是在springboot2.6.0中将SpringMVC 默认路径匹配策略从AntPathMatcher 更改为 PathPatternParser，
  # 导致出错，解决办法是切换回原先的AntPathMatcher
  mvc:
    pathmatch:
      matching-strategy: ant_path_matcher

mybatis-plus:
  mapper-locations: classpath*:/mapper/*.xml
  type-aliases-package: cn.com.easyExcel.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

Application 启动类

package cn.com.easyExcel;

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

@EnableAsync
@MapperScan({"cn.com.easyExcel.mapper"})
@SpringBootApplication
public class easyExcelApplication {

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

}

Config 相关配置类

MybatisPlusConfig.java

package cn.com.easyExcel.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;
    }

}

Knife4jConfiguration.java

package cn.com.easyExcel.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2WebMvc;

@Configuration
@EnableSwagger2WebMvc
public class Knife4jConfiguration {

    @Bean(value = "createRestFulApi")
    public Docket createRestFulApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(new ApiInfoBuilder()
                        .description("<div style='font-size:16;color:red'> swagger-bootstrap-ui RestFul APIs</div>")
                        .version("1.0")
                        .build())
                //分组名称
                .groupName("2.X版本")
                .select()
                //这里指定Controller扫描包路径
                .apis(RequestHandlerSelectors.basePackage("cn.com.easyExcel.controller"))
                .paths(PathSelectors.any())
                .build();
    }

}

创建员工信息表

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `gender` char(1) DEFAULT NULL COMMENT '性别：0-男，1-女',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `birthday` timestamp NULL DEFAULT NULL COMMENT '生日',
  `marital_status` char(1) DEFAULT NULL COMMENT '婚姻状态：0-未婚，1-已婚',
  `education` char(1) DEFAULT NULL COMMENT '学历：0-大专，1-本科，2-硕士，3-研究生',
  `blood_type` char(1) DEFAULT NULL COMMENT '血型：A，B，O，AB',
  `mobile` varchar(11) DEFAULT NULL COMMENT '电话',
  `department_name` varchar(50) DEFAULT NULL COMMENT '部门',
  `national_area` varchar(50) DEFAULT NULL COMMENT '国家地区',
  `province` varchar(30) DEFAULT NULL COMMENT '省',
  `city` varchar(30) DEFAULT NULL COMMENT '市',
  `id_card_number` varchar(18) DEFAULT NULL COMMENT '身份证',
  `personal_mail_box` varchar(50) DEFAULT NULL COMMENT '个人邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

导入和导出实体

导入实体：EmployeeImport .java
converter : 转换器，可以自定义，也可以用 easyExcel 封装好的。
导入实体和导出实体的差别就在解析时间字段，其他都是一样的。

package cn.com.easyExcel.pojo;

import cn.com.easyExcel.excel.converter.EducationConverter;
import cn.com.easyExcel.excel.converter.GenderConverter;
import cn.com.easyExcel.excel.converter.MaritalStatusConverter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

@Data
@TableName("employee")
@ApiModel(value = "EmployeeImport ", description = "员工信息导入实体")
public class EmployeeImport {

    @ApiModelProperty("主键")
    @ExcelIgnore
    private Long id;

    @ApiModelProperty("姓名")
    @TableField("user_name")
    @ColumnWidth(16)
    @ExcelProperty(value = "姓名", index = 0)
    private String userName;

    @ApiModelProperty("性别")
    @ColumnWidth(16)
    @ExcelProperty(value = "性别", index = 1, converter = GenderConverter.class)
    private String gender;

    @ApiModelProperty("年龄")
    @ColumnWidth(16)
    @ExcelProperty(value = "年龄", index = 2)
    private int age;

    @ApiModelProperty("生日")
    @ColumnWidth(28)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("生日")
    private String birthday;

    @ApiModelProperty("婚姻状况")
    @TableField("marital_status")
    @ColumnWidth(20)
    @ExcelProperty(value = "婚姻状况", index = 4, converter = MaritalStatusConverter.class)
    private String maritalStatus;

    @ApiModelProperty("学历")
    @ColumnWidth(18)
    @ExcelProperty(value = "学历", index = 5, converter = EducationConverter.class)
    private String education;

    @ApiModelProperty("血型")
    @TableField("blood_type")
    @ColumnWidth(18)
    @ExcelProperty(value = "血型", index = 6)
    private String bloodType;

    @ApiModelProperty("手机号码")
    @ColumnWidth(22)
    @ExcelProperty(value = "手机号码", index = 7)
    private String mobile;

    @ApiModelProperty("部门")
    @TableField("department_name")
    @ColumnWidth(20)
    @ExcelProperty(value = "部门", index = 8)
    private String departmentName;

    @ApiModelProperty("国家地区")
    @TableField("national_area")
    @ColumnWidth(20)
    @ExcelProperty(value = "国家地区", index = 9)
    private String nationalArea;

    @ApiModelProperty("省")
    @ColumnWidth(20)
    @ExcelProperty(value = "省", index = 10)
    private String province;

    @ApiModelProperty("市")
    @ColumnWidth(20)
    @ExcelProperty(value = "市", index = 11)
    private String city;

    @ApiModelProperty("身份证")
    @TableField("id_card_number")
    @ColumnWidth(24)
    @ExcelProperty(value = "身份证", index = 12)
    private String idCardNumber;

    @ApiModelProperty("个人邮箱")
    @TableField("personal_mail_box")
    @ColumnWidth(24)
    @ExcelProperty(value = "个人邮箱", index = 13)
    private String personalMailBox;

}

导出实体：EmployeeExporter.java
与导入实体差别在时间相关的字段，数据库是Date，所以用Date。

package cn.com.easyExcel.pojo;

import cn.com.easyExcel.excel.converter.EducationConverter;
import cn.com.easyExcel.excel.converter.GenderConverter;
import cn.com.easyExcel.excel.converter.MaritalStatusConverter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.converters.date.DateDateConverter;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

@Data
@TableName("employee")
@ApiModel(value = "EmployeeExporter", description = "员工信息导出实体")
public class EmployeeExporter {

    @ApiModelProperty("主键")
    @ExcelIgnore
    private Long id;

    @ApiModelProperty("姓名")
    @TableField("user_name")
    @ColumnWidth(16)
    @ExcelProperty(value = "姓名", index = 0)
    private String userName;

    @ApiModelProperty("性别")
    @ColumnWidth(16)
    @ExcelProperty(value = "性别", index = 1, converter = GenderConverter.class)
    private String gender;

    @ApiModelProperty("年龄")
    @ColumnWidth(16)
    @ExcelProperty(value = "年龄", index = 2)
    private int age;

    @ApiModelProperty("生日")
    @ColumnWidth(28)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("生日")
//    @ExcelProperty(value = "生日", index = 3, converter = DateDateConverter.class)
    private Date birthday;

    @ApiModelProperty("婚姻状况")
    @TableField("marital_status")
    @ColumnWidth(20)
    @ExcelProperty(value = "婚姻状况", index = 4, converter = MaritalStatusConverter.class)
    private String maritalStatus;

    @ApiModelProperty("学历")
    @ColumnWidth(18)
    @ExcelProperty(value = "学历", index = 5, converter = EducationConverter.class)
    private String education;

    @ApiModelProperty("血型")
    @TableField("blood_type")
    @ColumnWidth(18)
    @ExcelProperty(value = "血型", index = 6)
    private String bloodType;

    @ApiModelProperty("手机号码")
    @ColumnWidth(22)
    @ExcelProperty(value = "手机号码", index = 7)
    private String mobile;

    @ApiModelProperty("部门")
    @TableField("department_name")
    @ColumnWidth(20)
    @ExcelProperty(value = "部门", index = 8)
    private String departmentName;

    @ApiModelProperty("国家地区")
    @TableField("national_area")
    @ColumnWidth(20)
    @ExcelProperty(value = "国家地区", index = 9)
    private String nationalArea;

    @ApiModelProperty("省")
    @ColumnWidth(20)
    @ExcelProperty(value = "省", index = 10)
    private String province;

    @ApiModelProperty("市")
    @ColumnWidth(20)
    @ExcelProperty(value = "市", index = 11)
    private String city;

    @ApiModelProperty("身份证")
    @TableField("id_card_number")
    @ColumnWidth(24)
    @ExcelProperty(value = "身份证", index = 12)
    private String idCardNumber;

    @ApiModelProperty("个人邮箱")
    @TableField("personal_mail_box")
    @ColumnWidth(24)
    @ExcelProperty(value = "个人邮箱", index = 13)
    private String personalMailBox;

}

Controller 层

EmployeeExcelController.java

package cn.com.easyExcel.controller;

import cn.com.easyExcel.service.EmployeeService;
import cn.com.easyExcel.vo.ResultVo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
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;

@Api(tags = "excel操作")
@RestController
@RequestMapping(value = "/employee/excel")
public class EmployeeExcelController {

    @Autowired
    private EmployeeService employeeService;

    @ApiOperation(value = "初始化数据")
    @GetMapping("/initData")
    public ResultVo initData(){
        employeeService.initData();
        return ResultVo.success();
    }

    @ApiOperation(value = "导入")
    @PostMapping(value="/import")
    public ResultVo importExcel(@RequestParam(name = "file") MultipartFile file) throws IOException {
        employeeService.importExcel(file);
        return ResultVo.successMsg("导入成功");
    }

    @ApiOperation(value = "导出")
    @PostMapping(value = "/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        employeeService.exportExcel(response);
    }
}

ResultVo.java

package cn.com.easyExcel.vo;

import lombok.Data;

@Data
public class ResultVo<T> {
    private static final String SUCCESS_CODE = "S10000";
    private static final String UNKNOWN_FAIL_CODE = "E10001";

    private T data;
    private String code;
    private String message;
    private boolean success;

    public ResultVo() {
    }

    public ResultVo(boolean success, String code) {
        this.success = success;
        this.code = code;
    }

    public ResultVo(T data, boolean success, String code, String message) {
        this.data = data;
        this.success = success;
        this.code = code;
        this.message = message;
    }

    public static ResultVo<Void> success() {
        return success(null, (String)null);
    }

    public static <R> ResultVo<R> success(R data) {
        return success(data, (String)null);
    }

    public static <R> ResultVo<R> success(R data, String message) {
        return new ResultVo(data, true, SUCCESS_CODE, message);
    }

    public static ResultVo<Void> successMsg(String message) {
        return success(null, message);
    }

    public static <R> ResultVo<R> failure(String code) {
        return failure(code, (String)null);
    }

    public static <R> ResultVo<R> failure(String code, String message) {
        return failure(null, code, message);
    }

    public static <R> ResultVo<R> failure(R data, String code, String message) {
        return new ResultVo(data, false, code, message);
    }

    public static <R> ResultVo<R> failureMsg(String message) {
        return failure(UNKNOWN_FAIL_CODE, message);
    }

}

Service 层

EmployeeService.java

package cn.com.easyExcel.service;

import org.springframework.web.multipart.MultipartFile;

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

public interface EmployeeService {

    void initData();

    void importExcel(MultipartFile file) throws IOException;

    void exportExcel(HttpServletResponse response) throws IOException;
}

EmployeeServiceImpl.java 实现类

package cn.com.easyExcel.service.impl;

import cn.com.easyExcel.excel.listener.ExportListener;
import cn.com.easyExcel.excel.listener.ImportListener;
import cn.com.easyExcel.mapper.EmployeeMapper;
import cn.com.easyExcel.pojo.EmployeeExporter;
import cn.com.easyExcel.service.EmployeeService;
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;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class EmployeeServiceImpl implements EmployeeService {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Override
    public void initData() {
        long beforeTime = System.currentTimeMillis();
        List<EmployeeExporter> employees = new ArrayList<EmployeeExporter>();
        for (int i = 0; i < 5000; i++) {
            EmployeeExporter employee = new EmployeeExporter();
            employee.setUserName(getRandomName());
            employee.setGender(getRandomGender());
            employee.setAge(getRandomAge());
            employee.setMaritalStatus(getRandomGender());
            employee.setEducation(getRandomEducation());
            employee.setMobile("18866998888");
            employee.setDepartmentName(getRandomDP());
            employee.setNationalArea("中国");
            employee.setCity("深圳");
            employees.add(employee);
            if(employees.size() % 1000 == 0){
                employeeMapper.batchInsert(employees);
                employees.clear();
            }
        }
        long afterTime = System.currentTimeMillis();
        log.info("耗时:{}", afterTime - beforeTime);
    }

    @Override
    public void importExcel(MultipartFile file) throws IOException {
        long beforeTime = System.currentTimeMillis();
        EasyExcel.read(file.getInputStream(),
                EmployeeExporter.class,
                new ImportListener(employeeMapper)).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<EmployeeExporter> queryWrapper = new QueryWrapper();
        queryWrapper.gt("age", 20);
        queryWrapper.between("education", "0", "3");

        new ExportListener<>(employeeMapper).
                exportExcel(response, "员工信息", EmployeeExporter.class,
                        queryWrapper);


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

    /**
     * 随机取名字
     * @return
     */
    public String getRandomName(){
        String[] doc = {"朝歌晚酒", "都怪时光太动听", "笑我孤陋", "水墨青花","时光清浅", "草帽撸夫", "江山如画",
        "热度不够", "盏茶浅抿", "把酒临风", "且听风吟", "梦忆笙歌", "倾城月下", "清风墨竹", "自愈心暖", "几许轻唱",
        "平凡之路", "半夏倾城", "南栀倾寒", "孤君独战", "温酒杯暖", "眉目亦如画", "旧雪烹茶", "律断华章", "清酒暖风",
        "清羽墨安", "一夕夙愿", "南顾春衫", "和云相伴", "夕颜若雪", "时城旧巷", "梦屿千寻"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    /**
     * 性别随机
     * @return
     */
    public String getRandomGender(){
        String[] doc = {"0", "1"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    /**
     * 年龄随机
     * @return
     */
    public int getRandomAge(){
        int[] doc = {16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    public String getRandomEducation(){
        String[] doc = {"0", "1", "2", "3"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    public String getRandomDP(){
        String[] doc = {"行政部", "财务部", "技术部", "市场部", "公关部"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }
}

easyExcel 导入导出监听器和转换器（重中之重）

导入监听器

ImportListener.java

package cn.com.easyExcel.excel.listener;

import cn.com.easyExcel.mapper.BaseDaoMapper;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.annotation.Async;

import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class ImportListener<T> implements ReadListener<T> {

    private final BaseDaoMapper baseDaoMapper;

    /**
     * 每隔100条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private final AtomicInteger count = new AtomicInteger(0);

    public ImportListener(BaseDaoMapper baseDaoMapper) {
        this.baseDaoMapper = baseDaoMapper;
    }

    @Override
    public void invoke(T entity, AnalysisContext analysisContext) {
        count.addAndGet(1);

        log.info("解析到一条数据:{}", JSON.toJSONString(entity));
        cachedDataList.add(entity);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            batchInsert();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        batchInsert();
        log.info("所有数据解析完成！");
    }

    @Async
    public void batchInsert() {
        log.info("{}条数据，开始存储数据库!", count.get());
        baseDaoMapper.batchInsert(cachedDataList);
        log.info("存储数据库成功!");
    }
}

导出监听器

ExportListener.java。查询数据这段逻辑可以优化，如果每次都要 selectCount 一下，遇到数据量非常大，这里就需要耗费很多时间，具体优化细节请移步 EasyExcel3.0.5 加快大数据查询速度，查询性能优化

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 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 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 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"));
        //发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据
        response.setHeader(CACHE_CONTROL, NO_STORE);
        response.addHeader(CACHE_CONTROL, MAX_AGE);
        return response.getOutputStream();
    }

}

转换器

EducationConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

import java.util.HashMap;
import java.util.Map;

public class EducationConverter implements Converter<String> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里是读的时候会调用
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            Map<String, String> edMap = getEducation();
            for (Map.Entry<String, String> entry : edMap.entrySet()) {
                if (readCellValue.equals(entry.getValue())) {
                    readCellValue = entry.getKey();
                }
            }
        }
        return readCellValue;
    }

    /**
     * 这里是写的时候会调用
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = getEducation().get(cellValue);
        }
        return new WriteCellData<>(cellValue);
    }

    public Map<String, String> getEducation(){
        Map<String, String> edMap = new HashMap<>();
        edMap.put("0", "大专");
        edMap.put("1", "本科");
        edMap.put("2", "硕士");
        edMap.put("3", "研究生");
        return edMap;
    }
}

GenderConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

public class GenderConverter implements Converter<String> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里是读的时候会调用
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            readCellValue = ("男").equals(readCellValue) ? "1":"0";
        }
        return readCellValue;
    }

    /**
     * 这里是写的时候会调用
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = ("1").equals(context.getValue()) ? "男":"女";
        }
        return new WriteCellData<>(cellValue);
    }
}

MaritalStatusConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

public class MaritalStatusConverter implements Converter<String> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里是读的时候会调用
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            readCellValue = ("已婚").equals(readCellValue) ? "1":"0";
        }
        return readCellValue;
    }

    /**
     * 这里是写的时候会调用
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = ("1").equals(context.getValue()) ? "已婚":"未婚";
        }
        return new WriteCellData<>(cellValue);
    }
}

Mapper 层

BaseDaoMapper.java

package cn.com.easyExcel.mapper;

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

import java.util.List;

public interface BaseDaoMapper<T> extends BaseMapper<T> {
    void batchInsert(List<T> list);
}

EmployeeMapper.java

package cn.com.easyExcel.mapper;

import cn.com.easyExcel.pojo.EmployeeExporter;
import java.util.List;

public interface EmployeeMapper extends BaseDaoMapper<EmployeeExporter> {

    /**
     * 批量插入
     * @param employees 员工表
     */
    void batchInsert(List<EmployeeExporter> employees);
}

mapper.xml

EmployeeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.easyExcel.mapper.EmployeeMapper">

    <insert id="batchInsert">
        INSERT INTO `employee` (
            `user_name`,
            `gender`,
            `age`,
            `birthday`,
            `marital_status`,
            `education`,
            `blood_type`,
            `mobile`,
            `department_name`,
            `national_area`,
            `province`,
            `city`,
            `id_card_number`,
            `personal_mail_box`
        )
        VALUES
        <foreach collection="list" item="employee" separator=",">
            (
                #{employee.userName},
                #{employee.gender},
                #{employee.age},
                #{employee.birthday},
                #{employee.maritalStatus},
                #{employee.education},
                #{employee.bloodType},
                #{employee.mobile},
                #{employee.departmentName},
                #{employee.nationalArea},
                #{employee.province},
                #{employee.city},
                #{employee.idCardNumber},
                #{employee.personalMailBox}
            )
        </foreach>
    </insert>

</mapper>

EasyExcel3.0.5 解决大数据导入导出，防止OOM
EasyExcel3.0.5 解决大数据导入导出，防止OOM
好了，分享结束。希望对各位有帮助。

版权声明：程序员胖胖胖虎阿 发表于 2022年10月24日 上午2:00。
转载请注明：EasyExcel3.0.5 解决大数据导入导出，防止OOM | 胖虎的工具箱-编程导航

相关文章

idea的2019 激活码(windows/mac idea2022.1破解码成功激活)
程序员胖胖胖虎阿
154
MySql基础知识总结（SQL优化篇）
程序员胖胖胖虎阿
251
windows/mac idea最新永久免费激活码(IntelliJ IDEA2019破解版绝对有效)
程序员胖胖胖虎阿
229
IntelliJ IDEA永久永久激活码(IDEA2019注册码,永久有效)
程序员胖胖胖虎阿
197
在 SQL 中计算总行数的百分比
程序员胖胖胖虎阿
236
idea的专业版激活码(windows/mac idea2022破解图文教程,激活至2099年)
程序员胖胖胖虎阿
131

暂无评论

暂无评论...
致力于称为最好的程序员导航网站

友链申请 免责声明 关于我们

Copyright © 2022  胖虎的工具箱-编程导航   陇ICP备2022001249号