Recommended

Skip Navigation LinksHome / Articles / View Article

MS Word Mail Merge with Silverlight 4 COM Automation

+ Add to SilverlightShow Favorites
1 comments   /   posted by Andrej Tozon on Mar 24, 2010
(3 votes)
Categories: Tutorials

One of the new Silverlight 4 features allows applications talking to native COM components through COM Automation classes. Perhaps one of the most interesting use case for this new COM interoperability feature in Silverlight LOB applications is interacting with locally installed Microsoft Office applications to work with their documents.

This article will focus on creating a new MS Word document using Word’s mail merge feature. Mail merging lets user create a number of personalized documents using pre-made templates. A template contains special placeholders (fields), which, in the process of mail merging, are populated with data from a structured data source.

Technologies, used for this article

The core of the sample application is of course Silverlight 4 and it’s automation features. WCF RIA Services will serve as a bridge between the server (where the data will be) and the client (application), while MEF (Managed Extensibility Framework) will serve as means of separating application components to be composed at runtime.

What are we going to build?

The sample application is about creating invitation cards to a child’s birthday. The birthday girl maintains a list of her friends in a database on a server and when her birthday is coming up, she updates the invitation template and picks the friends she’d like to have at the party. After a simple click on a button, a document, containing cards for all her friends, appear on the screen, ready to be printed.

The data

Rather than having a proper database on the server, this sample uses a simple sample data generator to create fake user data. Names and surnames are taken from www.namestatistics.com and paired randomly to avoid any unintentional reference to a real-world person.

 1: public static class DataGenerator
 2: {
 3:     private static readonly Random Randomizer = new Random();
 4:  
 5:     private static readonly string[] Names = new[] { "James", "John", "Robert", "Michael", "William", "David", "Richard", "Charles", "Joseph", "Thomas", "Mary", "Patricia", "Linda", "Barabara", "Elizabeth", "Jennifer", "Maria", "Susan", "Margaret", "Dorothy" };
 6:     private static readonly string[] LastNames = new[] { "Smith", "Johnson", "Williams", "Jones", "Brown", "Davis", "Miller", "Wilson", "Moore", "Taylor", "Anderson", "Thomas", "Jackson", "White", "Harris", "Martin", "Thompson", "Garcia", "Martinez", "Robinson" };
 7:  
 8:     public static Person Create(int i)
 9:     {
 10:         int nameIndex = Randomizer.Next(Names.Length);
 11:         return new Person
 12:         {
 13:             Id = i,
 14:             FirstName = Names[nameIndex],
 15:             LastName = LastNames[Randomizer.Next(LastNames.Length)],
 16:             Gender = (nameIndex < Names.Length / 2) ? Gender.Boy : Gender.Girl,
 17:         };
 18:     }
 19: }

The generator creates an instance of a simple Person class, which is declared as:

 1: public class Person
 2: {
 3:     [Key]
 4:     public int Id { get; set; }
 5:     public string FirstName { get; set; }
 6:     public string LastName { get; set; }
 7:     public Gender Gender { get; set; }
 8: }

The [Key] attribute over the Id property indicates that the Person class is going to take part in the RIA Services contract. I’m not going to dive into details on setting the Silverlight projects to use WCF RIA Services (and RIA Services in general) because that’s beyond the scope of this article; but at this point, I will include the domain service class, which is responsible for getting the data and exposing it on a server endpoint:

 1: [EnableClientAccess]
 2: public class PartyDomainService : DomainService
 3: {
 4:     public IEnumerable<Person> GetChildren()
 5:     {
 6:         List<Person> children = new List<Person>();
 7:         for (int i = 0; i < 10; i++)
 8:         {
 9:             children.Add(DataGenerator.Create(i));
 10:         }
 11:         return children;
 12:     }
 13: }

PartyDomainService is a simple custom DomainService having a single method that will create and return a [mocked] list of 10 children that our birthday girl keeps track of in her database and would like to see on her birthday party. If RIA Services are set correctly, PartyDomainService should get replicated in the client project – see the Generated_Code folder:

RIA Services generated code

