Close





 
Subscribe to this RSS feed
Wednesday, 19 January 2011 06:50

How to Create Group in Excel

What is Excel Group?

Excel has some pretty nifty features for those who know where to find them. Among the easier ones to use it is Group and Outline, which allows you to group large quantities of data. It is a basic and useful function for Excel. Here I will show you how to create group by using VB.NET/C# create group in Excel with Spire.XLS below.

How to Create Group with Spire.XLS?

Spire.XLS presents you an easiest way to create group in Excel worksheet. You may use sheet.GroupByRows and sheet.GroupByColumns methods to group rows and columns respectively. In sheet.GroupByRows method, you should give three parameters: First row of the group, last row of the group, the third one is a bool value, it specifies whether the collapsed.
Following is the demo, in this demo in order to reflect the effect conveniently, we delete other grids of the worksheet by assigning the sheet.GridLinesVisible property false. What's more, we apply a style and borders into it.
[C#]
using Spire.Xls;
using System.Drawing;

namespace Edit
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook
            Workbook workbook = new Workbook();

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

            //Country
            sheet.Range["A1"].Value = "Country";
            sheet.Range["A2"].Value = "Cuba";
            sheet.Range["A3"].Value = "Mexico";
            sheet.Range["A4"].Value = "France";
            sheet.Range["A5"].Value = "German";

            //Jun
            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 6000;
            sheet.Range["B3"].NumberValue = 8000;
            sheet.Range["B4"].NumberValue = 9000;
            sheet.Range["B5"].NumberValue = 8500;

            //Aug
            sheet.Range["C1"].Value = "Aug";
            sheet.Range["C2"].NumberValue = 3000;
            sheet.Range["C3"].NumberValue = 2000;
            sheet.Range["C4"].NumberValue = 2300;
            sheet.Range["C5"].NumberValue = 4200;

            //Style
            sheet.Range["A1:C1"].Style.Font.IsBold = true;
            sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
            sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;

            //Border
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Sheet line style
            sheet.GridLinesVisible = false;

            //Group rows
            sheet.GroupByRows(1, 5, false);

            //Group columns
            sheet.GroupByColumns(1, 3, false);

            //Save the file
            workbook.SaveToFile("Sample.xls");

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
}
[Visual Basic]
Imports Spire.Xls
Imports System.Drawing

Module Module1

    Sub Main()
        'Create a new workbook
        Dim workbook As New Workbook()

        'Initialize worksheet
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'Country
        sheet.Range("A1").Value = "Country"
        sheet.Range("A2").Value = "Cuba"
        sheet.Range("A3").Value = "Mexico"
        sheet.Range("A4").Value = "France"
        sheet.Range("A5").Value = "German"

        'Jun
        sheet.Range("B1").Value = "Jun"
        sheet.Range("B2").NumberValue = 6000
        sheet.Range("B3").NumberValue = 8000
        sheet.Range("B4").NumberValue = 9000
        sheet.Range("B5").NumberValue = 8500

        'Aug
        sheet.Range("C1").Value = "Aug"
        sheet.Range("C2").NumberValue = 3000
        sheet.Range("C3").NumberValue = 2000
        sheet.Range("C4").NumberValue = 2300
        sheet.Range("C5").NumberValue = 4200

        'Style
        sheet.Range("A1:C1").Style.Font.IsBold = True
        sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow
        sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange
        sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise

        'Border
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        'Set number format
        sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"

        'Set worksheet style
        sheet.GridLinesVisible = False

        'Group rows
        sheet.GroupByRows(1,5,False)

        'Group columns
        sheet.GroupByColumns(1,3,False)

        'Save doc file.
        workbook.SaveToFile("Sample.xls")

        'Launch the file.
        System.Diagnostics.Process.Start("Sample.xls")
    End Sub
End Module
After running the demo, you will find the worksheet is divided into a group.
(Back to content)
Published in Program Guide
Read more...
Saturday, 03 July 2010 10:01

EXCEL Group for C#, VB.NET

 

The sample demonstrates how to create group in an excel workbook..

Group.gif

Published in Group
Read more...