单行表头验证

导入模板：

单行表头： 单行表头.png

实体类(注意:加上下标不容易出错)

package com.customer.sekill.provider.domin;

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


/*
 * @author  wyz
 * @date 2022/12/8 11:25
 * @Deacription:用户对象
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class BindBoxRecordTest {


    @ExcelProperty(value = "用户手机号",index = 0)
    private String userMobile;

    @ExcelProperty(value = "用户姓名",index = 1)
    private String userName;

}

监听器

package com.customer.sekill.provider.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.customer.sekill.provider.domin.BindBoxRecordTest;
import com.customer.sekill.provider.utils.EasyExcelValidator;
import com.customer.sekill.provider.utils.RedisCache;
import com.customer.sekill.provider.utils.SpringUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

@Slf4j
public class DemoDataListener extends AnalysisEventListener<BindBoxRecordTest> {
    EasyExcelValidator easyExcelValidator = new EasyExcelValidator();
    RedisCache redisCache = SpringUtils.getBean(RedisCache.class);


    @Override
    public void invoke(BindBoxRecordTest prizeTest, AnalysisContext analysisContext) {
         //需要处理的业务
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
     //验证通过后要做的业务
    }

    /**
     * 获取表头(两行)
     *
     * @param headMap
     * @param context
     */
    @SneakyThrows
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//单行表头验证方式
        List excelTitle = easyExcelValidator.getExcelTitle(headMap, context);
        easyExcelValidator.validatorSimpleExcelHeads(BindBoxRecordTest.class.getDeclaredFields(), excelTitle, "prizeTestExceptionKey", 2);
    }
}

controller层

@Slf4j
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private RedisCache redisCache;

    @GetMapping("/excelTest")
    public void excelTest(MultipartFile file) {

        ExcelReaderBuilder read = null;
        try {
            read = EasyExcel.read(file.getInputStream(), BindBoxRecordTest.class, new DemoDataListener());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            redisCache.deleteObject("prizeTestExceptionKey");
        }
        //数据开始行（除去表头从0开始数）
        ExcelReaderSheetBuilder sheet = read.sheet().headRowNumber(0);
        sheet.doRead();
    }

}

两行表头验证

多行表头.png

实体类

package com.customer.sekill.provider.domin;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 奖品实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class PrizeTest {
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    @ExcelIgnore
    private Long id;

    /**
     * 奖品名
     */
    @Excel(name = "奖品名")
    @ExcelProperty(value = {"奖品名", "A奖品名"}, index = 0)
    private String prizeNameA;

    /**
     * 奖品名
     */
    @Excel(name = "奖品名")
    @ExcelProperty(value = {"奖品名", "B奖品名"}, index = 1)
    private String prizeNameB;

    /**
     * 奖品名
     */
    @Excel(name = "奖品名")
    @ExcelProperty(value = {"奖品名", "C奖品名"}, index = 2)
    private String prizeNameC;

    /**
     * 中奖概率
     */
    @Excel(name = "A奖品中奖概率")
    @ExcelProperty(value = {"奖品概率", "A奖品中奖概率"}, index = 3)
    private Double probabilityA;

    /**
     * B奖品中奖概率
     */
    @Excel(name = "奖品概率")
    @ExcelProperty(value = {"奖品概率", "B奖品中奖概率"}, index = 4)
    private Double probabilityB;

    /**
     * 奖品颜色
     */
    @Excel(name = "奖品颜色")
    @ExcelProperty(value = {"奖品颜色", "红色"}, index = 5)
    private String colorA;

    /**
     * 奖品颜色
     */
    @Excel(name = "奖品颜色")
    @ExcelProperty(value = {"奖品颜色", "黄色"}, index = 6)
    private String colorB;

    /**
     * 奖品颜色
     */
    @Excel(name = "奖品颜色")
    @ExcelProperty(value = {"奖品颜色", "绿色"}, index = 7)
    private String colorC;
}

监听器

package com.customer.sekill.provider.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.customer.sekill.provider.domin.PrizeTest;
import com.customer.sekill.provider.utils.EasyExcelValidator;
import com.customer.sekill.provider.utils.RedisCache;
import com.customer.sekill.provider.utils.SpringUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

