Documents for Excel .NET Edition | GCDocuments
Features / Group / Outline Subtotals
In This Topic
    Outline Subtotals
    In This Topic

    You can derive meaningful and summarized insights from outline data by applying the subtotals to the grouped values.

    In GcExcel, you can apply outline subtotals to organize the sorted data into groups and display subtotals at the end of each group. 

    Create Outline Subtotals

    The outline subtotals are created using the Subtotal method of IRange interface. The method provides different parameters to group by fields, assign subtotal function, replace existing subtotals, add page breaks and place summary data.

    The below sample data is used to create outline subtotals:

    Copy Code
    public void PopulateData(Workbook workbook)
        IWorksheet worksheet = workbook.Worksheets[0];
        // Defining data in the range           
        worksheet.Range["A1:C20"].Value = new object[,]
            {"Item", "Units", "Unit Price"},
            {"Pen Set", 62, 4.99},
            {"Binder", 29, 1.99},
            {"Pen Set", 55, 12.49},
            {"Binder", 81, 19.99},
            {"Pen Set", 42, 23.95},
            {"Pencil", 35, 4.99},
            {"Desk", 3,  275},
            {"Desk", 2, 125},
            {"Pencil", 7, 1.29},
            {"Pen Set", 16, 15.99},
            {"Pen", 76, 1.99},
            {"Binder", 28, 8.99},
            {"Binder", 57, 19.99},
            {"Pen", 64, 8.99},
            {"Pencil", 14, 1.29},
            {"Pen", 15, 19.99},
            {"Binder", 11, 4.99},
            {"Pen Set", 96, 4.99},
            {"Binder", 94, 19.99}

    Refer to the below example code to create outline subtotals.

    Copy Code
    IWorksheet _worksheet = workbook.Worksheets[0];
    //Sort by value, use Sort() method.
    _worksheet.Range["A2:C20"].Sort(_worksheet.Range["A2:A20"], orientation: SortOrientation.Columns);
    //Create groups and sub-total the grouped values using Subtotal() method
    _worksheet.Range["$A$1:$D$20"].Subtotal(1, ConsolidationFunction.Sum, new[] { 2, 3 });
    //Save workbook

    Outline subtotals

    Remove Outline Subtotals

    The outline subtotals can be removed using the RemoveSubtotal method of the IRange interface.

    Refer to the below example code to remove outline subtotals.

    Copy Code
    Workbook workbook = new Workbook();
    IWorksheet _worksheet = workbook.Worksheets[0];
    //Remove Subtotals, pass the cell range inclusive of the subtotal/total rows
    //Save workbook