How to: Perform a Mail Merge

  • 3 min to read

This tutorial shows how to use the Spreadsheet Mail Merge functionality to automatically generate a document based on data retrieved from a data source.

TIP

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

  1. Create a mail merge template and specify template ranges.

    // Create a mail merge template.
    IWorkbook workbook = spreadsheetControl.Document;
    Worksheet template = workbook.Worksheets[0];
    template.Cells["C2"].Formula = "FIELDPICTURE(\"Photo\", \"range\", C2, FALSE, 50)";
    template.Cells["C3"].Formula = "=FIELD(\"FirstName\")&\" \"&FIELD(\"LastName\")";
    template.Cells["B4"].Value = "Position:";
    template.Cells["C4"].Formula = "FIELD(\"Title\")";
    template.Cells["B5"].Value = "Birth Date:";
    template.Cells["C5"].Formula = "FIELD(\"BirthDate\")";
    template.Cells["C5"].NumberFormat = "MMMM dd, yyyy";
    template.Cells["B6"].Value = "Hire Date:";
    template.Cells["C6"].Formula = "FIELD(\"HireDate\")";
    template.Cells["C6"].NumberFormat = "MMMM dd, yyyy";
    template.Cells["B7"].Value = "Home Phone:";
    template.Cells["C7"].Formula = "FIELD(\"HomePhone\")";
    template.Cells["B8"].Value = "Address:";
    template.Cells["C8"].Formula = "=FIELD(\"Address\")&\" \"&FIELD(\"City\")";
    template.Cells["B9"].Value = "About:";
    template.Cells["C9"].Formula = "FIELD(\"Notes\")";
    
    // Set a detail range in the template.
    CellRange detail = template.Range["C1:C9"];
    detail.Name = "DETAILRANGE";
    
    // Set a header range in the template.
    CellRange header = template.Range["B1:B9"];
    header.Name = "HEADERRANGE";
    

    The resulting template is shown in the following image:

    WPFSpreadsheet_MailMergeTemplate

  2. Define the mail merge mode and document orientation. In this example, the mail merge mode is set to Multiple Sheets so that each data record is merged into a separate worksheet in a single workbook.

    // Set the mail merge mode to "Multiple Sheets".
    workbook.DefinedNames.Add("MAILMERGEMODE", "\"Worksheets\"");
    
    // Set the document orientation.
    workbook.DefinedNames.Add("HORIZONTALMODE", "TRUE");
    
  3. Use the IWorkbook.MailMergeDataSource property to bind a mail merge template to a data source. Perform a mail merge using the IWorkbook.GenerateMailMergeDocuments method. The resulting workbook can be saved to a file or stream, or sent to another SpreadsheetControl for further processing.

    workbook.MailMergeDataSource = EmployeesInfo.GetData();
    // Perform a mail merge.
    IList<IWorkbook> result = workbook.GenerateMailMergeDocuments();
    result[0].SaveDocument("MailMergeResult.xlsx");
    

    The image below shows the result:

    WPFSpreadsheet_MailMergeResult