How to Set Group by Rows and Columns in Excel with C#

Introduction

Excel Group is used to classify data information with categories. If information in a workbook has been divided into several parts, we can form each part as a group. For example, each product information will be set as a group in a sales report. 

Generally speaking, group is created by rows or columns. Therefore, it is very important to confirm data range before creating group. Also,  data in one group can be hide or display. After setting, we can find a “-” symbol and the group data is presented. Click it. It will become “+” and the data is hidden.

In this post, I will show the method about how to create Excel group according to data information by using C#. 

Background

In my example, I have an Excel file which saves personal information of vip customers. I will separate information from two groups based on rows and columns. The following steps show details.

And, Spire.XLS, a component to operate Excel for .Net, so I add its dll file as reference in project.

Steps

  1. Load file which I want to create group.
  2. Initialize worksheet. In order to make the appearance be more wonderful, set the grid lines as invisible.
  3. Create Groups. There are two methods, GroupByRows, GroupByColumns. For each method, we need to pass three parameters: first row/column, last row/column and a bool value which defines if the group will be folded.
  4. Save and Launch file.

Main Coding

using System;
using Spire.Xls;

namespace Excel_Group
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\VIP.xlsx”);

            //Initialize worksheet
            Worksheet sheet = workbook.Worksheets[0];
            sheet.GridLinesVisible = false;

            //Set Group by Rows and Columns
            sheet.GroupByRows(1, 10, false);
            sheet.GroupByColumns(1, 5, false);

            //Save and Launch File
            workbook.SaveToFile(“Group.xlsx”,ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result Shown by Following:

_____________________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s