[Hint: to see the above folder/file, check the “Show All Files” option button in Solution Explorer]

Bringing in MEF

One good thing about client-generated RIA Services code is that all generated classes are partial, thus extendable. That means that developers can easily make any DomainContext class a MEF export by extending the generated class and decorating it with an appropriate Export attribute. All you need to be aware of when extending partial classes is to put the extending class into the same namespace as the generated class:

 1: [Export]
 2: public partial class PartyDomainContext
 3: {
 4: }

Domain context will get imported into a MailMerge class, which is responsible for doing the actual data merging. Importing is done through constructor injection:

 1: [ImportingConstructor]
 2: public MailMerge(PartyDomainContext dataContext)
 3: {
 4:     this.dataContext = dataContext;
 5: }

When composition happens, the MailMerge class, which itself is an export too, will be imported into the MainPageViewModel as a property import. This way, the ViewModel will automatically have access to the MailMerge class without having to instantiate it by itself.

Mail merging

Mail merging in MS Word requires two things – a template and a data source to fill it. But before we continue with the MailMerge class, let’s take a look at the birthday invitation template our young girl has put together in Word:image

Note the underlined text on the top – Title, FirstName and LastName are MailMerge fields that were inserted into the template; these are meant to be replaced with actual data from our “database” and correlate to Person’s Gender, FirstName and LastName properties.

The princess saved the template into the My Documents folder where she’ll be able to pick it from later. Our next task is creating the data source, which is going to be another Word document with the data table inserted in it. And this Silverlight application will be the one that creates it!

Accessing local file system to create the data source

Trusted Silverlight 4 applications now have access to local file system which means they are allowed to read and write files in those special ‘My’ user folders (My Documents, My Pictures, …) without having to explicitly ask the user for those permissions. With templatePath being the full path to the template file, here’s how the name for data source document is chosen:

 1: string myDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
 2: string filename = Path.GetFileNameWithoutExtension(templatePath);
 3: string dataFilePath = Path.Combine(myDocumentsFolder, filename + ".dat");

Running with elevated permissions, the application will be allowed to write to that file. The data source document won’t be created by our code, we’ll leave this to be done by Word.

Talking to Word

To instantiate the COM object, we’re required to call it by it’s name, and the syntax is very similar to what we were doing years ago with classic ASP or VB script. Except, this time it’s the AutomationFactory class that provides the CreateObject method:

 1: dynamic wordApplication = AutomationFactory.CreateObject("Word.Application");

The dynamic keyword is new in C# 4 syntax and provides the late-binding mechanism we knew back in the VB6/COM days. You can also see the use of dynamic keyword as a shorter syntax for reflection, all serving the purpose of dynamic objects not being type checked during the compilation, but rather at run time only.

Using the dynamic keyword also means you don’t get any intellisense help in Visual Studio which subsequently means you’ll end up reading a lot of help files to understand the API on objects you’re working with. For example, try this Word method for opening a single document:

 1: [application].Documents.Open(FileName, ConfirmConversions, ReadOnly, AddToRecentFiles, PasswordDocument, 
 2:                         PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Format, 
 3:                         Encoding, Visible, OpenConflictDocument, OpenAndRepair, DocumentDirection, NoEncodingDialog)

Not exactly the prettiest you’ve seen, right? Fortunately, all but the first parameter in the above method are optional and you can leave them out – C# 4 provided with yet another fine concept of having optional parameters in method calls. That means that instead of writing this:

 1: dynamic document = wordApplication.Documents.Open(ref templatePath, ref missingValue, ref missingValue, 
 2:                    ref missingValue, ref missingValue, ref missingValue, ref missingValue, ref missingValue, 
 3:                    ref missingValue, ref missingValue, ref missingValue, ref missingValue, ref missingValue, 
 4:                    ref missingValue, ref missingValue, ref missingValue);

we can now simply write:

 1: dynamic document = wordApplication.Documents.Open(templatePath);

Sweet!

To create the data source document, another method with 9 parameters has to be invoked. We only need to provide two of them, but they are a few parameters apart, so this is the best we can come up with:

 1: document.MailMerge.CreateDataSource(templatePath, Missing.Value, Missing.Value, header);

