GemBox.Document is a .NET component that enables you to read, write, edit, convert, and print document files from your .NET applications using one simple API.

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/ime and \# for numbers) in the input template document.

Screenshot
Nested Merge (object) Screenshot

See the full code below, use Run Example to execute.

Upload your file(Drag files here)

Download a sample file

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using GemBox.Document;
using GemBox.Document.MailMerging;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY");

        DocumentModel document = DocumentModel.Load("InvoiceForNestedMailMerge.docx");

        int numberOfProjects = 4;
        int itemsPerProject = 7;

        // 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).docx");
    }
}
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.IO
Imports System.Linq
Imports GemBox.Document
Imports GemBox.Document.MailMerging

Module Samples

    Sub Main()

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

        Dim document As DocumentModel = DocumentModel.Load("InvoiceForNestedMailMerge.docx")

        Dim numberOfProjects As Integer = 4
        Dim itemsPerProject As Integer = 7

        ' 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).docx")

    End Sub

End Module

Check next sample.