阿里出品Excel工具EasyExcel使用小結

throwable發表於2020-09-12
阿里Excel

前提

筆者做小資料和零號提數工具人已經有一段時間，服務的物件是運營和商務的大佬，一般要求匯出的資料是Excel檔案，考慮到初創團隊機器資源十分有限的前提下，選用了阿里出品的Excel工具EasyExcel。這裡簡單分享一下EasyExcel的使用心得。EasyExcel從其依賴樹來看是對apache-poi的封裝，筆者從開始接觸Excel處理就選用了EasyExcel，避免了廣泛流傳的apache-poi導致的記憶體洩漏問題。

引入EasyExcel依賴

引入EasyExcel的Maven如下：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>

當前（2020-09-08）的最新版本為2.2.6。

API簡介

Excel檔案主要圍繞讀和寫操作進行處理，EasyExcel的API也是圍繞這兩個方面進行設計。先看讀操作的相關API：

// 新建一個ExcelReaderBuilder例項
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 讀取的檔案物件，可以是File、路徑（字串）或者InputStream例項
readerBuilder.file("");
// 檔案的密碼
readerBuilder.password("");
// 指定sheet，可以是數字序號sheetNo或者字串sheetName，若不指定則會讀取所有的sheet
readerBuilder.sheet("");
// 是否自動關閉輸入流
readerBuilder.autoCloseStream(true);
// Excel檔案格式，包括ExcelTypeEnum.XLSX和ExcelTypeEnum.XLS
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 指定檔案的標題行，可以是Class物件（結合@ExcelProperty註解使用），或者List<List<String>>例項
readerBuilder.head(Collections.singletonList(Collections.singletonList("head")));
// 註冊讀取事件的監聽器，預設的資料型別為Map<Integer,String>，第一列的元素的下標從0開始
readerBuilder.registerReadListener(new AnalysisEventListener() {

    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        // 這裡會回撥標題行，檔案內容的首行會認為是標題行
    }

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        // 這裡會回撥每行的資料
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
});
// 構建讀取器
ExcelReader excelReader = readerBuilder.build();
// 讀取資料
excelReader.readAll();
excelReader.finish();

可以看到，讀操作主要使用Builder模式和事件監聽（或者可以理解為觀察者模式）的設計。一般情況下，上面的程式碼可以簡化如下：

Map<Integer, String> head = new HashMap<>();
List<Map<Integer, String>> data = new LinkedList<>();
EasyExcel.read("檔案的絕對路徑").sheet()
        .registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                head.putAll(headMap);
            }

            @Override
            public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
                data.add(row);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    // 這裡可以列印日誌告知所有行讀取完畢
            }
        }).doRead();

如果需要讀取資料並且轉換為對應的物件列表，則需要指定標題行的Class，結合註解@ExcelProperty使用：

檔案內容：

|訂單編號|手機號|
|ORDER_ID_1|112222|
|ORDER_ID_2|334455|

@Data
private static class OrderDTO {

    @ExcelProperty(value = "訂單編號")
    private String orderId;

    @ExcelProperty(value = "手機號")
    private String phone;
}

Map<Integer, String> head = new HashMap<>();
List<OrderDTO> data = new LinkedList<>();
EasyExcel.read("檔案的絕對路徑").head(OrderDTO.class).sheet()
        .registerReadListener(new AnalysisEventListener<OrderDTO>() {

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                head.putAll(headMap);
            }

            @Override
            public void invoke(OrderDTO row, AnalysisContext analysisContext) {
                data.add(row);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 這裡可以列印日誌告知所有行讀取完畢
            }
        }).doRead();

如果資料量巨大，建議使用Map<Integer, String>型別讀取和運算元據物件，否則大量的反射操作會使讀取資料的耗時大大增加，極端情況下，例如屬性多的時候反射操作的耗時有可能比讀取和遍歷的時間長。

接著看寫操作的API：

// 新建一個ExcelWriterBuilder例項
ExcelWriterBuilder writerBuilder = EasyExcel.write();
// 輸出的檔案物件，可以是File、路徑（字串）或者OutputStream例項
writerBuilder.file("");
// 指定sheet，可以是數字序號sheetNo或者字串sheetName，可以不設定，由下面提到的WriteSheet覆蓋
writerBuilder.sheet("");
// 檔案的密碼
writerBuilder.password("");
// Excel檔案格式，包括ExcelTypeEnum.XLSX和ExcelTypeEnum.XLS
writerBuilder.excelType(ExcelTypeEnum.XLSX);
// 是否自動關閉輸出流
writerBuilder.autoCloseStream(true);
// 指定檔案的標題行，可以是Class物件（結合@ExcelProperty註解使用），或者List<List<String>>例項
writerBuilder.head(Collections.singletonList(Collections.singletonList("head")));
// 構建ExcelWriter例項
ExcelWriter excelWriter = writerBuilder.build();
List<List<String>> data = new ArrayList<>();
// 構建輸出的sheet
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("target");
excelWriter.write(data, writeSheet);
// 這一步一定要呼叫，否則輸出的檔案有可能不完整
excelWriter.finish();