@Slf4j
public class PrizeTestListener extends AnalysisEventListener<PrizeTest> {
    EasyExcelValidator easyExcelValidator = new EasyExcelValidator();
    RedisCache redisCache = SpringUtils.getBean(RedisCache.class);


    @Override
    public void invoke(PrizeTest prizeTest, AnalysisContext analysisContext) {
        //业务处理
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //业务处理完存入数据库
        List<String> prizeTestExceptionKey = redisCache.getCacheList("prizeTestExceptionKey");
        if (CollectionUtils.isNotEmpty(prizeTestExceptionKey)) {
            log.info("表头验证失败！{}", prizeTestExceptionKey);
        } else {
            log.info("表头验证成功！");
        }
    }

    /**
     * 获取表头(两行)
     *
     * @param headMap
     * @param context
     */
    @SneakyThrows
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        Integer approximateTotalRowNumber = context.readSheetHolder().getHeadRowNumber() - 1;
        List excelTitle = easyExcelValidator.getExcelTitles(headMap, context);
        if (context.readRowHolder().getRowIndex() == approximateTotalRowNumber) {
            log.info("解析到一行表头{}", excelTitle);
            easyExcelValidator.validatorExcelComHeads(PrizeTest.class.getDeclaredFields(), excelTitle, "prizeTestExceptionKey", 8);
        }

    }
}

controller

@Slf4j
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private RedisCache redisCache;

    @GetMapping("/prizeTestTest")
    public void excelTest(MultipartFile file) {

        ExcelReaderBuilder read = null;
        try {
            read = EasyExcel.read(file.getInputStream(), BindBoxRecordTest.class, new DemoDataListener());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            redisCache.deleteObject("prizeTestExceptionKey");
        }
        //数据开始行（除去表头从0开始数）
        ExcelReaderSheetBuilder sheet = read.sheet().headRowNumber(2);
        sheet.doRead();
    }

}

工具类

package com.customer.sekill.provider.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/*
 * @author  wyz
 * @date 2023/1/10 9:28
 * @Deacription:表头验证工具类
 */
@Slf4j
public class EasyExcelValidator {
    List keyList = new ArrayList<>();
    List temptList = new ArrayList<>();
    List resultList = new ArrayList<>();

    /**
     * 获取表头(两行）
     *
     * @param headMap
     * @param context
     * @return
     */
    public List getExcelTitles(Map<Integer, String> headMap, AnalysisContext context) {
        //合并表头
        //遍历获取第一行和第二行表头，存入keyList
        Set<Integer> integerSet = headMap.keySet();
        for (int i1 = 0; i1 < integerSet.size(); i1++) {
            keyList.add(headMap.get(i1));
        }
        //合并单元格的表格，默认值为第一个单元格的，被合并的其他单元格为null;循环遍历，把合并单元格的每一个单元格都填为一样的值
        if (context.readRowHolder().getRowIndex() == 0 && CollectionUtils.isNotEmpty(keyList)) {
            for (int i = 0; i < keyList.size(); i++) {
                if (i != 0 && keyList.get(i) == null) {
                    keyList.set(i, keyList.get(i - 1));
                } else {
                    keyList.set(i, keyList.get(i));
                }
            }
        }


        //遍历表头，把两行表头合为一行，第一行合第二行用"-"连接
        if (context.readRowHolder().getRowIndex() == 1 && CollectionUtils.isNotEmpty(keyList)) {
            for (int i = 0; i < keyList.size() / 2; i++) {
                if (i != 0 && keyList.get(i - 1).equals(keyList.get(i))) {
                    //判断如果当前单元格为空，则把都二行表头合并到第一行表头
                    temptList.add(i - 1, keyList.get(i - 1) + "-" + keyList.get(keyList.size() / 2 + i - 1));
                    temptList.add(i, keyList.get(i - 1) + "-" + keyList.get(keyList.size() / 2 + i));
                    continue;
                }
                temptList.add(i, keyList.get(i));
            }
            resultList.addAll(temptList.subList(0, keyList.size() / 2));
            log.info("解析到一条数据{}", resultList);
        }
        return resultList;
    }


    /**
     * 获取表头(一行）
     *
     * @param headMap
     * @param context
     * @return
     */
    public List getExcelTitle(Map<Integer, String> headMap, AnalysisContext context) {
        //合并表头
        //遍历获取第一行和第二行表头，存入keyList
        Set<Integer> integerSet = headMap.keySet();
        for (int i1 = 0; i1 < integerSet.size(); i1++) {
            keyList.add(headMap.get(i1));
        }
        return keyList;
    }


