excel获取分级展示 折叠展开 父子级关系

excel获取分级展示 折叠展开 父子级关系

apache poi 4.1.2 通过方法Row.getOutlineLevel和Row.getZeroHeight来提供

我们可以遍历工作表中的所有行，并具有可以从一行中收集行组信息的方法来确定每一行的父子。在以下代码中，这是方法

// 行级别
int findStartOfRowOutlineGroup(Row row)
int findEndOfRowOutlineGroup(Row row)
boolean getIsWholeRowGroupHidden(Sheet sheet, int startOfRowOutlineGroup, int endOfRowOutlineGroup)

// 列级别
int findStartOfColOutlineGroup(Sheet sheet, int col)
int findEndOfColOutlineGroup(Sheet sheet, int col)
boolean getIsWholeColGroupHidden(Sheet sheet, int startOfColOutlineGroup, int endOfColOutlineGroup)

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class ExcelColumnGroupsRowGroups {

 static String getRowGroupInfos(Row row) {
  int outlineLevel = row.getOutlineLevel();
  String result = "Row "+(row.getRowNum()+1)+" belongs to no group.";
  if (outlineLevel > 0) {
   int startOfRowOutlineGroup = findStartOfRowOutlineGroup(row); //0-based
   int endOfRowOutlineGroup = findEndOfRowOutlineGroup(row); //0-based
   boolean isRowHidden = row.getZeroHeight();
   boolean isWholeGroupHidden = getIsWholeRowGroupHidden(row.getSheet(), startOfRowOutlineGroup, endOfRowOutlineGroup);
   result =  "Row "+(row.getRowNum()+1)+" belongs to group having outline level:"+outlineLevel+". "
            +"Group starts at row "+(startOfRowOutlineGroup+1)+" and ends at row "+(endOfRowOutlineGroup+1)+". "
            +"Row "+(row.getRowNum()+1)+" is "+((isRowHidden)?"hidden":"not hidden")+". "
            +"Whole group is "+((isWholeGroupHidden)?"hidden":"not hidden")+". ";
  }
  return result;
 }

 static int findStartOfRowOutlineGroup(Row row) { //0-based
  int outlineLevel = row.getOutlineLevel();
  int r = row.getRowNum();
  if (outlineLevel > 0) {
   while (r >= 0) {
    row = row.getSheet().getRow(r);
    if (row == null) break;
    int prevOutlineLevel = row.getOutlineLevel();
    if (prevOutlineLevel < outlineLevel) break;
    r--;
   }
  }
  return r+1;
 }

 static int findEndOfRowOutlineGroup(Row row) { //0-based
  int outlineLevel = row.getOutlineLevel();
  int r = row.getRowNum();
  if (outlineLevel > 0) {
   while (r <= row.getSheet().getLastRowNum()) {
    row = row.getSheet().getRow(r);
    if (row == null) break;
    int prevOutlineLevel = row.getOutlineLevel();
    if (prevOutlineLevel < outlineLevel) break;
    r++;
   }
  }
  return r-1;
 }

 static boolean getIsWholeRowGroupHidden(Sheet sheet, int startOfRowOutlineGroup, int endOfRowOutlineGroup) {
  boolean result = true;
  Row row;
  for (int r = startOfRowOutlineGroup; r <= endOfRowOutlineGroup; r++) {
   row = sheet.getRow(r);
   if (row == null) {
    result = false;
    break;
   } else if (!row.getZeroHeight()) {
    result = false;
    break;
   }
  }
  return result;
 }

 static String getColGroupInfos(Sheet sheet, int c) {
  int outlineLevel = sheet.getColumnOutlineLevel(c);
  String result = "Col "+(c+1)+" belongs to no group.";
  if (outlineLevel > 0) {
   int startOfColOutlineGroup = findStartOfColOutlineGroup(sheet, c); //0-based
   int endOfColOutlineGroup = findEndOfColOutlineGroup(sheet, c); //0-based
   boolean isColHidden = sheet.isColumnHidden(c);
   boolean isWholeGroupHidden = getIsWholeColGroupHidden(sheet, startOfColOutlineGroup, endOfColOutlineGroup);
   result =  "Col "+(c+1)+" belongs to group having outline level:"+outlineLevel+". "
             +"Group starts at col "+(startOfColOutlineGroup+1)+" and ends at col "+(endOfColOutlineGroup+1)+". "
             +"Col "+(c+1)+" is "+((isColHidden)?"hidden":"not hidden")+". "
             +"Whole group is "+((isWholeGroupHidden)?"hidden":"not hidden")+". ";
  }
  return result;
 }

 static int findStartOfColOutlineGroup(Sheet sheet, int col) { //0-based
  int outlineLevel = sheet.getColumnOutlineLevel(col);
  int c = col;
  if (outlineLevel > 0) {
   while (c >= 0) {
    int prevOutlineLevel = sheet.getColumnOutlineLevel(c);
    if (prevOutlineLevel < outlineLevel) break;
    c--;
   }
  }
  return c+1;
 }

 static int findEndOfColOutlineGroup(Sheet sheet, int col) { //0-based
  int outlineLevel = sheet.getColumnOutlineLevel(col);
  int c = col;
  if (outlineLevel > 0) {
   while (c <= sheet.getWorkbook().getSpreadsheetVersion().getLastColumnIndex()) {
    int prevOutlineLevel = sheet.getColumnOutlineLevel(c);
    if (prevOutlineLevel < outlineLevel) break;
    c++;
   }
  }
  return c-1;
 }

 static boolean getIsWholeColGroupHidden(Sheet sheet, int startOfColOutlineGroup, int endOfColOutlineGroup) {
  boolean result = true;
  for (int c = startOfColOutlineGroup; c <= endOfColOutlineGroup; c++) {
   if (!sheet.isColumnHidden(c)) {
    result = false;
    break;
   }
  }
  return result;
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelSheetGroupedColsAndRows.xlsx"));
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelSheetGroupedColsAndRows.xls"));
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   String rowGroupInfos = getRowGroupInfos(row);
   System.out.println(rowGroupInfos);
  }

  for (int c = 0; c <= workbook.getSpreadsheetVersion().getLastColumnIndex(); c++) {
   if (sheet.getColumnOutlineLevel(c) > 0) {
    String colGroupInfos = getColGroupInfos(sheet, c);
    System.out.println(colGroupInfos);
   }
  }

  workbook.close();
 }
}

