业务背景:
甲方提供大量的台账excel,需要读取表头并转换成json存到数据库,在导出或生成导入模板的时候动态生成表头,我使用easyexcel解决了该问题
样例表格:
maven依赖:
业务实现:
@Getter
@Setter
@EqualsAndHashCode
public class ExcelReadHeader {
private String string;
private Date date;
private Double doubleData;
}
只写到两行数据处理,更多行可以自己动手继续写 ,json格式也可以自己定义
@Slf4j
public 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<>();
@Override
public void invoke(ExcelReadHeader data, AnalysisContext context) {
// log.info("解析到的一条数据: excelRow = {}", data);
importList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 解析完所有excel行, 保存到数据库或进行业务处理
//log.info("解析的所有数据 list = {}", importList);
}
@Override
public 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")
@ResponseBody
public Msg readerExcel( @RequestBody Msg msg ) throws Exception {
String fileName = "D:/readerExcel.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet
EasyExcel.read(fileName, ExcelReadHeader.class, new ExcelHeaderListener()).sheet()
// 这里可以设置1,因为头就是一行。如果多行头,可以设置其他值。不传入也可以,因为默认会根据DemoData 来解析,他没有指定头,也就是默认1行
.headRowNumber(Integer.valueOf(msg.get("num"))).doRead();
return new Msg().success();
}
最终效果: