windowsforms-16986-controls-and-libraries-spreadsheet-examples-mail-merge-how-to-group-data.md
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.
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).
Copy the Northwind database (the nwind.mdb file located in the C:\Users\Public\Documents\DevExpress Demos 25.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.
Populate the underlying InvoicesDataTable table with data from the database using the Fill method of the InvoicesTableAdapter object.
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.
In this example, group data by orders. To do this, follow the steps below.
Select any cell within the Detail range and click the Sort Fields button in the Ribbon.
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.
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 .
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.
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 |
Click the Mail Merge Preview button to review the result. Each order is inserted in a separate worksheet.
See Also