Row or Column Group Information

GcExcel allows you to retrieve the information of row or column groups by using the RowGroupInfo or ColumnGroupInfo property 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 StartIndex, EndIndex, Level, Parent, Children and IsCollapsed properties 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 RowGroupInfo property to get the row group information, Collapse method to collapse groups and identifies the rows where row level is two:

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var sheet = workbook.ActiveSheet;
object[,] data = new object[,]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165},
{"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134},
{"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163},
{"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176},
{"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145}
};

sheet.Range["A1:F7"].Value = data;
sheet.Range["A:F"].ColumnWidth = 15;

sheet.Range["A:F"].Group();
sheet.Range["A:B"].Group();
sheet.Range["D:E"].Group();

//get group information and collapes some group.
var groupInfo = sheet.Outline.ColumnGroupInfo;
foreach (var item in groupInfo)
{
    if (item.Children != null)
    {
        foreach (var childItem in item.Children)
        {
            if (childItem.StartIndex > 2)
            {
                childItem.Collapse();
            }
        }
    }
}

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

Get Column Group Information

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

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var sheet = workbook.ActiveSheet;
IRange targetRange = sheet.Range["A1:C9"];
// Set data
targetRange.Value = 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(groupBy: 2, // group by 'Side' column
    subtotalFunction: ConsolidationFunction.Count,
    totalList: new[] { 2 } // displays total of 'Side' column
    );

targetRange.AutoFit();

//get group information and collapes some group.
var groupInfo = sheet.Outline.RowGroupInfo;
var rowInfo = new Dictionary();
foreach (var item in groupInfo)
{
    if (item.Children != null)
    {
        foreach (var childItem in item.Children)
        {
            if (childItem.StartIndex > 3)
            {
                childItem.Collapse();
            }
            if (childItem.Level == 2)
            {
                rowInfo.Add(childItem.StartIndex, childItem.EndIndex);
            }
        }
    }
}

StringBuilder builder = new StringBuilder();
foreach (var item in rowInfo)
{
    builder.Append("row " + (item.Key + 1) + " to row " + (item.Value + 1) + ", ");
}

sheet.Range["A15"].Value = "The rows where the group level is 2 are: " + builder.ToString();
sheet.Range["A15"].RowHeight = 25;
sheet.Range["A15"].Font.Color = Color.Red;
sheet.Range["A15"].Font.Size = 15;

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