How Do I: Export Data to a Word Mailing Labels Document


I’m going to start off my first “How Do I” post with a simple application that exports the application’s data to a Word Mailing Label Document.  At the end of the blog you’ll find links to the the VS LightSwitch project code for both C# and VB.Net.

Back Story

I was sending several dozen birth announcements recently to friends and family, and realized that I needed a better way to manage all the addresses and contact information I had floating around my house.  That, and needing to brush up on my Visual Studio LightSwitch (LS) skills, led to the creation of a Contacts manager application. 

I was able to quickly create an application using LS which I thought would meet everyone’s needs.  But my wife insisted she wasn’t interested in using it unless it could export all the contact information to a Word Document.  Fortunately, I was able to get a start on figuring out how to add this functionality by reading Beth Massi’s blog post on creating reports in Word for LS.

Using the System.Runtime.InteropServices.Automation.AutomationFactory class I was able to do just what I needed all from the Client side.

So let’s get started creating a very basic application to manage our contacts, then add some functionality to create a mailing label document using the COM Object and the AutomationFactory class..

Step 1 – Create our simple Contacts table and screen

  1. Create a Visual Studio LightSwitch C# or VB Project
  2. Add a table – Contact, with the following properties:
    1. FirstName | String | Required
    2. MiddleName | String
    3. LastName | String | Required
    4. AddressLine1 | String
    5. AddressLine2 | String
    6. City | String
    7. State | String
    8. ZipCode | String
    9. Country | String
    10. PhoneNumber1 | PhoneNumber
    11. PhoneNumber2 | PhoneNumber
    12. BirthDate | Date
    13. MiscellaneousInformation | String
  3. Add a List and Details screen, called Contacts, which uses the Contacts table data
  4. OPTIONAL – I made some minor changes to the “List” portion of the screen
    1. In the screen designer, navigate to Columns Layout-> Rows Layout –> List and change “List” to “Data Grid” using the drop down arrow
    2. Under Columns Layout –> Rows Layout –> Data Grid –> Data Grid Row remove all fields except for “First Name” and “Last Name”
  5. In the screen designer, under Columns Layout –> Screen Command Bar add a button
  6. Call the button “Export to Word Mailing Label Document” (we’ll add the code later)
  7. Your screen designer and Solution Explorer should look something like this now:
  8. image

