Document Solutions for Excel, .NET 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 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:

    C#
    Copy Code 
    //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:

    C#
    Copy Code 
    //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<int, int>();
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");