[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications, and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [Expand]Chart Control
  [Expand]Map Control
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Tables
   [Expand]Charts and Graphics
   [Expand]Mail Merge
    Operation Restrictions
    Find and Replace
   [Expand]Visual Elements
   [Expand]Keyboard Shortcuts
    [Expand]Rows and Columns
    [Expand]Import and Export Data
    [Expand]Data Binding
    [Collapse]Mail Merge
      How to: Perform a Mail Merge
      How to: Create a Master-Detail Report
      How to: Sort Data
      How to: Group Data
      How to: Filter Data
      How to: Create a Master-Detail Report Without Coding
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Pivot Tables
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

How to: Group Data

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

Expanded 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 19.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.

Expanded 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.

Expanded 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.

Expanded 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

Expanded Get the Result

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

Expanded See Also

Is this topic helpful?​​​​​​​