Step 2 – Add Client side code to generate our Word document

  1. In Solution Explorer, click the drop down arrow next to the “Logical View” button and select “File View”
    1. The Solution Explorer tree switches to the File View mode which displays all the code under the Client, Common and Server projects
    2. image
  2. We are going to add our own User Class to the Client project, so right click the Client project, and select Add –> Class.
  3. Add a new Class called ExportToWord.  Paste the below code into the class:
    //' Copyright © Microsoft Corporation.  All Rights Reserved.
    
    //' This code released under the terms of the 
    
    //' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html)
    
    using Microsoft.LightSwitch;
    
    using Microsoft.LightSwitch.Framework;
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Text;
    
    using System.IO;
    
    
    
    namespace LightSwitchApplication.UserCode
    
    {
    
        class ExportToWord
    
        {
    
            /// <summary>
    
            /// Create a mailing label document based upon our Contacts and their corresponding address information
    
            /// </summary>
    
            /// <returns>A byte array containing the generated Word Mailing Label Document</returns>
    
            public static byte[] ExportToMailingLabelDocumentWithCOM()
    
            {
    
                Object fileName = String.Concat(
    
                    Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
    
                    @"\Contacts_" + System.DateTime.UtcNow.ToFileTimeUtc() + ".docx");
    
                Object labelName = "8660 Easy Peel Address Labels";
    
    
    
                dynamic wordObject = System.Runtime.InteropServices.Automation.AutomationFactory.CreateObject("Word.Application");
    
                wordObject.Documents.Add();
    
    
    
                // Create an empty mailing label document
    
                dynamic mailingLabelDoc = wordObject.MailingLabel.CreateNewDocument(ref labelName);
    
                try
    
                {
    
                    // Format the contacts address information
    
                    List<string> contacts = FormatContactsAddressInformation();
    
    
    
                    // Populate the word document with labels
    
                    PopulateLabels(contacts, mailingLabelDoc);
    
    
    
                    // Save our label doc as the default word format (i.e. docx)
    
                    mailingLabelDoc.SaveAs2(ref fileName);
    
                }
    
    
    
                catch (Exception e)
    
                {
    
                    throw new InvalidOperationException("Failed to create document", e);
    
                }
    
    
    
                finally
    
                {
    
                    // Wait for Word to shut down to ensure the document is released
    
                    if (mailingLabelDoc != null)
    
                    {
    
                        mailingLabelDoc.Close();
    
                    }
    
                    if (wordObject != null)
    
                    {
    
                        wordObject.Quit();
    
                    }
    
    
    
                    // Cast our wordObject as IDisposable so that we can 
    
                    // invoke .Dispose() and be sure the COM object is released
    
                    IDisposable disposable = wordObject as IDisposable;
    
                    if (disposable != null)
    
                    {
    
                        disposable.Dispose();
    
                    }
    
                }
    
    
    
                // Take our newly created word document, and stick into our a Byte[] array
    
                byte[] buffer = File.ReadAllBytes((string)fileName);
    
    
    
                // Delete the file now that we are done with it
    
                // We'll pass back the byte[] buffer and let the user choose a filename and location
    
                File.Delete((string)fileName);
    
    
    
                return buffer;
    
            }
    
    
    
            /// <summary>
    
            /// Formats each contacts address information and stores the data into a List
    
            /// </summary>
    
            private static List<string> FormatContactsAddressInformation()
    
            {
    
                // Populate a List with our contacts and their address information
    
                List<string> contacts = new List<string>();
    
    
    
                EntitySet<Contact> contactsSet = Application.Current.CreateDataWorkspace().ApplicationData.Contacts;
    
                foreach (Contact contact in contactsSet.OrderBy(x => x.LastName))
    
                {
    
                    string formattedAddress = contact.FirstName + " " + contact.LastName + Environment.NewLine + contact.AddressLine1 + Environment.NewLine;
    
                    if (!String.IsNullOrEmpty(contact.AddressLine2))
    
                    {
    
                        formattedAddress += contact.AddressLine2 + Environment.NewLine;
    
                    }
    
    
    
                    formattedAddress += contact.City + " " + contact.State + " " + contact.ZipCode + Environment.NewLine;
    
    
    
                    if (!String.IsNullOrEmpty(contact.Country))
    
                    {
    
                        formattedAddress += contact.Country;
    
                    }
    
    
    
                    contacts.Add(formattedAddress);
    
                }
    
    
    
                return contacts;
    
            }
    
    
    
            /// <summary>
    
            /// Populate the Labels in a given word document with our Contact's information
    
            /// </summary>
    
            private static void PopulateLabels(List<string> contacts, dynamic wordDocument)
    
            {
    
                dynamic docTable = wordDocument.Tables[1];
    
    
    
                // Starting at row 1, iterate through each row and column in the Mailing Label's Table
    
                // And for each cell, insert our Contact's address information
    
                int row = 1;
    
                int currentContactIndex = 0;
    
                while (currentContactIndex < contacts.Count)
    
                {
    
                    // Skip even numbered columns since they are "divider" columns and not intended for cell text
    
                    for (int column = 1; column <= docTable.Columns.Count; column += 2)
    
                    {
    
                        // If we've run out of Contacts, break out
    
                        if (currentContactIndex >= contacts.Count)
    
                        {
    
                            break;
    
                        }
    
                        docTable.Cell(row, column).Range.Text = contacts[currentContactIndex];
    
                        docTable.Cell(row, column).Range.ParagraphFormat.Alignment = 1;
    
                        docTable.Cell(row, column).Range.Cells.VerticalAlignment = 1;
    
                        docTable.Cell(row, column).Range.Rows.Alignment = 1;
    
                        currentContactIndex++;
    
                    }
    
                    // Keep track of what row we are on in our table
    
                    row++;
    
    
    
                    // Check to see if we've run out of rows in our table
    
                    if (row > docTable.Rows.Count)
    
                    {
    
                        // Add a new row to our table
    
                        docTable.Rows.Add();
    
                    }
    
                }
    
            }
    
        }
    
    }
    
    
    
  4. Here’s the breakdown of what the ExportToWord’s class methods are doing:
    • PopulateLabels():
      1. Iterates over the table cell’s in a Word document instance.
      2. Populates each cell with a Contact’s address information.
    • FormatContactsAddressInformation():
      1. Iterates over each Contact in the SQL database, ordering the contacts by LastName .
      2. Takes the necessary address information from each contact and formats it into a nice looking string.
      3. Each string is added to a List of address information.
    • ExportToMailingLabelDocumentWithCOM():
      1. Creates a Word.Application COM Object.
      2. Creates an empty Mailing Label Document based off the “8660 Easy Peel Address Labels” template.
      3. Calls FormatContactsAddressInformation() and PopulateLabels()
      4. Now the mailing labels are populated with the correct data
      5. Save the document and then store it in a byte array
      6. Delete the original document, and pass the byte array back to the calling method

