how to validate a column, row or cell in apache poi is in a group and whether or not it is collapsed

A group is present if rows or columns have outline levels greater than 0. And a group is collapsed if all group members (rows or columns) are hidden.

For rows it is possible determining the outline level and hidden status of the rows. The current apache poi 4.1.2 provides that by methods Row.getOutlineLevel and Row.getZeroHeight. If a row has a outline level greater than 0, then it belongs to a group having that outline level. The group starts at the smallest previous row having an outline level which is not lower than the outline level of that row. The group ends at the biggest subsequent row having an outline level which is not lower than the outline level of that row. And if all consecutive rows between group's start row and group's end row are hidden, then the whole group is hidden and is collapsed.

So we could loop over all rows in the sheet and have methods which can collect row-group-information from a row. In following code this are methods

int findStartOfRowOutlineGroup(Row row)

and

int findEndOfRowOutlineGroup(Row row)

and

boolean getIsWholeRowGroupHidden(Sheet sheet, int startOfRowOutlineGroup, int endOfRowOutlineGroup)

For columns it is possible determining the outline level and hidden status of the columns from the sheet. The current apache poi 4.1.2 provides that by methods Sheet.getColumnOutlineLevel and Sheet.isColumnHidden. If a column has an outline level greater than 0, then it belongs to a group having that outline level. The group starts at the smallest previous column having an outline level which is not lower than the outline level of that column. The group ends at the biggest subsequent column having an outline level which is not lower than the outline level of that column. And if all consecutive columns between group's start column and group's end column are hidden, then the whole group is hidden and is collapsed.

So we could loop over all columns in the sheet and have methods which can collect col-group-information from the sheet. In following code this are methods

int findStartOfColOutlineGroup(Sheet sheet, int col)

and

int findEndOfColOutlineGroup(Sheet sheet, int col)

and

boolean getIsWholeColGroupHidden(Sheet sheet, int startOfColOutlineGroup, int endOfColOutlineGroup)

Complete example:

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();
 }
}