Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

How to: Perform a Mail Merge

  • 3 minutes 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.

  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