Use Nested Mail Merge in C# and VB.NET
Nested mail merge is a powerful feature that enables you to import a relational or hierarchical data source into a template document in a single statement.
You can find more info about nested merge ranges on the Nested mail merge help page.
The following examples will show how you can use the nested mail merge feature in C# and VB.NET, using the GemBox.Document component.
Nested Merge with DataSet
A relational data source is a data source that has a tabular structure. The information is stored in multiple tables(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.
using GemBox.Document;
using System;
using System.Data;
using System.Linq;
class Program
{
static void Main()
{
// If using the 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.MergeContext.RangeName == "Projects" && e.FieldName == "TotalPrice")
{
var total = data.Tables[e.MergeContext.RangeName].Rows[e.MergeContext.RecordIndex]
.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("MergedNestedRangesOutput.%OutputFileType%");
}
}
Imports GemBox.Document
Imports System
Imports System.Data
Imports System.Linq
Module Program
Sub Main()
' If using the 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.MergeContext.RangeName = "Projects" And e.FieldName = "TotalPrice" Then
Dim total = data.Tables(e.MergeContext.RangeName).Rows(e.MergeContext.RecordIndex) _
.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("MergedNestedRangesOutput.%OutputFileType%")
End Sub
End Module
Nested Merge with Object
A hierarchical data source is a data source 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.
using GemBox.Document;
using System;
using System.Linq;
class Program
{
static void Main()
{
// If using the 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.MergeContext.RangeName == "Projects" && e.FieldName == "TotalPrice")
{
var total = projects[e.MergeContext.RecordIndex].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("MergedNestedRangesOutputWithObject.%OutputFileType%");
}
}
Imports GemBox.Document
Imports System
Imports System.Linq
Module Program
Sub Main()
' If using the 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.MergeContext.RangeName = "Projects" And e.FieldName = "TotalPrice" Then
Dim total = projects(e.MergeContext.RecordIndex).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("MergedNestedRangesOutputWithObject.%OutputFileType%")
End Sub
End Module