ExcelWriterBuilder中還有很多樣式、行處理器、轉換器設定等方法，筆者覺得不常用，這裡不做舉例，內容的樣式通常在輸出檔案之後再次加工會更加容易操作。寫操作一般可以簡化如下：

List<List<String>> head = new ArrayList<>();
List<List<String>> data = new LinkedList<>();
EasyExcel.write("輸出檔案絕對路徑")
        .head(head)
        .excelType(ExcelTypeEnum.XLSX)
        .sheet("target")
        .doWrite(data);

實用技巧

下面簡單介紹一下生產中用到的實用技巧。

多執行緒讀

使用EasyExcel多執行緒讀建議在限定的前提條件下使用：

  • 原始檔已經被分割成多個小檔案，並且每個小檔案的標題行和列數一致。
  • 機器記憶體要充足，因為併發讀取的結果最後需要合併成一個大的結果集，全部資料存放在記憶體中。

經常遇到外部反饋的多份檔案需要緊急進行資料分析或者交叉校對，為了加快檔案讀取，筆者通常使用這種方式批量讀取格式一致的Excel檔案

一個簡單的例子如下：

@Slf4j
public class EasyExcelConcurrentRead {

    static final int N_CPU = Runtime.getRuntime().availableProcessors();

    public static void main(String[] args) throws Exception {
        // 假設I盤的temp目錄下有一堆同格式的Excel檔案
        String dir = "I:\\temp";
        List<Map<Integer, String>> mergeResult = Lists.newLinkedList();
        ThreadPoolExecutor executor = new ThreadPoolExecutor(N_CPU, N_CPU * 2, 0, TimeUnit.SECONDS,
                new LinkedBlockingQueue<>(), new ThreadFactory() {

            private final AtomicInteger counter = new AtomicInteger();

            @Override
            public Thread newThread(@NotNull Runnable r) {
                Thread thread = new Thread(r);
                thread.setDaemon(true);
                thread.setName("ExcelReadWorker-" + counter.getAndIncrement());
                return thread;
            }
        });
        Path dirPath = Paths.get(dir);
        if (Files.isDirectory(dirPath)) {
            List<Future<List<Map<Integer, String>>>> futures = Files.list(dirPath)
                    .map(path -> path.toAbsolutePath().toString())
                    .filter(absolutePath -> absolutePath.endsWith(".xls") || absolutePath.endsWith(".xlsx"))
                    .map(absolutePath -> executor.submit(new ReadTask(absolutePath)))
                    .collect(Collectors.toList());
            for (Future<List<Map<Integer, String>>> future : futures) {
                mergeResult.addAll(future.get());
            }
        }
        log.info("讀取[{}]目錄下的檔案成功,一共載入:{}行資料", dir, mergeResult.size());
        // 其他業務邏輯.....
    }

    @RequiredArgsConstructor
    private static class ReadTask implements Callable<List<Map<Integer, String>>> {

        private final String location;

        @Override
        public List<Map<Integer, String>> call() throws Exception {
            List<Map<Integer, String>> data = Lists.newLinkedList();
            EasyExcel.read(location).sheet()
                    .registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {

                        @Override
                        public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
                            data.add(row);
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                            log.info("讀取路徑[{}]檔案成功,一共[{}]行", location, data.size());
                        }
                    }).doRead();
            return data;
        }
    }
}

這裡採用ThreadPoolExecutor#submit()提交併發讀的任務，然後使用Future#get()等待所有任務完成之後再合併最終的讀取結果。

注意，一般檔案的寫操作不能併發執行，否則很大的概率會導致資料錯亂

多Sheet寫

多Sheet寫，其實就是使用同一個ExcelWriter例項，寫入多個WriteSheet例項中，每個Sheet的標題行可以通過WriteSheet例項中的配置屬性進行覆蓋，程式碼如下：

public class EasyExcelMultiSheetWrite {

