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

Screenshot

Nested Merge (DataSet) 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
32        DataSet ds = new DataSet();
33
34        // Project details
35        DataTable projects = new DataTable("Projects");
36        projects.Columns.Add("ProjectId", typeof(int));
37        projects.Columns.Add("ContactName", typeof(string));
38        projects.Columns.Add("ProjectName", typeof(string));
39        ds.Tables.Add(projects);
40
41        // Item details
42        DataTable items = new DataTable("Items");
43        items.Columns.Add("ProjectId", typeof(int));
44        items.Columns.Add("Date", typeof(DateTime));
45        items.Columns.Add("Hours", typeof(double));
46        items.Columns.Add("Price", typeof(double));
47        items.Columns.Add("Total", typeof(double));
48        ds.Tables.Add(items);
49
50        // Add parent-child relation 
51        ds.Relations.Add("Items", projects.Columns["ProjectId"], items.Columns["ProjectId"]);
52
53        // Fill DataSource.
54        for (int i = 0; i < numberOfProjects; i++)
55        {
56            int projectId = i + 1;
57            string contactName = "John Doe";
58            string projectName = "Project " + projectId;
59            projects.Rows.Add(projectId, contactName, projectName);
60
61            DateTime startDate = DateTime.Now.AddDays(-itemsPerProject);
62            for (int j = 0; j < itemsPerProject; j++)
63            {
64                // We worked between 6 and 8 hours per day.
65                int workHours = ((i + 1) * j) % 3 + 6;
66                double totalPrice = workHours * 35;
67                items.Rows.Add(projectId, startDate.AddDays(j), workHours, 35, totalPrice);
68            }
69        }
70
71        document.MailMerge.FieldMerging += (sender, e) =>
72        {
73            if (e.IsValueFound)
74            {
75                // Define custom formatting.
76                switch (e.FieldName)
77                {
78                    case "Date":
79                        ((Run)e.Inline).Text = ((DateTime)e.Value).ToString("dddd, MMMM d, yyyy");
80                        break;
81                    case "Price":
82                    case "Total":
83                        ((Run)e.Inline).Text = ((double)e.Value).ToString("0.00");
84                        break;
85                }
86            }
87            else if (e.RangeName == "Projects" && e.FieldName == "TotalPrice")
88            {
89                // For each project calculate Total.
90                e.Inline = new Run(e.Document, ds.Tables[e.RangeName].Rows[e.RecordNumber - 1].GetChildRows("Items").Sum(item => (double)item["Total"]).ToString("0.00"));
91                e.Cancel = false;
92            }
93        };
94
95        // Execute nested mail merge.
96        document.MailMerge.Execute(ds, null);
97
98        document.Save("Nested Merge (DataSet).docx");
99    }
100}
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
29        Dim ds As New DataSet()
30
31        ' Project details
32        Dim projects As New DataTable("Projects")
33        projects.Columns.Add("ProjectId", GetType(Integer))
34        projects.Columns.Add("ContactName", GetType(String))
35        projects.Columns.Add("ProjectName", GetType(String))
36        ds.Tables.Add(projects)
37
38        ' Item details
39        Dim items As New DataTable("Items")
40        items.Columns.Add("ProjectId", GetType(Integer))
41        items.Columns.Add("Date", GetType(DateTime))
42        items.Columns.Add("Hours", GetType(Double))
43        items.Columns.Add("Price", GetType(Double))
44        items.Columns.Add("Total", GetType(Double))
45        ds.Tables.Add(items)
46
47        ' Add parent-child relation 
48        ds.Relations.Add("Items", projects.Columns("ProjectId"), items.Columns("ProjectId"))
49
50        ' Fill DataSource.
51        For i As Integer = 0 To numberOfProjects - 1
52            Dim projectId As Integer = i + 1
53            Dim contactName As String = "John Doe"
54            Dim projectName As String = "Project " & projectId
55            projects.Rows.Add(projectId, contactName, projectName)
56
57            Dim startDate As DateTime = DateTime.Now.AddDays(-itemsPerProject)
58            For j As Integer = 0 To itemsPerProject - 1
59                ' We worked between 6 and 8 hours per day.
60                Dim workHours As Integer = ((i + 1) * j) Mod 3 + 6
61                Dim totalPrice As Double = workHours * 35
62                items.Rows.Add(projectId, startDate.AddDays(j), workHours, 35, totalPrice)
63            Next
64        Next
65
66        AddHandler document.MailMerge.FieldMerging, _
67            Sub(sender, e)
68                If e.IsValueFound Then
69                    ' Define custom formatting.
70                    Select Case e.FieldName
71                        Case "Date"
72                            DirectCast(e.Inline, Run).Text = DirectCast(e.Value, DateTime).ToString("dddd, MMMM d, yyyy")
73                            Exit Select
74                        Case "Price", "Total"
75                            DirectCast(e.Inline, Run).Text = CDbl(e.Value).ToString("0.00")
76                            Exit Select
77                    End Select
78                ElseIf e.RangeName = "Projects" AndAlso e.FieldName = "TotalPrice" Then
79                    ' For each project calculate Total.
80                    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"))
81                    e.Cancel = False
82                End If
83
84            End Sub
85
86        ' Execute nested mail merge.
87        document.MailMerge.Execute(ds, Nothing)
88
89        document.Save("Nested Merge (DataSet).docx")
90
91    End Sub
92
93End Module

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