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

demo 代码:

github:ishuaige/easyExcelDemo: easyExcel 的 Demo (github.com)

gitee:easyExcelDemo: easyExcel 的小 demo (gitee.com)

一.准备工作

  1. 导入数据库文件
    1. 我们做一个类似积分系统,数据库包含用户名,上月积分和当前积分
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

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
`oldScore` int(0) NOT NULL COMMENT '旧分数',
`currentScore` int(0) NULL DEFAULT NULL COMMENT '当前分数',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '覃瑾瑜', 2, 815562);
INSERT INTO `user` VALUES (2, '高炎彬', 8907860, 42442602);
INSERT INTO `user` VALUES (3, '石嘉熙', 665342, 86753);
INSERT INTO `user` VALUES (4, '赵越彬', 426, 999);
INSERT INTO `user` VALUES (5, '廖智渊', 733840, 6529367);
INSERT INTO `user` VALUES (6, '傅明辉', 5, 57647299);
INSERT INTO `user` VALUES (7, '龚越彬', 43, 78204464);
INSERT INTO `user` VALUES (8, '陆烨霖', 1681509, 3);
INSERT INTO `user` VALUES (9, '黎晋鹏', 2, 134809752);
INSERT INTO `user` VALUES (10, '雷昊焱', 20, 3489470);
INSERT INTO `user` VALUES (11, '唐子骞', 62, 20720);
INSERT INTO `user` VALUES (12, '杜健雄', 2594, 3);
INSERT INTO `user` VALUES (13, '刘烨霖', 25, 120);
INSERT INTO `user` VALUES (14, '吕远航', 1, 59870);

SET FOREIGN_KEY_CHECKS = 1;

  1. 创建一个 springboot 工程
    1. 依赖 springboot 2.7.7
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

<dependencies>
<!--fastjson序列化-->
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.23</version>
</dependency>
<!--easyexcel操作excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.0</version>
</dependency>
<!-- 接口文档 -->
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</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>
</dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
spring:
application:
name: easyExcelDemo
# Mysql config
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/easyexcel
username: root
password: 123456
mvc:
pathmatch:
matching-strategy: ANT_PATH_MATCHER
mybatis-plus:
configuration:
map-underscore-to-camel-case: false
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
logic-delete-field: isDelete
logic-delete-value: 1
logic-not-delete-value: 0
  1. 创建好操作数据的 model、mapper、service
    1. 使用 mybatis X 插件创建

这里不赘述以上技术的玩法哈

二.简单的写

主要 api: EasyExcel.write()

  1. 导入 easyExcel 和 fastjson 的依赖,上面的依赖中已有
  2. 定义 excel 的表头
    1. 我们在这里自己算出增长积分
    2. @ExcelProperty(“姓名”) 声明表头,在代码中变量不好取中文,就可以使用注解声明
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.niuma.easyexceldemo.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
* @author niumazlb
* @create 2023-01-19 20:35
*/
@Data
public class ExcelUserData {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("上月积分")
private Integer oldScore;
@ExcelProperty("总积分")
private Integer currentScore;
@ExcelProperty("增长积分")
private Integer growScore;

}
  1. 编写测试类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@SpringBootTest
public class easyExcelTest {
@Resource
UserService userService;

public static final String PATH = "D:\\code\\IDEA\\easyExcelDemo\\";
public static final String fileName = "testEasyExcelWrite.xlsx";
public static final String pathName = PATH + fileName;

@Test
public void testEasyExcelWrite() {
List<User> userList = userService.list();
List<ExcelUserData> excelDataList = userList.stream().map(user -> {
ExcelUserData excelData = new ExcelUserData();
excelData.setName(user.getName());
excelData.setOldScore(user.getOldScore());
excelData.setCurrentScore(user.getCurrentScore());
excelData.setGrowScore(user.getCurrentScore() - user.getOldScore());
return excelData;
}).sorted(Comparator.comparingInt(ExcelUserData::getGrowScore)).collect(Collectors.toList());
//参数声明路径名和表头
EasyExcel.write(pathName,ExcelData.class).sheet().doWrite(excelDataList);
}
}
  • 其实就是调用EasyExcel.write()这个 api,参数是路径名,表头
    • 链式调用 sheet(),可以声明页信息,如页号,页名
    • dowrite(),执行写操作,参数填入数据集
  1. 校验结果
    1. 在对应的路径下就可以看到导出的 excel 表

image-20230119222018025

三.简单的读

在导入依赖和定义了表头后开始读操作的编写测试

