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 DataSet 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 relational data source 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.

Nested Merge (DataSet) 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
    static void Main(string[] args)
        // If using Professional version, put your serial key below.

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

        int numberOfProjects = 4;
        int itemsPerProject = 7;

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

        // Create data source
        DataSet ds = new DataSet();

        // Project details
        DataTable projects = new DataTable("Projects");
        projects.Columns.Add("ProjectId", typeof(int));
        projects.Columns.Add("ContactName", typeof(string));
        projects.Columns.Add("ProjectName", typeof(string));

        // Item details
        DataTable items = new DataTable("Items");
        items.Columns.Add("ProjectId", typeof(int));
        items.Columns.Add("Date", typeof(DateTime));
        items.Columns.Add("Hours", typeof(double));
        items.Columns.Add("Price", typeof(double));
        items.Columns.Add("Total", typeof(double));

        // Add parent-child relation 
        ds.Relations.Add("Items", projects.Columns["ProjectId"], items.Columns["ProjectId"]);

        // Fill DataSource.
        for (int i = 0; i < numberOfProjects; i++)
            int projectId = i + 1;
            string contactName = "John Doe";
            string projectName = "Project " + projectId;
            projects.Rows.Add(projectId, contactName, projectName);

            DateTime startDate = DateTime.Now.AddDays(-itemsPerProject);
            for (int j = 0; j < itemsPerProject; j++)
                // We worked between 6 and 8 hours per day.
                int workHours = ((i + 1) * j) % 3 + 6;
                double totalPrice = workHours * 35;
                items.Rows.Add(projectId, startDate.AddDays(j), workHours, 35, totalPrice);

        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");
                    case "Price":
                    case "Total":
                        ((Run)e.Inline).Text = ((double)e.Value).ToString("0.00");
            else if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
                // For each project calculate Total.
                e.Inline = new Run(e.Document, ds.Tables[e.RangeName].Rows[e.RecordNumber - 1].GetChildRows("Items").Sum(item => (double)item["Total"]).ToString("0.00"));
                e.Cancel = false;

        // Execute nested mail merge.
        document.MailMerge.Execute(ds, null);

        document.Save("Nested Merge (DataSet).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.

        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
        Dim ds As New DataSet()

        ' Project details
        Dim projects As New DataTable("Projects")
        projects.Columns.Add("ProjectId", GetType(Integer))
        projects.Columns.Add("ContactName", GetType(String))
        projects.Columns.Add("ProjectName", GetType(String))

        ' Item details
        Dim items As New DataTable("Items")
        items.Columns.Add("ProjectId", GetType(Integer))
        items.Columns.Add("Date", GetType(DateTime))
        items.Columns.Add("Hours", GetType(Double))
        items.Columns.Add("Price", GetType(Double))
        items.Columns.Add("Total", GetType(Double))

        ' Add parent-child relation 
        ds.Relations.Add("Items", projects.Columns("ProjectId"), items.Columns("ProjectId"))

        ' Fill DataSource.
        For i As Integer = 0 To numberOfProjects - 1
            Dim projectId As Integer = i + 1
            Dim contactName As String = "John Doe"
            Dim projectName As String = "Project " & projectId
            projects.Rows.Add(projectId, contactName, projectName)

            Dim startDate As DateTime = DateTime.Now.AddDays(-itemsPerProject)
            For j As Integer = 0 To itemsPerProject - 1
                ' We worked between 6 and 8 hours per day.
                Dim workHours As Integer = ((i + 1) * j) Mod 3 + 6
                Dim totalPrice As Double = workHours * 35
                items.Rows.Add(projectId, startDate.AddDays(j), workHours, 35, totalPrice)

        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 = CDbl(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, ds.Tables(e.RangeName).Rows(e.RecordNumber - 1).GetChildRows("Items").Sum(Function(item) CDbl(item("Total"))).ToString("0.00"))
                    e.Cancel = False
                End If

            End Sub

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

        document.Save("Nested Merge (DataSet).docx")

    End Sub

End Module

Check next sample.