Excel文件上传工具类
通常系统需要支持通过excel批量上传数据到数据库，需要大量的校验逻辑，故封装以支持公司场景
拓展：如何通过 lambda 表达式获得对应的字段名字？
比如调用 checkDuplicateData(RoadExcelVo::getRoadName) 如何获得 roadName 字段名字
首先需要一个 FunctionalInterface, 这样可以传入 lambda 表达式 注：Java的 Function没有实现 Serializable 接口，不可直接通过 writeReplace 获得
java
@FunctionalInterface
public interface PropertyFunc<T, R> extends Function<T, R>, Serializable {}java
private static <T> String getFieldName(PropertyFunc<T, ?> field) {
Class<?> clazz = field.getClass();
try {
Method method = clazz.getDeclaredMethod("writeReplace");
method.setAccessible(true);
SerializedLambda lambda = (SerializedLambda) method.invoke(field);
return uncapitalize(lambda.getImplMethodName());
} catch (Exception e) {
if (!Object.class.isAssignableFrom(clazz.getSuperclass())) {
return getFieldName(field);
}
throw new RuntimeException("current property is not exists");
}
}使用方法
java
public ImportResultResponseVo importExcel(MultipartFile file) {
File tempFile = null;
try {
tempFile = File.createTempFile(String.valueOf(UUID.randomUUID()), file.getOriginalFilename());
file.transferTo(tempFile);
ExcelHelper<RoadExcelVo> helper = new ExcelHelper<>(ROAD_SHEET_NAME, tempFile, RoadExcelVo.class);
List<BaseExcelData<RoadExcelVo>> excelDataList = helper.readExcel();
ImportResultResponseVo responseVo = helper.getResponseVo();
if (responseVo.getFailCount() > 0) {
return responseVo;
}
if (CollectionUtils.isEmpty(excelDataList)) {
throw DEVICE_INFO_ERROR_0029.buildException();
}
Boolean b1 = helper.checkDuplicateData(RoadExcelVo::getRoadId, RoadExcelVo::getRoadName);
Boolean b2 = helper.checkDataFormat(validator);
Boolean b3 = helper.checkDataInDb(RoadExcelVo::getRoadName, roadName -> roadMapper.checkRoadNameExist(roadName));
Boolean b4 = helper.checkDataInDb(RoadExcelVo::getRoadId, roadName -> roadMapper.checkRoadIdExist(roadName));
if (b1 || b2 || b3 || b4 || responseVo.getFailCount() > 0) {
return responseVo;
}
// 检测areaName是否存在数据库，不存在则记录错误，直接返回
helper.cacheDataAndReportError((excelList, resultResponseVo, cacheMap) -> {
val areaNameRowIdxMap = excelList.stream()
.collect(Collectors.groupingBy(it -> it.getExcelVo().getAreaName(), Collectors.mapping(BaseExcelData::getRowIndex, Collectors.toList())));
List<List<String>> partitions = ListUtils.partition(new ArrayList<>(areaNameRowIdxMap.keySet()), BATCH_INSERT_SIZE);
for (List<String> partition : partitions) {
List<Area> areas = areaMapper.selectByAreaNames(partition);
Set<String> areaNameFromDb = areas.stream().map(Area::getAreaName).collect(Collectors.toSet());
HashSet<String> notFoundAreaNames = Sets.newHashSet(areaNameRowIdxMap.keySet());
notFoundAreaNames.removeAll(areaNameFromDb);
int errorCount = 0;
for (String a : notFoundAreaNames) {
for (Integer row : areaNameRowIdxMap.getOrDefault(a, Collections.emptyList())) {
resultResponseVo.add(ImportError.builder()
.errorMsg("数据库中未找到 所属区域名称: " + a)
.rowIndex(row)
.sheetName(ROAD_SHEET_NAME)
.columnName("所属区域名称（必填）")
.build());
errorCount++;
}
}
resultResponseVo.setFailCount(errorCount);
Map<String, String> areaNameNoMap = areas.stream().collect(Collectors.toMap(Area::getAreaName, Area::getAreaNo));
cacheMap.putAll(areaNameNoMap);
}
});
if (responseVo.getFailCount() > 0) {
return responseVo;
}
UserDetail userDetail = UserContext.userDetail();
String creator = userDetail == null ? "" : (StringUtils.isNotBlank(userDetail.getUserName()) ? userDetail.getUserName() : userDetail.getRealName());
helper.convertWithCacheDataAndInsert(
// convert
(e, cache) -> {
Road road = new Road();
RoadExcelVo excelVo = e.getExcelVo();
String areaName = excelVo.getAreaName();
BeanUtils.copyProperties(excelVo, road);
String areaNo = cache.getOrDefault(areaName, "");
road.setRoadNo(snowflake.nextIdStr());
road.setCreator(creator);
road.setAreaNo(areaNo);
this.initRoadRangeAndLen(road);
return road;
},
// batch insert
roads -> transactionTemplate.execute(transactionStatus -> {
Set<String> areaNos = roads.stream().map(Road::getAreaNo).collect(Collectors.toSet());
areaNos.forEach(areaMapper::increaseRef);
return roadMapper.batchInsert(roads);
})
);
return responseVo;
} catch (BusinessException e) {
log.error(e.getMessage(), e);
throw e;
} finally {
FileUtils.deleteQuietly(tempFile);
}
}util
java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.nio.file.Files;
import java.util.*;
import java.util.function.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@Slf4j
public class ExcelHelper<T> {
private static final String XLSX_ROW_ERROR_INFO = "%s 第%s行 %s 导入错误";
private static final String DB_CHECK_TEMPLATE = "导入失败，数据库数据校验失败 %s";
public static final String EXCEL_SAME_MSG_TEMPLATE = "第%s行 %s 相同,请删除表格中相同 %s 再导入";
private final String sheetName;
private final InputStream inputStream;
private final Class<T> c;
@Getter
private ImportResultResponseVo responseVo;
@Getter
private List<BaseExcelData<T>> excelDataList;
private final Map<String, String> excelPropertyMap;
private final Map<String, String> cacheMap;
public ExcelHelper(String sheetName, File file, Class<T> c) throws IOException {
this(sheetName, Files.newInputStream(file.toPath()), c);
}
public ExcelHelper(String sheetName, InputStream inputStream, Class<T> c) {
this.sheetName = sheetName;
this.inputStream = inputStream;
this.c = c;
this.responseVo = new ImportResultResponseVo();
this.excelDataList = new ArrayList<>();
this.cacheMap = new HashMap<>();
this.excelPropertyMap = Arrays.stream(c.getDeclaredFields()).filter(f -> f.isAnnotationPresent(ExcelProperty.class))
.collect(Collectors.toMap(Field::getName, f -> Optional.ofNullable(f.getAnnotation(ExcelProperty.class).value()).map(arr -> arr[0]).orElse(f.getName())));
}
public List<BaseExcelData<T>> readExcel() {
try (ExcelReader excelReader = EasyExcel.read(inputStream).build()) {
String sheetName = excelReader.excelExecutor().sheetList().get(0).getSheetName();
if (!EXCEL_SHEET0_NAME.equals(sheetName)) {
throw DEVICE_INFO_ERROR_0030.buildException();
}
ReadSheet sheet = EasyExcel.readSheet(this.sheetName)
.head(c)
.registerReadListener(new ExcelAnalysisListener<T>(excelDataList, responseVo)).build();
excelReader.read(sheet);
}
return excelDataList;
}
/**
* 根据类的 getter 方法检测重复数据
*
* @param func
* @return
*/
@SafeVarargs
public final Boolean checkDuplicateData(PropertyFunc<T, Object>... func) {
if (func.length == 0) {
log.warn("needCheckFields is empty");
return true;
}
Map<String, PropertyFunc<T, ?>> funcMap = Stream.of(func)
.collect(Collectors.toMap(
t -> String.valueOf(getFieldName(t)),
Function.identity()));
Map<String, Map<String, List<Integer>>> sameNameMap = new HashMap<>(funcMap.size());
for (BaseExcelData<T> vo : excelDataList) {
T excelVo = vo.getExcelVo();
funcMap.forEach((name, f) -> {
Object fieldVal = f.apply(excelVo);
Map<String, List<Integer>> map = sameNameMap.computeIfAbsent(name, v -> Maps.newHashMap());
map.merge(String.valueOf(fieldVal), Collections.singletonList(vo.getRowIndex()), ListUtils::union);
});
}
sameNameMap.forEach((fieldName, map) -> {
String excelPropertyVal = excelPropertyMap.get(fieldName);
map.forEach((fieldVal, dupList) -> {
if (dupList.size() < 2) {
return;
}
for (Integer rowIndex : dupList) {
ImportError importError = new ImportError(sheetName, rowIndex, excelPropertyVal, String.format(EXCEL_SAME_MSG_TEMPLATE, rowIndex, excelPropertyVal, excelPropertyVal));
responseVo.add(importError);
}
});
});
return responseVo.getFailCount() > 0;
}
/**
* 根据类的 字段名字检测重复数据
*
* @return
*/
public Boolean checkDuplicateData(String... needCheckFields) {
if (needCheckFields.length == 0) {
log.warn("needCheckFields is empty");
return true;
}
HashSet<String> checkSet = Sets.newHashSet(needCheckFields);
Map<String, Field> fieldsNeedCheckDup = Arrays.stream(c.getDeclaredFields())
.filter(field -> checkSet.contains(field.getName()))
.collect(Collectors.toMap(Field::getName, f -> f));
Collection<Field> fieldsWithAnno = fieldsNeedCheckDup.values();
Map<String, Map<String, List<Integer>>> sameNameMap = new HashMap<>(fieldsWithAnno.size());
for (Field field : fieldsWithAnno) {
String name = field.getName();
sameNameMap.put(name, new HashMap<>());
}
for (BaseExcelData<T> vo : excelDataList) {
T excelVo = vo.getExcelVo();
for (Field field : fieldsWithAnno) {
String name = field.getName();
Object fieldVal = null;
try {
field.setAccessible(true);
fieldVal = field.get(excelVo);
} catch (IllegalAccessException e) {
log.error(e.getMessage(), e);
throw new RuntimeException(e);
}
Map<String, List<Integer>> map = sameNameMap.get(name);
map.merge(String.valueOf(fieldVal), Collections.singletonList(vo.getRowIndex()), ListUtils::union);
}
}
sameNameMap.forEach((fieldName, map) -> {
Field field = fieldsNeedCheckDup.get(fieldName);
String excelPropertyVal = Optional.ofNullable(field.getAnnotation(ExcelProperty.class))
.map(ExcelProperty::value)
.map(arr -> arr[0])
.orElse(fieldName);
map.forEach((fieldVal, dupList) -> {
if (dupList.size() < 2) {
return;
}
for (Integer rowIndex : dupList) {
ImportError importError = new ImportError(sheetName, rowIndex, excelPropertyVal, String.format(EXCEL_SAME_MSG_TEMPLATE, rowIndex, excelPropertyVal, excelPropertyVal));
responseVo.add(importError);
}
});
});
return responseVo.getFailCount() > 0;
}
/**
* 检测数据是否存在db
*
* @param valTransFunc 转换为mapper查询参数的函数
* @param mapperCheck 查询函数
* @param <F>
* @return
*/
public <F> Boolean checkDataInDb(PropertyFunc<T, F> valTransFunc,
PropertyPredicate<F> mapperCheck) {
String fieldName = getFieldName(valTransFunc);
String excelProperty = excelPropertyMap.getOrDefault(fieldName, fieldName);
excelDataList.forEach(x -> {
if (mapperCheck.testOrFalse(valTransFunc.apply(x.getExcelVo()))) {
responseVo.add(new ImportError(sheetName, x.getRowIndex(), excelProperty, String.format(DB_CHECK_TEMPLATE, valTransFunc.apply(x.getExcelVo()))));
}
});
return responseVo.getFailCount() > 0;
}
/**
* 检测数据是否存在db
*
* @param valTransFunc 转换为mapper查询参数的函数
* @param mapperCheck 查询函数
* @param msg 错误消息
* @param <F>
* @return
*/
public <F> Boolean checkDataInDb(PropertyFunc<T, F> valTransFunc,
Predicate<F> mapperCheck,
String msg) {
excelDataList.forEach(x -> {
if (mapperCheck.test(valTransFunc.apply(x.getExcelVo()))) {
responseVo.add(new ImportError(sheetName, x.getRowIndex(), msg, String.format(DB_CHECK_TEMPLATE, valTransFunc.apply(x.getExcelVo()))));
}
});
return responseVo.getFailCount() > 0;
}
/**
* 检测是否 符合 validator 格式
*
* @param validator
* @return
*/
public Boolean checkDataFormat(Validator validator) {
Iterator<BaseExcelData<T>> iterator = excelDataList.iterator();
while (iterator.hasNext()) {
BaseExcelData<T> red = iterator.next();
T excelVo = red.getExcelVo();
Set<ConstraintViolation<T>> violations = validator.validate(excelVo);
if (CollectionUtils.isNotEmpty(violations)) {
for (ConstraintViolation<T> violation : violations) {
String columnName = getColumnName(violation.getPropertyPath().toString(), ReflectUtil.getFields(violation.getRootBeanClass()));
String errorMsg = violation.getMessage();
responseVo.add(new ImportError(sheetName, red.getRowIndex(), columnName, errorMsg));
}
iterator.remove();
}
}
return responseVo.getFailCount() > 0;
}
public interface MyConsumer<DATA, ERROR, CACHE> {
void accept(DATA d, ERROR e, CACHE cache);
}
public void cacheDataAndReportError(MyConsumer<List<BaseExcelData<T>>, ImportResultResponseVo, Map<String, String>> c) {
c.accept(this.excelDataList, this.responseVo, this.cacheMap);
}
public void cacheDataAndReportError(BiConsumer<List<BaseExcelData<T>>, ImportResultResponseVo> c) {
c.accept(this.excelDataList, this.responseVo);
}
public <ENTITY> void convertAndInsert(
Function<BaseExcelData<T>, ENTITY> dataConvert,
Function<List<ENTITY>, Integer> consumer) {
convertAndInsert(dataConvert, null, consumer);
}
public <ENTITY> void convertWithCacheDataAndInsert(
BiFunction<BaseExcelData<T>, Map<String, String>, ENTITY> dataConvert,
Function<List<ENTITY>, Integer> consumer) {
// convert data
List<ENTITY> dataInset = excelDataList.stream()
.map(d -> dataConvert.apply(d, cacheMap)).collect(Collectors.toList());
// insert data
List<List<ENTITY>> partitions = ListUtils.partition(dataInset, BATCH_INSERT_SIZE);
for (List<ENTITY> partition : partitions) {
Integer effectRows = consumer.apply(partition);
int size = partition.size();
if (effectRows != size) {
log.warn("batch insert row not eq list size, rows affected: {}, list size: {}", effectRows, size);
}
responseVo.setSuccessCount(responseVo.getSuccessCount() + size);
}
}
public <ENTITY> void convertAndInsert(
Function<BaseExcelData<T>, ENTITY> dataConvert,
Consumer<List<ENTITY>> check,
Function<List<ENTITY>, Integer> consumer) {
// convert data
List<ENTITY> dataInset = excelDataList.stream().map(dataConvert).collect(Collectors.toList());
// check data
if (check != null) {
check.accept(dataInset);
}
// insert data
List<List<ENTITY>> partitions = ListUtils.partition(dataInset, BATCH_INSERT_SIZE);
for (List<ENTITY> partition : partitions) {
Integer effectRows = consumer.apply(partition);
int size = partition.size();
if (effectRows != size) {
log.warn("batch insert row not eq list size, rows affected: {}, list size: {}", effectRows, size);
}
responseVo.setSuccessCount(responseVo.getSuccessCount() + size);
}
}
private static String getColumnName(String fieldName, Field[] fields) {
for (Field f : fields) {
if (f.getName().equals(fieldName)) {
ExcelProperty excelProperty = f.getAnnotation(ExcelProperty.class);
if (excelProperty != null && ArrayUtil.isNotEmpty(excelProperty.value())) {
return excelProperty.value()[0];
}
}
}
return null;
}
public static class ExcelAnalysisListener<T> extends AnalysisEventListener<T> {
List<BaseExcelData<T>> excelDataList;
ImportResultResponseVo responseVo;
public ExcelAnalysisListener(List<BaseExcelData<T>> excelDataList, ImportResultResponseVo responseVo) {
this.excelDataList = excelDataList;
this.responseVo = responseVo;
}
@Override
public void onException(Exception exception, AnalysisContext context) {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException ex = (ExcelDataConvertException) exception;
Integer rowIndex = ex.getRowIndex();
ExcelProperty excelProperty = ex.getExcelContentProperty().getField().getDeclaredAnnotation(ExcelProperty.class);
String columnName = excelProperty.value()[0];
String errorMsg = String.format(XLSX_ROW_ERROR_INFO, context.readSheetHolder().getSheetName(), rowIndex, columnName);
responseVo.add(new ImportError(context.readSheetHolder().getSheetName(), rowIndex, columnName, errorMsg));
} else {
log.error("read excel data fail", exception);
}
}
@Override
public void invoke(T excelVo, AnalysisContext context) {
Util.trimObjectStrings(excelVo);
Integer rowIndex = context.readRowHolder().getRowIndex();
BaseExcelData<T> excelData = new BaseExcelData<>();
excelData.setRowIndex(rowIndex);
excelData.setExcelVo(excelVo);
excelDataList.add(excelData);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}
@FunctionalInterface
public interface PropertyFunc<T, R> extends Function<T, R>, Serializable {
}
@FunctionalInterface
public interface PropertyPredicate<T> extends Serializable {
/**
* Evaluates this predicate on the given argument.
*
* @param t the input argument
* @return {@code true} if the input argument matches the predicate,
* otherwise {@code false}
*/
Boolean test(T t);
default boolean testOrFalse(T t) {
return this.test(t) != null && this.test(t);
}
}
private static <T> String getFieldName(PropertyFunc<T, ?> field) {
Class<?> clazz = field.getClass();
try {
Method method = clazz.getDeclaredMethod("writeReplace");
method.setAccessible(true);
SerializedLambda lambda = (SerializedLambda) method.invoke(field);
return uncapitalize(lambda.getImplMethodName());
} catch (Exception e) {
if (!Object.class.isAssignableFrom(clazz.getSuperclass())) {
return getFieldName(field);
}
throw new RuntimeException("current property is not exists");
}
}
/**
* 去掉方法的 get/is 前缀，首字母小写
*/
private static String uncapitalize(String str) {
if (str == null || str.length() < 4) {
return str;
}
String fieldName = str.startsWith("get") ?
str.substring(3) : str.startsWith("is") ? str.substring(2) : str;
return fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);
}
}