大概意思:服务端要接收到 excel,我们需要知道

  • excel 文件:通过网络或本地路径(对谁做?)

  • 表头字段:转换成对象(什么样子?面向对象)

  • 监听器:定义读取数据后的处理方式(做什么?)

  1. 定义 Listeners 监听器,注意这里处理数据的方法,防止 OOM
    1. 实现 ReadListener<DataType>接口,泛型自然就是表头的字段
    2. 实现 2 个主要方法
      1. invoke(ExcelUserData excelUserData, AnalysisContext analysisContext):每读到一条数据都会调用这个函数,可以在这里对数据的预处理,比如将读到的 ExcelUserData 转换成数据库的 User
      2. doAfterAllAnalysed(AnalysisContext analysisContext):所有数据解析完成了 都会来调用 做收尾工作,确保最后遗留的数据也持久化(存储到数据库)
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
package com.niuma.easyexceldemo.excel.listeners;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson2.JSON;
import com.niuma.easyexceldemo.excel.model.ExcelUserData;
import com.niuma.easyexceldemo.service.UserService;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
* ExcelModelListener 不能被spring管理,要每次读取 excel 都要 new,然后里面用到 spring 可以构造方法传进去
*
* @author niumazlb
* @create 2023-01-19 20:59
*/
@Slf4j
public class UserDataListener implements ReadListener<ExcelUserData> {


/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理 list ,方便内存回收,避免 OOM
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据,在 invoke 函数中存储每次读到的数据,这里的泛型虽业务变化而变化,存储的可以是excel表数据处理后的数据
* 假如我要存入数据库中就需要将 ExcelUserData 转换成 User 那么这里的泛型就是User,在 invoke 中处理后添加
*/
private List<ExcelUserData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

/**
* 这个是一个DAO,当然有业务逻辑这个也可以是一个service。可以用来解析数据后操作数据库
*/
private UserService userService;

/**
* 每读到一条数据都会调用这个函数,可以在这里对数据的预处理
*
* @param excelUserData
* @param analysisContext
*/
@Override
public void invoke(ExcelUserData excelUserData, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(excelUserData));
cachedDataList.add(excelUserData);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
log.info("已达到BATCH_COUNT,共{}条数据", cachedDataList.size());

// 调用储存数据函数
saveData();

// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}

/**
* 所有数据解析完成了 都会来调用 做收尾工作,确保最后遗留的数据也持久化(存储到数据库)
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();

log.info("所有数据解析完成!");
}

/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());

// TODO 数据存储,使用批处理操作防止多次连接数据库,例如 userService.saveBatch();

log.info("存储数据库成功!");
}
}
  1. 测试
1
2
3
4
5
6
7
8
@Test
public void testRead(){
EasyExcel.read(
pathName,
ExcelUserData.class,
new UserDataListener()
).sheet().doRead();
}
  • 主要调用 api:EasyExcel.read() 参数就是前面我们需要知道的三个东西。链式调用 sheet 同写
  1. 检验结果

image-20230119223655282

四.web 接口调用

① 上传 excel 并解析

更多可参考:spring boot 中使用 easyExcel 实现 Excel 文件读取_夜半子时的博客

  • 解析逻辑在 Listener 里,这里演示只做日志打印
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

/**
* @author niumazlb
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;

/**
* 上传 excel 并解析,解析逻辑在 Listener 里
*
* @param file
* @return
*/
@PostMapping("/upload")
public BaseResponse<Boolean> uploadDataByExcel(@RequestBody MultipartFile file) {
try {
//判断文件类型是否正确
String originalFilename = file.getOriginalFilename();
String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
if (!".xls".equalsIgnoreCase(fileType) && !".xlsx".equalsIgnoreCase(fileType)) {
throw new BusinessException(ErrorCode.PARAMS_ERROR, "文件格式错误");
}

EasyExcel.read(file.getInputStream(), ExcelUserData.class, new UserDataListener()).sheet().doRead();
return ResultUtils.success(true);
} catch (IOException e) {
throw new BusinessException(ErrorCode.SYSTEM_ERROR);
}
}

}

② 下载文件

  • 用 swagger 不好使,直接在浏览器输入地址就好了
  • 这里应该将大段逻辑放到 service 中,只不过作演示方便
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
/**
* 下载文件,通过 easyExcel 写 excel 再传输到前端
*
* @param response
* @throws IOException
*/
@GetMapping("/download")
public void download(HttpServletResponse response) {
try {
// 这里注意 有同学反应使用 swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<User> userList = userService.list();
List<ExcelUserData> excelDataList = userList.stream().map(user -> {
ExcelUserData excelData = new ExcelUserData();
excelData.setName(user.getName());
excelData.setOldScore(user.getOldScore());
excelData.setCurrentScore(user.getCurrentScore());
excelData.setGrowScore(user.getCurrentScore() - user.getOldScore());
return excelData;
}).sorted(Comparator.comparingInt(ExcelUserData::getGrowScore)).collect(Collectors.toList());
EasyExcel.write(response.getOutputStream(), ExcelUserData.class).sheet().doWrite(excelDataList);
} catch (IOException e) {
e.printStackTrace();
}
}