• EasyExcel对大数据量表格操作导入导出


    前言

    最近有个项目里面中有大量的Excel文档导入导出需求,数据量最多的文档有上百万条数据,之前的导入导出都是用apache的POI,于是这次也决定使用POI,结果导入一个四十多万的文档就GG了,内存溢出...  于是找到EasyExcel的文档,学习了一番,解决了大数据量导入导出的痛点。

    由于项目中很多接口都需要用到导入导出,部分文档都是根据日期区分,部分文档是需要全表覆盖,于是抽出一个工具类,简化下重复代码,在此把实现过程记录一下。

     

    测试结果

    数据量100W

     

    导入 

    测试了几次,读取完加保存到数据库总耗时都是在140秒左右

     

    导出 

    由于在业务中不涉及到大数据量的导出,最多只有10W+数据的导出,所以用的是最简单的写,测试二十万的数据量五十秒左右

     

    依赖

    官方文档:https://easyexcel.opensource.alibaba.com/

    1
    2
    3
    4
    5
        com.alibaba
        easyexcel
        3.1.2

      

    具体实现

    实体类

    @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 {
     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 context) {
    25         return new WriteCellData<>(DateUtil.localDateToDayString(context.getValue()));
    26     }
    27 
    28 }
    复制代码

     

    工具类

    由于项目中很多接口都有使用到导入导出,且持久层框架是Mybatis Plus,在此封装成通用的方法。

    如果数据量不大,那么一行代码就可以解决了,直接用Mybatis Plus的批量插入:

    EasyExcel.read(file.getInputStream(), TestUserEntity.class, new PageReadListener(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 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> valueList = dataList.stream().map(v -> {
     33             List 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  Boolean importExcel(MultipartFile file, Integer date, SFunction getCreateDate, BiConsumer setCreateDate, BaseMapper mapper, Class 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(
    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  Boolean importExcel(MultipartFile file, Class 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  void exportExcel(Class clazz, List 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 columnList;
    17 
    18     //value集合
    19     private List> 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 
     2 
     3 
     4     
     5         insert into ${dto.tableName}
     6         
     7             `${item}`
     8         
     9         values
    10         
    11             (
    12                 
    13                     #{value}
    14                 
    15             )
    16         
    17     
    18 
    19 
    20     
    21         truncate table ${tableName}
    22     
    23 
    24 
    复制代码

     

    调用

    复制代码
     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 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 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 }
    复制代码

     

  • 相关阅读:
    Flink操作——状态与容错
    JS流程控制语句_循环语句
    密码学系列之:PEM和PKCS7,PKCS8,PKCS12
    在面试提问环节应该问那些内容
    Delphi的函数指针传递和调用
    【Matplotlib绘制图像大全】(十五):Matplotlib绘制误差曲线
    关于myBase个人笔记数据库的同步
    面试突击:MVCC 和间隙锁有什么区别?
    js几种常见的遍历方式
    modernC++手撸任意层神经网络22前向传播反向传播&梯度下降等23代码补全的例子0901b
  • 原文地址:https://www.cnblogs.com/-tang/p/16900064.html