Mail Merge in Rich Text Editor
- 16 minutes to read
This article shows how you can implement Mail Merge with Word Processing Document API. The example involves the following steps:
- Load a Document Template
- Add Mail Merge Fields to the Template
- Add a Data Source
- Merge Data into Template and Save Document
Load a Document Template
The template is a document that contains field placeholders. You will later replace these placeholders with actual fields that refer to data source columns.
Call the RichEditControl.LoadDocument or RichEditControl.LoadDocumentTemplate method to load a document template.
The code sample below loads the following template:
using DevExpress.Office.Services;
using DevExpress.Xpf.RichEdit;
using DevExpress.XtraRichEdit.API.Native;
richEditControl.LoadDocument("template.docx");
AddMailMergeFields(richEditControl.Document);
//...
Add Mail Merge Fields
The FieldCollection.Create method allows you to add fields to the template. Use one of the following fields to insert data from the database:
- MERGEFIELD
- Inserts plain text.
- DOCVARIABLE
- Inserts formatted content: variables, document elements, or entire document content.
- INCLUDEPICTURE
Inserts images. Use the INCLUDEPICTURE field as follows:
{INCLUDEPICTURE "{PictureLocation}" \d}(the PictureLocation is the path to the desired picture).Use the IUriStreamProvider service to insert pictures from a database.
The code sample below shows how to insert MERGEFIELD and INCLUDEPICTURE fields in the template. The first INCLUDEPICTURE field inserts an image from a database. (The "dbimg://" prefix is a stream generated by the IUriStreamProvider implementation). The second field inserts a static image from a file.
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...
private void AddMailMergeFields(Document document)
{
DocumentRange[] pictureRanges = document.FindAll("Photo", SearchOptions.WholeWord);
foreach (var pictureRange in pictureRanges)
{
DocumentPosition picturePosition = pictureRange.End;
// Delete the placeholder
document.Delete(pictureRange);
// Insert a field to a picture from a database
Field pictureField = document.Fields.Create(picturePosition, @"INCLUDEPICTURE ""dbimg://{ placeholder }""");
// Find a placeholder for a nested MERGEFIELD
DocumentRange nestedFieldRange =
document.FindAll("{ placeholder }", SearchOptions.WholeWord, pictureField.CodeRange).First();
// Clear the placeholder range
document.Delete(nestedFieldRange);
// Create a nested field
document.Fields.Create(nestedFieldRange.Start, "MERGEFIELD EmployeeID");
}
// Find a placeholder for another image
// in the footer
SubDocument footer = document.Sections[0].BeginUpdateFooter();
DocumentRange[] imageRanges =
footer.FindAll("image", SearchOptions.WholeWord);
foreach (var imageRange in imageRanges)
{
DocumentPosition imagePosition = imageRange.End;
// Delete the phrase
footer.Delete(imageRange);
// Create a field at the placeholder's position
footer.Fields.Create(imagePosition, @"INCLUDEPICTURE ""DevExpress-Logo-Large-Color.png""");
}
footer.EndUpdate();
// Find a placeholder for the FirstName field:
DocumentRange[] nameRanges =
document.FindAll("FirstName", SearchOptions.WholeWord);
foreach (var nameRange in nameRanges)
{
DocumentPosition namePosition = nameRange.End;
// Delete the phrase
document.Delete(nameRange);
// Create a field at the placeholder position
document.Fields.Create(namePosition, @"MERGEFIELD ""FirstName""");
}
}
IUriStreamProvider Service Implementation
The code sample below shows the IUriStreamProvider implementation used to insert images from a database. The INCLUDEPICTURE field in the template has a nested MERGEFIELD that refers to the EmployeeID field from the database. The IUriStreamProvider.GetStream method parses the received URI (the INCLUDEPICTURE field), finds the required data row, and returns the MemoryStream with an image.
Note
Make sure that the MERGEFIELD field nested in the INCLUDEPICTURE field refers to the data table’s primary key. Otherwise, the IUriStreamProvider service cannot correctly find the required table row.
using DevExpress.Office.Services;
using System;
using System.Data;
using System.IO;
public class ImageStreamProvider : IUriStreamProvider
{
static readonly string prefix = "dbimg://";
DataTable table;
string columnName;
public ImageStreamProvider(DataTable sourceTable, string imageColumn)
{
this.table = sourceTable;
this.columnName = imageColumn;
}
public Stream GetStream(string uri)
{
// Parse the retrieved URI string
uri = uri.Trim();
if (!uri.StartsWith(prefix))
return null;
// Remove the prefix from the retrieved URI string
string strId = uri.Substring(prefix.Length).Trim();
int id;
// Check if the string contains the primary key
if (!int.TryParse(strId, out id))
return null;
// Retrieve the row that corresponds
// with the key
DataRow row = table.Rows.Find(id);
if (row == null)
return null;
// Convert the image string from this row
// to a byte array
byte[] bytes = Convert.FromBase64String(row[columnName] as string) as byte[];
if (bytes == null)
return null;
// Return the MemoryStream with an image
MemoryStream memoryStream = new MemoryStream(bytes);
return memoryStream;
}
}
Use DOCVARIABLE Fields in the Mail Merge Template
The DOCVARIABLE field allows you to insert any type of content into a document – from a simple variable to dynamic data from a database or another document. Variables can be stored in a document or calculated within the RichEditControl.CalculateDocumentVariable event.
The code sample below demonstrates how to handle the event to calculate the DOCVARIABLE field value. In this example, this field should insert a number of years the employee worked at a company. The nested MERGEFIELD refers to the HireDate entry in the database.
private static void RichEditControl_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
if (e.Arguments.Count > 0)
{
// Retrieve the MERGEFIELD field value
DateTimeOffset hireDate = Convert.ToDateTime(e.Arguments[0].Value);
DateTimeOffset currentDate = DateTime.Now;
// Calculate the difference between the current date
// and the hire date
var dif = currentDate.Subtract(hireDate);
// Calculate the number of years
int years = dif.Days / 365;
// Specify the DOCVARIABLE field value
e.Value = years.ToString();
e.Handled = true;
}
e.Handled = true;
}
Add a Data Source
RichEditControl supports the following data source types:
System.Collections.IListSystem.Collections.ArrayListSystem.Data.DataTable
Use the RichEditMailMergeOptions.DataSource property to specify the data source used in a mail merge. If the data source contains multiple data tables, use the RichEditMailMergeOptions.DataMember property to define a specific data member.
The code sample below shows how to use the XML data source:
using DevExpress.XtraRichEdit;
using System.Data;
DataSet xmlDataSet = new DataSet();
xmlDataSet.ReadXml("Employees.xml");
xmlDataSet.Tables[0].PrimaryKey = new DataColumn[] {
xmlDataSet.Tables[0].Columns[0] };
richEditControl.Options.MailMerge.DataSource = xmlDataSet.Tables[0];
//...
Merge Data into Template and Save the Document
Call the RichEditControl.MailMerge method to merge data into a template and save the document to the specified file or stream. You can use the MailMergeOptions class properties to specify additional mail merge options. Pass the MailMergeOptions object as the MailMerge method parameter to apply these options.
Note
The control’s Options.MailMerge.DataSource property is ignored if you call the MailMerge method and pass a MailMergeOptions object as a parameter. Use the method parameter’s DataSource property to specify the data source instead.
The code sample below specifies mail merge options, runs the mail merge, and saves the document to the specified file.
using DevExpress.Office.Services;
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...
RichEditControl.CalculateDocumentVariable += RichEditControl_CalculateDocumentVariable;
// Register the URI provider service
IUriStreamService uriStreamService = richEditControl.GetService<IUriStreamService>();
uriStreamService.RegisterProvider(new ImageStreamProvider(xmlDataSet.Tables[0], "Photo"));
MailMergeOptions myMergeOptions =
richEditControl.Document.CreateMailMergeOptions();
myMergeOptions.DataSource = xmlDataSet.Tables[0];
myMergeOptions.MergeMode = MergeMode.NewSection;
richEditControl.MailMerge(myMergeOptions, "result.docx", DocumentFormat.OpenXml);
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...
private void AddMailMergeFields(Document document)
{
DocumentRange[] pictureRanges = document.FindAll("Photo", SearchOptions.WholeWord);
foreach (var pictureRange in pictureRanges)
{
DocumentPosition picturePosition = pictureRange.End;
// Delete the placeholder
document.Delete(pictureRange);
// Insert a field to a picture from a database
Field pictureField = document.Fields.Create(picturePosition, @"INCLUDEPICTURE ""dbimg://{ placeholder }""");
// Find a placeholder for a nested MERGEFIELD
DocumentRange nestedFieldRange =
document.FindAll("{ placeholder }", SearchOptions.WholeWord, pictureField.CodeRange).First();
// Clear the placeholder range
document.Delete(nestedFieldRange);
// Create a nested field
document.Fields.Create(nestedFieldRange.Start, "MERGEFIELD EmployeeID");
}
// Find a placeholder for another image
// in the footer
SubDocument footer = document.Sections[0].BeginUpdateFooter();
DocumentRange[] imageRanges =
footer.FindAll("image", SearchOptions.WholeWord);
foreach (var imageRange in imageRanges)
{
DocumentPosition imagePosition = imageRange.End;
// Delete the phrase
footer.Delete(imageRange);
// Create a field at the placeholder's position
footer.Fields.Create(imagePosition, @"INCLUDEPICTURE ""DevExpress-Logo-Large-Color.png""");
}
footer.EndUpdate();
// Find a placeholder for the FirstName field:
DocumentRange[] nameRanges =
document.FindAll("FirstName", SearchOptions.WholeWord);
foreach (var nameRange in nameRanges)
{
DocumentPosition namePosition = nameRange.End;
// Delete the phrase
document.Delete(nameRange);
// Create a field at the placeholder position
document.Fields.Create(namePosition, @"MERGEFIELD ""FirstName""");
}
}
IUriStreamProvider Service Implementation
The code sample below shows the IUriStreamProvider implementation used to insert images from a database. The INCLUDEPICTURE field in the template has a nested MERGEFIELD that refers to the EmployeeID field from the database. The IUriStreamProvider.GetStream method parses the received URI (the INCLUDEPICTURE field), finds the required data row, and returns the MemoryStream with an image.
Note
Make sure that the MERGEFIELD field nested in the INCLUDEPICTURE field refers to the data table’s primary key. Otherwise, the IUriStreamProvider service cannot correctly find the required table row.
using DevExpress.Office.Services;
using System;
using System.Data;
using System.IO;
public class ImageStreamProvider : IUriStreamProvider
{
static readonly string prefix = "dbimg://";
DataTable table;
string columnName;
public ImageStreamProvider(DataTable sourceTable, string imageColumn)
{
this.table = sourceTable;
this.columnName = imageColumn;
}
public Stream GetStream(string uri)
{
// Parse the retrieved URI string
uri = uri.Trim();
if (!uri.StartsWith(prefix))
return null;
// Remove the prefix from the retrieved URI string
string strId = uri.Substring(prefix.Length).Trim();
int id;
// Check if the string contains the primary key
if (!int.TryParse(strId, out id))
return null;
// Retrieve the row that corresponds
// with the key
DataRow row = table.Rows.Find(id);
if (row == null)
return null;
// Convert the image string from this row
// to a byte array
byte[] bytes = Convert.FromBase64String(row[columnName] as string) as byte[];
if (bytes == null)
return null;
// Return the MemoryStream with an image
MemoryStream memoryStream = new MemoryStream(bytes);
return memoryStream;
}
}
Use DOCVARIABLE Fields in the Mail Merge Template
The DOCVARIABLE field allows you to insert any type of content into a document – from a simple variable to dynamic data from a database or another document. Variables can be stored in a document or calculated within the RichEditControl.CalculateDocumentVariable event.
The code sample below demonstrates how to handle the event to calculate the DOCVARIABLE field value. In this example, this field should insert a number of years the employee worked at a company. The nested MERGEFIELD refers to the HireDate entry in the database.
private static void RichEditControl_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
if (e.Arguments.Count > 0)
{
// Retrieve the MERGEFIELD field value
DateTimeOffset hireDate = Convert.ToDateTime(e.Arguments[0].Value);
DateTimeOffset currentDate = DateTime.Now;
// Calculate the difference between the current date
// and the hire date
var dif = currentDate.Subtract(hireDate);
// Calculate the number of years
int years = dif.Days / 365;
// Specify the DOCVARIABLE field value
e.Value = years.ToString();
e.Handled = true;
}
e.Handled = true;
}
Add a Data Source
RichEditControl supports the following data source types:
System.Collections.IListSystem.Collections.ArrayListSystem.Data.DataTable
Use the RichEditMailMergeOptions.DataSource property to specify the data source used in a mail merge. If the data source contains multiple data tables, use the RichEditMailMergeOptions.DataMember property to define a specific data member.
The code sample below shows how to use the XML data source:
using DevExpress.XtraRichEdit;
using System.Data;
DataSet xmlDataSet = new DataSet();
xmlDataSet.ReadXml("Employees.xml");
xmlDataSet.Tables[0].PrimaryKey = new DataColumn[] {
xmlDataSet.Tables[0].Columns[0] };
richEditControl.Options.MailMerge.DataSource = xmlDataSet.Tables[0];
//...
Merge Data into Template and Save the Document
Call the RichEditControl.MailMerge method to merge data into a template and save the document to the specified file or stream. You can use the MailMergeOptions class properties to specify additional mail merge options. Pass the MailMergeOptions object as the MailMerge method parameter to apply these options.
Note
The control’s Options.MailMerge.DataSource property is ignored if you call the MailMerge method and pass a MailMergeOptions object as a parameter. Use the method parameter’s DataSource property to specify the data source instead.
The code sample below specifies mail merge options, runs the mail merge, and saves the document to the specified file.
using DevExpress.Office.Services;
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...
RichEditControl.CalculateDocumentVariable += RichEditControl_CalculateDocumentVariable;
// Register the URI provider service
IUriStreamService uriStreamService = richEditControl.GetService<IUriStreamService>();
uriStreamService.RegisterProvider(new ImageStreamProvider(xmlDataSet.Tables[0], "Photo"));
MailMergeOptions myMergeOptions =
richEditControl.Document.CreateMailMergeOptions();
myMergeOptions.DataSource = xmlDataSet.Tables[0];
myMergeOptions.MergeMode = MergeMode.NewSection;
richEditControl.MailMerge(myMergeOptions, "result.docx", DocumentFormat.OpenXml);
Mail Merge Events
RichEditControl ships with the following events to control specific mail merge steps:
- MailMergeStarted
- Fires before mail merge starts.
- MailMergeFinished
- Fires the when mail merge is completed.
- MailMergeRecordStarted
- Fires before each data record is merged with the document in the mail merge process.
- MailMergeRecordFinished
- Fires after each data record is merged with the document in the mail merge process.
The code sample below shows how to handle the MailMergeRecordStarted and MailMergeRecordFinished events to insert additional content to the document:
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...
private static void RichEditControl_MailMergeRecordStarted(object sender, MailMergeRecordStartedEventArgs e)
{
DocumentPosition position = e.RecordDocument.Range.Start;
DocumentRange _range =
e.RecordDocument.InsertText(position, String.Format("Created on {0:G}\n\n", DateTime.Now));
CharacterProperties cp = e.RecordDocument.BeginUpdateCharacters(_range);
cp.FontSize = 8;
cp.ForeColor = Color.Red;
cp.Hidden = true;
e.RecordDocument.EndUpdateCharacters(cp);
}
private static void RichEditControl_MailMergeRecordFinished(object sender, MailMergeRecordFinishedEventArgs e)
{
e.RecordDocument.AppendDocumentContent("Docs\\bungalow.docx", DocumentFormat.OpenXml);
}
Tip
If your template document contains fields that are not bound to database fields, you can replace those fields with their values (unlink fields). Use the following methods to do this:
Mail Merge Functionality in the User Interface
The RichEditControl ships with the Mail Merge ribbon tab which allows users to insert merge fields, toggle field codes and results and view the merged data. Refer to the Create a Simple Rich Text Editor topic for details on how to create a ribbon UI for the RichEditControl.
