SpringBoot项目中使用EasyExcel快速导入和导出数据

1. EasyExcel简介

• 在Java的生态中，解析Excel、生成Excel比较有名的框架是Apache POI，但是Apache POI存在一个严重的问题：内存消耗较大
• EasyExcel的诞生正是为了解决Apache POI内存消耗大的问题
• EasyExcel是阿里巴巴开源的一个框架，它在Apache POI的基础上重写了对Excel的解析，大大地降低了内存的消耗，收到了广大开发者的喜爱

以下是EasyExcel与Apache POI的对比（摘录自官网）

easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出

官网：https://easyexcel.opensource.alibaba.com

GitHub地址：https://github.com/alibaba/easyexcel（开源不易，记得点个小星星）

Gitee地址：https://gitee.com/easyexcel/easyexcel（开源不易，记得点个小星星）

• 在3.0.0版本之后，EasyExcel支持解析CSV文件
• 读的时候会自动判断文件的类型，和读Excel一样，但在写的时候需要指定excelType为CSV

2. 快速开始

以下内容大部分来自于官网，我只是知识的搬运工

如果想了解更多与EasyExcel有关的知识，可以前往EasyExcel的官网

本次演示使用的jdk版本为1.8，数据库使用的是MySQL 8.0.32

项目的整体结构如下

2.1 准备工作

请确保你当前的项目为SpringBoot项目，同时引入了SpringWeb依赖、数据库连接驱动、MyBatis依赖（也可以使用MyBatisPlus)、lombok依赖、fastjson2依赖、PageHelper依赖

SpringWeb依赖

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

数据库连接驱动

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

MyBatis依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.3.1</version>
</dependency>

lombok依赖

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

fastjson2依赖

<dependency>
    <groupId>com.alibaba.fastjson2</groupId>
    <artifactId>fastjson2</artifactId>
    <version>2.0.48</version>
</dependency>

PageHelper依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

2.2 导入EasyExcel依赖

在项目的 pom.xml 文件中添加以下依赖

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

2.3 根据Excel文件的表头编写实体类

本次演示使用的Excel文件比较简单，只有三个表头（工资以分为单位）

姓名	生日	工资

注意事项：

• User实体类 @ExcelProperty 注解指定的的属性要和Excel文件的表头对应，否则EasyExcel将会跳过这一列数据
• 用 String 去接日期才能格式化

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@EqualsAndHashCode
public class User {
    @ExcelProperty("姓名")
    private String name;

    /**
     * 这里用 String类型 去接日期才能格式化
     */
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("生日")
    private String birthday;

    @ExcelProperty(value = "工资")
    private Long salary;

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", birthdate=" + birthday +
                ", salary=" + salary +
                '}';
    }
}

2.4 创建数据库和表

