本文介绍了一种通用的Excel动态表头导入工具类实现方法，支持表头动态配置及数据校验，提高了灵活性和实用性。
通用的Excel动态表头导入工具类实现（附带数据校验）
前言
在工作中，遇到了一个工资导入功能的处理，有两个不同的工资表，需要导入对应的工资。一开始通过自定义注解来标注表头和字段的对应关系，将其一一对应，但是这种方式只要导入模板发生改变，哪怕是交换两个表头的位置，就需要改动代码，因此，组长希望实现一个可以动态配置表头的通用Excel导入工具类，这里将实现作一个记录。
一、实现分析
- 动态表头的含义：我们可以动态的增加、减少表头或者是修改表头的名称。例如实体字段age，可以对应Excel表的年龄，也可以对应Excel表的岁数。
- 既然是动态，就需要一个地方存储实体字段和表头的对应关系，我们项目采用的是数据字典的方式，一条字典的记录内容包括CODE(编码)、VALUE(值)、REMARK(备注)，所有相同类型的字典放在同一父编码的字典集合中，通过父编码和CODE编码可以获得对应的内容，因此我们使用CODE存储实体字段，通过VALUE存储对应的表头内容，使用REMARK存储校验规则。例如其中一条记录是
|CODE|VALUE|REMARK
|SFGZ|实发工资，实际发放工资|^-?\d+(\.\d+)?
|NAME|姓名，职员姓名，用户名|
从上可以看出，Excel的表头可以是姓名，职员姓名，用户名等，哪怕Excel表头用猪猪，只要我们在字典上配好，就可以导入数据。注意这个字典是在系统上有配置页面的，非常方便。 - 整体流程：获取到Excel的所有表头和所有的表头实体对应关系(这里只是我们的业务实现，可以根据自己的业务情况具体实现，只要保证实体字段是唯一的即可)，通过循环处理上面的内容，获取表头和字段一一对应的数据，可以使用Map存储，我是用的是effectTitle，同时，那些没有对应的表头也可以存入另一个Map，用来判断用户上传的Excel文件是否和模板一致。得到这个effectTitle的Map集合后，就可以去遍历Excel的具体数据了，逐行读取，然后将实体字段和数据一一对应，同理也是存入一个Map集合，注意，这里做了数据校验，例如对于某些数据，我们只希望得到数值，因此，我们在字典记录中就记录了正则规则，这在我们代码中使用regMap这个集合存储，我们得到某个字段及其值后，可以根据这个字段获取其正则表达式，然后判断值是否满足规则，对于不满足规则的我们将其记录下来，并将这条记录抛弃(这里的抛弃只是不把它放入导入数据集合，然后将其记录下来放到提示修改集合中)。上面就是大概的流程，做完这些后我们就得到了导入数据集合datas，没有匹配的表头集合noEffectTitle，没有匹配的表头数量noEffectTitleNum，不符合规则的数据集合regFailUserAndTitle，不符合规则的数据数量noPatternRow，后续在自己的具体业务中可以进行进一步的处理，例如返回不匹配数据，导入合规数据等等。
- 在下面的源码中，我对一些变量做了注释，对于输入变量这里做一些简单的解释。file肯定是对应的Excel文件，parentDictCode是我们业务中字典集合的父编码，通过这个获取所有字典记录，这个是可以根据具体业务去修改的，看具体实现方式，startRow是数据行开始的行数，noUseRow是不需要导入的行数，例如在末尾行可能有一些说明性的文字，用户不希望去掉，我们只能通过这个值控制数据行的结束，title故名思意就是表头行了，我这里用数组是因为我们业务中有两行表头。
- 匹配数字的正则是
^-?\\d+(\\.\\d+)?$，但是因为需要通过sql进行数据库的修改，$的存在会让sql报错，我尝试了^-?\\d+(\\.\\d+)?匹配正则，暂时没发现问题。 - 说了这么多，可能会有一点点懵逼，还是来看看代码吧，我做了一些注释，应该是可以明白的！！！
二、源码
代码如下：
public class ExcelCellToEntity {
/**
* 处理Excel文件
* @param file excel文件
* @param parentDictCode 当前excel文件对应的字典父编码
* @param startRow 数据开始行
* @param noUseRow 不需要导入数据行数
* @param title 标题行行号数组
* @return
* @throws Exception
*/
public static Map<String, Object> test(MultipartFile file, String parentDictCode, int startRow, int noUseRow, int []title) throws Exception {
Map<String, Object> result = new HashMap<>();
//根据父编码获取所有字典项及其编码（编码存放的是数据库实体类字段，值存的是可能表头名称，以逗号分隔），这里可以换做其他业务实现哦
List dictListByParentCode = DictUtil.getDictListByParentCode(parentDictCode);
//所有当前父编码的字典项，就是字典数据的集合
Map<String,String>dictMap = new HashMap<>();
//key是字典code，value是对应的正则规则，这个是正则集合
Map<String,String>regMap = new HashMap<>();
for (int i = 0; i < dictListByParentCode.size(); i++) {
Map map = (Map) dictListByParentCode.get(i);
//对应的表头名称，可能是多个，将可能的表头名称都做个记录
String[]codes = String.valueOf(map.get("VALUE")).split(",");
//表头对应的字段名称，例如年龄->age，这个CODE是实体字段
String value = String.valueOf(map.get("CODE"));
//当前字段的正则校验，例如检验这个是否是数值类型，这里我用%分割是因为一开始没有做数据校验，只是一个备注，后来新增了需求，才想到通过%分割，在其后面保存正则规则，当然，正则规则也可以配置多个哦
String reg = String.valueOf(map.get("REMARK"));
if(reg.lastIndexOf("%")!=-1){
//正则放在字典备注里，在"split"后，若有多个用","隔开，这里存储所有正则，后面需要分隔，因为key是code，保证唯一性
reg = reg.substring(reg.lastIndexOf("%")+1);
}else{
reg = "";
}
//这里key是表头，value是实体字段，数组表示多个表头对同一个实体字段，然后根据传入的模板的表头去取值
for (int j = 0; j < codes.length; j++) {
dictMap.put(codes[j].trim(),value);
}
//正则集合
regMap.put(value,reg);
}
InputStream inputStream = file.getInputStream();
Workbook workbook = WorkbookFactory.create(inputStream);
//key是Integer类型，说明存储的是excel表的列数，后续不管是表头还是数据都是通过列数来取到的
//存放有效的表头对应的实体字段即excel表列数
Map<Integer, Object> effectTitle = new HashMap<>();
//存放没有对应上的表头
Map<Integer, String> noEffectTitle = new HashMap<>();
Map<String,String>oneToOneDict = new HashMap();
// Map<String,String>regFailUserAndTitle = new HashMap<>();
//正则匹配失败的记录
List<String>regFailUserAndTitle = new ArrayList<>();
//没有对应的表头数量
int noEffectTitleNum = 0;
//正则匹配失败的数量
int noPatternRow = 0;
//实体字段和数据对应的集合
List<Map<String, Object>> datas = new ArrayList<>();
//sheet页数量
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
//当前sheet页
Sheet sheet = workbook.getSheetAt(i);
if(!AegisCommonUtils.isNull(sheet) && sheet.getLastRowNum()!=0){
//总行数
int rowCount = sheet.getPhysicalNumberOfRows();
//是否将当前列名及其列号加入集合
boolean flag = true;
//取出表头数据
for (int t = 0; t < title.length; t++) {
//当前行是表头
Row row = sheet.getRow(title[t]);
int cellCount = row.getPhysicalNumberOfCells();
for (int c = 0; c < cellCount; c++) {
//当前列
Cell cell = row.getCell(c);
if(!AegisCommonUtils.isNull(cell)) {
String cellValue = ExcelAnalysisUtils.getCellValue(cell);
//去掉表头的换行和空格
String s = cellValue.replaceAll("\r\n|\r|\n", "");
if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK || "".equals(s)){
//遇到了表头是空单元格，处理下一个
}else{
if(AegisCommonUtils.isNull(dictMap.get(s))){
//没有从所有字典项中找到当前列名对应的实体类编码
noEffectTitle.put(c,s);
noEffectTitleNum++;
}else{
//判断当前表头是否已经加入
for (Integer integer : effectTitle.keySet()) {
String str = String.valueOf(effectTitle.get(integer));
if(str.equals(dictMap.get(s))){
flag = false;
break;
}
}
if(flag){
effectTitle.put(c,dictMap.get(s));
oneToOneDict.put(dictMap.get(s),s);
}
}
}
}
}
}
//这里startRow代表第一行要导入的数据所在的行数
for (int r = startRow; r < rowCount-noUseRow; r++) {
Row row = sheet.getRow(r);
Map<String,Object>temp = new HashMap<>();
//列数
int cellCount = row.getPhysicalNumberOfCells();
int noPatternNum = 0;
StringBuilder sb = new StringBuilder();
Pattern pattern = null;
for (int c = 0; c < cellCount; c++) {
//当前列
Cell cell = row.getCell(c);
if(!AegisCommonUtils.isNull(cell)) {
//这里行是数据
if(!AegisCommonUtils.isNull(effectTitle.get(c))){
String cellValue = ExcelAnalysisUtils.getCellValue(cell);
String reg = regMap.get(String.valueOf(effectTitle.get(c)));
if(!AegisCommonUtils.isNull(reg)){
//匹配正则
String[]regs = reg.split(",");
for (String rreg : regs) {
pattern = Pattern.compile(rreg);
if(!pattern.matcher(cellValue).matches()){
noPatternNum++;
sb.append(oneToOneDict.get(effectTitle.get(c)));
sb.append(",");
break;
}
}
}
temp.put(String.valueOf(effectTitle.get(c)),cellValue);
}
}
}
//把数据集合加入，这里temp对应一整行的数据
if(!AegisCommonUtils.isNull(temp)){
if(noPatternNum == 0){
datas.add(temp);
}else{
noPatternRow++;
regFailUserAndTitle.add(String.valueOf(temp.get("userName"))+","+sb.toString().substring(0,sb.toString().length()-1));
// regFailUserAndTitle.put(String.valueOf(temp.get("userName")),sb.toString().substring(0,sb.toString().length()-1)+"列的数据不符合规范");
}
}
}
}
}
result.put("datas",datas);
result.put("noEffectTitle",noEffectTitle);
result.put("noEffectTitleNum",noEffectTitleNum);
result.put("regFailUserAndTitle",regFailUserAndTitle);
result.put("noPatternRow",noPatternRow);
return result;
}
}
总结
总体来说，这个工具类还是能处理一些常见的业务，对于复杂的Excel文件，可能要进行相应的修改，相比于写死数据列的方式，这种方式还是比较好的，对于上面有任何疑问的也可以给我留言，我在看到的第一时间一定回复，这是我的第一篇文章，如有不清晰的地方还请谅解。对于上面提到的写死字段的方式，在本站有许多文章做了详细说明，我也是参考了其中的内容。对于正则表达式的问题，我自己也是在学习，如果使用有问题，就需要再学习改进一下了。
扫一扫
943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
