Excel obtains hierarchical display, folding and expansion, parent-child relationship
apache poi 4.1.2 is provided through the methods Row.getOutlineLevel and Row.getZeroHeight
We can iterate through all the rows in the worksheet and have methods that can collect row group information from a row to determine the parent and child of each row. In the following code this is the method
//Row level int findStartOfRowOutlineGroup(Row row) int findEndOfRowOutlineGroup(Row row) boolean getIsWholeRowGroupHidden(Sheet sheet, int startOfRowOutlineGroup, int endOfRowOutlineGroup)
//Column level 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();
}
}
Above is the reference:
https://www.nuomiphp.com/eplan/173104.html
The following code is the code to construct Figure 1
@ApiOperation(value = "excel automatic grading")
@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;
// Skip the first line here. We will add top-level nodes later.
for (Row row : sheet) {<!-- -->
MsgDto dto = getRowGroupInfos(row);
if(dto == null || dto.getRow() == 1) {<!-- -->
continue;
}
//Here is the key
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));
}
}
// Traverse two
for (Row row : sheet) {<!-- -->
MsgDto dto = getRowGroupInfos(row);
if(dto == null || dto.getRow() == 1) {<!-- -->
continue;
}
String parentNumberStr = StrUtil.join("",dto.getStartOfRowOutlineGroup(), dto.getEndOfRowOutlineGroup());
//Here is key two
list.add(new RowParentPo(dto.getRow().toString(), parentmap.get(parentNumberStr)));
}
//Add top-level nodes
list.add(new RowParentPo("1","0"));
// This place can use your own method to construct a tree structure based on parent-child levels, if you want to use mine. Please refer to my other article
// https://blog.csdn.net/qq_37749537/article/details/128949451
TreeUtils<RowParentPo, String> tree = new TreeUtils<>(
list, RowParentPo::getRow,
RowParentPo::getParent,
RowParentPo::setChild);
tree.build();
// The parent-child construction of excel level has been completed here.
List<RowParentPo> jsonToList = list.stream().filter(f -> f.getRow().equals("1")).collect(Collectors.toList());
// generate
\t\t//\t 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();
//Flat
recursionEq(jsonToList, excels);
// Sort by line number
excels = excels.stream()
.sorted(Comparator.comparing(RowParentPo::getSort))
.collect(Collectors.toList());
//The number output by the console can be directly pasted into 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);
}
}