Step 4 – Add Button code to call into our ExportToWord class

  1. We are now going to add code to our button to invoke the code we added in the previous steps.
  2. In the screen designer, under Columns Layout –> Screen Command Bar, right click the “Export to Word Mailing Label Document” button and select “Edit Execute code”
  3. Paste the following code into the your button’s method :
                // Call into our Client User Class to create the word document, then return it as a byte array
    
                byte[] wordDocument = ExportToWord.ExportToMailingLabelDocumentWithCOM();
    
    
    
                this.DisplaySaveAsFileDialog(wordDocument);
  4. This won’t compile now until we add the DisplaySaveAsFileDialog() method to the same class as well.  So copy and paste the below method into the class:
            /// <summary>
    
            /// This method will display a SaveAsFileDialog to the user to save
    
            /// a given file buffer
    
            /// </summary>
    
            private void DisplaySaveAsFileDialog(byte[] fileBuffer)
    
            {
    
                // Need to switch back to the Main UI thread before we can pop up any dialogs.
    
                // We need to do this, because by default the button code operates outside of the main
    
                // UI thread, and the only way the user is going to see a Dialog box is for it to 
    
                // appear inside the main UI thread.
    
                Dispatchers.Main.BeginInvoke(() =>
    
                {
    
                    SaveFileDialog dialog = new SaveFileDialog();
    
                    dialog.DefaultExt = ".docx";
    
                    bool dialogResult = (bool)dialog.ShowDialog();
    
    
    
                    if (dialogResult == true)
    
                    {
    
                        using (FileStream fileStream = (FileStream)dialog.OpenFile())
    
                        {
    
                            fileStream.Write(fileBuffer, 0, fileBuffer.Length);
    
                            fileStream.Close();
    
                        }
    
                    }
    
                });
    
            }
  5. And then, we will need to add these using statements.
    using System.Windows;
    
    using System.Windows.Controls;
    
    using Microsoft.LightSwitch.Threading;
    
    //Namespace of our ExportToWord class
    
    using LightSwitchApplication.UserCode;
  6. In addition to invoking the ExportToWord.ExportToMailingLabelDocumentWithCOM() method we wrote, this button code will display a Save File Dialog so that the user can decide where on their client side machine they want to save the Word document.
  7. To create the file, the Save File Dialog will read in the byte[] array which we returned earlier from the ExportToMailingLabelDocumentWithCOM() method.
  8. As the code comments in DisplaySaveAsFileDialog() state, to invoke the Save File Dialog, we have to switch to the main UI thread since the button’s code operates outside the main UI thread.  And if we want to do something like pop up a dialog, it has to be done in the main UI thread.

Step 6 – Run it

  1. Compile and run our Contacts application now. 
  2. Enter in some Contact information.
  3. We should have a screen looking something like this:
  4. image
  5. Now if we click the “Export to Word Mailing Label Document” button, our application will display a “Save As” File dialog.
  6. After specifying a name for our file, we should be able to open it and see our mailing labels.
    1. I specified the 8660 Easy Peel Address Labels in our code when generating the Word document, but you could customize this code for a different mailing label template if needed.

Code samples are here: http://code.msdn.microsoft.com/Visual-Studio-LightSwitch-d2ca025e

In the near future I should have another blog post on how to create a document on the Server side (allowing you to take advantage of Intellisense) and transfer that document back to the Client.

Let me know if you have any questions!

Thanks –  Matt Sampson Martini glass

