SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

macrozheng 發表於 2022-01-12
Spring Excel
操作Excel實現匯入匯出是個非常常見的需求,之前介紹了一款非常好用的工具EasyPoi 。有讀者提出在資料量大的情況下,EasyPoi佔用記憶體大,效能不夠好。今天給大家推薦一款效能更好的Excel匯入匯出工具EasyExcel,希望對大家有所幫助!

SpringBoot實戰電商專案mall(50k+star)地址:https://github.com/macrozheng/mall

EasyExcel簡介

EasyExcel是一款阿里開源的Excel匯入匯出工具,具有處理快速、佔用記憶體小、使用方便的特點,在Github上已有22k+Star,可見其非常流行。

EasyExcel讀取75M(46W行25列)的Excel,僅需使用64M記憶體,耗時20s,極速模式還可以更快!

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

整合

在SpringBoot中整合EasyExcel非常簡單,僅需一個依賴即可。
<!--EasyExcel相關依賴-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

使用

EasyExcel和EasyPoi的使用非常類似,都是通過註解來控制匯入匯出。接下來我們以會員資訊和訂單資訊的匯入匯出為例,分別實現下簡單的單表匯出和具有一對多關係的複雜匯出。

簡單匯出

我們以會員資訊的匯出為例,來體驗下EasyExcel的匯出功能。
  • 首先建立一個會員物件Member,封裝會員資訊,這裡使用了EasyExcel的註解;
/**
 * 購物會員
 * Created by macro on 2021/10/12.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
    @ExcelProperty("ID")
    @ColumnWidth(10)
    private Long id;
    @ExcelProperty("使用者名稱")
    @ColumnWidth(20)
    private String username;
    @ExcelIgnore
    private String password;
    @ExcelProperty("暱稱")
    @ColumnWidth(20)
    private String nickname;
    @ExcelProperty("出生日期")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;
    @ExcelProperty("手機號")
    @ColumnWidth(20)
    private String phone;
    @ExcelIgnore
    private String icon;
    @ExcelProperty(value = "性別", converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}
  • 上面程式碼使用到了EasyExcel的核心註解,我們分別來了解下:

    • @ExcelProperty:核心註解,value屬性可用來設定表頭名稱,converter屬性可以用來設定型別轉換器;
    • @ColumnWidth:用於設定表格列的寬度;
    • @DateTimeFormat:用於設定日期轉換格式。
  • 在EasyExcel中,如果你想實現列舉型別到字串的轉換(比如gender屬性中,0->男1->女),需要自定義轉換器,下面為自定義的GenderConverter程式碼實現;
/**
 * excel性別轉換器
 * Created by macro on 2021/12/29.
 */
public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        //物件屬性型別
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData屬性型別
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //CellData轉物件屬性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if ("男".equals(cellStr)) {
            return 0;
        } else if ("女".equals(cellStr)) {
            return 1;
        } else {
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        //物件屬性轉CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue == 0) {
            return new WriteCellData<>("男");
        } else if (cellValue == 1) {
            return new WriteCellData<>("女");
        } else {
            return new WriteCellData<>("");
        }
    }
}
  • 接下來我們在Controller中新增一個介面,用於匯出會員列表到Excel,還需給響應頭設定下載excel的屬性,具體程式碼如下;
/**
 * EasyExcel匯入匯出測試Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel匯入匯出測試")
@RequestMapping("/easyExcel")
public class EasyExcelController {

    @SneakyThrows(IOException.class)
    @ApiOperation(value = "匯出會員列表Excel")
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "會員列表");
        List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
        EasyExcel.write(response.getOutputStream())
                .head(Member.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("會員列表")
                .doWrite(memberList);
    }
    
  /**
   * 設定excel下載響應頭屬性
   */
  private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  }
}
  • 執行專案,通過Swagger測試介面,注意在Swagger中訪問介面無法直接下載,需要點選返回結果中的下載按鈕才行,訪問地址:http://localhost:8088/swagger...

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

  • 下載完成後,檢視下檔案,一個標準的Excel檔案已經被匯出了。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

簡單匯入

