Mail Merging
The Mail Merge functionality provided by the Bootstrap Spreadsheet enables you to automatically generate a set of documents based on a common template and fill each document with unique data values retrieved from a data source. This feature can be useful for a variety of business requirements, such as personalizing letters, and composing catalogs and reports.
To perform a mail merge, you need a template and a data source.
- A template is a spreadsheet document that contains static information (which is the same for all resulting documents) and mail merge fields. 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 special functions in cell formulas: FIELD and FIELDPICTURE.
- A data source contains data that will be merged into fields in a template to create merged documents.
To bind a template to a data source, use the IWorkbook.MailMergeDataSource and IWorkbook.MailMergeDataMember properties of the template workbook.
Within a template, you can choose a mail merge mode specifying how the result of the mail merge should be generated. The mail merge mode is specified programmatically at the template workbook level, by setting a specific defined name ("MAILMERGEMODE") to a particular string constant ("Worksheets" or "OneWorksheet").
- "OneWorksheet" (Single Sheet) - Used in this demo. Merged ranges for all data source records are inserted one after another (vertically or horizontally, depending on the document orientation) into a single worksheet.
- "Worksheets" (Multiple Sheets) - The merged range for each data source record is inserted into a separate worksheet in a single workbook.
Note that by default, the "MAILMERGEMODE" defined name does not exist in a template workbook and the "Single Sheet" mode is used.
To finalize the mail merge process, use the IWorkbook.GenerateMailMergeDocuments method of the template workbook. This method returns a collection of resulting workbooks containing the merged documents (if the "Single Sheet" or "Multiple Sheets" mail merge mode is used, the collection contains a single workbook).
In this demo, we illustrate the merging of images along with personal information for contacts from the Northwind database’s Employees table.
<dx:BootstrapSpreadsheet ID="SpreadsheetMailMerging" runat="server" ActiveTabIndex="0" ShowConfirmOnLosingChanges="false"
OnLoad="SpreadsheetMailMerging_Load">
</dx:BootstrapSpreadsheet>
protected void SpreadsheetMailMerging_Load(object sender, EventArgs e) {
if(!Page.IsPostBack) {
var resultingWorkbook = GenerateMailMergeDocument();
if(resultingWorkbook != null)
OpenResultingDocument(resultingWorkbook);
}
}
DevExpress.Spreadsheet.IWorkbook GenerateMailMergeDocument() {
var workbook = SpreadsheetMailMerging.Document;
LoadMailMergeTemplate(workbook);
workbook.MailMergeDataSource = GetMailMergeDataSource();
return workbook.GenerateMailMergeDocuments()[0];
}
void LoadMailMergeTemplate(DevExpress.Spreadsheet.IWorkbook workbook) {
workbook.LoadDocument(Path.Combine(DirectoryManagmentUtils.CurrentDataDirectory, @"Spreadsheet\\EmployeesHorizontalTemplate.xlsx"));
}
List<Employee> GetMailMergeDataSource() {
var context = new NorthwindContextSL();
return context.Employees.ToList();
}
void OpenResultingDocument(DevExpress.Spreadsheet.IWorkbook resultingWorkbook) {
using(MemoryStream result = new MemoryStream()) {
for(int i = 0; i < resultingWorkbook.Worksheets.Count; i++) {
var worksheet = resultingWorkbook.Worksheets[i];
worksheet.Shapes[0].Left = (float)(worksheet.Columns[0].Width + (worksheet.Columns[1].Width - worksheet.Shapes[0].Width) / 2);
}
resultingWorkbook.SaveDocument(result, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
result.Seek(0, SeekOrigin.Begin);
SpreadsheetMailMerging.Open(Guid.NewGuid().ToString(), DevExpress.Spreadsheet.DocumentFormat.OpenXml, () => { return result; });
}
}