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

© SautinSoft 2002 - 2019