Nested Mail Merge

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

You can find more info about nested merge ranges on the Nested mail merge help page.

Nested Merge with DataSet

A relational data source is data that has a tabular structure. The information is stored in multiple relations (DataTable objects) that are logically connected with relationships (DataRelation objects).

The following example shows how you can perform a nested mail merge with a relational object (DataSet) as a data source.

Word document generated from merging nested ranges with relational DataSet object
Screenshot of Word file created with relational nested merge
Upload your file (Drag file here)
using System;
using System.Data;
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");

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

        string projectsRangeName = "Projects";
        string itemsRangeName = "Items";

        // Create relational data.
        var projects = new DataTable(projectsRangeName);
        projects.Columns.Add("Id", typeof(int));
        projects.Columns.Add("Name", typeof(string));

        var items = new DataTable(itemsRangeName);
        items.Columns.Add("ProjectId", typeof(int));
        items.Columns.Add("Date", typeof(DateTime));
        items.Columns.Add("Hours", typeof(int));
        items.Columns.Add("Unit", typeof(double));
        items.Columns.Add("Price", typeof(double));

        // Create DataSet with parent-child relation.
        var data = new DataSet();
        data.Tables.Add(projects);
        data.Tables.Add(items);
        data.Relations.Add(itemsRangeName, projects.Columns["Id"], items.Columns["ProjectId"]);

        for (int projectIndex = 1; projectIndex <= numberOfProjects; projectIndex++)
        {
            int id = projectIndex;
            string name = $"Project {projectIndex}";

            projects.Rows.Add(id, name);

            for (int itemIndex = 1; itemIndex <= itemsPerProject; itemIndex++)
            {
                DateTime date = DateTime.Today
                    .AddMonths(projectIndex - numberOfProjects)
                    .AddDays(itemIndex - itemsPerProject);
                int hours = itemIndex % 3 + 6;
                double unit = projectIndex * 35.0;
                double price = hours * unit;

                items.Rows.Add(id, date, hours, unit, price);
            }
        }

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

        // Customize mail merging to achieve calculation of "TotalPrice" for each project.
        document.MailMerge.FieldMerging += (sender, e) =>
        {
            if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
            {
                var total = data.Tables[e.RangeName].Rows[e.RecordNumber - 1]
                    .GetChildRows(itemsRangeName).Sum(item => (double)item["Price"]);

                var totalRun = new Run(e.Document, total.ToString("0.00"));
                totalRun.CharacterFormat = e.Field.CharacterFormat.Clone();

                e.Inline = totalRun;
                e.Cancel = false;
            }
        };

        // Execute nested mail merge.
        document.MailMerge.Execute(data, null);
        
        document.Save("Merged Nested Ranges Output.%OutputFileType%");
    }
}
Imports System
Imports System.Data
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 numberOfProjects As Integer = %ProjectsCount%
        Dim itemsPerProject As Integer = %ItemsCount%

        Dim projectsRangeName As String = "Projects"
        Dim itemsRangeName As String = "Items"

        ' Create relational data.
        Dim projects As New DataTable(projectsRangeName)
        projects.Columns.Add("Id", GetType(Integer))
        projects.Columns.Add("Name", GetType(String))

        Dim items As New DataTable(itemsRangeName)
        items.Columns.Add("ProjectId", GetType(Integer))
        items.Columns.Add("Date", GetType(DateTime))
        items.Columns.Add("Hours", GetType(Integer))
        items.Columns.Add("Unit", GetType(Double))
        items.Columns.Add("Price", GetType(Double))

        ' Create DataSet with parent-child relation.
        Dim data As New DataSet()
        data.Tables.Add(projects)
        data.Tables.Add(items)
        data.Relations.Add(itemsRangeName, projects.Columns("Id"), items.Columns("ProjectId"))

        For projectIndex As Integer = 1 To numberOfProjects

            Dim id As Integer = projectIndex
            Dim name As String = $"Project {projectIndex}"

            projects.Rows.Add(id, name)

            For itemIndex As Integer = 1 To itemsPerProject

                Dim [date] As DateTime = DateTime.Today _
                    .AddMonths(projectIndex - numberOfProjects) _
                    .AddDays(itemIndex - itemsPerProject)
                Dim hours As Integer = itemIndex Mod 3 + 6
                Dim unit As Double = projectIndex * 35.0
                Dim price As Double = hours * unit

                items.Rows.Add(id, [date], hours, unit, price)

            Next
        Next

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

        ' Customize mail merging to achieve calculation of "TotalPrice" for each project.
        AddHandler document.MailMerge.FieldMerging,
            Sub(sender, e)
                If e.RangeName = "Projects" And e.FieldName = "TotalPrice" Then

                    Dim total = data.Tables(e.RangeName).Rows(e.RecordNumber - 1) _
                        .GetChildRows(itemsRangeName).Sum(Function(item) CDbl(item("Price")))

                    Dim totalRun As New Run(e.Document, total.ToString("0.00"))
                    totalRun.CharacterFormat = e.Field.CharacterFormat.Clone()

                    e.Inline = totalRun
                    e.Cancel = False

                End If
            End Sub

        ' Execute nested mail merge.
        document.MailMerge.Execute(data, Nothing)

        document.Save("Merged Nested Ranges Output.%OutputFileType%")

    End Sub
