本文涉及：無

目錄

EasyExcel目錄

前言

EasyExcel使用（二）：公共監(jiān)聽器

背景

在上一章介紹了監(jiān)聽器在提取公共方法的思路族铆，這一章配合通用業(yè)務(wù)場(chǎng)景進(jìn)行增強(qiáng)。

解決方案

主要從幾個(gè)方面進(jìn)行修改哭尝，對(duì)于導(dǎo)入錯(cuò)誤/校驗(yàn)不通過的記錄哥攘，我們需要保存這些數(shù)據(jù)，在返回前端時(shí)進(jìn)行展示提醒材鹦，方便用戶感知導(dǎo)入結(jié)果逝淹；

RowData

public class RowData {

    //行數(shù)
    private Integer rowIndex;

    //excel導(dǎo)入轉(zhuǎn)換后實(shí)體類
    private Object data;

    public RowData(){}

    public RowData(Integer rowIndex, Object data){
        this.rowIndex = rowIndex;
        this.data = data;
    }

}

BaseDataListener

     /**
     * 保存錯(cuò)誤行數(shù)及數(shù)據(jù)
     */
    private final List<RowData> errorList;

     /**
     * 每一條數(shù)據(jù)解析時(shí)操作
     *
     * @param data    已經(jīng)轉(zhuǎn)換成實(shí)體的excel數(shù)據(jù)
     * @param context EasyExcel上下文
     */
    @Override
    public void invoke(T data, AnalysisContext context) {

        LOGGER.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
        //符合校驗(yàn)規(guī)則則添加
        if (!predicate.test(data)) {
            errorList.add(new RowData(
                    context.readRowHolder().getRowIndex()
                    , data));
            return;
        }
        list.add(data);
        // 達(dá)到BATCH_COUNT了，需要去存儲(chǔ)一次數(shù)據(jù)庫桶唐，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存栅葡，容易OOM
        if (list.size() >= BATCH_COUNT) {
            consumer.accept(list);
            list.clear();
        }
    }

     /**
     * 獲取轉(zhuǎn)換異常
     *
     * @param exception ExcelDataConvertException
     * @param context   excel上下文
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 如果是某一個(gè)單元格的轉(zhuǎn)換異常 能獲取到具體行號(hào)
        // 如果要獲取頭的信息 配合doAfterAllAnalysedHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            LOGGER.error("第{}行，第{}列解析異常", excelDataConvertException.getRowIndex() + 1,
                    excelDataConvertException.getColumnIndex() + 1);
            errorList.add(new RowData(
                    context.readRowHolder().getRowIndex()
                    , context.readRowHolder().getCurrentRowAnalysisResult()));
        }
    }

     /**
     * 維護(hù)上傳信息以供前端顯示
     *
     * @return successRowCount:成功行數(shù)
     * errorRowCount:行數(shù)
     * errorRowIndex:錯(cuò)誤行數(shù)定位
     */
    public UploadMessage getUploadMessage(String errorMessage) {
        return new UploadMessage(list.size(), errorList, errorMessage);
    }

UploadMessage

package com.kt.common.entity.excel;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

 /**
 * @author kwongting
 * @version 1.0
 * @date 2021/7/7 10:42
 * @description 上傳信息
 */
@Data
public class UploadMessage {

    @ApiModelProperty(value = "成功行數(shù)")
    private int successRowCount;

    @ApiModelProperty(value = "失敗行數(shù)")
    private int errorRowCount;

    @ApiModelProperty(value = "失敗行數(shù)定位")
    private List<RowData> errorRowData;

    @ApiModelProperty(value = "失敗錯(cuò)誤信息")
    private String errorMessage;

    public UploadMessage() {
    }

    public UploadMessage(String errorMessage) {
        this.errorMessage = errorMessage;
    }

    public UploadMessage(int successRowCount, List<RowData> errorRowData) {
        this(successRowCount,errorRowData,null);
    }

    public UploadMessage(int successRowCount, List<RowData> errorRowData, String errorMessage) {
        this.successRowCount = successRowCount;
        this.errorRowCount = errorRowData.size();
        this.errorRowData = errorRowData;
        this.errorMessage = errorMessage;
    }

}

EasyExcelUtil

    /**
     * 解析EasyExcel導(dǎo)入情況信息
     *
     * @param message BaseDataListener.getUploadMessage()
     * @return 統(tǒng)一響應(yīng)
     */
    public static Response analysisUpload(UploadMessage message) {
        int successRowCount = message.getSuccessRowCount();
        int errorRowCount = message.getErrorRowCount();
        String errorMessage = message.getErrorMessage() == null ? "請(qǐng)核對(duì)字段是否有誤" : message.getErrorMessage();
        List<RowData> rowData = message.getErrorRowData();

        if (successRowCount == 0) {
            return ResultUtils.error(String.format("全部導(dǎo)入失敗尤泽，%s", errorMessage));
        } else {
            if (errorRowCount == 0) {
                return ResultUtils.success(String.format("全部導(dǎo)入成功欣簇！總共%d條", successRowCount));
            } else {
                return ResultUtils.success(String.format("部分導(dǎo)入成功！成功%d條坯约，失敗%d條熊咽，%s", successRowCount, errorRowCount, errorMessage), rowData);
            }
        }
    }

測(cè)試

    @PostMapping("/upload")
    public Response upload(MultipartFile file) {
        return EasyExcelUtil.analysisUpload(service.upload(file));
    }

public UploadMessage upload(MultipartFile file) {
        BaseDataListener<TestPO> baseDataListener = new BaseDataListener<>(
                po -> {
                    if (po的各個(gè)字段驗(yàn)證情況) {
                        return true;
                    }
                    return false;
                },
                    mapper::insertBatchSomeColumn);
        }));
        try {
            EasyExcel.read(file.getInputStream(), TestPO.class, baseDataListener).sheet().doRead();
        } catch (IOException e) {
            LOGGER.error("導(dǎo)入失敗:[{}]", e.getMessage());
            return new UploadMessage(e.getMessage());
        }
        return baseDataListener.getUploadMessage("請(qǐng)判斷[字段名]是否正確");
    }

至此一個(gè)簡(jiǎn)單的監(jiān)聽器就完畢了～下一章分享一下EasyExcel模板下載對(duì)于一些枚舉字段再excel做成下拉框（數(shù)據(jù)有效性）進(jìn)行更友好的輸入～

后記