树结构导入

  @Testpublic void testExcel1() {// 写法1：JDK8+ ,不用额外写一个DemoDataListener// since: 3.0.0-beta1EasyExcelFactory.read(new File("C:\\Users\\Admin\\Desktop\\树导入.xlsx"), null, new ReadListener<Map>() {public static final int BATCH_COUNT = 100;private List<Map> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);@Overridepublic void invoke(Map data, AnalysisContext context) {cachedDataList.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//List<Map>System.out.println(cachedDataList);System.out.println("+++++++++");//转换后的List<List<String>>System.out.println(mapToList(cachedDataList));
//                insertBatch(mapToList(cachedDataList));?}}).headRowNumber(0).build().readAll();}//将List<Map>集合转成List<List<String>>public static List<List<String>> mapToList(List<Map> maps) {List<List<String>> dataList = new ArrayList<>();for (Map map : maps) {List<String> list = new ArrayList<>();map.forEach((k, v) -> list.add(ObjectUtils.isEmpty(v) ? "" : v.toString()));dataList.add(list);}return dataList;}public List<CategoryVO> insertBatch(List<List<String>> datas){// 定义主键id 对应数据库中cat_idShort index = 1;Short parentId = null;String levelName = null;//分类名称// tempMap为临时变量,记录那些已经添加过了 key为CatName+层级 value 为记录对象,例如// <Home_1,CategoryVO> 表示存储第一级对象Map<String, CategoryVO> tempMap = new HashMap<String, CategoryVO>();// 记录最终需要插入数据库的数据List<CategoryVO> insertDatas = new ArrayList<CategoryVO>();CategoryVO level = null;int allCols = datas.get(0).size();//获取导入数据的列数for (int column = 0; column < allCols; column++){for (int i = 0; i < datas.size(); i++){List<String> rows = datas.get(i);//设置层级的关联关系if (column == 0){// 如果是第一列,parentid 默认为0parentId = (short) 0;}else{// 如果不是一列,则在tempMap中寻找对应的父类id作为parentid,当前列向后退一列parentId = Short.valueOf(tempMap.get(rows.get(column - 1) + "_" + String.valueOf(column)).getCatId());}levelName = rows.get(column);if (StringUtils.isEmpty(levelName)){System.out.println("+++++++++失败,表格中有空白+++++++++");}//tempMap中没有记录过则表示是要插入的数据if (!tempMap.containsKey(levelName + "_" + (column + 1))){level = new CategoryVO();level.setCatId(String.valueOf(index++));level.setCatName(levelName);level.setParentId(String.valueOf(parentId));level.setGrade(String.valueOf(Byte.valueOf(String.valueOf(column + 1))));tempMap.put(levelName + "_" + (column + 1), level);//添加到最终要导入的列表中insertDatas.add(level);}}}//批量插入System.out.println(insertDatas);return insertDatas;}