End Module

Nested Merge with Object

A hierarchical data source is a data that has a tree-like structure. The information is stored in multiple records (any Objects) that are connected through links (with Properties).

The following example shows how you can perform a nested mail merge with a hierarchical object (created using LINQ and anonymous type) as a data source.

Word document generated from merging nested ranges with hierarchical anonymous object
Screenshot of Word file created with hierarchical nested merge
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");

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

        // Create hierarchical data source using LINQ and anonymous types.
        var projects = Enumerable.Range(1, numberOfProjects).Select(projectIndex =>
        {
            return new
            {
                Name = $"Project {projectIndex}",
                Items = Enumerable.Range(1, itemsPerProject).Select(itemIndex =>
                {
                    DateTime date = DateTime.Today
                        .AddMonths(projectIndex - numberOfProjects)
                        .AddDays(itemIndex - itemsPerProject);
                    int hours = itemIndex % 3 + 6;
                    double unit = projectIndex * 35.0;
                    double price = hours * unit;

                    return new { Date = date, Hours = hours, Unit = unit, Price = price };
                }).ToArray()
            };
        }).ToArray();

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

        // Customize mail merging to achieve calculation of "TotalPrice" for each project.
        document.MailMerge.FieldMerging += (sender, e) =>
        {
            if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
            {
                var total = projects[e.RecordNumber - 1].Items.Sum(item => item.Price);

                var totalRun = new Run(e.Document, total.ToString("0.00"));
                totalRun.CharacterFormat = e.Field.CharacterFormat.Clone();

                e.Inline = totalRun;
                e.Cancel = false;
            }
        };

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

        document.Save("Merged Nested Ranges Output.%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 numberOfProjects As Integer = %ProjectsCount%
        Dim itemsPerProject As Integer = %ItemsCount%

        ' Create hierarchical data source using LINQ and anonymous types.
        Dim projects = Enumerable.Range(1, numberOfProjects).Select(
            Function(projectIndex)
                Return New With
                {
                    .Name = $"Project {projectIndex}",
                    .Items = Enumerable.Range(1, itemsPerProject).Select(
                        Function(itemIndex)

                            Dim [date] As DateTime = DateTime.Today _
                                .AddMonths(projectIndex - numberOfProjects) _
                                .AddDays(itemIndex - itemsPerProject)
                            Dim hours As Integer = itemIndex Mod 3 + 6
                            Dim unit As Double = projectIndex * 35.0
                            Dim price As Double = hours * unit

                            Return New With {.Date = [date], .Hours = hours, .Unit = unit, .Price = price}
                        End Function).ToArray()
                }
            End Function).ToArray()

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

        ' Customize mail merging to achieve calculation of "TotalPrice" for each project.
        AddHandler document.MailMerge.FieldMerging,
            Sub(sender, e)
                If e.RangeName = "Projects" And e.FieldName = "TotalPrice" Then

                    Dim total = projects(e.RecordNumber - 1).Items.Sum(Function(item) item.Price)

                    Dim totalRun As New Run(e.Document, total.ToString("0.00"))
                    totalRun.CharacterFormat = e.Field.CharacterFormat.Clone()

                    e.Inline = totalRun
                    e.Cancel = False

                End If
            End Sub

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

        document.Save("Merged Nested Ranges Output.%OutputFileType%")

    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Document yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.