技術文章 » EasyExcel對巨量資料量表格操作匯入匯出

EasyExcel對巨量資料量表格操作匯入匯出

2022-11-18 12:00:19

前言

最近有個專案裡面中有大量的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 }