初次邂逅 EasyExcel
前言
由于工作原因，有这种需求，就是把数据库中的数据导出成 Excel 表格，同时，也得支持人家用 Excel 表格导入数据到数据库。当前项目也是在用 EasyExcel，所以我不得不学啦！
以前学习的过程中，有听过 EasyExcel 这么一个东西，不过从来没用过，所以，正好借此机会学习，看看如何使用它来实现需求。
在学习 EasyExcel 的这段时间里，也了解到工作中这种导入导出的需求还是挺常见的，所以决定记录下来。
需求
用户点击导入按钮，就能够上传 Excel 文件，将 Excel 文件的数据导入到系统中。
用户勾选目标数据 id，点击导出按钮，就能将系统中的数据以 Excel 文件的格式下载到本地。
分析
导入，从用户的视角来看，就是导入 Excel 文件；从开发者的视角，或者说系统的视角来看，就是读取用户的 Excel 文件的数据到系统中（实际上是读取到计算机的内存中），最后将读取到的数据存储到数据库，EasyExcel 在导入的过程中进行了读操作。
导出，同理，用户的视角就是导出，开发者的视角就是把系统的数据写入到用户的计算机上，即写操作。
简而言之，涉及 IO 操作的，视角不同，说法不同（初学IO时就没搞清楚，为我后续的学习留下了大坑T_T！）。
当然我们也可以把导入说成写操作，毕竟数据是最终是存储在系统的数据库中的，即写到了系统的数据库里了。自己别搞混了就行。
准备
本 Demo 使用 Spring Boot 构建，配合 MyBaits Plus，以游戏数据的导入和导出作为需求；一些工具依赖如下：
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.72</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</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>
依赖
今天 EasyExcel 主菜，需要加其依赖才能食用~
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
配置
server:
port: 4790
spring:
application:
name: easyexcel-demo
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/easy_excel_demo?useUnicode=true&autoReconnect=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&characterEncoding=utf8
username: root
password: 123456
游戏实体类
咱们的游戏类就这些属性：id、游戏名称、价格、uuid，发售日期、创建时间、修改时间
/**
* @author god23bin
* @version 1.0
* @description 游戏
* @date 2022/10/21 16:51:02
*/
@Data
@TableName("t_game")
public class Game {
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private Double price;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date releaseDate;
@TableField(fill = FieldFill.INSERT)
private String uuid;
@TableField(fill = FieldFill.INSERT)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date gmtCreate;
@TableField(fill = FieldFill.INSERT_UPDATE)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date gmtModified;
}
模型
啥是模型？别慌，先假设需要导入的 Excel 表格长这样：
那么这个就是所谓的模型啦，不管是读还是写，都需要相对应的对象，所以一般会编写一个读对象的类和写对象的类，当然，如果读写的表头字段都是一模一样，直接一个类就可以了，导入导出都用这个类。
读对象
读对象-GameImportExcelModel：
/**
* @author god23bin
* @version 1.0
* @description Game 导入的 Excel 数据模型（读对象）
* @date 2022/10/21 17:18:50
*/
@Data
public class GameImportExcelModel {
private String name;
private Double price;
private Date releaseDate;
}
写对象
写对象-GameExportExcelModel：
/**
* @author god23bin
* @version 1.0
* @description Game 导出的 Excel 数据模型（写对象）
* @date 2022/10/21 17:18:50
*/
@Data
public class GameExportExcelModel {
@ExcelProperty("游戏ID")
private Long id;
@ExcelProperty("游戏名")
private String name;
@ExcelProperty("价格")
private Double price;
@ExcelProperty("发售日期")
private Date releaseDate;
}
实现
导入功能
用户点击导入按钮，就能够上传 Excel 文件，将 Excel 文件的数据导入到系统中。
前端实现一个上传文件的按钮，后端就接收这个文件，读取这个文件的数据，存储到数据库中。
开胃菜-后端
搭个整体的代码框架先！
持久层
GameMapper：
@Mapper
public interface GameMapper extends BaseMapper<Game> {
}
业务层
GameService：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:36:43
*/
public interface GameService {
/**
* 导入Excel数据到数据库
* @date 2022/11/8 14:38
* @param file Excel文件
* @return boolean
**/
boolean importExcel(MultipartFile file);
}
GameServiceImpl：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:40:08
*/
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {
@Resource
private GameMapper gameMapper;
/**
* 导入Excel数据到数据库
*
* @param file Excel文件
* @return boolean
* @date 2022/11/8 14:38
**/
@Override
public boolean importExcel(MultipartFile file) {
// 这里就需要用到“读监听器”了，需要我们自己实现
return null;
}
}
控制层
GameController：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:31:50
*/
@RestController
public class GameController {
@Resource
private GameService gameService;
@PostMapping("/excel/import/game")
public ResponseEntity<String> importExcel(@RequestPart("file") MultipartFile file) {
gameService.importExcel(file);
return new ResponseEntity<>("OK", HttpStatus.OK);
}
}
正餐-读数据需要用到的监听器
对于读取，有一个监听器需要我们实现，根据文档的说明，这个监听器是不可以让 Spring 来管理的。
有个很重要的点 DemoDataListener 不能被 spring管理，要每次读取 excel都要 new，然后里面用到 spring 可以构造方法传进去
所以我们也不需要加上 @Component 注解把这个类作为组件让 Spring 扫描。直接一个普通的类就行。
具体代码如下，需要知道的是：
- 需要继承 AnalysisEventListener 类，参数化的类型（泛型）为 GameImportExcelModel（读对象）
GameImportExcelListener：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/10/24 08:45:15
*/
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
/**
* 每隔100条存储到数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);
/**
* 每解析一行数据就会执行这个方法
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
Game game = new Game();
BeanUtil.copyProperties(data, game);
cachedDataList.add(game);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList.clear();
}
}
private void saveData() {
// 这里写存储到数据库的逻辑代码
}
/**
* 解析完之后会执行这个方法，如果有其他事情需要做，可以在这里加上代码来完成
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
}
但是！如果我们想要使用 Spring IOC 管理对象，比如 Dao、Mapper 这些对象，现在当前类是用不了 @Autowired 注解将这些对象注入的，那我们怎么获取它们？
方法就是：在该类中写一个构造方法，将这些被 Spring 管理的对象作为参数传入进来！
比如我这里需要用到 GameMapper 对象，那么就将它作构造方法的参数传进来。
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
// 省略其他代码
private GameMapper gameMapper;
public GameImportExcelListener(GameMapper gameMapper) {
this.gameMapper = gameMapper;
}
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
// ...
}
private void saveData() {
// ...
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// ...
}
}
完整的监听器代码
GameImportExcelListener：
package cn.god23bin.demo.excel.listener;
import cn.god23bin.demo.entity.Game;
import cn.god23bin.demo.excel.bean.GameImportExcelModel;
import cn.god23bin.demo.mapper.GameMapper;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* @author zwb
* @version 1.0
* @description
* @date 2022/10/24 08:45:15
*/
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
/**
* 每隔100条存储到数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);
private GameMapper gameMapper;
public GameImportExcelListener(GameMapper gameMapper) {
this.gameMapper = gameMapper;
}
/**
* 每解析一行数据就会执行这个方法
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
Game game = new Game();
BeanUtil.copyProperties(data, game);
cachedDataList.add(game);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList.clear();
}
}
private void saveData() {
// 这里写存储到数据库的逻辑代码
for (Game game : cachedDataList) {
gameMapper.insert(game);
}
}
/**
* 解析完之后会执行这个方法，如果有其他事情需要做，可以在这里加上代码来完成
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
}
完善业务层
- 使用
EasyExcel.read()方法构建一个 Excel reader builder，第一个参数是文件输入流，第二个参数是读对象，指定它这个class去读，第三个参数就是读监听器 - 接着链式调用
sheet()方法和doRead()方法，完成整个 Excel 的读取操作。
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {
@Resource
private GameMapper gameMapper;
/**
* 导入Excel数据到数据库
*
* @param file Excel文件
* @return boolean
* @date 2022/11/8 14:38
**/
@Override
public boolean importExcel(MultipartFile file) {
try {
// 使用 EasyExcel.read() 方法构建一个 Excel reader builder，第一个参数是文件输入流，第二个参数是读对象，指定它这个class去读，第三个参数就是读监听器
EasyExcel.read(file.getInputStream(), GameImportExcelModel.class, new GameImportExcelListener(gameMapper))
.sheet()
.doRead();
} catch (IOException e) {
log.error("Error importing: {}", e.getMessage());
return false;
}
return true;
}
}
测试
使用 Postman 测试，请求后端的导入 Excel 的接口，在 Postman 中选好 Post 请求并且输入请求路径。
点击 Headers 设置请求头：
- Key 中输入 Content-Type，属性的值输入
multipart/form-data
点击 Body 设置请求体：
- 选择 form-data 格式，Key 中输入
file，便可以选择文件进行上传了
测试结果：
可以看到Excel中的数据成功存储到数据库中了，这就完成了导入的功能！
导出功能
用户勾选目标数据 id，点击导出按钮，就能将系统中的数据以 Excel 文件的格式下载到本地。
细节：需要导出的文件名称为这种格式：游戏列表-2022-11-11-12-30-00.xlsx
在这个导出文件的场景下，就需要后端返回前端文件数据，后端有两种方式可以返回，让前端进行下载。
- 后端返回文件所在的 URL，前端直接根据 URL 进行下载。
- 后端以二进制流的形式返回文件流，前端再接受这个流下载到本地。
由于我们的数据是在数据库中的，并不是直接存储 Excel 文件的，所以我们以二进制流的形式返回文件流给前端，让前端下载。
看看 EasyExcel 的 write 方法签名，里面有好多个重载的 write 方法，我们看看这个就行：
public static ExcelWriterBuilder write(OutputStream outputStream, Class head)
参数说明：
- 第一个参数是文件输出流
- 第二个参数是指定我们要参照哪个模型类去写这个 Excel，head 意思就是该类的属性将作为 Excel 的表头。
代码
主要逻辑
// 假设这里是从数据库获取的集合
List<Game> data = ...;
// 文件格式
String fileName = new String("");
String format = "yyyy-MM-dd-HH-mm-ss";
fileName = fileName + DateUtil.format(new Date(), format);
// 将数据写到输出流返回给前端
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.doWrite(dataList);
Excel 工具类
/**
* @author god23bin
* @version 1.0
* @description Excel 工具类
* @date 2022/11/18 17:55:48
*/
public class ExcelUtil {
/**
* 获取响应输出流
* @date 2022/11/18 18:10
* @param fileName 文件名
* @param response 响应
* @return java.io.OutputStream
**/
public static OutputStream getResponseOutputStream(String fileName, HttpServletResponse response) {
try {
// 给文件名编码，则前端接收后进行解码
fileName = URLEncoder.encode(fileName, "UTF-8");
// 指定客户端接收的响应内容类型为Excel以及字符编码为UTF-8
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
// 让浏览器提供打开、保存的对话框，以附件的形式下载，同时设置文件名格式
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
// 禁止缓存
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
控制层
用 Set 集合去重，防止多次查询同一数据。
@PostMapping("/excel/export/game")
public ResponseEntity<String> exportExcel(@RequestBody Set<String> uuidList, HttpServletResponse response) {
gameService.exportExcel(new ArrayList<>(uuidList), response);
return new ResponseEntity<>("OK", HttpStatus.OK);
}
业务层
主要逻辑是去数据库查询出需要导出的数据，然后转成对应的导出模型对象，最后使用 EasyExcel 的 write() 方法将数据写到输出流。
/**
* 导出数据库记录到Excel
*
* @param uuidList uuid集合
* @param response 响应
* @return boolean
* @date 2022/11/11 14:23
**/
@Override
public boolean exportExcel(List<String> uuidList, HttpServletResponse response) {
// 根据uuid找到需要导出的记录集合
LambdaQueryWrapper<Game> achievementWrapper = new LambdaQueryWrapper<>();
achievementWrapper.in(Game::getUuid, uuidList);
achievementWrapper.orderByDesc(Game::getGmtCreate);
List<Game> games = this.baseMapper.selectList(achievementWrapper);
// 将查询到的数据转换成对应Excel的导出模型对象
List<GameExportExcelModel> dataList = games.stream().map(game -> {
GameExportExcelModel gameExportExcelModel = new GameExportExcelModel();
BeanUtil.copyProperties(game, gameExportExcelModel);
return gameExportExcelModel;
}).collect(Collectors.toList());
// 文件格式
String fileName = new String("");
String format = "yyyy-MM-dd-HH-mm-ss";
fileName = fileName + DateUtil.format(new Date(), format);
// 将数据写到输出流返回给前端
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.doWrite(dataList);
return true;
}
测试
以请求体的方式传递一个需要导出的游戏的 uuid 数组
点击 Send 按钮旁边的三角符号，点击 Send and Download，这样就可以下载了，最后下载的 Excel 打开后如下：
额，翻车，格式有点点问题，问题不大，这时候就需要一个自定义的拦截器帮我们处理单元格。
自定义拦截器
拿来主义，写法基本是这样：
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(16);
public CustomCellWriteHandler() {
}
/**
* Sets the column width when head create
*
* @param writeSheetHolder
* @param cellDataList
* @param cell
* @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>(16);
cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData) cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
但是为什么这样写，好吧，目前不了解T_T，有待研究。
接着注册这个拦截器，让它知道该如何处理，修改业务层的代码：
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(dataList);
最后导出效果：
总结
某张 Excel 表需要你导入到系统中，这里系统指的就是你所做的项目，更准确来说将 Excel 的数据插入到你系统中的数据库里。那么就可以使用 EasyExcel。
常见的需求就是对 Excel 数据的导入导出，我们需要做的就是根据 Excel 表进行建模，创建对应的读对象和写对象。
对于导入，就需要读对象配合读监听器来实现。
对于导出，就直接通过 write 方法，以二进制流的方式将数据写到响应体中。
最后的最后
由本人水平所限，难免有错误以及不足之处， 屏幕前的靓仔靓女们 如有发现，恳请指出！
最后，谢谢你看到这里，谢谢你认真对待我的努力，希望这篇博客对你有所帮助！
你轻轻地点了个赞，那将在我的心里世界增添一颗明亮而耀眼的星！
