Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [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]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [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]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap 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]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

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

    Show Me

    A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T110288.

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
GROUPHEADER0 Sheet1!$B$4:$H$12 SORTFIELD0
GROUPFOOTER0 =Sheet1!$B$14:$H$16 SORTFIELD0

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

How would you rate this topic?​​​​​​​