Comments (11)

  1. Paul Patterson says:

    Thanks Matt,

    This is a great article, and very timely too. I was looking for some examples on using Word. Generating the tables via code is a simple way to integrate the LightSwitch data into Word.

    I've seen Beth Massi's article on integrating Word using XML (see here… blogs.msdn.com/…/using-microsoft-word-to-create-reports-for-lightswitch-or-silverlight.aspx )

    Would you have any examples of how to achieve the same scenario usage as Beth's example via XML and content controls? I have been struggling at creating a table in Word and binding it to content controls.

    Any information is appreciated, and thanks again. Hope to see more articles from you.

    Cheers,

    Paul

    http://www.PaulSPatterson.com

  2. @Paul:

    Thanks a lot, Paul.

    I've got another blog post planned (and already have the code ready) where I will:

    1) Generate a Word Document, using OpenXML

    2) The Word Doc will have a table embedded in it

    3) Data from the application will be exported to a corresponding "cell" in the table

    Maybe not quite what you were looking for, but does this sound like something that would help?

  3. Paul Patterson says:

    Awesome, I'll be stalking you for it…I mean, I'll be diligently keeping a watchful eye for your post.

    Cheers Matt!

  4. Dan Moyer says:

    A great article Matt.   I like seeing another example how to use LS with Word as well as other Office apps.

    Seeing your earlier blog, so Fargo has part of the LightSwitch team?  That's cool.   So much knowledge there on what's needed for Line of Busineess apps.

    Cool to see ideas from Microsoft Business Frameworks reincarnated into LS.

  5. @Dan –

    Thanks, Dan.

    Yep, Fargo has a smallish VS LightSwitch team.  The campus here is mostly made up of MS Dynamics teams however.

    It's definitely a campus focused around business related applications, no doubt.

  6. Keith Watford,UK says:

    Thanks Matt,

    This has really added value to the LightSwitch concept and I'm sure the community will thank you for sharing your work.

    You have given me a valuable approach for enhancing my LS apps.

    I hope we see some more of your contributions.

    Thanks again, Keith

  7. Stuart says:

    When I run it, it won't let me add any contacts. The Add button is grayed out.  Why?  How can I fix it?

  8. @Stuart –

    Sorry man, I can't reproduce the issue you are seeing.

    If you give me some more detail I might be able to help.

    Was this with the code from the Code Gallery?  If so, was it C# or VB?

    Are you running it out of Visual Studio in debug mode?  Or are you publishing it and then running it?

    Or you running it as the admin?

  9. @Stuart –

    Sorry man, I can't reproduce the issue you are seeing.

    If you give me some more detail I might be able to help.

    Was this with the code from the Code Gallery?  If so, was it C# or VB?

    Are you running it out of Visual Studio in debug mode?  Or are you publishing it and then running it?

    Or you running it as the admin?

  10. Mark Sorgedrager says:

    A great article Matt, just wat I needed!!..only one problem, the "save as" file dialog won't apear?

    Can you tell me what i'm doing wrong, here's my code:

    using System;

    using System.Linq;

    using System.IO;

    using System.IO.IsolatedStorage;

    using System.Collections.Generic;

    using Microsoft.LightSwitch;

    using Microsoft.LightSwitch.Framework.Client;

    using Microsoft.LightSwitch.Presentation;

    using Microsoft.LightSwitch.Presentation.Extensions;

    using System.Windows;

    using System.Windows.Controls;

    using Microsoft.LightSwitch.Threading;

    using LightSwitchApplication.UserCode;

    namespace LightSwitchApplication

    {

       public partial class Contacts

       {

           partial void Export_to_Word_Mailing_Label_Document_Execute()

           {

               // Call into our Client User Class to create the word document, then return it as a byte array

               byte[] wordDocument = ExportToWord.ExportToMailingLabelDocumentWithCOM();

           }

           private void DisplaySaveAsFileDialog(byte[] fileBuffer)

           {

               // Need to switch back to the Main UI thread before we can pop up any dialogs.

               // We need to do this, because by default the button code operates outside of the main

               // UI thread, and the only way the user is going to see a Dialog box is for it to

               // appear inside the main UI thread.

               Dispatchers.Main.BeginInvoke(() =>

               {

                   SaveFileDialog dialog = new SaveFileDialog();

                   dialog.DefaultExt = ".docx";

                   bool dialogResult = (bool)dialog.ShowDialog();

                   if (dialogResult == true)

                   {

                       using (FileStream fileStream = (FileStream)dialog.OpenFile())

                       {

                           fileStream.Write(fileBuffer, 0, fileBuffer.Length);

                           fileStream.Close();

                       }

                   }

               });

           }

       }

    }

  11. Joost says:

    Hi Matt,

    Great tut. Is there a way to retrieve the same data but then from two tables instead of one?

    So that the address information is separated in a different table? Can the contacts information and address information be merged into one cell?