EasyExcel 上传excel实战

一 概述

        EasyExcel是阿里巴巴的一个开源项目，在官网已经有很清晰明确的讲解，并且附带了通俗易懂的代码，可以点击关于Easyexcel | Easy Excel详细阅读。本文主要在简单实现的基础上，根据真实项目实战需求做的简单扩展实现demo，很多不足望提出后续改进。

二 上传Excel文件代码

        实战内容：上传Excel文件时，可以通过监视器检测到当前行信息的上传情况，以及成功数量。同时可以讲正确和错误的数据保存返给前端展示。

1、监视器，用于实时检测上传的情况

package com.cyl.excel.upload;

import lombok.extern.slf4j.Slf4j;

import javax.validation.constraints.NotBlank;
import java.time.Duration;
import java.time.LocalDateTime;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;

@Slf4j
public class DataImportMonitor {
    private final AtomicInteger succeeded = new AtomicInteger(0);
    private final AtomicInteger failed = new AtomicInteger(0);
    private LocalDateTime startTime;

    public void startImport() {
        startTime = LocalDateTime.now();
    }

    public void finishImport() {
        Duration duration = Duration.between(startTime, LocalDateTime.now());
        log.info("Import finished. Duration: {}h{}m{}s",
                duration.toHoursPart(), duration.toMinutesPart(), duration.toSecondsPart());
    }

    public void succeeded(String code, Integer rowNumber) {
        log.info("Succeed for code {}, row {}", code, rowNumber);
        this.succeeded.incrementAndGet();
    }

    public void failed(@NotBlank String code, Integer rawRowNumber, Exception e) {
        log.warn("Failed for code {}, row {}: {}", code, rawRowNumber, e.getMessage(), e);
        this.failed.incrementAndGet();
    }

    public void logImportedCount() {
        log.info("{} documents succeeded", this.succeeded.get());
        log.info("{} documents failed", this.failed.get());
    }

    public void nonDataReceived() {
        log.warn("Empty excel file");
    }

    public void processRow(String code, Integer rawRowNumber) {
        log.info("Started for code {}, row {}", code, rawRowNumber);
    }

    public <T> void failedForBatch(List<ImportDataRow<T>> batchRows, Function<T, String> getCodeFunction, Exception e) {
        for (ImportDataRow<T> row : batchRows) {
            log.warn("Failed for code {}, row {}: {}", getCodeFunction.apply(row.getData()), row.getRawRowNumber(), e.getMessage(), e);
        }
    }

    public void validationFailed(String code, Integer rowNumber, String errorMessage) {
        log.error("Validation failed for code {}, row {}: {}", code, rowNumber, errorMessage);
        this.failed.incrementAndGet();
    }
}

2、Api层

package com.cyl.excel.upload;

import com.cyl.api.ExcelApi;
import io.lettuce.core.dynamic.annotation.Param;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@AllArgsConstructor
@Slf4j
@RestController
@RequestMapping
public class ExcelApiImpl implements ExcelApi {
    private final UserDocumentImportService userDocumentImportService;
    @PostMapping("/excel/import")
    @Override
    public void importUser(MultipartFile file, DataImportMonitor monitor) throws IOException {
        monitor.startImport();
        userDocumentImportService.importUser(file.getInputStream(), monitor);
        monitor.finishImport();
    }

}

3、导入DTO模型

package com.cyl.excel.upload;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ImportUserDto {
    @ExcelProperty("id")
    private String id;
    @ExcelProperty("name")
    private String name;
    @ExcelProperty("age")
    private String age;
    @ExcelProperty("departmentId")
    private String departmentId;
}

4、构建行数和数据对象，记录行号和行数据，可以用于展示成功与失败的行数据。

package com.cyl.excel.upload;

import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.LinkedList;
import java.util.List;

@Getter
@AllArgsConstructor(staticName = "of")
public class ImportDataRow<T> {
    private final Integer rawRowNumber;

    private final T data;

    public static <T> List<ImportDataRow<T>> from(List<T> allData, Integer lineNumberOffset) {
        List<ImportDataRow<T>> dataRowList = new LinkedList<>();
        for (int i = 0; i < allData.size(); i++) {
            dataRowList.add(ImportDataRow.of(lineNumberOffset + i + 1, allData.get(i)));
        }
        return dataRowList;
    }
}

5、构建ReadAllListen，得到所有的行信息，可以进行数据的额外处理

package com.cyl.excel.upload;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;

import java.util.ArrayList;
import java.util.List;

public class ReadAllListen<T> implements ReadListener<T> {
    List<T> list = new ArrayList<>();

    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    public List<ImportDataRow<T>> getAllRow() {
        return ImportDataRow.from(list, 0);
    }
}

6、获取所有的行的service实现

package com.cyl.excel.upload;

import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.List;

@Component
public interface ParseExcelInterface<T> {
    List<ImportDataRow<T>> parseExcel(InputStream inputStream);
}

package com.cyl.excel.upload;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.List;

@Component
@AllArgsConstructor
public class UserDocumentParse implements ParseExcelInterface<ImportUserDto> {
    @Override
    public List<ImportDataRow<ImportUserDto>> parseExcel(InputStream inputStream) {
        ReadAllListen<ImportUserDto> readAllListen = new ReadAllListen<>();
        try {
            EasyExcelFactory.read(inputStream, ImportUserDto.class, readAllListen)
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(true)
                    .ignoreEmptyRow(true)
                    .autoTrim(true)
                    .sheet(0)
                    .headRowNumber(1)
                    .doRead();
        } catch (Exception e) {
            System.out.println("error");
        }
        return readAllListen.getAllRow();
    }
}

7、service实现，批量处理行数据

package com.cyl.excel.upload;

import org.springframework.stereotype.Component;

import java.io.InputStream;

@Component
public interface UserDocumentImportService {
    void importUser(InputStream inputStream, DataImportMonitor monitor);
}

package com.cyl.excel.upload;

import com.google.common.collect.Lists;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.List;

@Component
@AllArgsConstructor
@NoArgsConstructor
public class UserDocumentImportServiceImpl implements UserDocumentImportService {
    @Value("${data-import.batch-size: 100}")
    private int batch;
    @Autowired
    private UserDocumentParse userDocumentParse;

    @Override
    public void importUser(InputStream inputStream, DataImportMonitor monitor) {
        List<ImportDataRow<ImportUserDto>> exportUserDtos = userDocumentParse.parseExcel(inputStream);
        if (exportUserDtos.isEmpty()) {
            monitor.nonDataReceived();
            return;
        }
        Lists.partition(exportUserDtos, batch).forEach(batch -> {
            try {
                importForBatch(batch, monitor);
            } catch (Exception e) {
               monitor.failedForBatch(batch, ImportUserDto::getId, e);
            }
        });
        monitor.logImportedCount();
    }

    private void importForBatch(List<ImportDataRow<ImportUserDto>> dataRowList, DataImportMonitor monitor) {
        dataRowList.forEach(row -> {
            monitor.processRow(row.getData().getId(), row.getRawRowNumber());
            try {
                valid();
                monitor.succeeded(row.getData().getId(), row.getRawRowNumber());
            } catch (Exception e) {
                monitor.failed(row.getData().getId(), row.getRawRowNumber(), e);
            }
        });
        save();
    }

    private void save() {
        System.out.println("保存数据库！！");
    }

    private void valid() {
        System.out.println("校验数据！！");
    }

}