背景

最近做了两个项目，都有excel导入，而且代码量比较大，重性比较高，也没有什么技术含量
为了解决以后更快的开发导入，梳理出了一套可以借鉴的模版供大家参考。

项目依赖jar包

java 8 、Springboot、lombok、commons-lang3、hibernate-validator、Collection4

org.projectlomboklombok

cn.afterturneasypoi-spring-boot-starter4.3.0

org.apache.commonscommons-lang33.12.0

org.hibernatehibernate-validator6.0.1.Final

整体结构

1、读取excel内容
2、把excel行转成行对象
3、验证必填项「不符合规则删除」
4、验证格式「不符合规则删除」
5、验证重复项「不符合规则删除」
6、自定义规则验证
7、分组：如果满足5，需要进行处理是更新还是插入

定义需要的类

验证后消息汇总

@Data
@Accessors(chain = true)
@ApiModel
public class ExcelVerifyVo {@ApiModelProperty(example = "总条数")private int total;@ApiModelProperty(example = "正确数据条数")private int rightTotal;@ApiModelProperty(example = "异常数据条数")private int errorTotal;@ApiModelProperty(example = "不能为空提示")private List emptyErrorHint = new ArrayList<>();@ApiModelProperty(example = "信息格式错误提示")private List formatErrorHint = new ArrayList<>();@ApiModelProperty(example = "编号重复提示")private List repetitiveErrorHint = new ArrayList<>();@ApiModelProperty(example = "丢弃的编号提示")private List rejectErrorHint = new ArrayList<>();@ApiModelProperty(example = "编号存在提示")private List existHint = new ArrayList<>();@JsonIgnoreprivate List addList = new ArrayList<>();@JsonIgnoreprivate List updateList = new ArrayList<>();
}

所有excel行的父类

@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 getFieldMappingExcelColumnMap(Class clazz){Map 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;}
}

excel行内容

@Data
@Accessors(chain = true)
public class ExcelContent extends ExcelRowDetail{@ApiModelProperty(name = "")@NotBlankprivate String code;@ApiModelProperty(name = "")@NotBlank@Pattern(regexp = RegexpConsts.PIMERF_REGEXP)private String name;
}

excel工具类

@UtilityClass
public class ExcelUtil {/*** @description: 读取文件内容* @params: [file, rowCount, columnCount]* @return: java.util.List* @throws*/@SneakyThrowspublic static List 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];}@SneakyThrowspublic 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 readExcelContent(Workbook wb){if(Objects.isNull(wb)){throw new ServiceException(HttpStatus.BAD_REQUEST.value(),"文件不存在Sheet页！");}// 开始读取数据List sheetPOs = new ArrayList<>();// 解析sheetfor (int i = 0; i < wb.getNumberOfSheets(); i++) {Sheet sheet = wb.getSheetAt(i);List> 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