windowsforms-16942-controls-and-libraries-spreadsheet-examples-mail-merge-how-to-perform-a-mail-merge.md
This tutorial describes the process of creating a spreadsheet document based on data retrieved from a data source with the SpreadsheetControl’s mail merge functionality.
In this example, a spreadsheet document will be bound to the Categories table of the Northwind database using Visual Studio Designer. Alternatively, you can connect to a data source at run time, as described in the How to: Create a Master-Detail Report Without Coding document.
Copy the nwind.mdb file to the directory of your project. The default location of this file is C:\Users\Public\Documents\DevExpress Demos 25.2\Components\Data directory.
Right-click the nwind.mdb item in the Solution Explorer and select Include In Project in the invoked context menu. This will invoke the Data Source Configuration Wizard.
In the invoked window, select the Dataset database model and click Next.
The next page allows you to choose tables to be obtained from the database. Select the Categories table and click Finish.
As a result, Visual Studio will generate a set of classes that support the ADO.NET architecture. The main classes include the following:
Populate the underlying CategoriesDataTable table with data from the database using the Fill method of the CategoriesTableAdapter object.
using DevExpress.Spreadsheet;
using DevExpress.XtraBars.Ribbon;
using System.Collections.Generic;
namespace DXApplication1 {
public partial class Form1 : RibbonForm {
nwindDataSet dataSet;
nwindDataSetTableAdapters.CategoriesTableAdapter adapter;
IWorkbook template;
public Form1() {
InitializeComponent();
dataSet = new nwindDataSet();
adapter = new nwindDataSetTableAdapters.CategoriesTableAdapter();
adapter.Fill(dataSet.Categories);
}
private void Form1_Load(object sender, System.EventArgs e) {
spreadsheetControl1.LoadDocument("Documents\\MailMergeTemplate.xlsx");
template = spreadsheetControl1.Document;
template.MailMergeDataSource = dataSet;
template.MailMergeDataMember = "Categories";
}
Private dataSet As nwindDataSet
Private adapter As nwindDataSetTableAdapters.CategoriesTableAdapter
Private template As IWorkbook
Public Sub New()
InitializeComponent()
dataSet = New nwindDataSet()
adapter = New nwindDataSetTableAdapters.CategoriesTableAdapter()
adapter.Fill(dataSet.Categories)
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
spreadsheetControl1.LoadDocument("Data\MailMergeTemplate.xlsx")
template = spreadsheetControl1.Document
template.MailMergeDataSource = dataSet
template.MailMergeDataMember = "Categories"
End Sub
After providing the data source, proceed with creating a mail merge template.
A template is a workbook with a single worksheet based on which merged documents will be generated. A template contains the information that will be the same in each merged document, as well as the fields that will be filled with unique data values retrieved from the bound data source in each merged document.
The easiest way to transform a document into a mail merge template is to use the SpreadsheetControl’s Mail Merge Ribbon UI. Run the application and modify the default document loaded into the SpreadsheetControl (which includes a single worksheet under the “Sheet1” name).
Activate the Mail Merge Design View in the SpreadsheetControl to display the template structure (fields and ranges To do this, click the Mail Merge Design View button on the Mail Merge tab, in the Design group.
A mail merge field is a placeholder in which a value from the specified data source field will appear in a merged document. To insert fields into template cells, use the following mail merge functions in cell formulas: FIELD and FIELDPICTURE. You can manually add formulas with these functions to cells, or simply drag the necessary data field items from the Field List panel and drop them onto the template cells. (The corresponding formulas will be added to the cells automatically.)
Insert the CategoryName , Description and Picture fields as shown in the image below.
When the WorksheetView.ShowFormulas option is disabled (default value), template cells containing mail merge fields display data field names enclosed in square brackets. In the Mail Merge Design View , these cells are marked with database icons.
The specified ranges will be highlighted in the template as shown below.
Note that after you have specified mail merge template ranges as described above, the “DETAILRANGE” and “HEADERRANGE” defined names are added for the corresponding cell ranges into the Worksheet.DefinedNames collection of the “Sheet1” worksheet.
| Name | RefersTo |
|---|---|
| DETAILRANGE | Sheet1!$B$4:$D$7 |
| HEADERRANGE | Sheet1!$B$2:$D$2 |
In this example, use the Single Sheet mail merge mode and Vertical document orientation. Merged ranges for all records of the data source will be inserted one under the other into a single worksheet. This is the default mail merge mode and document orientation.
The mail merge template is now ready. Save it to the MailMergeTemplate.xlsx file in the Documents folder added previously to the project directory.
Add the following code to automatically load the created template into the SpreadsheetControl when invoking the application.
spreadsheetControl1.LoadDocument("Documents\\MailMergeTemplate.xlsx");
spreadsheetControl1.LoadDocument("Documents\MailMergeTemplate.xlsx")
To preview how a document is generated and filled with data after a mail merge is performed, click the Mail Merge Preview button on the Mail Merge tab, in the Design group. A new window with a resulting document opened in a SpreadsheetControl instance will be invoked. You can modify and save the merged document via the Ribbon UI.
To finish a mail merge, call the IWorkbook.GenerateMailMergeDocuments method of the template workbook. It returns a collection of resulting workbooks. (If the “Single Sheet” or “Multiple Sheets” mail merge mode is used, the collection will contain a single workbook.) The resulting workbook can be saved to a file or a stream, or sent for further processing to another SpreadsheetControl.
In this example, add a new Result group to the Mail Merge tab and create a new button with the Result caption.
In the Result button’s Click event handler, call the IWorkbook.GenerateMailMergeDocuments method and save the resulting documents to files.
private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) {
IList<IWorkbook> resultWorkbooks = spreadsheetControl1.Document.GenerateMailMergeDocuments();
int index = 0;
foreach (IWorkbook workbook in resultWorkbooks) {
string fileName = string.Format("Documents\\SavedDocument{0}" + ".xlsx", index++);
workbook.SaveDocument(fileName, DocumentFormat.Xlsx);
}
System.Diagnostics.Process.Start("explorer.exe", "Documents\\");
}
Private Sub barButtonItem1_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barButtonItem1.ItemClick
Dim resultWorkbooks As IList(Of IWorkbook) = spreadsheetControl1.Document.GenerateMailMergeDocuments()
Dim index As Integer = 0
For Each workbook As IWorkbook In resultWorkbooks
Dim fileName As String = String.Format("Data\SavedDocument{0}" & ".xlsx", index)
index += 1
workbook.SaveDocument(fileName, DocumentFormat.Xlsx)
Next workbook
System.Diagnostics.Process.Start("explorer.exe ", Application.StartupPath + "\Data")
End Sub
See Also