NativeExcel2 for .NET Developer's Reference

Group rows and columns

Example

This example demonstrates how to group rows and columns.

Output file: console-group.xls

[C#]
using System;
using System.Drawing;
using NativeExcel;

namespace Console_Group
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-group.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();

            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();

            //Range A3:F20 
            IRange level2 = sheet.Range[3,1,20,6]; 
            level2.Rows.Group();
            level2.Value = level2.Rows.OutlineLevel;
            level2.Interior.Color = Color.White;

            //Range B5:E9 
            IRange level3 = sheet.Range[5,2,9,5]; 
            level3.Rows.Group();
            level3.Value = level3.Rows.OutlineLevel;
            level3.Interior.Color = Color.LightGray;

            //Range B11:E19 
            level3 = sheet.Range[11,2,19,5]; 
            level3.Rows.Group();
            level3.Value = level3.Rows.OutlineLevel;;
            level3.Interior.Color = Color.LightGray;
            //Collapse range
            level3.Rows.Hidden = true; 

            //column width
            sheet.UsedRange.Columns.ColumnWidth = 3;

            //Save workbook
            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports System.Drawing
imports NativeExcel

Module Console_Group

    Sub Main()
        Dim FileName As String = "console-group.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)
        'Create a new workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()

        'Add worksheet
        Dim sheet As IWorksheet = book.Worksheets.Add()

        'Range A3:F20 
        Dim level2 As IRange = sheet.Range(3,1,20,6)
        level2.Rows.Group()
        level2.Value = level2.Rows.OutlineLevel
        level2.Interior.Color = Color.White

        'Range B5:E9 
        Dim level3 As IRange = sheet.Range(5,2,9,5) 
        level3.Rows.Group()
        level3.Value = level3.Rows.OutlineLevel
        level3.Interior.Color = Color.LightGray

        'Range B11:E19 
        level3 = sheet.Range(11,2,19,5) 
        level3.Rows.Group()
        level3.Value = level3.Rows.OutlineLevel
        level3.Interior.Color = Color.LightGray
        'Collapse range
        level3.Rows.Hidden = True 

        'column width
        sheet.UsedRange.Columns.ColumnWidth = 3

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module