How to: Group Data

• Dec 23, 2019
• 5 minutes to read

When performing a mail merge with the SpreadsheetControl, you can split sorted data into groups based on identical values in a data field (or data fields). This example describes how to generate invoices (each invoice is generated in a separate worksheet) based on data from the bound database. Data records are sorted and grouped so that each group of records represents an individual invoice, and has the following structure: a group header displaying general order information, a list of products in the order and a group footer displaying order totals.

This tutorial consists of the following sections.

1. Create an Application and Bind It to a Data Source
2. Prepare a Template
3. Group Data
4. How Group Headers and Footers are Saved in a Template
5. Get the Result

Create an Application and Bind It to a Data Source

1. Create a new Windows Forms Application with the SpreadsheetControl instance. Add the Field Lists panel and Ribbon UI (the File, Home and Mail Merge tabs).

2. Copy the Northwind database (the nwind.mdb file located in the C:\Users\Public\Documents\DevExpress Demos 23.2\Components\Data path, by default) to your project directory, include it in the project and select the Invoices view as the data source for the mail merge.

   The How to: Perform a Mail Merge document describes these first two steps in greater detail.

3. Populate the underlying InvoicesDataTable table with data from the database using the Fill method of the InvoicesTableAdapter object.

   To assign the Invoices view as a data source for mail merge in the SpreadsheetControl, set the IWorkbook.MailMergeDataSource property to the nwindDataSet object, and the IWorkbook.MailMergeDataMember property to the “Invoices” string.

   Form1.cs

   using DevExpress.XtraBars.Ribbon;
   using DevExpress.Spreadsheet;
   
   namespace DXApplication1 {
       public partial class Form1 : RibbonForm {
   
           nwindDataSet dataSet;
           nwindDataSetTableAdapters.InvoicesTableAdapter adapter;
           IWorkbook template;
   
           public Form1() {
               InitializeComponent();
   
               dataSet = new nwindDataSet();
               adapter = new nwindDataSetTableAdapters.InvoicesTableAdapter();
               adapter.Fill(dataSet.Invoices);
           }
   
           private void Form1_Load(object sender, System.EventArgs e) {
               spreadsheetControl1.LoadDocument("Documents\\MailMergeTemplate_GroupData.xlsx");
               template = spreadsheetControl1.Document;
               template.MailMergeDataSource = dataSet;
               template.MailMergeDataMember = "Invoices";
           }
       }
   }
   

   Form1.vb

   Imports DevExpress.XtraBars.Ribbon
   Imports DevExpress.Spreadsheet
   
   Namespace DXApplication1
       Partial Public Class Form1
           Inherits RibbonForm
   
           Private dataSet As nwindDataSet
           Private adapter As nwindDataSetTableAdapters.InvoicesTableAdapter
           Private template As IWorkbook
   
           Public Sub New()
               InitializeComponent()
   
               dataSet = New nwindDataSet()
               adapter = New nwindDataSetTableAdapters.InvoicesTableAdapter()
               adapter.Fill(dataSet.Invoices)
           End Sub
   
           Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
               spreadsheetControl1.LoadDocument("Documents\MailMergeTemplate_GroupData.xlsx")
               template = spreadsheetControl1.Document
               template.MailMergeDataSource = dataSet
               template.MailMergeDataMember = "Invoices"
           End Sub
       End Class
   End Namespace
   

Prepare a Template

Run the application. By default, the SpreadsheetControl opens a workbook with a single empty worksheet (“Sheet1”). With the following steps, you will transform this worksheet into a template for invoices.

• Activate the Mail Merge Design View in the SpreadsheetControl by clicking the Mail Merge Design View button on the Mail Merge Ribbon tab, in the Design group.
• Insert mail merge fields into template cells by dragging the required items from the Field List panel.
• Specify the Detail range to display invoice data (this range contains all mail merge fields), and the Header range to display the current date above each invoice.
• Set the mail merge mode to Multiple Sheets to generate each invoice in a separate worksheet.

Group Data

In this example, group data by orders. To do this, follow the steps below.

1. Select any cell within the Detail range and click the Sort Fields button in the Ribbon.
2. In the invoked dialog, click New and select the OrderID data field to be used as a criterion for sorting data. This field will also be used as a criterion for grouping data.

3. Create a group header to display general information for a group of records. In this example, each group is an order. So, the group header should be the “B4:H12” range, which contains order data such as order ID, customer, salesperson, dates, information on delivery, company to be billed, etc. The range also includes a row that serve as a header for the list of products ordered .

   Select the “B4:H12” cell range and click the Group Header button in the Sort & Group group on the Mail Merge Ribbon page. This will invoke the Select Sort Field dialog. In this dialog, choose the OrderID(Ascending) sort field, to use it as a criterion for creating groups in the merged document and click OK. The GroupHeader0 range will appear in the template worksheet.

4. Create a group footer to display totals at the end of a group of records (at the end of an order). To do this, select the “B14:H16” range, including the rows with the SubTotal, Freight and Total calculations, click Group Footer in the Ribbon and choose OrderID(Ascending) in the Select Sort Field dialog. The GroupFooter0 range will appear in the template worksheet.

A group header and group footer can be created only within the detail range for which you set criteria to sort data. You can create multiple group headers and footers for different sort fields within a detail range.

When the SpreadsheetControl groups data by the specified sort field, it also sorts groups by this field in the specified order. To sort records within groups, add an auxiliary sort criteria in the detail range.

How Group Headers and Footers are Saved in a Template

Each group header or group footer range specified in the mail merge template is stored as a “GROUPHEADER(n)” or “GROUPFOOTER(n)” defined name, respectively, where (n) is the zero-based index of the group header or group footer in the entire template. A cell range assigned to be a group header or group footer is set to the DefinedName.RefersTo property, and the sort field used for creating a group header or footer is set to DefinedName.Comment.

For example, after you have specified a sort criterion, a group header and a group footer as described above, the following defined names are added to the Worksheet.DefinedNames collection of the template worksheet.

Name	RefersTo	Comment
SORTFIELD0	OrderID	Ascending
GROUPHEADER0	Sheet1!$B$4:$H$12	SORTFIELD0
GROUPFOOTER0	=Sheet1!$B$14:$H$16	SORTFIELD0

Get the Result

Click the Mail Merge Preview button to review the result. Each order is inserted in a separate worksheet.