    public static void main(String[] args) throws Exception {
        ExcelWriterBuilder writerBuilder = EasyExcel.write();
        writerBuilder.excelType(ExcelTypeEnum.XLSX);
        writerBuilder.autoCloseStream(true);
        writerBuilder.file("I:\\temp\\temp.xlsx");
        ExcelWriter excelWriter = writerBuilder.build();
        WriteSheet firstSheet = new WriteSheet();
        firstSheet.setSheetName("first");
        firstSheet.setHead(Collections.singletonList(Collections.singletonList("第一個Sheet的Head")));
        // 寫入第一個命名為first的Sheet
        excelWriter.write(Collections.singletonList(Collections.singletonList("第一個Sheet的資料")), firstSheet);
        WriteSheet secondSheet = new WriteSheet();
        secondSheet.setSheetName("second");
        secondSheet.setHead(Collections.singletonList(Collections.singletonList("第二個Sheet的Head")));
        // 寫入第二個命名為second的Sheet
        excelWriter.write(Collections.singletonList(Collections.singletonList("第二個Sheet的資料")), secondSheet);
        excelWriter.finish();
    }
}

效果如下：

分頁查詢和批量寫

在一些資料量比較大的場景下，可以考慮分頁查詢和批量寫，其實就是分頁查詢原始資料 -> 資料聚合或者轉換 -> 寫目標資料 -> 下一頁查詢....。其實資料量少的情況下，一次性全量查詢和全量寫也只是分頁查詢和批量寫的一個特例，因此可以把查詢、轉換和寫操作抽象成一個可複用的模板方法：

int batchSize = 定義每篇查詢的條數;
OutputStream outputStream = 定義寫到何處;
ExcelWriter writer = new ExcelWriterBuilder()
        .autoCloseStream(true)
        .file(outputStream)
        .excelType(ExcelTypeEnum.XLSX)
        .head(ExcelModel.class);
for (;;){
    List<OriginModel> list = originModelRepository.分頁查詢();
    if (list.isEmpty()){
        writer.finish();
        break;
    }else {
        list 轉換-> List<ExcelModel> excelModelList;
        writer.write(excelModelList);
    }
}

參看筆者前面寫過的一篇非標題黨生產應用文章《百萬級別資料Excel匯出優化》，適用於大資料量匯出的場景，程式碼如下：

Excel上傳與下載

下面的例子適用於Servlet容器，常見的如Tomcat，應用於spring-boot-starter-web

Excel檔案上傳跟普通檔案上傳的操作差不多，然後使用EasyExcel的ExcelReader讀取請求物件MultipartHttpServletRequest中檔案部分抽象的InputStream例項即可：

@PostMapping(path = "/upload")
public ResponseEntity<?> upload(MultipartHttpServletRequest request) throws Exception {
    Map<String, MultipartFile> fileMap = request.getFileMap();
    for (Map.Entry<String, MultipartFile> part : fileMap.entrySet()) {
        InputStream inputStream = part.getValue().getInputStream();
        Map<Integer, String> head = new HashMap<>();
        List<Map<Integer, String>> data = new LinkedList<>();
        EasyExcel.read(inputStream).sheet()
                .registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {

                    @Override
                    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                        head.putAll(headMap);
                    }

                    @Override
                    public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
                        data.add(row);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        log.info("讀取檔案[{}]成功,一共:{}行......", part.getKey(), data.size());
                    }
                }).doRead();
        // 其他業務邏輯
    }
    return ResponseEntity.ok("success");
}

使用Postman請求如下：

使用EasyExcel進行Excel檔案匯出也比較簡單，只需要把響應物件HttpServletResponse中攜帶的OutputStream物件附著到EasyExcel的ExcelWriter例項即可：

@GetMapping(path = "/download")
public void download(HttpServletResponse response) throws Exception {
    // 這裡檔名如果涉及中文一定要使用URL編碼,否則會亂碼
    String fileName = URLEncoder.encode("檔名.xlsx", StandardCharsets.UTF_8.toString());
    // 封裝標題行
    List<List<String>> head = new ArrayList<>();
    // 封裝資料
    List<List<String>> data = new LinkedList<>();
    response.setContentType("application/force-download");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    EasyExcel.write(response.getOutputStream())
            .head(head)
            .autoCloseStream(true)
            .excelType(ExcelTypeEnum.XLSX)
            .sheet("Sheet名字")
            .doWrite(data);
}

這裡需要注意一下：

  • 檔名如果包含中文，需要進行URL編碼，否則一定會亂碼。
  • 無論匯入或者匯出，如果資料量大比較耗時，使用了Nginx的話記得調整Nginx中的連線、讀寫超時時間的上限配置。
  • 使用SpringBoot需要調整spring.servlet.multipart.max-request-size和spring.servlet.multipart.max-file-size的配置值，避免上傳的檔案過大出現異常。

小結

EasyExcel的API設計簡單易用，可以使用他快速開發有Excel資料匯入或者匯出的場景，實屬提數工具人的喜愛的工具之一。

（本文完 c-3-d e-a-20200909）

相關文章