With Silverlight-How to Create Excel Group

In Excel, we can divide data information into groups by rows or columns. One group can save a series of data. And the series of data may be one part of information in whole worksheet. So, it is convenient for users to create summary report with groups. For example, there is a report which saves staff information of each department in a company. User will create each department part as a group to show data information more clearly.

In this post, I want to introduce the method to create group in Excel with Silverlight. Also, Spire.XLS for Silverlight is used in this method. I have prepared an Excel worksheet which saves some information of vendors and I will create three groups by rows.

STEPS:

Step 1. Design User Control

Add a label in UserControl and change content which is a brief title about what I will do. Then, I set label background color, text color and font style to make appearance more beautiful. Next, add a RUN button.

MainPage.xaml

<UserControlx:Class="ExcelGroup.MainPage"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    mc:Ignorable="d"

    d:DesignHeight="211"d:DesignWidth="479"xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">

    <Gridx:Name="LayoutRoot"Background="#FFE6FFFF"Height="211"Width="478"Loaded="LayoutRoot_Loaded">

        <sdk:LabelHeight="97"HorizontalAlignment="Center"Margin="30,20,30,91"Name="label1"VerticalAlignment="Center"Width="394"Content="Assign Data with Group in Excel Worksheet"FontFamily="Times New Roman"FontSize="20"Foreground="White">

            <sdk:Label.Background>

                <LinearGradientBrushEndPoint="1,0.5"StartPoint="0,0.5">

                    <GradientStopColor="Black"Offset="0" />

                    <GradientStopColor="#FF2945E2"Offset="1" />

                    <GradientStopColor="#FF111D62"Offset="0" />

                    <GradientStopColor="#FF1E34AC"Offset="0" />

                </LinearGradientBrush>

            </sdk:Label.Background>

        </sdk:Label>

        <ButtonContent="RUN"Height="38"HorizontalAlignment="Left"Margin="400,147,0,0"Name="button1"VerticalAlignment="Top"Width="65"FontWeight="Bold"Click="button1_Click" />

    </Grid>

</UserControl>

Step 2. Add File

In this step, I need to load file which I want to create group. Firstly, right click project name to add an existed item (Excel file). Then, click this file and set its Build Action as Embedded Resource.

Step 3. Declare saveFileDialog

Declare a saveFileDialog for saving Excel file. Then, create a new workbook and set filter for saveFileDialog to choose which kind of Excel (Excel Version) I want to save. In this example, I set extension of Excel as .xlsx.

        Workbook workbook = null;

        SaveFileDialog saveFileDialog = new SaveFileDialog();

        public MainPage()

        {

            InitializeComponent();

            this.workbook = new Workbook();

            this.saveFileDialog.Filter = "Excel Document(*.xlsx)|*.xlsx";

        }

Step 4. Load File

Find Loaded Events and double click it to load the added file. Get Excel file name from assembly. If the name is the same as loaded file name, load this file from stream.

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)

        {

            Assembly assembly = this.GetType().Assembly;

            foreach (String name in assembly.GetManifestResourceNames())

            {

                if (name.EndsWith("VendorInfo.xlsx"))

                {

                    using (Stream fileStr = assembly.GetManifestResourceStream(name))

                    {

                        this.workbook.LoadFromStream(fileStr,ExcelVersion.Version2010);

                    }

                }

            }

        }

Step 5. Create Group

Double click RUN button and write code to create group. Firstly, initialize worksheet. Then, according to requirement, create group by rows with sheet.GroupByRows() method. Three methods are passed to this method, first row, last row and a bool value to judge if collapse group.

            Worksheet sheet = this.workbook.Worksheets[0];

            sheet.GroupByRows(2, 5, true);

            sheet.GroupByRows(7, 10, true);

            sheet.GroupByRows(12, 15, true);

Step 6. Save File

Judge if the saveFiledialog which has been declared can pop up. If yes, save the Excel file by using save file dialog box.

            bool? result = this.saveFileDialog.ShowDialog();

            if (result.HasValue && result.Value)

            {

                using (Stream stream = this.saveFileDialog.OpenFile())

                {

                    this.workbook.SaveToStream(stream);

                }

            }

Full MainPage.xaml.cs

using System;

using System.Windows;

using System.Windows.Controls;

using System.Reflection;

using System.IO;

using Spire.Xls;

namespace ExcelGroup

{

    public partial class MainPage : UserControl

    {

        Workbook workbook = null;

        SaveFileDialog saveFileDialog = new SaveFileDialog();

        public MainPage()

        {

            InitializeComponent();

            this.workbook = new Workbook();

            this.saveFileDialog.Filter = "Excel Document(*.xlsx)|*.xlsx";

        }

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)

        {

            Assembly assembly = this.GetType().Assembly;

            foreach (String name in assembly.GetManifestResourceNames())

            {

                if (name.EndsWith("VendorInfo.xlsx"))

                {

                    using (Stream fileStr = assembly.GetManifestResourceStream(name))

                    {

                        this.workbook.LoadFromStream(fileStr,ExcelVersion.Version2010);

                    }

                }

            }

        }

        private void button1_Click(object sender, RoutedEventArgs e)

        {

            Worksheet sheet = this.workbook.Worksheets[0];

            sheet.GroupByRows(2, 5, true);

            sheet.GroupByRows(7, 10, true);

            sheet.GroupByRows(12, 15, true);

            bool? result = this.saveFileDialog.ShowDialog();

            if (result.HasValue && result.Value)

            {

                using (Stream stream = this.saveFileDialog.OpenFile())

                {

                    this.workbook.SaveToStream(stream);

                }

            }

        }

    }

}

RESULT

_______________________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Spire.Office also can be used to realize this function.