    /**
     * 验证单表单表头
     *
     * @param fields        实体的字段列表
     * @param titleList     表头字段列表
     * @param exceptionName 异常名称(key)
     * @param num           需要验证的字段个数
     */
    public static void validatorSimpleExcelHeads(Field[] fields, List titleList, String exceptionName, Integer num) {
        RedisCache redisCache = SpringUtils.getBean(RedisCache.class);
        //判断需要验证的表头个数是否等于需要验证的字段个数
        if (num == titleList.size()) {
            // 遍历字段进行判断
            for (Field field : fields) {
                // 获取当前字段上的ExcelProperty注解信息
                ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
                // 判断当前字段上是否存在ExcelProperty注解
                if (fieldAnnotation != null) {
                    // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名
                    Object tile = titleList.get(fieldAnnotation.index());
                    // 判断表头是否为空或是否和当前字段设置的表头名不相同
                    if ("".equals(tile.toString()) || !tile.toString().equals(fieldAnnotation.value()[0])) {
                        // 如果为空或不相同，则抛出异常不再往下执行
                        redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，请检查导入模板!错误表头：" + tile);
                        throw new EasyException("模板错误，请检查导入模板!", redisCache.getCacheList(exceptionName));
                    }
                }
            }
        } else {
            // 如果为空或不相同，则抛出异常不再往下执行
            redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，导入的表格头过多，请检查导入模板");
            throw new EasyException("模板错误，导入的表格头过多，请检查导入模板!", redisCache.getCacheList(exceptionName));
        }
    }


    /**
     * 单张表复杂表头的验证
     *
     * @param fields        实体的字段列表
     * @param titleList     表头字段列表
     * @param exceptionName 异常名称(key)
     * @param num           需要验证的字段个数
     */
    public void validatorExcelComHeads(Field[] fields, List titleList, String exceptionName, Integer num) throws Exception {
        Object title = null;
        RedisCache redisCache = SpringUtils.getBean(RedisCache.class);
        if (num == titleList.size()) {
            // 遍历字段进行判断
            for (Field field : fields) {
                // 获取当前字段上的ExcelProperty注解信息
                ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
                // 判断当前字段上是否存在ExcelProperty注解
                if (fieldAnnotation != null) {
                    // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名
                    title = titleList.get(fieldAnnotation.index());
                    //多级表头验证
                    if (title.toString().contains("-")) {
                        try {
                            if ("".equals(title) || !(title.equals(fieldAnnotation.value()[0] + "-" + fieldAnnotation.value()[1]))) {
                                redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，请检查导入模板!错误：" + title);
                                throw new EasyException("模板错误，请检查导入模板", redisCache.getCacheList(exceptionName));
                            }
                        } catch (Exception e) {
                            redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，请检查导入模板!错误：" + title);
                            throw new EasyException("模板错误，请检查导入模板", redisCache.getCacheList(exceptionName));
                        }
                    } else {
                        // 判断表头是否为空或是否和当前字段设置的表头名不相同
                        if ("".equals(title) || !title.equals(fieldAnnotation.value()[0])) {
                            redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，请检查导入模板!错误：" + title);
                            throw new EasyException("模板错误，请检查导入模板", redisCache.getCacheList(exceptionName));
                        }
                    }
                }
            }
        } else {
            redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板错误，导入的表格头过多，请检查导入模板!");
            throw new EasyException("模板错误，导入的表格头过多，请检查导入模板", redisCache.getCacheList(exceptionName));
        }
    }


    /**
     * 验证单元格格式
     *
     * @param exception
     */
    public void checkUnitForm(Exception exception) {
        List<String> list = new ArrayList<>();
        if (exception instanceof EasyException) {
            list.add(exception.getMessage());
            throw new EasyException("导入失败！", list);
        } else if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            //格式不匹配异常
            String str = ("第" + (excelDataConvertException.getRowIndex() + 1) + "行" + (excelDataConvertException.getColumnIndex() + 1) + "列格式解析错误，请检查该单元格数据的格式！");
            list.add(str);
            throw new EasyException("导入失败！", list);
        }
    }
}