Document Solutions for Excel, Java Edition | Document Solutions
Features / Group / Row or Column Group Information
In This Topic
    Row or Column Group Information
    In This Topic

    DsExcel allows you to retrieve the information of row or column groups by using the getRowGroupInfo or getColumnGroupInfo method of IOutline interface.

    You can identify the cell ranges where the grouping exists and can expand or collapse the groups by using the expand and collapse methods of IGroupInfo interface.

    The IGroupInfo interface also provides getStartIndex, getEndIndex, getLevel, getParent, getChildren and IsCollapsed methods which can be used to retrieve grouping  information such as start or end index, level, parent, children or collpased status of the group.

    Get Row Group Information

    Refer to the below example code which uses getRowGroupInfo method to get the row group information, collapse method to collapse groups and identifies the rows where row level is two:

    Java
    Copy Code 
    //create a new workbook
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getActiveSheet();
IRange targetRange = sheet.getRange("A1:C9");
// Set data
targetRange.setValue(new Object[][]
{
    {"Player", "Side", "Commander"},
    {1, "Soviet", "AI"},
    {2, "Soviet", "AI"},
    {3, "Soviet", "Human"},
    {4, "Allied", "Human"},
    {5, "Allied", "Human"},
    {6, "Allied", "AI"},
    {7, "Empire", "AI"},
    {8, "Empire", "AI"}
});

// Subtotal
targetRange.subtotal(
    2, // Side
    ConsolidationFunction.Count,
    new int[] { 2 } // Side
);

List<IGroupInfo> groupInfo = sheet.getOutline().getRowGroupInfo();

HashMap<Integer, Integer> rowInfo = new HashMap<>();

for (IGroupInfo item : groupInfo) {
    if (item.getChildren() != null) {
        for (IGroupInfo childItem : item.getChildren()) {
            if (childItem.getStartIndex() > 3) {
                childItem.collapse();
            }
            if (childItem.getLevel() == 2) {
                rowInfo.put(childItem.getStartIndex(), childItem.getEndIndex());
            }
        }
    }
}

StringBuilder builder = new StringBuilder();
for (Map.Entry<Integer, Integer> item : rowInfo.entrySet()) {
    builder.append("row " + (item.getKey() + 1) + " to row " + (item.getValue() + 1) + ", ");
}

sheet.getRange("A15").setValue("The rows where the group level is 2 are: " + builder.toString());
sheet.getRange("A15").setRowHeight(25);
sheet.getRange("A15").getFont().setColor(Color.GetRed());
sheet.getRange("A15").getFont().setSize(15);

//save to an excel file
workbook.save("GetRowGroupInfo.xlsx");

    Get Column Group Information

    Refer to the below example code which uses getColumnGroupInfo method to get the row group information and collapse method to collapse groups:

    Java
    Copy Code 
    //create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
Object data = new Object[][]{
    {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
    {"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
    {"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
    {"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
    {"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
    {"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
    {"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

worksheet.getRange("A1:F7").setValue(data);
worksheet.getRange("A:F").setColumnWidth(15);

worksheet.getRange("A:F").group();
worksheet.getRange("A:B").group();
worksheet.getRange("D:E").group();

List<IGroupInfo> groupInfo = worksheet.getOutline().getColumnGroupInfo();
HashMap<Integer, Integer> colInfo = new HashMap<>();

for (IGroupInfo item : groupInfo) {
    if (item.getChildren() != null) {
        for (IGroupInfo childItem : item.getChildren()) {
            if (childItem.getStartIndex() > 2) {
                childItem.collapse();
            }
            if (childItem.getLevel() == 2) {
                colInfo.put(childItem.getStartIndex(), childItem.getEndIndex());
            }
        }
    }
}

StringBuilder builder = new StringBuilder();
for (Map.Entry<Integer, Integer> item : colInfo.entrySet()) {
builder.append("column " + (item.getKey() + 1) + " to column " + (item.getValue() + 1) + ", ");
}

worksheet.getRange("A12").setValue("The columns where the group level is 2 are: " + builder.toString());
worksheet.getRange("A12").setRowHeight(25);
worksheet.getRange("A12").getFont().setColor(Color.GetRed());
worksheet.getRange("A12").getFont().setSize(15);

//save to an excel file
workbook.save("GetColumnInfo.xlsx");