前言
最近有个项目里面中有大量的Excel文档导入导出需求，数据量最多的文档有上百万条数据，之前的导入导出都是用apache的POI，于是这次也决定使用POI，结果导入一个四十多万的文档就GG了，内存溢出... 于是找到EasyExcel的文档，学习了一番，解决了大数据量导入导出的痛点。
由于项目中很多接口都需要用到导入导出，部分文档都是根据日期区分，部分文档是需要全表覆盖，于是抽出一个工具类，简化下重复代码，在此把实现过程记录一下。
测试结果
数据量100W
导入
测试了几次，读取完加保存到数据库总耗时都是在140秒左右
导出
由于在业务中不涉及到大数据量的导出，最多只有10W+数据的导出，所以用的是最简单的写，测试二十万的数据量五十秒左右
依赖
官方文档：https://easyexcel.opensource.alibaba.com/
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.2</version> </dependency>
具体实现
实体类
@ExcelProperty注解对应Excel文档中的表头，其中默认属性是value，对应文字，也有index属性，可以对应下标。converter属性是指定一个转换器，这个转换器中实现了把Excel内容转换成java对象(导入使用)，Java对象转Excel内容(导出使用)，我这里实现的是LocalDateTime和文本的转换。
@ExcelIgnoreUnannotated注解的意思就是在导入导出的时候忽略掉未加@ExcelProperty注解的字段
1 @Data 2 @TableName("t_test_user") 3 @ApiModel(value = "TestUserEntity对象", description = "测试表") 4 @ExcelIgnoreUnannotated 5 public class TestUserEntity implements Serializable { 6 7 private static final long serialVersionUID = 1L; 8 9 @TableId(value = "id", type = IdType.AUTO) 10 private Long id; 11 12 @ExcelProperty("用户名") 13 @ApiModelProperty("用户名") 14 @TableField("user_name") 15 private String userName; 16 17 @ExcelProperty("账号") 18 @ApiModelProperty("账号") 19 @TableField("account") 20 private String account; 21 22 @ExcelProperty("性别") 23 @ApiModelProperty("性别") 24 @TableField("sex") 25 private String sex; 26 27 @ExcelProperty(value = "注册时间", converter = StringToLocalDateTimeConverter.class) 28 @ApiModelProperty("注册时间") 29 @TableField("registered_time") 30 private LocalDateTime registeredTime; 31 32 @ApiModelProperty("数据日期") 33 @TableField("data_date") 34 private Integer dataDate; 35 36 @ApiModelProperty("创建人") 37 @TableField("create_user") 38 private String createUser; 39 40 @ApiModelProperty("创建时间") 41 @TableField("create_time") 42 private LocalDateTime createTime; 43 }
转换器
在这里实现导入导出的数据格式转换
1 /** 2 * @author Tang 3 * @describe easyExcel格式转换器 4 * @date 2022年08月29日 09:41:03 5 */ 6 public class StringToLocalDateTimeConverter implements Converter<LocalDateTime> { 7 /** 8 * 这里读的时候会调用 9 */ 10 @Override 11 public LocalDateTime convertToJavaData(ReadConverterContext<?> context) { 12 String stringValue = context.getReadCellData().getStringValue(); 13 return StringUtils.isBlank(stringValue) ? null : DateUtil.stringToLocalDatetime(stringValue); 14 } 15 16 /** 17 * @describe 写的时候调用 18 * @Param context 19 * @return com.alibaba.excel.metadata.data.WriteCellData<?> 20 * @date 2022年11月17日 16:03:39 21 * @author Tang 22 */ 23 @Override 24 public WriteCellData<?> convertToExcelData(WriteConverterContext<LocalDateTime> context) { 25 return new WriteCellData<>(DateUtil.localDateToDayString(context.getValue())); 26 } 27 28 }
工具类
由于项目中很多接口都有使用到导入导出，且持久层框架是Mybatis Plus，在此封装成通用的方法。
如果数据量不大，那么一行代码就可以解决了，直接用Mybatis Plus的批量插入：
EasyExcel.read(file.getInputStream(), TestUserEntity.class, new PageReadListener<TestUserEntity>(TestUserService::saveBatch)).sheet().doRead();
PageReadListener是默认的监听器，在此监听器中传入一个Consumer接口的实现，由此来保存数据。它具体实现原理是从文件中分批次读取，然后在此监听器中实现保存到数据库，当然也可以重写监听器，定义自己想要实现的业务，如数据校验等。BATCH_COUNT参数是每次读取的数据条数，3.1.2的版本默认是100条，建议修改为3000。
导出也是一行代码：EasyExcel.write(response.getOutputStream(), clazz).sheet().doWrite(() -> testUserService.list());
数据量大的话用Mybatis Plus的批量插入还是会很慢，因为这个批量插入实际上还是一条条数据插入的，需要把所有数据拼接成insert into table(field1,field2) values(value1,value2),(value1,value2),(value,value2)...，配合数据库的rewriteBatchedStatements=true参数配置，可以实现快速批量插入，在下文中的114行调用实现。
1 /** 2 * @author Tang 3 * @describe EasyExcel工具类 4 * @date 2022年11月02日 17:56:45 5 */ 6 public class EasyExcelUtil { 7 8 /** 9 * @describe 封装成批量插入的参数对象 10 * @Param clazz 11 * @Param dataList 12 * @date 2022年11月17日 18:00:31 13 * @author Tang 14 */ 15 public static DynamicSqlDTO dynamicSql(Class<?> clazz, List<?> dataList) { 16 //字段集合 key=数据库列名 value=实体类get方法 17 Map<String, Method> getMethodMap = new LinkedHashMap<>(); 18 //获取所有字段 19 Field[] declaredFields = clazz.getDeclaredFields(); 20 for (Field field : declaredFields) { 21 field.setAccessible(true); 22 //获取注解为TableField的字段 23 TableField annotation = field.getAnnotation(TableField.class); 24 if (annotation != null && annotation.exist()) { 25 String column = annotation.value(); 26 Method getMethod = getGetMethod(clazz, field.getName()); 27 getMethodMap.put(column, getMethod); 28 } 29 } 30 31 //value集合 32 List<List<Object>> valueList = dataList.stream().map(v -> { 33 List<Object> tempList = new ArrayList<>(); 34 getMethodMap.forEach((key, value) -> { 35 try { 36 tempList.add(value.invoke(v)); 37 } catch (IllegalAccessException | InvocationTargetException e) { 38 tempList.add(null); 39 } 40 }); 41 return tempList; 42 }).collect(Collectors.toList()); 43 44 return DynamicSqlDTO.builder() 45 .tableName(clazz.getAnnotation(TableName.class).value()) 46 .columnList(new ArrayList<>(getMethodMap.keySet())) 47 .valueList(valueList) 48 .build(); 49 } 50 51 52 /** 53 * @describe java反射bean的get方法 54 * @Param objectClass 55 * @Param fieldName 56 * @date 2022年11月02日 17:52:03 57 * @author Tang 58 */ 59 private static Method getGetMethod(Class<?> objectClass, String fieldName) { 60 StringBuilder sb = new StringBuilder(); 61 sb.append("get"); 62 sb.append(fieldName.substring(0, 1).toUpperCase(Locale.ROOT)); 63 sb.append(fieldName.substring(1)); 64 try { 65 return objectClass.getMethod(sb.toString()); 66 } catch (NoSuchMethodException e) { 67 throw new RuntimeException("Reflect error!"); 68 } 69 } 70 71 72 /** 73 * @return boolean 74 * @describe EasyExcel公用导入方法(按日期覆盖) 75 * @Param file excel文件 76 * @Param date 数据日期 77 * @Param function 数据日期字段的get方法 如传入了date,则需要设置 78 * @Param setCreateDate 数据日期set方法 如传入了date,则需要设置 79 * @Param mapper 实体类对应的mapper对象 如传入了date,则需要设置 80 * @Param entityClass 实体类class 81 * @date 2022年11月11日 15:10:19 82 * @author Tang 83 */ 84 public static <T> Boolean importExcel(MultipartFile file, Integer date, SFunction<T, Integer> getCreateDate, BiConsumer<T, Integer> setCreateDate, BaseMapper<T> mapper, Class<T> entityClass) { 85 String userName = SecurityAuthorHolder.getSecurityUser().getUsername(); 86 LocalDateTime now = LocalDateTime.now(); 87 CustomSqlService customSqlService = ApplicationConfig.getBean(CustomSqlService.class); 88 89 //根据date来判断 为null则需要删除全表数据 否则删除当天数据 90 if (date == null) { 91 customSqlService.truncateTable(entityClass.getAnnotation(TableName.class).value()); 92 } else { 93 mapper.delete(Wrappers.lambdaQuery(entityClass).eq(getCreateDate, date)); 94 } 95 96 try { 97 Method setCreateUser = entityClass.getMethod("setCreateUser", String.class); 98 Method setCreateTime = entityClass.getMethod("setCreateTime", LocalDateTime.class); 99 100 EasyExcel.read(file.getInputStream(), entityClass, new PageReadListener<T>( 101 dataList -> { 102 dataList.forEach(v -> { 103 try { 104 setCreateUser.invoke(v, userName); 105 setCreateTime.invoke(v, now); 106 if (setCreateDate != null) { 107 setCreateDate.accept(v, date); 108 } 109 } catch (IllegalAccessException | InvocationTargetException e) { 110 e.printStackTrace(); 111 } 112 }); 113 if (CollectionUtil.isNotEmpty(dataList)) { 114 customSqlService.executeCustomSql(dynamicSql(entityClass, dataList)); 115 } 116 } 117 )).sheet().doRead(); 118 } catch (Exception e) { 119 e.printStackTrace(); 120 throw new ServerException("读取异常"); 121 } 122 return true; 123 } 124 125 /** 126 * @return boolean 127 * @describe EasyExcel公用导入方法(全表覆盖) 128 * @Param file 129 * @Param entityClass 130 * @date 2022年11月11日 15:33:07 131 * @author Tang 132 */ 133 public static <T> Boolean importExcel(MultipartFile file, Class<T> entityClass) { 134 return importExcel(file, null, null, null, null, entityClass); 135 } 136 137 /** 138 * @return void 139 * @describe EasyExcel公用导出方法 140 * @Param clazz 141 * @Param dataList 142 * @date 2022年11月11日 15:56:45 143 * @author Tang 144 */ 145 public static <T> void exportExcel(Class<T> clazz, List<T> dataList) { 146 HttpServletResponse response = ServletRequestUtil.getHttpServletResponse(); 147 try { 148 EasyExcel.write(response.getOutputStream(), clazz) 149 .sheet() 150 .doWrite(() -> dataList); 151 } catch (Exception e) { 152 e.printStackTrace(); 153 throw new ServerException("导出失败"); 154 } 155 } 156 }
DTO
1 /** 2 * @author Tang 3 * @describe 生成批量插入sqlDTO 4 * @date 2022年11月02日 17:53:33 5 */ 6 @Data 7 @Builder 8 @AllArgsConstructor 9 @NoArgsConstructor 10 public class DynamicSqlDTO { 11 12 //表名 13 private String tableName; 14 15 //列名集合 16 private List<String> columnList; 17 18 //value集合 19 private List<List<Object>> valueList; 20 }
Mapper
根据业务实现了两个方法，一个是批量插入，一个是全表覆盖删除
1 @Mapper 2 public interface CustomSqlMapper { 3 4 /** 5 * @describe 执行动态批量插入语句 6 * @Param dynamicSql 7 * @date 2022年11月03日 09:59:22 8 * @author Tang 9 */ 10 void executeCustomSql(@Param("dto") DynamicSqlDTO dto); 11 12 /** 13 * @describe 快速清空表 14 * @Param tableName 15 * @date 2022年11月08日 17:47:45 16 * @author Tang 17 */ 18 void truncateTable(@Param("tableName") String tableName); 19 }
XML
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="**.**.CustomSqlMapper"> 4 <insert id="executeCustomSql"> 5 insert into ${dto.tableName} 6 <foreach collection="dto.columnList" item="item" separator="," open="(" close=")"> 7 `${item}` 8 </foreach> 9 values 10 <foreach collection="dto.valueList" item="item" separator=","> 11 ( 12 <foreach collection="item" item="value" separator=","> 13 #{value} 14 </foreach> 15 ) 16 </foreach> 17 </insert> 18 19 20 <insert id="truncateTable"> 21 truncate table ${tableName} 22 </insert> 23 24 </mapper>
调用
1 @RestController 2 @Api(value = "测试-测试", tags = "测试-测试") 3 @RequestMapping("/test") 4 public class TestUserController { 5 6 @Resource 7 private TestUserMapper testUserMapper; 8 9 @PostMapping(value = "/import", produces = BaseConstant.REQUEST_HEADERS_CONTENT_TYPE) 10 @ApiOperation(value = "测试-导入(全表覆盖)", notes = "测试-导入(全表覆盖)") 11 public RR<Boolean> testImport(@RequestParam(value = "file") @ApiParam("上传文件") MultipartFile file) { 12 return RR.success( 13 EasyExcelUtil.importExcel( 14 file, 15 TestUserEntity.class 16 ) 17 ); 18 } 19 20 @PostMapping(value = "/import", produces = BaseConstant.REQUEST_HEADERS_CONTENT_TYPE) 21 @ApiOperation(value = "测试-导入(按日期覆盖)", notes = "测试-导入(按日期覆盖)") 22 public RR<Boolean> testImport(@RequestParam(value = "file") @ApiParam("上传文件") MultipartFile file, @ApiParam("日期 20110101") @RequestParam(value = "date") Integer date) { 23 return RR.success( 24 EasyExcelUtil.importExcel( 25 file, 26 date, 27 TestUserEntity::getDataDate, 28 TestUserEntity::setDataDate, 29 testUserMapper, 30 TestUserEntity.class 31 ) 32 ); 33 } 34 35 @PostMapping(value = "/export", produces = BaseConstant.REQUEST_HEADERS_CONTENT_TYPE) 36 @ApiOperation(value = "测试-导出", notes = "测试-导出") 37 public void testExport() { 38 EasyExcelUtil.exportExcel( 39 TestUserEntity.class, 40 testUserMapper.selectList(null) 41 ); 42 } 43 }