接下來我們以會員資訊的匯入為例,來體驗下EasyExcel的匯入功能。
  • 在Controller中新增會員資訊匯入的介面,這裡需要注意的是使用@RequestPart註解修飾檔案上傳引數,否則在Swagger中就沒法顯示上傳按鈕了;
/**
 * EasyExcel匯入匯出測試Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel匯入匯出測試")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @ApiOperation("從Excel匯入會員列表")
    @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
        List<Member> memberList = EasyExcel.read(file.getInputStream())
                .head(Member.class)
                .sheet()
                .doReadSync();
        return CommonResult.success(memberList);
    }
}
  • 然後在Swagger中測試介面,選擇之前匯出的Excel檔案即可,匯入成功後會返回解析到的資料。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

複雜匯出

當然EasyExcel也可以實現更加複雜的匯出,比如匯出一個巢狀了商品資訊的訂單列表,下面我們來實現下!

使用EasyPoi實現

之前我們使用過EasyPoi實現該功能,由於EasyPoi本來就支援巢狀物件的匯出,直接使用內建的@ExcelCollection註解即可實現,非常方便也符合物件導向的思想。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

尋找方案

由於EasyExcel本身並不支援這種一對多的資訊匯出,所以我們得自行實現下,這裡分享一個我平時常用的快速查詢解決方案的辦法。

我們可以直接從開源專案的issues裡面去搜尋,比如搜尋下一對多,會直接找到有無一對多匯出比較優雅的方案這個issue。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

從此issue的回覆我們可以發現,專案維護者建議建立自定義合併策略來實現,有位回覆的老哥已經給出了實現程式碼,接下來我們就用這個方案來實現下。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

解決思路

為什麼自定義單元格合併策略能實現一對多的列表資訊的匯出呢?首先我們來看下將巢狀資料平鋪,不進行合併匯出的Excel。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

看完之後我們很容易理解解決思路,只要把訂單ID相同的列中需要合併的列給合併了,就可以實現這種一對多巢狀資訊的匯出了。

實現過程

  • 首先我們得把原來巢狀的訂單商品資訊給平鋪了,建立一個專門的匯出物件OrderData,包含訂單和商品資訊,二級表頭可以通過設定@ExcelProperty的value為陣列來實現;
/**
 * 訂單匯出
 * Created by macro on 2021/12/30.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
    @ExcelProperty(value = "訂單ID")
    @ColumnWidth(10)
    @CustomMerge(needMerge = true, isPk = true)
    private String id;
    @ExcelProperty(value = "訂單編碼")
    @ColumnWidth(20)
    @CustomMerge(needMerge = true)
    private String orderSn;
    @ExcelProperty(value = "建立時間")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    @CustomMerge(needMerge = true)
    private Date createTime;
    @ExcelProperty(value = "收貨地址")
    @CustomMerge(needMerge = true)
    @ColumnWidth(20)
    private String receiverAddress;
    @ExcelProperty(value = {"商品資訊", "商品編碼"})
    @ColumnWidth(20)
    private String productSn;
    @ExcelProperty(value = {"商品資訊", "商品名稱"})
    @ColumnWidth(20)
    private String name;
    @ExcelProperty(value = {"商品資訊", "商品標題"})
    @ColumnWidth(30)
    private String subTitle;
    @ExcelProperty(value = {"商品資訊", "品牌名稱"})
    @ColumnWidth(20)
    private String brandName;
    @ExcelProperty(value = {"商品資訊", "商品價格"})
    @ColumnWidth(20)
    private BigDecimal price;
    @ExcelProperty(value = {"商品資訊", "商品數量"})
    @ColumnWidth(20)
    private Integer count;
}
  • 然後將原來巢狀的Order物件列表轉換為OrderData物件列表;
/**
 * EasyExcel匯入匯出測試Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel匯入匯出測試")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    private List<OrderData> convert(List<Order> orderList) {
        List<OrderData> result = new ArrayList<>();
        for (Order order : orderList) {
            List<Product> productList = order.getProductList();
            for (Product product : productList) {
                OrderData orderData = new OrderData();
                BeanUtil.copyProperties(product,orderData);
                BeanUtil.copyProperties(order,orderData);
                result.add(orderData);
            }
        }
        return result;
    }
}
  • 再建立一個自定義註解CustomMerge,用於標記哪些屬性需要合併,哪個是主鍵;
/**
 * 自定義註解,用於判斷是否需要合併以及合併的主鍵
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否需要合併單元格
     */
    boolean needMerge() default false;

    /**
     * 是否是主鍵,即該欄位相同的行合併
     */
    boolean isPk() default false;
}
  • 再建立自定義單元格合併策略類CustomMergeStrategy,當Excel中兩列主鍵相同時,合併被標記需要合併的列;
