业务背景:
甲方提供大量的台账excel,需要读取表头并转换成json存到数据库,在导出或生成导入模板的时候动态生成表头,我使用easyexcel解决了该问题
样例表格:
maven依赖:
业务实现:
@Getter@Setter@EqualsAndHashCodepublic class ExcelReadHeader {private String string;private Date date;private Double doubleData;}
只写到两行数据处理,更多行可以自己动手继续写,json格式也可以自己定义
@Slf4jpublic class ExcelHeaderListener extends AnalysisEventListener<ExcelReadHeader> {public static List<ExcelReadHeader> importList = new ArrayList<>();public List<Map<Integer,String>> headers=new ArrayList<>();/*** 暂定最多有三层表头,headMap1,headMap2,headMap3分别存储*/public Map<Integer, String> headMap1=new HashMap<>();public Map<Integer, String> headMap2=new HashMap<>();public Map<Integer, String> headMap3=new HashMap<>();@Overridepublic void invoke(ExcelReadHeader data, AnalysisContext context) {// log.info("解析到的一条数据: excelRow = {}", data);importList.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 解析完所有excel行, 保存到数据库或进行业务处理//log.info("解析的所有数据 list = {}", importList);}@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {if(CollectionUtils.isEmpty(headers)){headMap1=headMap;}headers.add(headMap);if(headers.size()==2){JSONArray jsonArray=new JSONArray();Map<Integer, String> headMap2=headers.get(1);Map<Integer,List<String>> headerMap=new LinkedHashMap<>();int beginIndex=0;for (int i=0;i<headMap1.size();i++){if(!StringUtils.isEmpty(headMap1.get(i))){beginIndex=i;}if(!StringUtils.isEmpty(headMap2.get(i))){if(!headerMap.containsKey(beginIndex)){List<String> keys=new ArrayList<>();headerMap.put(beginIndex,keys);}headerMap.get(beginIndex).add(headMap2.get(i));}}for (int key=0;key<headMap1.size();key++){String prop=IdUtil.fastSimpleUUID();if(!StringUtils.isEmpty(headMap1.get(key))){Map<String,Object> map=new LinkedHashMap();map.put("prop",prop);map.put("type","string");map.put("label",headMap1.get(key));map.put("value","");map.put("isNull","false");if(headerMap.containsKey(key)){List<String> values=headerMap.get(key);List<Map<String,Object>> childMaps=new ArrayList<>();for (String str:values){String prop1=IdUtil.fastSimpleUUID();Map<String,Object> childMap=new LinkedHashMap();childMap.put("prop",prop1);childMap.put("type","string");childMap.put("label",str);childMap.put("value","");childMap.put("isNull","false");childMaps.add(childMap);}map.put("children",childMaps);}JSONObject jsonObject=new JSONObject(map);jsonArray.add(jsonObject);}}log.info("最终数据"+jsonArray.toJSONString());}if(headers.size()==3){Map<Integer, String> headMap3=headers.get(2);}}}
msg.get("num")是你要读取几行作为表头
@PostMapping(value = "/readerExcel")@ResponseBodypublic Msg readerExcel( @RequestBody Msg msg ) throws Exception {String fileName = "D:/readerExcel.xlsx";// 这里 需要指定读用哪个class去读,然后读取第一个sheetEasyExcel.read(fileName, ExcelReadHeader.class, new ExcelHeaderListener()).sheet()// 这里可以设置1,因为头就是一行。如果多行头,可以设置其他值。不传入也可以,因为默认会根据DemoData 来解析,他没有指定头,也就是默认1行.headRowNumber(Integer.valueOf(msg.get("num"))).doRead();return new Msg().success();}
最终效果: