最近做了两个项目，都有excel导入，而且代码量比较大，重性比较高，也没有什么技术含量
为了解决以后更快的开发导入，梳理出了一套可以借鉴的模版供大家参考。
java 8 、Springboot、lombok、commons-lang3、hibernate-validator、Collection4
<dependency>
<groupId>org.projectlombokgroupId>
<artifactId>lombokartifactId>
dependency>
<dependency>
<groupId>cn.afterturngroupId>
<artifactId>easypoi-spring-boot-starterartifactId>
<version>4.3.0version>
dependency>
<dependency>
<groupId>org.apache.commonsgroupId>
<artifactId>commons-lang3artifactId>
<version>3.12.0version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-validatorartifactId>
<version>6.0.1.Finalversion>
dependency>
1、读取excel内容
2、把excel行转成行对象
3、验证必填项「不符合规则删除」
4、验证格式「不符合规则删除」
5、验证重复项「不符合规则删除」
6、自定义规则验证
7、分组：如果满足5，需要进行处理是更新还是插入
@Data
@Accessors(chain = true)
@ApiModel
public class ExcelVerifyVo<T> {
@ApiModelProperty(example = "总条数")
private int total;
@ApiModelProperty(example = "正确数据条数")
private int rightTotal;
@ApiModelProperty(example = "异常数据条数")
private int errorTotal;
@ApiModelProperty(example = "不能为空提示")
private List<String> emptyErrorHint = new ArrayList<>();
@ApiModelProperty(example = "信息格式错误提示")
private List<String> formatErrorHint = new ArrayList<>();
@ApiModelProperty(example = "编号重复提示")
private List<String> repetitiveErrorHint = new ArrayList<>();
@ApiModelProperty(example = "丢弃的编号提示")
private List<String> rejectErrorHint = new ArrayList<>();
@ApiModelProperty(example = "编号存在提示")
private List<String> existHint = new ArrayList<>();
@JsonIgnore
private List<T> addList = new ArrayList<>();
@JsonIgnore
private List<T> updateList = new ArrayList<>();
}
@Data
@Accessors(chain = true)
public class ExcelRowDetail {
private String sheetName;
private int rowNo;
public static String getSheetNameFieldName(){
return "sheetName";
}
public static String getRowNoFieldName(){
return "rowNo";
}
/**
* @description: 获取类属性名称对应excel列，默认是属性名称【有机会会补全order注解】
* @params: [clazz]
* @return: java.util.Map
* @throws
*/
public static Map<Integer,String> getFieldMappingExcelColumnMap(Class clazz){
Map<Integer,String> fieldMap = Maps.newHashMap();
Field[] Fields = clazz.getDeclaredFields();
if(!Objects.isNull(Fields) && Fields.length > 0){
int colNum = 0;
for(Field field : Fields){
ExcelFeildIgnore annotation = field.getAnnotation(ExcelFeildIgnore.class);
if(Objects.isNull(annotation)){
fieldMap.put(colNum,field.getName());
colNum ++;
}
}
}
return fieldMap;
}
}
@Data
@Accessors(chain = true)
public class ExcelContent extends ExcelRowDetail{
@ApiModelProperty(name = "")
@NotBlank
private String code;
@ApiModelProperty(name = "")
@NotBlank
@Pattern(regexp = RegexpConsts.PIMERF_REGEXP)
private String name;
}
@UtilityClass
public class ExcelUtil {
/**
* @description: 读取文件内容
* @params: [file, rowCount, columnCount]
* @return: java.util.List
* @throws
*/
@SneakyThrows
public static List<ExcelSheetPo> readExcel(MultipartFile file){
Workbook wb = getWorkbook(file.getInputStream(),file.getOriginalFilename());
return readExcelContent(wb);
}
public static String getSuffixByName(String filename) {
String[] arr = filename.split("\\.");
int suffixIndex = arr.length - 1;
return strArray[suffixIndex];
}
@SneakyThrows
public static Workbook getWorkbook(InputStream inputStream, String fileName){
String extName = getSuffixByName(fileName);
Workbook wb;
if (ExcelVersion.V2003.getSuffix().equals(extName)) {
wb = new HSSFWorkbook(inputStream);
} else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
wb = new XSSFWorkbook(inputStream);
} else {
throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"Invalid excel version");
}
return wb;
}
/**
* @description: 只获取第一个sheet页
* @params: [wb, rowCount, columnCount]
* @return: java.util.List
* @throws
*/
public static List<ExcelSheetPo> readExcelContent(Workbook wb){
if(Objects.isNull(wb)){
throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"文件不存在Sheet页！");
}
// 开始读取数据
List<ExcelSheetPo> sheetPOs = new ArrayList<>();
// 解析sheet
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<Object>> dataList = new ArrayList<>();
ExcelSheetPo sheetPO = new ExcelSheetPo();
sheetPO.setSheetName(sheet.getSheetName());
sheetPO.setDataList(dataList);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
// 解析sheet 的行
for (int j = firstRowNum; j <= lastRowNum; j++) {
List<Object> rowValue = new ArrayList<>();
Row row = sheet.getRow(j);
if (row == null) {
dataList.add(null);
continue;
}
int readColumnCount = row.getLastCellNum();
// 解析row 的列
for (int k = 0; k < readColumnCount; k++) {
Cell cell = row.getCell(k);
rowValue.add(getCellValue(cell));
}
dataList.add(rowValue);
}
sheetPOs.add(sheetPO);
return sheetPOs;
}
return sheetPOs;
}
private static Object getCellValue(Cell cell) {
Object columnValue = null;
if (cell != null) {
CellType cellType = cell.getCellType();
if(cellType.getCode() == CellType.NUMERIC.getCode()){
boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
if(isDate){
return DateUtil.formatDate(cell.getDateCellValue(),DateUtil.DATE_PATTERN_YYYY_MM_DD);
}
}
try {
columnValue = cell.getStringCellValue();
}catch (Exception e){
columnValue = cell.toString();
}
}
return columnValue;
}
/**
* @description: 填充excel内容
* @params: [file 上传的文件, clazz 实体类,用于和excel中列相对应，必须继承 ExcelRowDetail, startRowIndex 开始行索引, endColumnIndex 结束列索引]
* @return: java.util.List
* @throws
*/
@SneakyThrows
public static <T> List<T> populateExcelContent(MultipartFile file, Class<T> clazz, int startRowIndex) {
if(startRowIndex < 0){
throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"startRowIndex or endColumnIndex must gt; 0");
}
if(Objects.isNull(clazz)){
throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"clazz must be not null ");
}
T t = clazz.newInstance();
if(!(t instanceof ExcelRowDetail)){
throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"clazz must instanceof ExcelRowDetail");
}
List<ExcelSheetPo> excelSheetPos = readExcel(file);
return populateExcelContent(excelSheetPos,clazz,startRowIndex);
}
/**
* @description: excel 内容读取出来后，统一使用String接
* @params: [inputStream, clazz, startRowIndex, endColumnIndex]
* @return: java.util.List
* @throws
*/
@SneakyThrows
public static <T> List<T> populateExcelContent(List<ExcelSheetPo> excelSheetPos, Class<T> clazz, int startRowIndex){
List<T> result = new ArrayList<>();
if(CollectionUtils.isEmpty(excelSheetPos)){
return result;
}
Map<Integer, String> fieldMappingExcelColumnMap = ExcelRowDetail.getFieldMappingExcelColumnMap(clazz);
int endColumnIndex = fieldMappingExcelColumnMap.size() - 1;
Map<String,Field> fieldCache = Maps.newHashMap();
Field[] fields = clazz.getDeclaredFields();
// 当前类
for(Field field : fields){
field.setAccessible(true);
fieldCache.put(field.getName(),field);
}
// 父类
Class<? super T> superclass = clazz.getSuperclass();
fields = superclass.getDeclaredFields();
for(Field field : fields){
field.setAccessible(true);
fieldCache.put(field.getName(),field);
}
for(ExcelSheetPo excelSheetPo : excelSheetPos){
String sheetName = excelSheetPo.getSheetName();
List<List<Object>> dataList = excelSheetPo.getDataList();
if(CollectionUtils.isEmpty(dataList) || dataList.size()-1 < startRowIndex){
continue;
}
int rowCount = dataList.size();
for(int i = startRowIndex;i < rowCount ; i++ ){
List<Object> rowContent = dataList.get(i);
if(CollectionUtils.isEmpty(rowContent)){
continue;
}
T t = clazz.newInstance();
for(int k = 0 ; k <= endColumnIndex;k++){
String value = null;
try {
Object o = rowContent.get(k);
if(!Objects.isNull(o)){
value = o.toString();
}
}catch (Exception e){
// ignore
}
String fieldName = fieldMappingExcelColumnMap.get(k);
Field field = fieldCache.get(fieldName);
field.set(t,value);
}
// 设置sheet名称和行号
Field field = fieldCache.get(ExcelRowDetail.getSheetNameFieldName());
field.set(t,sheetName);
field = fieldCache.get(ExcelRowDetail.getRowNoFieldName());
field.set(t,i+1);
result.add(t);
}
}
return result;
}
/**
* @description: 验证必填项，根据注解
* @params: [contentList, vo]
* @return: void
* @throws
*/
@SneakyThrows
public static <T> void verifyExcelRequiredField(List<T> contentList, ExcelVerifyVo<T> vo) {
List<String> emptyErrorHint = vo.getEmptyErrorHint();
Map<String,List<Integer>> emptyVerifySheetRow = Maps.newHashMap();
Iterator<T> iterator = contentList.iterator();
while(iterator.hasNext()){
T t = iterator.next();
Class clazz = t.getClass();
Class superclass = clazz.getSuperclass();
Field sheetNameField = superclass.getDeclaredField(ExcelRowDetail.getSheetNameFieldName());
sheetNameField.setAccessible(true);
String sheetName = (String)sheetNameField.get(t);
Field rowNoField = superclass.getDeclaredField(ExcelRowDetail.getRowNoFieldName());
rowNoField.setAccessible(true);
Integer rowNo = (Integer) rowNoField.get(t);
// 验证不通过标识
boolean checked = false;
Field[] declaredFields = clazz.getDeclaredFields();
for(Field field : declaredFields){
field.setAccessible(true);
NotBlank notBlank = field.getAnnotation(NotBlank.class);
boolean flag = !Objects.isNull(notBlank) && (Objects.isNull(field.get(t)) || StringUtils.isBlank(field.get(t).toString()));
if(flag){
checked = true;
break;
}
}
if(checked){
if(emptyVerifySheetRow.containsKey(sheetName)){
emptyVerifySheetRow.get(sheetName).add(rowNo);
}else{
List<Integer> inner = new ArrayList<>();
inner.add(rowNo);
emptyVerifySheetRow.put(sheetName,inner);
}
iterator.remove();
}
}
if(emptyVerifySheetRow.size() > 0){
// 有必填项验证不通过
emptyVerifySheetRow.forEach((sheetName,rowList)->{
StringBuilder sb = new StringBuilder();
sb.append(sheetName).append("中第")
.append(StringUtils.join(rowList,"、"))
.append("行：信息填写不全；");
emptyErrorHint.add(sb.toString());
});
emptyErrorHint.add("继续导入将跳过此数据；");
}
}
/**
* @description: 验证excel格式错误
* @params: [contentList, vo]
* @return: void
* @throws
*/
@SneakyThrows
public static <T> void verifyExcelFormatErrorField(List<T> contentList, ExcelVerifyVo<T> vo) {
if(!CollectionUtils.isNotEmpty(contentList)){
return ;
}
Map<String,List<Integer>> sheetErrorHintMap = Maps.newHashMap();
Iterator<T> iterator = contentList.iterator();
while(iterator.hasNext()){
boolean flag = false;
T rowContent = iterator.next();
Class clazz = rowContent.getClass();
// 验证字段
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields){
field.setAccessible(true);
// TODO 根据场景不同可以增加不同的校验规则
Pattern patternAnno = field.getAnnotation(Pattern.class);
Object callObj = field.get(rowContent);
if(!Objects.isNull(patternAnno)){
String value;
if(Objects.isNull(callObj) || StringUtils.isBlank(value = callObj.toString())){
continue;
}
String regexp = patternAnno.regexp().replace("\n", "\\\\n");
java.util.regex.Pattern compile = java.util.regex.Pattern.compile(regexp);
Matcher matcher = compile.matcher(value);
if(!matcher.matches()){
flag = true;
break;
}
}
}
if(flag){
iterator.remove();
// 验证不通过
Class<?> superclass = clazz.getSuperclass();
Field field = superclass.getDeclaredField(ExcelRowDetail.getRowNoFieldName());
field.setAccessible(true);
Integer row = (Integer) field.get(rowContent);
Field sheetNameField = superclass.getDeclaredField(ExcelRowDetail.getSheetNameFieldName());
sheetNameField.setAccessible(true);
String sheetName = sheetNameField.get(rowContent).toString();
if(sheetErrorHintMap.containsKey(sheetName)){
sheetErrorHintMap.get(sheetName).add(row);
}else{
List<Integer> rows = new ArrayList<>();
rows.add(row);
sheetErrorHintMap.put(sheetName,rows);
}
}
}
if(sheetErrorHintMap.isEmpty()){
return ;
}
List<String> list = vo.getFormatErrorHint();
for(Map.Entry<String, List<Integer>> entry : sheetErrorHintMap.entrySet()){
String sheetName = entry.getKey();
StringBuilder sb = new StringBuilder();
List<Integer> rows = entry.getValue();
Collections.sort(rows);
String join = StringUtils.join(rows, "、");
sb.append(sheetName).append("中第").append(join)
.append("行：信息格式错误，请检查；");
list.add(sb.toString());
}
}
/**
* @description: 验证excel中重复的标识
* @params: [groupRep, vo]
* @return: void
* @throws
*/
@SneakyThrows
public static <T> void verifyExcelRepetitive(Map<String, List<T>> groupRep, ExcelVerifyVo<T> vo) {
if(Objects.isNull(groupRep) || groupRep.size() == 0){
return ;
}
List<String> repetitiveErrorHint = vo.getRepetitiveErrorHint();
for(Map.Entry<String, List<T>> entry : groupRep.entrySet()){
String uniqueKey = entry.getKey();
List<T> contentList = entry.getValue();
StringBuilder sb = new StringBuilder();
sb.append("【").append(uniqueKey).append("】").append("编号重复：");
// 把rep中的数据，按照sheet页进行分组
Map<String, List<T>> innerGroup = Maps.newHashMap();
for(T t : contentList){
Class<?> clazz = t.getClass();
Class<?> superclass = clazz.getSuperclass();
Field field = superclass.getDeclaredField(ExcelRowDetail.getSheetNameFieldName());
field.setAccessible(true);
String sheetName = (String)field.get(t);
if(innerGroup.containsKey(sheetName)){
innerGroup.get(sheetName).add(t);
}else{
List<T> inner = new ArrayList<>();
inner.add(t);
innerGroup.put(sheetName,inner);
}
}
for(Map.Entry<String, List<T>> sEntry : innerGroup.entrySet()){
String sheetName = sEntry.getKey();
List<T> rowContents = sEntry.getValue();
sb.append(sheetName).append("中第");
List<Integer> rows = new ArrayList<>();
for(T content : rowContents){
Class<?> clazz = content.getClass();
Class<?> superclass = clazz.getSuperclass();
Field field = superclass.getDeclaredField(ExcelRowDetail.getRowNoFieldName());
field.setAccessible(true);
Integer row = (Integer) field.get(content);
rows.add(row);
}
Collections.sort(rows);
String join = StringUtils.join(rows, "、");
sb.append(join).append("行").append("、");
sb.deleteCharAt(sb.length()-1).append("；");
repetitiveErrorHint.add(sb.toString());
}
}
repetitiveErrorHint.add("继续操作将导入最后一条数据；");
}
public static <T> void excelAlreadyExistRejectedNoHint(List<String> nos, ExcelVerifyVo<T> vo) {
if(!CollectionUtils.isNotEmpty(nos)){
return ;
}
List rejectErrorHint = vo.getRejectErrorHint();
StringBuilder sb = new StringBuilder();
sb.append("编号【").append(org.apache.commons.lang3.StringUtils.join(nos, SplitSymbolEnum.SPLIT_PAUSE.getSymbol()));
sb.append("】").append("系统中已存在；");
rejectErrorHint.add(sb.toString());
rejectErrorHint.add("继续将跳过此数据；");
}
@SneakyThrows
public static <T> void verifyExcelAlreadyExist(Map<String, List<T>> existsSheetGroup, ExcelVerifyVo<T> vo,String uniqueFeildName) {
List<String> existErrorHint = vo.getExistHint();
if(existsSheetGroup.isEmpty()){
return ;
}
for(Map.Entry<String, List<T>> entry : existsSheetGroup.entrySet()){
String sheetName = entry.getKey();
List<T> existsList = entry.getValue();
StringBuilder sb = new StringBuilder();
sb.append(sheetName).append("中第");
List<Integer> rowNos = new ArrayList<>();
List<String> nos = new ArrayList<>();
for (T excelContent : existsList){
Class<?> clazz = excelContent.getClass();
Field uniqueKeyField = clazz.getDeclaredField(uniqueFeildName);
uniqueKeyField.setAccessible(true);
String uniqueKeyValue = (String)uniqueKeyField.get(excelContent);
Class<?> superclass = clazz.getSuperclass();
Field rowNoField = superclass.getDeclaredField(ExcelRowDetail.getRowNoFieldName());
rowNoField.setAccessible(true);
Integer rowNo = (Integer) rowNoField.get(excelContent);
rowNos.add(rowNo);
nos.add(uniqueKeyValue);
}
Collections.sort(rowNos);
sb.append(StringUtils.join(rowNos,"、")).append("行；编号【");
sb.append(StringUtils.join(nos,"、")).append("】的唯一编号已存在；");
existErrorHint.add(sb.toString());
}
existErrorHint.add("继续导入将更新数据；");
}
}
@Override
public ExcelVerifyVo importVerify(MultipartFile file) {
ExcelVerifyVo<ExcelContent> vo = new ExcelVerifyVo();
List<ExcelContent> contentList = ExcelUtil.populateExcelContent(file,ExcelContent.class,1);
if(CollectionUtils.isEmpty(contentList)){
return vo;
}
vo.setTotal(contentList.size());
// ------------过滤，验证必填项------------>
ExcelUtil.verifyExcelRequiredField(contentList,vo);
// ------------过滤，验证格式------------>
ExcelUtil.verifyExcelFormatErrorField(contentList,vo);
// ------------过滤，验证重复------------>
Map<String,ExcelContent> groupContent = Maps.newHashMap();
Map<String,List<ExcelContent>> groupRep = Maps.newHashMap();
contentList.forEach(excelContent -> {
if(groupContent.containsKey(excelContent.getPimerNo())){
if(groupRep.containsKey(excelContent.getPimerNo())){
groupRep.get(excelContent.getPimerNo()).add(excelContent);
}else{
List<ExcelContent> inner = new ArrayList<>();
inner.add(groupContent.get(excelContent.getPimerNo()));
inner.add(excelContent);
groupRep.put(excelContent.getPimerNo(),inner);
}
}
// 存在就更新，保留最后一条
groupContent.put(excelContent.getPimerNo(),excelContent);
});
contentList.clear();
groupContent.forEach((key, value)-> contentList.add(value));
ExcelUtil.verifyExcelRepetitive(groupRep,vo);
groupContent.clear();
// ------------验证权限 非实验员自己的数据，需要丢弃掉------------>
List<ExcelContent> addList = vo.getAddList();
List<ExcelContent> updateList = vo.getUpdateList();
List<String> nos = new ArrayList<>();
Iterator<ExcelContent> iterator = contentList.iterator();
SysUser currentUser = UserUtil.getCurrentUser();
while(iterator.hasNext()){
ExcelContent excelContent = iterator.next();
Data data = repository.findByNo(excelContent.getNo());
if(!Objects.isNull(data)){
updateList.add(excelContent);
}else{
addList.add(excelContent);
}
}
ExcelUtil.excelAlreadyExistRejectedNoHint(nos,vo);
// ------------过滤，验证重复------------>
Map<String,List<ExcelContent>> existsSheetGroup = Maps.newHashMap();
updateList.forEach(excelContent -> {
if(existsSheetGroup.containsKey(excelContent.getSheetName())){
existsSheetGroup.get(excelContent.getSheetName()).add(excelContent);
}else{
List<ExcelContent> inner = new ArrayList();
inner.add(excelContent);
existsSheetGroup.put(excelContent.getSheetName(),inner);
}
});
ExcelUtil.verifyExcelAlreadyExist(existsSheetGroup,vo,"pimerNo");
vo.setRightTotal(contentList.size());
vo.setErrorTotal(vo.getTotal()-vo.getRightTotal());
return vo;
}
持久化动作就比较简单了
@Override
public int importCommit(MultipartFile file) {
ExcelVerifyVo<ExcelContent> vo = this.importVerify(file);
if(vo.getRightTotal() == 0){
return 0;
}
List<ExcelContent> addList = vo.getAddList();
List<ExcelContent> updateList = vo.getUpdateList();
if(CollectionUtils.isEmpty(addList) && CollectionUtils.isEmpty(updateList)){
throw new ServiceException(HttpStatus.NO_CONTENT.value(),"excel中无内容！");
}
if(CollectionUtils.isNotEmpty(addList)){
List<DataPimer> insertList = new ArrayList();
Data data;
for(ExcelContent excelContent : addList){
data = new Data();
BeanUtils.copyProperties(excelContent,data);
pimer.setId(IdUtils.getUuid());
insertList.add(data);
}
pimerRepository.saveAll(insertList);
}
if(CollectionUtils.isNotEmpty(updateList)){
List<Data> updateDataList = new ArrayList<>();
// 这里会有并发问题【根据项目的实际情况处理】
updateList.forEach(excelContent->{
Data data = repository.findByNo(excelContent.getNo());
BeanUtils.copyProperties(excelContent,data);
}
repository.saveAll(updateList);
}
return vo.getRightTotal();
}