/**
 * 自定義單元格合併策略
 */
public class CustomMergeStrategy implements RowWriteHandler {
    /**
     * 主鍵下標
     */
    private Integer pkIndex;

    /**
     * 需要合併的列的下標集合
     */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * DTO資料型別
     */
    private Class<?> elementType;

    public CustomMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是標題,則直接返回
        if (isHead) {
            return;
        }

        // 獲取當前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 獲取標題行
        Row titleRow = sheet.getRow(0);

        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }

        // 判斷是否需要和上一行進行合併
        // 不能和標題合併,只能資料行之間合併
        if (row.getRowNum() <= 1) {
            return;
        }
        // 獲取上一行資料
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 將本行和上一行是同一型別的資料(通過主鍵欄位進行判斷),則需要合併
        if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                        needMerIndex, needMerIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主鍵下標和需要合併欄位的下標
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 獲取當前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 獲取標題行
        Row titleRow = sheet.getRow(0);
        // 獲取DTO的型別
        Class<?> eleType = this.elementType;

        // 獲取DTO所有的屬性
        Field[] fields = eleType.getDeclaredFields();

        // 遍歷所有的欄位,因為是基於DTO的欄位來構建excel,所以欄位數 >= excel的列數
        for (Field theField : fields) {
            // 獲取@ExcelProperty註解,用於獲取該欄位對應在excel中的列的下標
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 為空,則表示該欄位不需要匯入到excel,直接處理下一個欄位
            if (null == easyExcelAnno) {
                continue;
            }
            // 獲取自定義的註解,用於合併單元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 沒有@CustomMerge註解的預設不合並
            if (null == customMerge) {
                continue;
            }

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 當配置為不需要匯出時,返回的為null,這裡作一下判斷,防止NPE
                if (null == theCell) {
                    continue;
                }
                // 將欄位和excel的表頭匹配上
                if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }

                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }

        // 沒有指定主鍵,則異常
        if (null == this.pkIndex) {
            throw new IllegalStateException("使用@CustomMerge註解必須指定主鍵");
        }

    }
}
  • 接下來在Controller中新增匯出訂單列表的介面,將我們自定義的合併策略CustomMergeStrategy給註冊上去;
/**
 * EasyExcel匯入匯出測試Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel匯入匯出測試")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @ApiOperation(value = "匯出訂單列表Excel")
    @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
    public void exportOrderList(HttpServletResponse response) {
        List<Order> orderList = getOrderList();
        List<OrderData> orderDataList = convert(orderList);
        setExcelRespProp(response, "訂單列表");
        EasyExcel.write(response.getOutputStream())
                .head(OrderData.class)
                .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("訂單列表")
                .doWrite(orderDataList);
    }
}
  • 在Swagger中訪問介面測試,匯出訂單列表對應Excel;

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

  • 下載完成後,檢視下檔案,由於EasyExcel需要自己來實現,對比之前使用EasyPoi來實現麻煩了不少。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

其他使用

由於EasyExcel的官方文件介紹的比較簡單,如果你想要更深入地進行使用的話,建議大家看下官方Demo。

SpringBoot實現Excel匯入匯出,效能爆表,用起來夠優雅!

總結

體驗了一把EasyExcel,使用還是挺方便的,效能也很優秀。但是比較常見的一對多匯出實現比較複雜,而且功能也不如EasyPoi 強大。如果你的Excel匯出資料量不大的話,可以使用EasyPoi,如果資料量大,比較在意效能的話,還是使用EasyExcel吧。

參考資料

專案原始碼地址

https://github.com/macrozheng...

本文 GitHub https://github.com/macrozheng/mall-learning 已經收錄,歡迎大家Star!