Mail Merge: Table Report with Regions and FieldMerging event in C# and VB .Net

Table Report with Regions and FieldMerging event
in C# and VB .Net

This advanced example shows how to generate Table Report with regions using a mail merge template and XML-document as a data source.

Here we'll generate invoices for a pastry shop. To generate invoices, all what we need are DOCX-template and XML-document populated by orders and pastry items.
Furthermore, we'll show you how with help of FieldMerging event insert pictures and format the currency sign inside specific fields and ranges.

1. Create a template in MS Word with Merge Fields or use a ready file «InvoiceTemplate.docx».

Note. If the order sheet should start from a new page, do the following.

2. Then we create XML-document populated by data. As you may see that tag names have the same names as the merge fields.
This our XML-document: «Orders.xml».

By the way, you may create similar XML-document in your app using a Serialization. Below you will see the demo WPF-app where we have used Serialization too.

3. After execution of mail merge method we'll get the «Invoices.pdf» as result. The complete code in C# and VB.Net is located at the bottom ot this page.

Interesting: To see this functionality firsthand, we've created for you complete WPF C# demo-app which shows the full process of creating invoices in a real Pastry Shop.

With help of this application you can create new XML-document populated by orders with pies, cakes, bread and buns and generate an invoice in PDF, DOCX, RTF and HTML formats. Download WPF - Pastry Shop, C# sources. (115 Kb).

Complete code

using System;
using System.Data;
using System.IO;
using System.Collections.Generic;

using SautinSoft.Document;
using SautinSoft.Document.Drawing;
using System.Globalization;

namespace Sample
{
    class Sample
    {
        static void Main(string[] args)
        {
            TableReportWithRegionsAdvanced();
        }

        /// <summary>
        /// Generates a table report with regions using XML document as a data source and FieldMerging event.
        /// </summary>
        /// <remarks>
        /// See details at: https://www.sautinsoft.com/products/document/examples/mail-merge-table-report-with-regions-advanced-net-csharp-vb.php
        /// </remarks>
        public static void TableReportWithRegionsAdvanced()
        {
            // Scan directory for image files.
            Dictionary<string, string> icons = new Dictionary<string, string>();
            foreach (string iconPath in Directory.EnumerateFiles(@"..\..\icons\", @"*.jpg"))
            {
                icons[Path.GetFileNameWithoutExtension(iconPath.ToLower())] = iconPath;

                switch (Path.GetFileName(iconPath))
                {
                    case "Cherry-apple pie.jpg": icons["Cherry/apple/pie".ToLower()] = iconPath; break;
                    case "Dark-milk-white chocolate.jpg": icons["Dark/milk/white chocolate".ToLower()] = iconPath; break;
                    case "Rice-lemon-vanilla pudding.jpg": icons["Rice/lemon/vanilla pudding".ToLower()] = iconPath; break;
                    case "Spice-cake honey-cake.jpg": icons["Spice-cake, honey-cake".ToLower()] = iconPath; break;
                    case "Chocolate-strawberry-vanilla ice cream.jpg": icons["Chocolate/strawberry/vanilla ice cream".ToLower()] = iconPath; break;
                    default:break;
                }
            }

            // Create the Dataset and read the XML.
            DataSet ds = new DataSet();

            ds.ReadXml(@"..\..\Orders.xml");

            // Load the template document.
            string templatePath = @"..\..\InvoiceTemplate.docx";

            DocumentCore dc = DocumentCore.Load(templatePath);

            // Each product will be decorated by appropriate icon.
            dc.MailMerge.FieldMerging += (sender, e) =>
            {
                // Insert an icon before the product name
                if (e.RangeName == "Product" && e.FieldName == "Name")
                {
                    e.Inlines.Clear();

                    string iconPath;
                    if (icons.TryGetValue(((string)e.Value).ToLower(), out iconPath))
                    {
                        e.Inlines.Add(new Picture(dc, iconPath) { Layout = new InlineLayout(new Size(30, 30)) });                        
                        e.Inlines.Add(new SpecialCharacter(dc, SpecialCharacterType.Tab));
                    }

                    e.Inlines.Add(new Run(dc, (string)e.Value));
                    e.Cancel = false;
                }
                // Add the currency sign into "Total" field. 
                // You may change the culture "en-GB" to any desired.
                if (e.RangeName == "Order" && e.FieldName == "OrderTotal")
                {
                    decimal total = 0;
                    if (Decimal.TryParse((string)e.Value, out total))
                    {
                        e.Inlines.Clear();                        
                        e.Inlines.Add(new Run(dc, String.Format(new CultureInfo("en-GB"), "{0:C}", total)));
                    }                    
                }
            };

            // Execute the mail merge.
            dc.MailMerge.Execute(ds.Tables["Order"]);

            string resultPath = "Invoices.pdf";

            // Save the output to file
            dc.Save(resultPath);

            // Open the result for demonstation purposes.
            System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(resultPath) { UseShellExecute = true });
        }
    }
}

Download.

        
            Imports System
Imports System.Data
Imports System.IO
Imports System.Collections.Generic

Imports SautinSoft.Document
Imports SautinSoft.Document.Drawing
Imports System.Globalization

Namespace Sample
	Friend Class Sample
		Shared Sub Main(ByVal args() As String)
			TableReportWithRegionsAdvanced()
		End Sub