If you’re tempted to use named parameters at this point – you can’t. Currently, named parameters are simply not supported in Silverlight COM Automation scenarios.

OK, we successfully created the data source document. Let’s open it and write some data into it. The Person collection is converted to the two-dimensional array, transforming some of the data to fit what’s needed for the template:

 1: string header = "FirstName;LastName;Title";
 2: IEnumerable<IEnumerable<string>> data = p.Entities.Select(person => new[]
 3:     {
 4:         person.FirstName,
 5:         person.LastName,
 6:         person.Gender == Gender.Boy ? "prince" : "princess"
 7:     }.AsEnumerable());

Header string is used by the CreateDataSource for creating a table in the document. The array will be written to that same table as its data content:

 1: dynamic table = dataDocument.Tables(1);
 2:  
 3: for (int i = 0; i < data.Count(); i++)
 4: {
 5:     dynamic row = i == 0 ? table.Rows(2) : table.Rows.Add();
 6:     InsertRow(row, data.ElementAt(i));
 7: }

Almost done! One last thing needed to be done is doing the actual merge:

 1: document.MailMerge.Execute(false);

At this point, Word will take the template and the data source document, and if everything’s set up correctly, start creating a new page for every row in data source table.

It’s worth noting that COM Automation feature is only allowed with trusted application, and on systems that support it (currently that’s Windows only). To check if COM Automation is available to your code, check the following API:

 1: bool isAutomationAvaliable = AutomationFactory.IsAvailable;

Also, in order to use the dynamic keyword, a project should include a reference to the Microsoft.CSharp.dll.

UI / Conclusion

The accompanying project includes a piece of UI, which will let our young princess select a Word template and process it with the data from the server. Please see other code in there as well as it provides the complete solution presented in this article.

UI

Full source code of the project is available to download here.

COM Automation in Silverlight 4 is big because of all the opportunities it provides to the developer. Due to its nature, it currently only works when the application is deployed to a Windows OS, but Microsoft is investigating means for providing similar stories on other OS’s as well. Elevated permissions are required to use the Automation.

The article discussed a process of creating birthday invitations; you can use exactly the same concept for issuing invoices, for example. Microsoft Office integration has never been so exciting as it is now, with Silverlight leading the way.

Share


Comments

Comments RSS RSS
  • RE: MS Word Mail Merge with Silverlight 4 COM Automation  

    posted by Vincent Houck on May 13, 2010 07:33
    Hi,

    Not sure if I missed something but when I run the WordMailMergeTestPage.aspx, I get a permission error:

    "Unhandled Error in Silverlight Application File operation not permitted. Access to path '' is denied.   at System.IO.FileSecurityState.EnsureState()
       at System.IO.FileSystemInfo.get_FullName()
       at WordMailMerge.MainPageViewModel.OnBrowse(Object obj)
       at WordMailMerge.Input.DelegateCommand.Execute(Object parameter)
       at System.Windows.Controls.Primitives.ButtonBase.ExecuteCommand()
       at System.Windows.Controls.Primitives.ButtonBase.OnClick()
       at System.Windows.Controls.Button.OnClick()
       at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
       at System.Windows.Controls.Control.OnMouseLeftButtonUp(Control ctrl, EventArgs e)
       at MS.Internal.JoltHelper.FireEvent(IntPtr unmanagedObj, IntPtr unmanagedObjArgs, Int32 argsTypeIndex, String eventName)
    Line: 1
    Char: 1
    Code: 0
    URI: http://localhost:55124/WordMailMergeTestPage.aspx"

    Can anyone help?

    Cheers, 

    Vincent

Add Comment

 
 

   
  
  
   
Please add 6 and 7 and type the answer here:

Help us make SilverlightShow even better and win a free t-shirt. Whether you'd like to suggest a change in the structure, content organization, section layout or any other aspect of SilverlightShow appearance - we'd love to hear from you! Need a material (article, tutorial, or other) on a specific topic? Let us know and SilverlightShow content authors will work to have that prepared for you. (hide this)