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 sample 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)

1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.IO;
5using System.Linq;
6using GemBox.Document;
7using GemBox.Document.MailMerging;
8
9class Sample
10{
11    [STAThread]
12    static void Main(string[] args)
13    {
14        // If using Professional version, put your serial key below.
15        ComponentInfo.SetLicense("FREE-LIMITED-KEY");
16
17        DocumentModel document = DocumentModel.Load("InvoiceForNestedMailMerge.docx");
18
19        int numberOfProjects = 4;
20        int itemsPerProject = 7;
21
22        // Fill document header.
23        document.MailMerge.Execute(
24            new
25            {
26                CompanyName = "ACME Corporation",
27                Address = "240 Old Country Road, Springfield, IL",
28                PrintDate = DateTime.Now.ToLongDateString()
29            });
30
31        // Create data source using LINQ and anonymous types.
32        var projects = Enumerable.Range(0, numberOfProjects).Select(projectIndex =>
33            new
34            {
35                ProjectId = projectIndex + 1,
36                ContactName = "John Doe",
37                ProjectName = "Project " + (projectIndex + 1),
38                Items = Enumerable.Range(0, itemsPerProject).Select(itemIndex =>
39                    new
40                    {
41                        // Some random date.
42                        Date = DateTime.Now.AddDays(-itemsPerProject + itemIndex),
43                        Hours = ((projectIndex + 1) * itemIndex) % 3 + 6,
44                        Price = 35,
45                        Total = (((projectIndex + 1) * itemIndex) % 3 + 6) * 35
46                    }).ToArray()
47            }).ToArray();
48
49        document.MailMerge.FieldMerging += (sender, e) =>
50        {
51            if (e.IsValueFound)
52            {
53                // Define custom formatting.
54                switch (e.FieldName)
55                {
56                    case "Date":
57                        ((Run)e.Inline).Text = ((DateTime)e.Value).ToString("dddd, MMMM d, yyyy");
58                        break;
59                    case "Price":
60                    case "Total":
61                        ((Run)e.Inline).Text = ((int)e.Value).ToString("0.00");
62                        break;
63                }
64            }
65            else if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
66            {
67                // For each project calculate Total.
68                e.Inline = new Run(e.Document, projects[e.RecordNumber - 1].Items.Sum(item => item.Total).ToString("0.00"));
69                e.Cancel = false;
70            }
71        };    
72
73        // Execute nested mail merge.
74        document.MailMerge.Execute(projects, "Projects");
75
76        document.Save("Nested Merge (object).docx");
77    }
78}
1Imports System
2Imports System.Collections.Generic
3Imports System.Data
4Imports System.IO
5Imports System.Linq
6Imports GemBox.Document
7Imports GemBox.Document.MailMerging
8
9Module Samples
10
11    Sub Main()
12
13        ' If using Professional version, put your serial key below.
14        ComponentInfo.SetLicense("FREE-LIMITED-KEY")
15
16        Dim document As DocumentModel = DocumentModel.Load("InvoiceForNestedMailMerge.docx")
17
18        Dim numberOfProjects As Integer = 4
19        Dim itemsPerProject As Integer = 7
20
21        ' Fill document header.
22        document.MailMerge.Execute(New With { _
23         .CompanyName = "ACME Corporation", _
24         .Address = "240 Old Country Road, Springfield, IL", _
25         .PrintDate = DateTime.Now.ToLongDateString() _
26        })
27
28        ' Create data source using LINQ and anonymous types.
29        Dim projects = Enumerable.Range(0, numberOfProjects).Select(Function(projectIndex) New With { _
30          .ProjectId = projectIndex + 1, _
31          .ContactName = "John Doe", _
32          .ProjectName = "Project " & (projectIndex + 1), _
33          .Items = Enumerable.Range(0, itemsPerProject).Select(Function(itemIndex) New With { _
34            .Date = DateTime.Now.AddDays(-itemsPerProject + itemIndex), _
35            .Hours = ((projectIndex + 1) * itemIndex) Mod 3 + 6, _
36            .Price = 35, _
37            .Total = (((projectIndex + 1) * itemIndex) Mod 3 + 6) * 35 _
38            }).ToArray() _
39        }).ToArray()
40
41        AddHandler document.MailMerge.FieldMerging, _
42            Sub(sender, e)
43                If e.IsValueFound Then
44                    ' Define custom formatting.
45                    Select Case e.FieldName
46                        Case "Date"
47                            DirectCast(e.Inline, Run).Text = DirectCast(e.Value, DateTime).ToString("dddd, MMMM d, yyyy")
48                            Exit Select
49                        Case "Price", "Total"
50                            DirectCast(e.Inline, Run).Text = CInt(e.Value).ToString("0.00")
51                            Exit Select
52                    End Select
53                ElseIf e.RangeName = "Projects" AndAlso e.FieldName = "TotalPrice" Then
54                    ' For each project calculate Total.
55                    e.Inline = New Run(e.Document, projects(e.RecordNumber - 1).Items.Sum(Function(item) item.Total).ToString("0.00"))
56                    e.Cancel = False
57                End If
58            End Sub
59
60        ' Execute nested mail merge.
61        document.MailMerge.Execute(projects, "Projects")
62
63        document.Save("Nested Merge (object).docx")
64
65    End Sub
66
67End Module

Check next sample or find out more about GemBox.Document and GemBox Software.