前言
因为工作起因,有这种需要,就是把数据库中的数据导出成 Excel 表格,同时,也得反对人家用 Excel 表格导入数据到数据库。以后我的项目也是在用 EasyExcel,所以我不得不学啦!
以前学习的过程中,有听过 EasyExcel 这么一个货色,不过素来没用过,所以,正好借此机会学习,看看如何应用它来实现需求。
在学习 EasyExcel 的这段时间里,也理解到工作中这种导入导出的需要还是挺常见的,所以决定记录下来。
官网文档:https://easyexcel.opensource.alibaba.com/docs/current/
需要
用户点击导入按钮,就可能上传 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 关上后如下:
额,翻车,格局有点点问题,问题不大,这时候就须要一个自定义的拦截器帮咱们处理单元格。
自定义拦截器
拿来主义,写法根本是这样:
EasyExcel 导出主动适应列宽 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 办法,以二进制流的形式将数据写到响应体中。
最初的最初
由自己程度所限,不免有谬误以及不足之处,屏幕前的靓仔靓女们
如有发现,恳请指出!
最初,谢谢你看到这里,谢谢你认真对待我的致力,心愿这篇博客对你有所帮忙!
你轻轻地点了个赞,那将在我的心里世界削减一颗亮堂而夺目的星!