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 去接日期才能格式化

importcom.alibaba.excel.annotation.ExcelProperty;importcom.alibaba.excel.annotation.format.DateTimeFormat;importlombok.EqualsAndHashCode;importlombok.Getter;importlombok.Setter;@Getter@Setter@EqualsAndHashCodepublicclassUser{@ExcelProperty("姓名")privateString name;/**
     * 这里用 String类型 去接日期才能格式化
     */@DateTimeFormat("yyyy-MM-dd")@ExcelProperty("生日")privateString birthday;@ExcelProperty(value ="工资")privateLong salary;@OverridepublicStringtoString(){return"User{"+"name='"+ name +'\''+", birthdate="+ birthday +", salary="+ salary +'}';}}

2.4 创建数据库和表

先创建一个名为

easy_excel

的数据库，再运行以下 sql 文件

DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(32)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'姓名',`birthday`dateNOTNULLCOMMENT'生日',`salary`bigintNOTNULLCOMMENT'工资',INDEX`user_id_index`(`id`ASC)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2.5 修改配置、编写Mapper

注意事项：

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

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

server:port:16256spring:datasource:url: jdbc:mysql://localhost:3306/easy_excel?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&&allowPublicKeyRetrieval=true&rewriteBatchedStatements=trueusername: root
    password:123456driver-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

importorg.apache.ibatis.annotations.Mapper;importorg.apache.ibatis.annotations.Param;importorg.example.pojo.User;importjava.util.List;@MapperpublicinterfaceUserMapper{voidbatchInsert(@Param("users")List<User> users);}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="org.example.mapper.UserMapper"><!-- 批量插入用户 --><insertid="batchInsert"parameterType="java.util.List">
        INSERT INTO user (name, birthday, salary)
        VALUES
        <foreachcollection="users"item="user"separator=",">
            (#{user.name}, #{user.birthday}, #{user.salary})
        </foreach></insert><!-- 查询所有用户 --><selectid="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

改成你的文件名

importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.read.listener.PageReadListener;importcom.alibaba.fastjson2.JSON;importlombok.extern.slf4j.Slf4j;importorg.example.mapper.UserMapper;importorg.example.pojo.User;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.net.URL;@Slf4j@SpringBootTestpublicclassReadAndSaveTest{@AutowiredprivateUserMapper userMapper;/**
     * 1. 创建excel对应的实体对象
     * 2. 读取数据
     * 3. 将读取到的数据批量插入到数据库中
     * 注意事项：PageReadListener不能被spring管理，每次读取excel都要new一个新的实例
     */@TestpublicvoidreadAndSave(){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,newPageReadListener<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

<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>EasyExcel</title></head><body><divclass="app"><inputtype="file"id="fileInput"accept=".xlsx, .xls, .csv"><buttononclick="upload()">上传</button><buttononclick="download()">导出</button></div></body><scriptsrc="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script><script>constupload=()=>{// 获取文件输入元素const fileInput = document.getElementById('fileInput')// 获取选中的文件const file = fileInput.files[0]if(!file){alert('请选择一个文件')return}// 创建 FormData 对象const formData =newFormData()// 将文件添加到 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)});}constdownload=()=>{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*，尝试使用filenameconst filenameMatch =/filename="(.*)"/.exec(contentDisposition);if(filenameMatch !=null&& filenameMatch[1]){
                    filename = filenameMatch[1]}}// 创建一个a标签用于下载const a = document.createElement('a')// 创建一个URL对象，指向下载的文件const url = window.URL.createObjectURL(newBlob([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

importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.read.listener.PageReadListener;importcom.alibaba.fastjson2.JSON;importorg.example.mapper.UserMapper;importorg.example.pojo.User;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.PostMapping;importorg.springframework.web.bind.annotation.RestController;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.net.URLEncoder;importjava.util.List;@RestController("/")publicclassUserController{privatestaticfinalLogger log =LoggerFactory.getLogger(UserController.class);privatefinalUserMapper userMapper;publicUserController(UserMapper userMapper){this.userMapper = userMapper;}@PostMapping("upload")publicvoidupload(MultipartFile file)throwsIOException{EasyExcel.read(file.getInputStream(),User.class,newPageReadListener<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文件时可以接收三种类型的参数

FIle对象
文件路径
文件输入流（InputStream对象）

在这里插入图片描述

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

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

sheet示意如下

在这里插入图片描述

5.1 编写测试类

注意事项：

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

WriteTest.java

importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.ExcelWriter;importcom.alibaba.excel.write.metadata.WriteSheet;importcom.github.pagehelper.PageHelper;importcom.github.pagehelper.PageInfo;importlombok.extern.slf4j.Slf4j;importorg.example.mapper.UserMapper;importorg.example.pojo.User;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.util.List;@Slf4j@SpringBootTestpublicclassWriteTest{@AutowiredprivateUserMapper userMapper;/**
     * 重复多次写入
     */@TestpublicvoidrepeatedWrite(){// 写入到同一个sheetString fileName ="F:\\EasyExcel\\users.xlsx";// 指定实体类（提供实体类的字节码文件）try(ExcelWriter excelWriter =EasyExcel.write(fileName,User.class).build()){// 如果只有一个sheet，只需要创建一次WriteSheetWriteSheet 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 =newPageInfo<>(userList);// 输出分页信息
                log.info("总记录数：{}", pageInfo.getTotal());
                log.info("总页数：{}", pageInfo.getPages());// 执行写入操作
                excelWriter.write(userList, writeSheet);}}}}

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

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

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

UserController.java

importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.read.listener.PageReadListener;importcom.alibaba.fastjson2.JSON;importorg.example.mapper.UserMapper;importorg.example.pojo.User;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.PostMapping;importorg.springframework.web.bind.annotation.RestController;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.net.URLEncoder;importjava.util.List;@RestController("/")publicclassUserController{privatestaticfinalLogger log =LoggerFactory.getLogger(UserController.class);privatefinalUserMapper userMapper;publicUserController(UserMapper userMapper){this.userMapper = userMapper;}@PostMapping("upload")publicvoidupload(MultipartFile file)throwsIOException{EasyExcel.read(file.getInputStream(),User.class,newPageReadListener<User>(users ->{for(User user : users){
                log.info("读到一条数据{}",JSON.toJSONString(user));}// 将读取到的数据批量插入到数据库中
            userMapper.batchInsert(users);})).sheet().doRead();}@GetMapping("/download")publicvoiddownload(HttpServletResponse httpServletResponse)throwsIOException{// 使用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

在这里插入图片描述

标签： java spring boot excel

本文转载自: https://blog.csdn.net/m0_62128476/article/details/137620042
版权归原作者 聂可以 所有，如有侵权，请联系我们删除。

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

文章目录

1. EasyExcel简介

2. 快速开始

2.1 准备工作

2.2 导入EasyExcel依赖

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

2.4 创建数据库和表

2.5 修改配置、编写Mapper

3. 生成测试数据

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

4.1 编写测试类

4.2 测试结果

4.3 编写简单的静态页面

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

4.5 测试

4.6 补充：EasyExcel.read方法

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

5.1 编写测试类

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

5.3 测试

发表评论

“SpringBoot项目中使用EasyExcel快速导入和导出数据”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航