上面是参考 ：

下面代码是构造图一的代码

  @ApiOperation(value = "excel自动分级")
    @PostMapping(value = "/EquipmentFenJi")
    public List EquipmentFenJi(@RequestParam("file") MultipartFile multipartFile) throws Exception {
//        Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelSheetGroupedColsAndRows.xlsx"));
        Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
        Sheet sheet = workbook.getSheetAt(1);

        Map<String, String> parentmap = new HashMap<String, String>();
        List<RowParentPo> list =new ArrayList();
        List<String> parentNumberlist = new ArrayList<>();
        int parentNumberInt = 0;

        // 这里跳过第一行。后面再补充顶级节点
        for (Row row : sheet) {
            MsgDto dto = getRowGroupInfos(row);
            if(dto == null || dto.getRow() == 1) {
                continue;
            }
            // 这里是关键一
            String parentNumberStr = StrUtil.join("",dto.getStartOfRowOutlineGroup(), dto.getEndOfRowOutlineGroup());

            if (!CollUtil.contains(parentNumberlist, parentNumberStr)) {
                parentNumberlist.add(parentNumberStr);
                parentNumberInt = parentNumberInt + 1;
                parentmap.put(parentNumberStr, String.valueOf(dto.getRow()-1));
            }
        }

        // 遍历二
        for (Row row : sheet) {
            MsgDto dto = getRowGroupInfos(row);
            if(dto == null || dto.getRow() == 1) {
                continue;
            }
            String parentNumberStr = StrUtil.join("",dto.getStartOfRowOutlineGroup(), dto.getEndOfRowOutlineGroup());

			// 这里是关键二
            list.add(new RowParentPo(dto.getRow().toString(), parentmap.get(parentNumberStr)));
        }

		// 补充顶级节点
        list.add(new RowParentPo("1","0"));

        // 这个地方可以用自己的根据父子级构造树结构方法，如果想用我的。请参照我另外一篇内容
        // https:///qq_37749537/article/details/128949451
        TreeUtils<RowParentPo, String> tree = new TreeUtils<>(
                list, RowParentPo::getRow,
                RowParentPo::getParent,
                RowParentPo::setChild);

        tree.build();

        // 这里已经完成excel层级的父子构建
        List<RowParentPo> jsonToList = list.stream().filter(f -> f.getRow().equals("1")).collect(Collectors.toList());

		// 生成
		//	   1、
		//	   1.1、
		//     1.2、
		//     1.3
		//     1.3.1
		//     1.3.2
        for (int i = 0; i < jsonToList.size(); i++) {
            RowParentPo treeVO = jsonToList.get(i);
            String levelPath = i + 1 + "";
            treeVO.setStr(levelPath);
            recursiveLoopCreateSeqNum(treeVO.getChild(), levelPath);
        }
        List<RowParentPo> excels = new ArrayList();
        
        //扁平化
        recursionEq(jsonToList, excels);

		// 根据行号排序
        excels = excels.stream()
                .sorted(Comparator.comparing(RowParentPo::getSort))
                .collect(Collectors.toList());
        
        // 控制台输出的号码可以直接负责粘贴到excel
        for (RowParentPo excel : excels) {
            System.out.println(excel.getStr());
        }
        workbook.close();
        return new ArrayList();
    }

    void recursiveLoopCreateSeqNum(List<RowParentPo> treeList, String levelPath) {
        if (null == treeList || treeList.size() < 1) {
            return;
        }
        for (int i = 0; i < treeList.size(); i++) {
            RowParentPo treeVO = treeList.get(i);
            String childLevelPath = levelPath + "." + (i + 1);
            treeVO.setStr(childLevelPath);
            recursiveLoopCreateSeqNum(treeVO.getChild(), childLevelPath);
        }
    }

    public void recursionEq(List<RowParentPo> jsonToList, List<RowParentPo> excels) {
        for (RowParentPo vo: jsonToList) {
            excels.add(vo);
            if (CollUtil.isNotEmpty(vo.getChild())) {
                recursionEq(vo.getChild(), excels);
            }
        }
    }

@Data
public class RowParentPo {

    private String row;

    private String parent;

    private String str = "0";

    private List<RowParentPo> child;

    private Integer sort;

    public RowParentPo() {
    }

    public RowParentPo(String row, String parent) {
        this.row = row;
        this.parent = parent;
    }

    public Integer getSort() {
        return Integer.valueOf(row);
    }

}