        ''' <summary>
        ''' Generates a table report with regions using XML document as a data source and FieldMerging event.
        ''' </summary>
        ''' <remarks>
        ''' See details at: https://www.sautinsoft.com/products/document/examples/mail-merge-table-report-with-regions-advanced-net-csharp-vb.php
        ''' </remarks>
        Public Shared Sub TableReportWithRegionsAdvanced()
			' Scan directory for image files.
			Dim icons As New Dictionary(Of String, String)()
            For Each iconPath As String In Directory.EnumerateFiles("..\icons\", "*.jpg")
                icons(Path.GetFileNameWithoutExtension(iconPath.ToLower())) = iconPath

                Select Case Path.GetFileName(iconPath)
                    Case "Cherry-apple pie.jpg"
                        icons("Cherry/apple/pie".ToLower()) = iconPath
                    Case "Dark-milk-white chocolate.jpg"
                        icons("Dark/milk/white chocolate".ToLower()) = iconPath
                    Case "Rice-lemon-vanilla pudding.jpg"
                        icons("Rice/lemon/vanilla pudding".ToLower()) = iconPath
                    Case "Spice-cake honey-cake.jpg"
                        icons("Spice-cake, honey-cake".ToLower()) = iconPath
                    Case "Chocolate-strawberry-vanilla ice cream.jpg"
                        icons("Chocolate/strawberry/vanilla ice cream".ToLower()) = iconPath
                    Case Else
                End Select
            Next iconPath

            ' Create the Dataset and read the XML.
            Dim ds As New DataSet()

            ds.ReadXml("..\Orders.xml")

            ' Load the template document.
            Dim templatePath As String = "..\InvoiceTemplate.docx"

            Dim dc As DocumentCore = DocumentCore.Load(templatePath)

			' Each product will be decorated by appropriate icon.
			AddHandler dc.MailMerge.FieldMerging, Sub(sender, e)
				' Insert an icon before the product name
				If e.RangeName = "Product" AndAlso e.FieldName = "Name" Then
					e.Inlines.Clear()

					Dim iconPath As String = Nothing
					If icons.TryGetValue(CStr(e.Value).ToLower(), iconPath) Then
						e.Inlines.Add(New Picture(dc, iconPath) With {.Layout = New InlineLayout(New Size(30, 30))})
						e.Inlines.Add(New SpecialCharacter(dc, SpecialCharacterType.Tab))
					End If

					e.Inlines.Add(New Run(dc, CStr(e.Value)))
					e.Cancel = False
				End If
				' Add the currency sign into "Total" field.
				' You may change the culture "en-GB" to any desired.
				If e.RangeName = "Order" AndAlso e.FieldName = "OrderTotal" Then
					Dim total As Decimal = 0
					If Decimal.TryParse(CStr(e.Value), total) Then
						e.Inlines.Clear()
						e.Inlines.Add(New Run(dc, String.Format(New CultureInfo("en-GB"), "{0:C}", total)))
					End If
				End If
			End Sub

			' Execute the mail merge.
			dc.MailMerge.Execute(ds.Tables("Order"))

			Dim resultPath As String = "Invoices.pdf"

			' Save the output to file
			dc.Save(resultPath)

			' Open the result for demonstation purposes.
			System.Diagnostics.Process.Start(New System.Diagnostics.ProcessStartInfo(resultPath) With {.UseShellExecute = True})
		End Sub
	End Class
End Namespace

Download.


If you need a new code example or have a question: email us at support@sautinsoft.com or ask at Online Chat (right-bottom corner of this page) or use the Form below:



Questions and suggestions from you are always welcome!

We are developing .Net components since 2002. We know PDF, DOCX, RTF, HTML, XLSX and Images formats. If you need any assistance with creating, modifying or converting documents in various formats, we can help you. We will write any code example for you absolutely free.

© SautinSoft 2002 - 2019