wordpress hit counter
Mail Merging with a Custom Client Using the Open XML SDK 2.0 - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Mail Merging with a Custom Client Using the Open XML SDK 2.0

Mail Merging with a Custom Client Using the Open XML SDK 2.0

Rate This
  • Comments 12

By Johannes Prinz

Introduction

In my life as a developer I’ve implemented many different flavours of Mail Merge style applications which can be run on a server without having to install any word processing clients. This has ranged from simple email auto responders, using customizable email templates, to complex reports based on customizable templates. In many cases I’ve had to reach for 3rd party applications like Aspose Words for.NET to get the job done within the budget and time frames allowed. With the release of the Open XML SDK 2.0, I wanted to see how easy it is to build my own mail merge application. So here we have Another Mail Merge Client (AMMC).


Overview

In Word users can create their mail merge templates that look like this.

Dated: «Date»

Hi «Recipient», 

This is a test to see if my «Adjectives» mail merge worked.

«Spiel»

Johannes

This same template file can also be used to generate documents in an application using data from the application. By using Open XML to do this you do not require Word to be installed, which is a bad idea anyway.

The code here so far does not support multi-valued data, but you should be able to generate even complex word documents as long as there is just one row of data for each document you generate.

To generate complex reports take a look at the XSLT approach taken here.

 

Code Sample Structure

The completed Visual Studio 2008 solution (inside /AMMC-src) is included in this code sample, along with a pre-compiled sample binary (inside /AMMC-bin).

The AMMC is built using two projects with the following files.

  • MailMerge (a reusable class library you call from your application).
    >
    MailMerger.cs (where all the Open XML action is)
  • AMMC (Another Mail Merge Client application, which is a simple test harness to demonstrate calling the MailMerger class.)
    >
    TestMaterial (Folder containing some test assets).  
          -
    MergeTemplate.docx (Mailmerge template created in MS Word 2007)
          -
    TestData.csv (Test data created in MS Excel 2007). 
    >
    Program.cs (YAMMC programm using the MailMerge class library)

The DocumentFormat.OpenXml.dll assembly is also included with the binary version of the application. AMMC is a command line application which will print the help documentation whenever invalid input is given.


Understanding Mail Merge in Open XML

I started creating a simple Mail Merge document in Microsoft Word 2007 to use as my template. As my data source I put together a simple comma-separated values (CSV) file using Microsoft Excel 2007. To get a basic understanding of how a Mail Merge document looks in Open XML, I opened up my template document using The Document Reflector, one of several usefull tools found in the Open XML SDK 2.0, which can be found in the Open XML Format SDK\V2.0\tools\ directory.

 

Scanning through the document I quicly find the 3 key parts I have to work with.

  1. A section in the settings.xml file with information on connecting to the datasource file. This will have to be removed in my generated documents.
  2. A simple field with a MERGEFIELD attribute indicating the field to insert for every mail merge field I used in the template document. This will be where I’ll insert the data.
  3. Hidden near the top is a recipientData.xml file. This field needs to be removed or the document will open with errors.


The Mail Merger Explained

There are three main parts to the MailMerger and they are all inside the only method public void MailMerge. At this stage the MailMerger only has the one key method but as it’s in its own class library this can be expanded on later.

The processing works by reading in the merge template document, and then replacing values the mail merge field codes with the actual data then saving as the new file.

To start off we need to collect all the elements which describe the mail merge fields. These are identified by attribute called "instr" which contains a string value prefixed with "MERGEFIELD". For this I used LINQ to XML.

// Get all Mail Merge Fields

IList<XElement> mailMergeFields =

    (from el in newBody.Descendants()

     where el.Attribute(XMLNS + "instr") != null

     select el).ToList();

Once we have a collection of these we can iterate through them and replace the field with the data. It is important to remember to save the changes at the end.

// Replace all merge fields with Data

foreach (XElement field in mailMergeFields)

{

    string fieldName = field.Attribute(XMLNS + "instr").Value.Replace("MERGEFIELD", string.Empty).Trim();

    if (row.Table.Columns.Contains(fieldName))

    {

        XElement newElement = field.Descendants(XMLNS + "r").First();

        newElement.Descendants(XMLNS + "t").First().Value = row.Field<string>(fieldName);

        field.ReplaceWith(newElement);

    }

}

wordDocument.MainDocumentPart.Document.Body = new Body(newBody.ToString());

wordDocument.MainDocumentPart.Document.Save();

Next we need to remove the recipientData.xml file out of the package.

// Delete MailMerge Data Source Part

DocumentSettingsPart settingsPart = wordDocument.MainDocumentPart.GetPartsOfType<DocumentSettingsPart>().First();

MailMergeRecipientDataPart mmrPart = settingsPart.GetPartsOfType<MailMergeRecipientDataPart>().First();

settingsPart.DeletePart(mmrPart);

Lastly we remove the element that describes the data connection to the external data file. If this is not removed the document will prompt to connect to the original data source which may no longer be there especially if opened on another machine or from another location. This element is called: "mailMerge" and I use LINQ to XML to find it.

// Delete refrence to Mail Merge Data sources

XElement settings = XElement.Parse(settingsPart.RootElement.OuterXml);

IList<XElement> mailMergeElements =

    (from el in settings.Descendants()

     where el.Name == (XMLNS + "mailMerge")

     select el).ToList();

foreach (XElement field in mailMergeElements)

{

    field.Remove();

}

settingsPart.RootElement.InnerXml = settings.ToString();

settingsPart.RootElement.Save();


Using the Mail Merger

To test and consume my Open XML Mail Merge utility I built the AMMC console application as a test harness. The Main method calls out to several methods to process and validate the arguments, load the data source and handle all the errors. There are only 2 lines which use the MailMerger class library. One to new up a new MailMerger object and one to do the mail merge for every row in the data source.

// Create the MailMerger

MailMerger merger = new MailMerger(Template.FullName);

// Do MailMerge for earch record

foreach (DataRow row in dataSet.Tables[Data.Name].Rows)

{

    // Get unique target file name

    string uniqueFileName = CreateUniqueFileName(Target.FullName + "\\MMDoc.docx");

    // Merge Data and Save File

    merger.MailMerge(row, uniqueFileName);

}


Possible Improvements

There are many ways this application can be improved but here are a couple that I have thought of while building this application.

  1. Manage the memory stream for the template a little better. There is no need to reload the template every time a row is merged.
  2. Provide a couple of overload methods for the merge method to return documents in different formats like byte arrays of WordDocument objects rather than just saving to file.


Conclusion

In this code sample I set out to use the new Open XML SDK 2.0 to create an application for generating Word documents from a Mail Merge template using my own dataset. I made ready use of the Document Reflector to inspect the template. I made use of Open XML SDK 2.0’s support for LINQ to XML. Overall, the supplied libraries and tools of the new Open XML SDK make the matter of building applications for document content generation much, much easier.

Attachment: AMMC.ZIP
Page 1 of 1 (12 items)