單行表頭驗(yàn)證
導(dǎo)入模板：
單行表頭：
單行表頭.png
實(shí)體類(注意:加上下標(biāo)不容易出錯)
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 = "用戶手機(jī)號",index = 0)
private String userMobile;
@ExcelProperty(value = "用戶姓名",index = 1)
private String userName;
}
監(jiān)聽器
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) {
//需要處理的業(yè)務(wù)
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//驗(yàn)證通過后要做的業(yè)務(wù)
}
/**
* 獲取表頭(兩行)
*
* @param headMap
* @param context
*/
@SneakyThrows
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//單行表頭驗(yàn)證方式
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");
}
//數(shù)據(jù)開始行（除去表頭從0開始數(shù)）
ExcelReaderSheetBuilder sheet = read.sheet().headRowNumber(0);
sheet.doRead();
}
}
兩行表頭驗(yàn)證
多行表頭.png
實(shí)體類
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;
/**
* 獎品實(shí)體類
*/
@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;
}
監(jiān)聽器
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) {
//業(yè)務(wù)處理
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//業(yè)務(wù)處理完存入數(shù)據(jù)庫
List<String> prizeTestExceptionKey = redisCache.getCacheList("prizeTestExceptionKey");
if (CollectionUtils.isNotEmpty(prizeTestExceptionKey)) {
log.info("表頭驗(yàn)證失敗！{}", prizeTestExceptionKey);
} else {
log.info("表頭驗(yàn)證成功请垛！");
}
}
/**
* 獲取表頭(兩行)
*
* @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");
}
//數(shù)據(jù)開始行（除去表頭從0開始數(shù)）
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:表頭驗(yàn)證工具類
*/
@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));
}
//合并單元格的表格亿扁，默認(rèn)值為第一個單元格的表谊，被合并的其他單元格為null;循環(huán)遍歷药有，把合并單元格的每一個單元格都填為一樣的值
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))) {
//判斷如果當(dāng)前單元格為空介劫，則把都二行表頭合并到第一行表頭
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("解析到一條數(shù)據(jù){}", 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;
}
/**
* 驗(yàn)證單表單表頭
*
* @param fields 實(shí)體的字段列表
* @param titleList 表頭字段列表
* @param exceptionName 異常名稱(key)
* @param num 需要驗(yàn)證的字段個數(shù)
*/
public static void validatorSimpleExcelHeads(Field[] fields, List titleList, String exceptionName, Integer num) {
RedisCache redisCache = SpringUtils.getBean(RedisCache.class);
//判斷需要驗(yàn)證的表頭個數(shù)是否等于需要驗(yàn)證的字段個數(shù)
if (num == titleList.size()) {
// 遍歷字段進(jìn)行判斷
for (Field field : fields) {
// 獲取當(dāng)前字段上的ExcelProperty注解信息
ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
// 判斷當(dāng)前字段上是否存在ExcelProperty注解
if (fieldAnnotation != null) {
// 存在ExcelProperty注解則根據(jù)注解的index索引到表頭中獲取對應(yīng)的表頭名
Object tile = titleList.get(fieldAnnotation.index());
// 判斷表頭是否為空或是否和當(dāng)前字段設(shè)置的表頭名不相同
if ("".equals(tile.toString()) || !tile.toString().equals(fieldAnnotation.value()[0])) {
// 如果為空或不相同，則拋出異常不再往下執(zhí)行
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤座韵，請檢查導(dǎo)入模板!錯誤表頭：" + tile);
throw new EasyException("模板錯誤险绘，請檢查導(dǎo)入模板!", redisCache.getCacheList(exceptionName));
}
}
}
} else {
// 如果為空或不相同，則拋出異常不再往下執(zhí)行
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤誉碴，導(dǎo)入的表格頭過多宦棺，請檢查導(dǎo)入模板");
throw new EasyException("模板錯誤，導(dǎo)入的表格頭過多黔帕，請檢查導(dǎo)入模板!", redisCache.getCacheList(exceptionName));
}
}
/**
* 單張表復(fù)雜表頭的驗(yàn)證
*
* @param fields 實(shí)體的字段列表
* @param titleList 表頭字段列表
* @param exceptionName 異常名稱(key)
* @param num 需要驗(yàn)證的字段個數(shù)
*/
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()) {
// 遍歷字段進(jìn)行判斷
for (Field field : fields) {
// 獲取當(dāng)前字段上的ExcelProperty注解信息
ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
// 判斷當(dāng)前字段上是否存在ExcelProperty注解
if (fieldAnnotation != null) {
// 存在ExcelProperty注解則根據(jù)注解的index索引到表頭中獲取對應(yīng)的表頭名
title = titleList.get(fieldAnnotation.index());
//多級表頭驗(yàn)證
if (title.toString().contains("-")) {
try {
if ("".equals(title) || !(title.equals(fieldAnnotation.value()[0] + "-" + fieldAnnotation.value()[1]))) {
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤代咸，請檢查導(dǎo)入模板!錯誤：" + title);
throw new EasyException("模板錯誤，請檢查導(dǎo)入模板", redisCache.getCacheList(exceptionName));
}
} catch (Exception e) {
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤成黄，請檢查導(dǎo)入模板!錯誤：" + title);
throw new EasyException("模板錯誤呐芥，請檢查導(dǎo)入模板", redisCache.getCacheList(exceptionName));
}
} else {
// 判斷表頭是否為空或是否和當(dāng)前字段設(shè)置的表頭名不相同
if ("".equals(title) || !title.equals(fieldAnnotation.value()[0])) {
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤，請檢查導(dǎo)入模板!錯誤：" + title);
throw new EasyException("模板錯誤奋岁，請檢查導(dǎo)入模板", redisCache.getCacheList(exceptionName));
}
}
}
}
} else {
redisCache.redisTemplate.opsForList().rightPush(exceptionName, "模板錯誤思瘟，導(dǎo)入的表格頭過多，請檢查導(dǎo)入模板!");
throw new EasyException("模板錯誤闻伶，導(dǎo)入的表格頭過多滨攻，請檢查導(dǎo)入模板", redisCache.getCacheList(exceptionName));
}
}
/**
* 驗(yàn)證單元格格式
*
* @param exception
*/
public void checkUnitForm(Exception exception) {
List<String> list = new ArrayList<>();
if (exception instanceof EasyException) {
list.add(exception.getMessage());
throw new EasyException("導(dǎo)入失敗蓝翰！", list);
} else if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
//格式不匹配異常
String str = ("第" + (excelDataConvertException.getRowIndex() + 1) + "行" + (excelDataConvertException.getColumnIndex() + 1) + "列格式解析錯誤光绕，請檢查該單元格數(shù)據(jù)的格式！");
list.add(str);
throw new EasyException("導(dǎo)入失旜俊奇钞！", list);
}
}
}
