Spring整合EasyExcel【基础设计】
2024-08-30 01:00:47
前言
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。
起步
- maven or gradle
- springboot
- api or blog
快速上手
简单需求demo
- demo地址
喜欢直接看项目的可以直接 >> demo-easy-excel
内容大致浏览
引入easyexcel
引入easyexcel (maven为例)，引入easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
- 自定义注解
/**
* @author quaint
* @since 17 February 2020
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelPropertyNotNull {
/**
* @return 开启校验
*/
boolean open() default true;
/**
* @return 提示消息
*/
String message() default "";
/**
* @return 列号
*/
int col() default -1;
}
- 创建对应Excel的Dto
业务中有各种类型，这里基于java8常用的类型进行测试。
/**
* 父类 可能业务需要继承
* @author quaint
* @date 2020-01-14 11:23
*/
@Data
public class DemoParentDto {
@ExcelProperty(index = 0,value = {"序号"})
private Integer num;
}
/**
* 子类 一般业务一个子类即可
* @author quaint
* @date 2020-01-14 11:20
*/
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@Data
public class DemoUserDto extends DemoParentDto{
@ExcelProperty(value = {"姓名"})
private String name;
@ExcelProperty(value = {"性别"})
private String sex;
/**
* @see LocalDateConverter （时间格式转换器）LocalDateTime同理，代码也会贴出来
*/
@ExcelProperty(value = "生日",converter = LocalDateConverter.class)
@DateTimeFormat("yyyy-MM-dd")
private LocalDate birthday;
@ExcelProperty(value = {"存款"})
@ExcelPropertyNotNull(message = "不可为空", col = 4)
private BigDecimal money;
/**
* 获取6个测试数据
* @return 6个
*/
public static List<DemoUserDto> getUserDtoTest6(String search){
List<DemoUserDto> list = new ArrayList<>();
list.add(new DemoUserDto("quaint","男",LocalDate.of(2011,11,11),BigDecimal.ONE));
list.add(new DemoUserDto("quaint2","女",LocalDate.of(2001,11,1),BigDecimal.TEN));
list.add(new DemoUserDto("quaint3","男",LocalDate.of(2010,2,7),new BigDecimal(11.11)));
list.add(new DemoUserDto("quaint4","男",LocalDate.of(2011,1,11),new BigDecimal(10.24)));
list.add(new DemoUserDto("quaint5","女",LocalDate.of(2021,5,12),BigDecimal.ZERO));
list.add(new DemoUserDto(search,"男",LocalDate.of(2010,7,11),BigDecimal.TEN));
return list;
}
}
- 创建converter（导入导出时自定义转换对应字段）
/**
* LocalDate and string converter
* @author quait
*/
public class LocalDateConverter implements Converter<LocalDate> {
@Override
public Class supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration){
// 将excel 中的 数据 转换为 LocalDate
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ISO_LOCAL_DATE);
} else {
// 获取注解的 format 注意,注解需要导入这个 excel.annotation.format.DateTimeFormat;
return LocalDate.parse(cellData.getStringValue(),
DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat()));
}
}
@Override
public CellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
// 将 LocalDateTime 转换为 String
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
return new CellData(value.toString());
} else {
return new CellData(value.format(DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat())));
}
}
}
/**
* LocalDateTime and string converter
*
* @author quait
*/
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration){
// 将excel 中的 数据 转换为 LocalDateTime
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ISO_LOCAL_DATE_TIME);
} else {
// 获取注解的 format 注意,注解需要导入这个 excel.annotation.format.DateTimeFormat;
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat());
return LocalDateTime.parse(cellData.getStringValue(), formatter);
}
}
@Override
public CellData convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
// 将 LocalDateTime 转换为 String
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
return new CellData(value.toString());
} else {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat());
return new CellData(value.format(formatter));
}
}
}
- 创建Listener（监听Excel导入）
/**
* 官方提示:有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
*
* 如果想被spring 管理的话, 改为原型模式, Controller 以 getBean 形式获取 本博客展示被spring管理
* @author quaint
*/
@EqualsAndHashCode(callSuper = true)
@Slf4j
@Data
@Scope(SCOPE_PROTOTYPE)
@Component
public class DemoUserListener extends AnalysisEventListener<DemoUserDto> {
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<DemoUserDto> list = new ArrayList<>();
/**
* 方式一
* 可以换成 @Autowired 注入 service 或者mapper
* 不被spring管理的话 使用构造函数 接收外面被spring管理的mapper -->constructor
* @Autowired
* DemoUserMapper demoUserMapper;
*/
private List<DemoUserDto> virtualDataBase = new ArrayList<>();
/**
* 方式二
* 假设 virtualDataBase 是 mapper, 这里就在外面new该类的时候传进来 调用方注入过得mapper
* 并且 把Scope、Component注解去掉
*/
// public DemoUserListener(List<DemoUserDto> virtualDataBase) {
// this.virtualDataBase = virtualDataBase;
// }
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(DemoUserDto data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSONObject.toJSONString(data));
// 校验非空
Field[] fields = DemoUserDto.class.getDeclaredFields();
for (Field f: fields) {
f.setAccessible(true);
ExcelPropertyNotNull ann = f.getAnnotation(ExcelPropertyNotNull.class);
if (null != ann && ann.open()){
try {
if(null == f.get(data)){
log.info("有一条数据未通过校验,message[{}]",ann.message());
log.info("列号：[{}]",ann.col());
return;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
* @param exception exception
* @param context context
* @throws Exception e
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
// 如果是某一个单元格的转换异常 能获取到具体行号
// 如果要获取头的信息 配合invokeHeadMap使用
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
log.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex());
}
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", list.size());
virtualDataBase.addAll(list);
log.info("存储数据库成功！");
}
}
- 创建Handler
/**
* 自定义拦截器。对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel
* 这里没有采用 spring 管理
* @author Jiaju Zhuang
*/
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
log.info("cell 创建之前");
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
log.info("cell 创建后");
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对cell进行任何操作
log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
}
}
- 创建handler控制单元格样式
/**
* 自定义写Excel handler 实现style 策略。
* @author quaint
* @date 14 February 2020
* @since 1.30
*/
public class ProductWriteErrHandler extends AbstractCellStyleStrategy {
/**
* 存储解析失败的行号和列号
*/
private Map<Integer, Integer> failureRowCol;
/**
* 可以这么理解: 外部定义样式
*/
private WriteCellStyle writeErrCellStyle;
/**
* 单元格样式
*/
private CellStyle errCellStyle;
/**
* 在这里自定义样式, 或者在外面定义样式
*/
public ProductWriteErrHandler(WriteCellStyle writeCellStyle,Map<Integer, Integer> failureRowCol) {
this.writeErrCellStyle = writeCellStyle;
this.failureRowCol = failureRowCol;
}
/**
* 单元格样式初始化方法
* @param workbook
*/
@Override
protected void initCellStyle(Workbook workbook) {
// 初始化
if (writeErrCellStyle!=null){
errCellStyle = StyleUtil.buildContentCellStyle(workbook, writeErrCellStyle);
}
}
/**
* 写头部样式
* @param cell
* @param head
* @param relativeRowIndex
*/
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
}
/**
* 写内容样式
* @param cell
* @param head
* @param relativeRowIndex
*/
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
// 判断 是否传入 错误的 map
if (!CollectionUtils.isEmpty(failureRowCol)){
// 如果错误 的行 和列 对应成功 --> 染色
if (failureRowCol.containsKey(cell.getRowIndex())
&& failureRowCol.get(cell.getRowIndex()).equals(cell.getColumnIndex())){
cell.setCellStyle(errCellStyle);
}
}
}
}
- 控制层Controller
/**
* @author quaint
* @date 2020-01-14 11:13
*/
@Controller
@Slf4j
public class DemoEasyExcelSpi implements ApplicationContextAware {
private ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.applicationContext = applicationContext;
}
@PostMapping("/in/excel")
public String inExcel(@RequestParam("inExcel") MultipartFile inExcel, Model model){
DemoUserListener demoUserListener = applicationContext.getBean(DemoUserListener.class);
log.info("demoUserListener 在 spi 调用之前 hashCode为 [{}]", demoUserListener.hashCode());
if (inExcel.isEmpty()){
// 读取 local 指定文件
List<DemoUserDto> demoUserList;
String filePath = System.getProperty("user.dir")+"/demo-easy-excel/src/main/resources/ExcelTest.xlsx";
try {
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(filePath, DemoUserDto.class, demoUserListener).sheet().doRead();
demoUserList = demoUserListener.getVirtualDataBase();
} catch (Exception e) {
e.printStackTrace();
return null;
}
model.addAttribute("users", demoUserList);
} else {
// 读取 web 上传的文件
List<DemoUserDto> demoUserList;
try {
EasyExcel.read(inExcel.getInputStream(), DemoUserDto.class, demoUserListener).sheet().doRead();
demoUserList = demoUserListener.getVirtualDataBase();
} catch (IOException e) {
e.printStackTrace();
return null;
}
model.addAttribute("users", demoUserList);
}
log.info("demoUserListener 在 spi 调用之后 hashCode为 [{}]", demoUserListener.hashCode());
return "index";
}
@PostMapping("/out/excel")
public void export(HttpServletResponse response){
String search = "@RequestBody Object search";
// 根据前端传入的查询条件 去库里查到要导出的dto
List<DemoUserDto> userDto = DemoUserDto.getUserDtoTest6(search);
// 要忽略的 字段
List<String> ignoreIndices = Collections.singletonList("性别");
// 根据类型获取要反射的对象
Class clazz = DemoUserDto.class;
// 遍历所有字段, 找到忽略的字段
Set<String> excludeFiledNames = new HashSet<>();
while (clazz != Object.class){
Arrays.stream(clazz.getDeclaredFields()).forEach(field -> {
ExcelProperty ann = field.getAnnotation(ExcelProperty.class);
if (ann!=null && ignoreIndices.contains(ann.value()[0])){
// 忽略 该字段
excludeFiledNames.add(field.getName());
}
});
clazz = clazz.getSuperclass();
}
// 设置序号
AtomicInteger i = new AtomicInteger(1);
userDto.forEach(u-> u.setNum(i.getAndIncrement()));
// 创建本地文件
EasyExcelUtils.exportLocalExcel(userDto,DemoUserDto.class,"ExcelTest",excludeFiledNames);
// 创建web文件
EasyExcelUtils.exportWebExcel(response,userDto,DemoUserDto.class,"ExcelTest",null);
}
}
- 导出工具类
/**
* EasyExcelUtils
* @author quaint
* @date 2020-01-14 14:26
*/
public abstract class EasyExcelUtils {
/**
* 导出excel
* @param response http下载
* @param dataList 导出的数据
* @param clazz 导出的模板类
* @param fileName 导出的文件名
* @param excludeFiledNames 要排除的filed
* @param <T> 模板
*/
public static <T> void exportWebExcel(HttpServletResponse response, List<T> dataList, Class<T> clazz,
String fileName, Set<String> excludeFiledNames) {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 单元格样式策略 定义
WriteCellStyle style = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
style.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.RED.getIndex());
Map<Integer,Integer> errRecord = new HashMap<>();
errRecord.put(1,1);
errRecord.put(2,2);
ProductWriteErrHandler handler = new ProductWriteErrHandler(style,errRecord);
try {
// 导出excel
EasyExcel.write(response.getOutputStream(), clazz)
// 设置过滤字段策略
.excludeColumnFiledNames(excludeFiledNames)
// 选择导入时的 handler
.registerWriteHandler(handler)
.sheet("fileName")
.doWrite(dataList);
} catch (IOException e) {
System.err.println("创建文件异常!");
}
}
/**
* 导出excel
* @param dataList 导出的数据
* @param clazz 导出的模板类
* @param fileName 导出的文件名
* @param excludeFiledNames 要排除的filed
* @param <T> 模板
*/
public static <T> void exportLocalExcel(List<T> dataList, Class<T> clazz, String fileName,
Set<String> excludeFiledNames){
//创建本地文件 test 使用
String filePath = System.getProperty("user.dir")+"/demo-easy-excel/src/main/resources/"+fileName+".xlsx";
File dbfFile = new File(filePath);
if (!dbfFile.exists() || dbfFile.isDirectory()) {
try {
dbfFile.createNewFile();
} catch (IOException e) {
System.err.println("创建文件异常!");
return;
}
}
// 导出excel
EasyExcel.write(filePath, clazz)
.registerWriteHandler(new CustomCellWriteHandler())
.excludeColumnFiledNames(excludeFiledNames)
.sheet("SheetName").doWrite(dataList);
}
}
- 前端代码
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<style>
.data-local{
border: 1px black;
}
</style>
<body>
<form th:action="@{/in/excel}" method="post" enctype="multipart/form-data">
<input name="inExcel" type="file" value="上传文件"/>
<input type="submit" value="导入excel"/>
</form>
<h2>导入的数据展示位置:</h2>
<div class="data-local" th:each="user : ${users}">
<span th:text="${user}"></span>
</div>
<form th:action="@{/out/excel}" method="post">
<input type="submit" value="导出下载文件"/>
</form>
</body>
</html>
- 导入效果图
- 导出效果图
总结
Listener和Handler的自定义写法可以满足绝大多数需求，大佬设计的代码用起来就是舒服。就是@ExcelProperty注解的index属性的排序混合使用，还需要看源码是如何排序的。这里知识匮乏，望以后可以补充。
最新文章
- Git学习笔记（7）——多人协作
- Winform中DockPanel(引用WeifenLuo.WinFormsUI.Docking.dll组件)的使用
- C++实现网格水印之调试笔记（三）—— 初有结果
- 【HDOJ】3686 Traffic Real Time Query System
- 测试darwin calendar 服务器
- mysql优化--博森瑞
- Java---网络编程(2)-UDP
- DataTable数据与Excel表格的相互转换
- 【转】Perl Unicode全攻略
- 7kb的javascript日期操作类库（XDate）
- Tomcat6 Session建立机制简要
- Dell-R730 【Pxe+dhcp+ftp+tftp+Kickstart+CentOs6.6】
- 我的第三个开源库GuaJiangViewDemo——中文文档
- 联想Y410P在Ubuntu系统下开关机及插耳机破音“啪啪”的解决办法
- JAVA初识，JAVA是什么？
- EditPlus 自用正则替换
- docker安装jenkins及其相关问题解决
- UI5-文档-4.25-Sorting and Grouping
- Zabbix proxy 3.2安装部署
- 【loj2325】「清华集训 2017」小Y和恐怖的奴隶主 概率dp+倍增+矩阵乘法