Nested Mail Merge with Object source

Nested mail merge is a powerful feature that enables you to import relational or hierarchical data source into the template document in a single statement.

Following example demonstrates how to create a hierarchical data source using LINQ and anonymous types and import it into the document using the nested mail merge feature.

Sample also shows how to customize the merging of some fields by handling FieldMerging event. To customize formatting of date/time or numeric values you can use even simpler approach by using field formatting switches (\@ for date/time and \# for numbers) in the input template document.

Nested Merge (object) Screenshot
Upload your file (Drag file here)
using System;
using System.Linq;
using GemBox.Document;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY");

        DocumentModel document = DocumentModel.Load("%InputFileName%");

        int numberOfProjects = %ProjectsCount%;
        int itemsPerProject = %ItemsCount%;

        // Fill document header.
        document.MailMerge.Execute(
            new
            {
                CompanyName = "ACME Corporation",
                Address = "240 Old Country Road, Springfield, IL",
                PrintDate = DateTime.Now.ToLongDateString()
            });

        // Create data source using LINQ and anonymous types.
        var projects = Enumerable.Range(0, numberOfProjects).Select(projectIndex =>
            new
            {
                ProjectId = projectIndex + 1,
                ContactName = "John Doe",
                ProjectName = "Project " + (projectIndex + 1),
                Items = Enumerable.Range(0, itemsPerProject).Select(itemIndex =>
                    new
                    {
                        // Some random date.
                        Date = DateTime.Now.AddDays(-itemsPerProject + itemIndex),
                        Hours = ((projectIndex + 1) * itemIndex) % 3 + 6,
                        Price = 35,
                        Total = (((projectIndex + 1) * itemIndex) % 3 + 6) * 35
                    }).ToArray()
            }).ToArray();

        document.MailMerge.FieldMerging += (sender, e) =>
        {
            if (e.IsValueFound)
            {
                // Define custom formatting.
                switch (e.FieldName)
                {
                    case "Date":
                        ((Run)e.Inline).Text = ((DateTime)e.Value).ToString("dddd, MMMM d, yyyy");
                        break;
                    case "Price":
                    case "Total":
                        ((Run)e.Inline).Text = ((int)e.Value).ToString("0.00");
                        break;
                }
            }
            else if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
            {
                // For each project calculate Total.
                e.Inline = new Run(e.Document, projects[e.RecordNumber - 1].Items.Sum(item => item.Total).ToString("0.00"));
                e.Cancel = false;
            }
        };

        // Execute nested mail merge.
        document.MailMerge.Execute(projects, "Projects");

        document.Save("Nested Merge (object).%OutputFileType%");
    }
}
Imports System
Imports System.Linq
Imports GemBox.Document

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY")

        Dim document As DocumentModel = DocumentModel.Load("%InputFileName%")

        Dim numberOfProjects As Integer = %ProjectsCount%
        Dim itemsPerProject As Integer = %ItemsCount%

        ' Fill document header.
        document.MailMerge.Execute(New With {
         .CompanyName = "ACME Corporation",
         .Address = "240 Old Country Road, Springfield, IL",
         .PrintDate = DateTime.Now.ToLongDateString()
        })

        ' Create data source using LINQ and anonymous types.
        Dim projects = Enumerable.Range(0, numberOfProjects).Select(Function(projectIndex) New With {
          .ProjectId = projectIndex + 1,
          .ContactName = "John Doe",
          .ProjectName = "Project " & (projectIndex + 1),
          .Items = Enumerable.Range(0, itemsPerProject).Select(Function(itemIndex) New With {
            .Date = DateTime.Now.AddDays(-itemsPerProject + itemIndex),
            .Hours = ((projectIndex + 1) * itemIndex) Mod 3 + 6,
            .Price = 35,
            .Total = (((projectIndex + 1) * itemIndex) Mod 3 + 6) * 35
            }).ToArray()
        }).ToArray()

        AddHandler document.MailMerge.FieldMerging,
            Sub(sender, e)
                If e.IsValueFound Then
                    ' Define custom formatting.
                    Select Case e.FieldName
                        Case "Date"
                            DirectCast(e.Inline, Run).Text = DirectCast(e.Value, DateTime).ToString("dddd, MMMM d, yyyy")
                            Exit Select
                        Case "Price", "Total"
                            DirectCast(e.Inline, Run).Text = CInt(e.Value).ToString("0.00")
                            Exit Select
                    End Select
                ElseIf e.RangeName = "Projects" AndAlso e.FieldName = "TotalPrice" Then
                    ' For each project calculate Total.
                    e.Inline = New Run(e.Document, projects(e.RecordNumber - 1).Items.Sum(Function(item) item.Total).ToString("0.00"))
                    e.Cancel = False
                End If
            End Sub

        ' Execute nested mail merge.
        document.MailMerge.Execute(projects, "Projects")

        document.Save("Nested Merge (object).%OutputFileType%")

    End Sub

End Module

Check next example or download examples from GitHub.