先创建一个名为 easy_excel 的数据库，再运行以下 sql 文件

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `birthday` date NOT NULL COMMENT '生日',
  `salary` bigint NOT NULL COMMENT '工资',
  INDEX `user_id_index`(`id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2.5 修改配置、编写Mapper

注意事项：

• 如果使用的是MyBatis，尽量别直接调用多次insert
• 可以自己写一个mapper，新增一个batchInsert方法，一次性插入所有数据
• 记得在application.yml文件中开启MySQL的批量插入功能

application.yml(记得修改与数据库相关的信息)

server:
  port: 16256
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/easy_excel?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:mapper/*.xml

UserMapper.java

• 记得在 List<User> users 前加上 @Param("users") 注解
• 因为当传入参数为list集合的时候，mybatis默认会将list封装为一个map，以"list"作为key

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.pojo.User;

import java.util.List;

@Mapper
public interface UserMapper {
    void batchInsert(@Param("users") List<User> users);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.UserMapper">
    <!-- 批量插入用户 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO user (name, birthday, salary)
        VALUES
        <foreach collection="users" item="user" separator=",">
            (#{user.name}, #{user.birthday}, #{user.salary})
        </foreach>
    </insert>

    <!-- 查询所有用户 -->
    <select id="selectAll" resultType="org.example.pojo.User">
        select name, birthday, salary from user
    </select>
</mapper>

3. 生成测试数据

网站：https://www.mockaroo.com

如果edge浏览器下载失败，就用谷歌浏览器（免费版每次最多只能生成1000条数据，可多次生成）

4. 读取Excel中的数据并将数据批量插入到数据库中

4.1 编写测试类

将Excel文件或csv文件复制到 resources 目录下，在SpringBootTest环境下编写测试类

ReadAndSaveTest.java

将 users.xlsx 改成你的文件名

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.example.mapper.UserMapper;
import org.example.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.net.URL;

@Slf4j
@SpringBootTest
public class ReadAndSaveTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 1. 创建excel对应的实体对象
     * 2. 读取数据
     * 3. 将读取到的数据批量插入到数据库中
     * 注意事项：PageReadListener不能被spring管理，每次读取excel都要new一个新的实例
     */
    @Test
    public void readAndSave() {
        URL resource = this.getClass().getClassLoader().getResource("users.xlsx");
        String fileName = null;
        if (resource != null) {
            fileName = resource.getFile();
        }
        if (fileName == null) {
            log.error("未找到文件，请检查文件名是否有误");
            return;
        }

        log.info("开始读取文件：{}", fileName);

        // EasyExcel.read方法，第一个参数填入file对象，第二个参数传入实体类的字节码
        // 默认每次读取100条数据（不计表头）
        // 如果需要指定每次读取的行数，可以在PageReadListener的构造函数的第二个参数中指定
        EasyExcel.read(fileName, User.class, new PageReadListener<User>(users -> {
            for (User user : users) {
                log.info("读到一条数据{}", JSON.toJSONString(user));
            }

            // 将读取到的数据批量插入到数据库中
            userMapper.batchInsert(users);
        })).sheet().doRead();
    }
}

4.2 测试结果

测试结果如下（总耗时1秒333毫秒）

注释掉遍历语句和输出语句后再次测试（总耗时962毫秒）

EasyExcel的性能还是不错的

4.3 编写简单的静态页面

修改 resources/static 中的 index.html 文件

前端发请求时headers记得设置正确的 Content-Type

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>EasyExcel</title>
</head>

<body>
<div class="app">
    <input type="file" id="fileInput" accept=".xlsx, .xls, .csv">
    <button onclick="upload()">上传</button>
    <button onclick="download()">导出</button>
</div>
</body>

<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script>
    const upload = () => {
        // 获取文件输入元素
        const fileInput = document.getElementById('fileInput')
        // 获取选中的文件
        const file = fileInput.files[0]

        if (!file) {
            alert('请选择一个文件')
            return
        }

        // 创建 FormData 对象
        const formData = new FormData()
        // 将文件添加到 FormData 对象
        formData.append('file', file)

        // 发送 POST 请求到后端
        axios.post('http://localhost:16256/upload', formData, {
            headers: {
                'Content-Type': 'multipart/form-data' // 设置正确的 Content-Type
            }
        }).then(response => {
            alert('文件上传成功')
            console.log('文件上传成功:', response.data)
        }).catch(error => {
            console.error('文件上传失败:', error)
        });
    }

    const download = () => {
        const url = 'http://localhost:16256/download'

        axios.get(url, {
            responseType: 'blob', // 设置响应类型为blob(二进制大对象）
        }).then(response => {
            // 从Content-Disposition头部中获取文件名
            const contentDisposition = response.headers['content-disposition']
            const matches = /filename\*=(utf-8'')(.*)/.exec(contentDisposition)
            let filename = 'downloaded.xlsx'
            if (matches != null && matches[2]) {
                // 解码RFC 5987编码的文件名
                filename = decodeURIComponent(matches[2].replace(/\+/g, ' '))
            } else {
                // 如果没有filename*，尝试使用filename
                const filenameMatch = /filename="(.*)"/.exec(contentDisposition);
                if (filenameMatch != null && filenameMatch[1]) {
                    filename = filenameMatch[1]
                }
            }

            // 创建一个a标签用于下载
            const a = document.createElement('a')
            // 创建一个URL对象，指向下载的文件
            const url = window.URL.createObjectURL(new Blob([response.data]))
            a.href = url
            a.download = filename // 设置文件名
            document.body.appendChild(a)
            a.click()
            document.body.removeChild(a)
            window.URL.revokeObjectURL(url)
        }).catch(error => {
            console.error('下载文件时出错:', error)
        })
    }

</script>

</html>

4.4 编写Controller（让用户能在浏览器上传Excel文件，实现数据导入）

方便起见，本次演示的业务代码统一在Controller层编写（(￣３￣) (￣３￣) (￣３￣) ）

UserController.java

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.fastjson2.JSON;
import org.example.mapper.UserMapper;
import org.example.pojo.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@RestController("/")
public class UserController {
    private static final Logger log = LoggerFactory.getLogger(UserController.class);
    private final UserMapper userMapper;

    public UserController(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    @PostMapping("upload")
    public void upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new PageReadListener<User>(users -> {
            for (User user : users) {
                log.info("读到一条数据{}", JSON.toJSONString(user));
            }

            // 将读取到的数据批量插入到数据库中
            userMapper.batchInsert(users);
        })).sheet().doRead();
    }
    
}

4.5 测试

启动SpringBoot项目，在浏览器中访问

http://localhost:16256

整体页面如下（虽然很丑，但功能还是全的）

点击选择文件按钮（支持的文件后缀名有.xlsx, .xls, .csv），选好文件后点击上传按钮，后台就会解析Excel文件中的数据，并将数据导入到数据库中

4.6 补充：EasyExcel.read方法

EasyExcel.read方法读取Excel文件时可以接收三种类型的参数

1. FIle对象
2. 文件路径
3. 文件输入流（InputStream对象）

5. 将数据库中的数据导出到Excel文件

本文演示的是写入一个sheet的情况，如果你想写入到多个sheet，可以在EasyExcel的官网上查找相关信息

sheet示意如下

5.1 编写测试类

注意事项：

• 生成的 Excel 文件中表头的顺序会与实体类中字段的顺序保持一致
• 在 String fileName = "F:\\EasyExcel\\users.xlsx"; 中更改文件的保存位置（如果Windows 安全中心开启了文件保护，确保一般用户对文件的保存目录有写的权限）

WriteTest.java

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.example.mapper.UserMapper;
import org.example.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@Slf4j
@SpringBootTest
public class WriteTest {
    @Autowired
    private UserMapper userMapper;

    /**
     * 重复多次写入
     */
    @Test
    public void repeatedWrite() {
        // 写入到同一个sheet
        String fileName = "F:\\EasyExcel\\users.xlsx";
        // 指定实体类（提供实体类的字节码文件）
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build()) {
            // 如果只有一个sheet，只需要创建一次WriteSheet
            WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();

            // 执行两次写入操作，每次从数据库中查询500条数据，实际使用时根据数据库分页的总页数进行调整
            for (int i = 0; i < 2; i++) {
                // 设置分页参数
                PageHelper.startPage((i + 1), 500 * (i + 1));
                // 查询数据
                List<User> userList = userMapper.selectAll();
                // 使用 PageInfo 包装查询结果
                PageInfo<User> pageInfo = new PageInfo<>(userList);

                // 输出分页信息
                log.info("总记录数：{}", pageInfo.getTotal());
                log.info("总页数：{}", pageInfo.getPages());

                // 执行写入操作
                excelWriter.write(userList, writeSheet);
            }
        }
    }

}

5.2 编写Controller(让用户能在浏览器下载Excel文件，实现数据导出)

在实际开发中，一般是用户点击导出数据按钮后，下载一个Excel文件

方便起见，本次演示的业务代码统一在Controller层编写（(￣３￣) (￣３￣) (￣３￣) ）

UserController.java

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.fastjson2.JSON;
import org.example.mapper.UserMapper;
import org.example.pojo.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@RestController("/")
public class UserController {
    private static final Logger log = LoggerFactory.getLogger(UserController.class);
    private final UserMapper userMapper;

    public UserController(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    @PostMapping("upload")
    public void upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new PageReadListener<User>(users -> {
            for (User user : users) {
                log.info("读到一条数据{}", JSON.toJSONString(user));
            }

            // 将读取到的数据批量插入到数据库中
            userMapper.batchInsert(users);
        })).sheet().doRead();
    }

    @GetMapping("/download")
    public void download(HttpServletResponse httpServletResponse) throws IOException {
        // 使用swagger可能会导致各种问题，请直接用浏览器或者用postman
        httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        httpServletResponse.setCharacterEncoding("utf-8");

        // 设置URLEncoder.encode可以防止中文乱码，和easyexcel没有关系
        String fileName = URLEncoder.encode("用户数据", "UTF-8").replaceAll("\\+", "%20");

        List<User> userList = userMapper.selectAll();

        httpServletResponse.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(httpServletResponse.getOutputStream(), User.class)
                .sheet("sheet")
                .doWrite(userList);
    }
}

补充：

• 在URL编码中，空格通常被表示为加号（+）
• 在某些情况下，需要严格遵循RFC 3986标准，RFC 3986标准规定空格应使用"%20"来表示
• 替换操作确保了空格始终以"%20"的形式出现在URL中，以符合特定协议、接口或服务器的规范要求，避免出现解析错误或不兼容性问题

5.3 测试

在浏览器访问以下地址，点击导出按钮，即可将数据库的数据导出到Excel文件

http://localhost:16256