Populating Word documents with data from external sources (database, SharePoint, Outlook, etc.)

Developing with Microsoft Word is largely about working with Word object to create and manipulate content within Word. From pages to paragraphs, properties to parts, etc. (I ran out of Ps)… you work with content in Word. Thus far, our samples assume the content originates in Word. It’s a fair assumption but not always valid.

We all have content whose home is outside Microsoft Word. We all want to have a sophisticated method for inserting this content (or data) into Word documents without repeatedly copying and pasting.

We’re developers. Let’s build something.

Basic strategy

Today, I have three main C# code samples that insert data into Word. Each sample method creates and then goes to town inserting data. It is a simple strategy and works very well to get you started integrating data into Word.

• Insert data from an Access database
• Import data from Outlook
• Import data from SharePoint

There is a little bit of setup involved. Let’s tackle that now.

The setup

We need a new Add-in Express based COM Add-in project. So, go ahead and create one. Be sure to specify C# as the language (I know, I know… I typically use VB.NET but today I’m mixing it up).

Adding required namespaces

After Visual Studio creates the add-in project and displays the AddinModule, open its code view and add these namespaces.

using System.Data.OleDb;
using Outlook = Microsoft.Office.Interop.Outlook;
using Microsoft.SharePoint.Client;
using Wictor.Office365;

The last line will upset Visual Studio because the namespace does not exist… yet. But it will. All in good time Visual Studio. In the meantime, resist the urge to compile.

Adding required references

We need to add a few references. Here is a screenshot from the sample code project’s references. For you convenience and coding pleasure, I highlighted the ones you need to add.

Completing the last prerequisite

The big finale today deals with inserting information from SharePoint into a document. I like to be hip and current so I’m using SharePoint online in the example. SharePoint Online requires an authentication token.

Trying to understand Microsoft’s documentation that shows how to do this 1) made my hair a bit grayer and 2) ticked me off a bit. Soooo… I decided to stop ignoring the search results that pointed to this article: How to do active authentication to Office 365 and SharePoint Online.

I’ve included the classes from his code sample in my code sample. I recommend that you import them too using this article’s sample or his sample. Here are the files to import:

For those of you from West Texas, the files to import are green-highlighted ones. These classes allow us to authenticate by passing the site url, the user name, and the password to an appropriate method. More on this later.

Create the table helper function

To promote code reuse, I created a helper function that creates a table and returns to the function that called. This function requires the table dimensions, the target document, and the target range. These four parameters are all it takes to make this helper function happy.

private Word.Table GiveMeATable(
    int columnCount, int rowCount, Word.Document doc, Word.Range rg)
{
    if (columnCount > 0 & rowCount > 0)
    {
        //We aren't goofing around. Let's make a table.
        Word.Table tbl = default(Word.Table);
        tbl = doc.Tables.Add(rg, rowCount, columnCount,
            Word.WdDefaultTableBehavior.wdWord9TableBehavior,
            Word.WdAutoFitBehavior.wdAutoFitContent);
        return tbl;
    }
    return null;            
}

The end result is a freshly created table… served up to the calling method to do as it pleases with it.

We are now ready to deal with the main event. There are three rounds in today’s main event.

Inserting data from an Access database

Let’s pretend we have lots of important data residing in an Access database. Don’t snicker… lots of companies do, indeed, store business critical data in Access. If you want to turn your nose up, just pretend we are using SQL Server. The idea is the same.

This method calls GiveMeATable to create a table. With the table in-hand, the method queries data from the Access database and proceeds to read through it.

private void InsertFromAccess ()
{
    //Create a table
    Word.Table tbl = GiveMeATable(1, 1, 
        this.WordApp.ActiveDocument, this.WordApp.Selection.Range);
 
    //Add some table stylings
    tbl.ApplyStyleHeadingRows = true;
    tbl.ApplyStyleRowBands = true;
    tbl.set_Style("Grid Table 4 - Accent 6"); //Green. I like green.
 
    //We need a counter...
    int i = 1;
    //And a table header
    tbl.Cell(i, 1).Range.Text = "Company Name";
    //And a new row
    tbl.Rows.Add();
    i = i + 1;
 
    //Call the database.
    OleDbConnection cnn = new OleDbConnection();
 
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        "C:\\Users\\Tyrant\\Desktop\\PopulateWordWithData\\Northwind2007.accdb;" +
        "Persist Security Info=False;";
 
    string sql = "SELECT Top 11 Company FROM Customers ";
    OleDbCommand command = new OleDbCommand(sql, cnn);
    cnn.Open();
    OleDbDataReader reader = command.ExecuteReader();
 
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            tbl.Rows.Add();
            i = i + i;
 
            tbl.Cell(i, 1).Range.Text = reader[0].ToString();
        }
    }
    Marshal.ReleaseComObject(tbl);
}

As it reads each line of query result, the method adds a new row to the table. Each row contains a single column with the company name in it. After you execute the code, the table looks like this:

I like green. You might prefer to style the table to your preferences.

Importing data from Outlook

To use Microsoft Outlook as a data source, we need to start Outlook, access the folder containing the data we want, read it, and insert it.

Of course, we need to get a table first via a call to GiveMeATable.

private void InsertFromOutlook()
{
    //Create a table
    Word.Table tbl = GiveMeATable(3, 1,
        this.WordApp.ActiveDocument, this.WordApp.Selection.Range);
 
    //Add some table stylings
    tbl.ApplyStyleHeadingRows = true;
    tbl.ApplyStyleRowBands = true;
    tbl.set_Style("Grid Table 4 - Accent 6");
    //Green. I like green.
 
    //We need a counter...
    int i = 1;
    //And a table header
    tbl.Cell(i, 1).Range.Text = "Contact Name";
    tbl.Cell(i, 2).Range.Text = "Phone Number";
    tbl.Cell(i, 3).Range.Text = "Email Address";
 
    //start outlook
    Outlook.Application appOL = new Outlook.Application();
    Outlook.Folder fldr = (Outlook.Folder)appOL.Session.
        GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);
 
    if (fldr.Items.Count > 0)
    {
        Outlook.ContactItem buddy;
        int iFldrCount = fldr.Items.Count;
        int iContacts = 1;
        do
        {
            tbl.Rows.Add();
            i = i + 1;
            buddy = fldr.Items[iContacts];
            tbl.Cell(i, 1).Range.Text = buddy.FullName;
            tbl.Cell(i, 2).Range.Text = buddy.MobileTelephoneNumber;
            tbl.Cell(i, 3).Range.Text = buddy.Email1Address;
            iContacts++;
            Marshal.ReleaseComObject(buddy);
        } while (iContacts < iFldrCount + 1);
    }
    Marshal.ReleaseComObject(fldr);
    appOL.Quit();
    Marshal.ReleaseComObject(appOL);
    Marshal.ReleaseComObject(tbl);
}

In this sample, the table contains 3 columns for storing the contact name, phone number, and email address. To access the data, we create a new Outlook Application object and grab the default contacts folder. If the folder contains items we read them and insert them into the table.

The code output looks like this.

Importing data from SharePoint

For the final round, we tackle the great SharePoint Online. This method access a SharePoint online site and inserts the name of each list into the document.

We begin like we always do… with a call to GiveMeATable. After that, we quickly move to authenticating with SharePoint online. This is where Wictor’s code proves itself more than a little bit useful.

private void InsertFromSharePoint(string url, string username, string password)
{
    if (url.Length > 0)
    {
        //Create a table
        Word.Table tbl = GiveMeATable(1, 1,
            this.WordApp.ActiveDocument, this.WordApp.Selection.Range);
 
        //Add some table stylings
        tbl.ApplyStyleHeadingRows = true;
        tbl.ApplyStyleRowBands = true;
        tbl.set_Style("Grid Table 4 - Accent 6")
        //Green. I like green.
 
        //Authenticate with SharePoint online
        MsOnlineClaimsHelper claimsHelper =
            new MsOnlineClaimsHelper(url, username, password);
 
        using (ClientContext context = new ClientContext(url))
        {
            context.ExecutingWebRequest +=
                claimsHelper.clientContext_ExecutingWebRequest;
            context.Load(context.Web);
            context.ExecuteQuery();
 
            //We need a counter...
            int i = 1;
            //And a table header
            tbl.Cell(i, 1).Range.Text = "SharePoint Site Lists - " +
                context.Web.Title;
 
            //Requery to get the lists
            context.Load(context.Web.Lists);
            context.ExecuteQuery();
            foreach (List list in context.Web.Lists)
            {
                //And a new row
                tbl.Rows.Add();
                i = i + 1;
                tbl.Cell(i, 1).Range.Text = list.Title;
            }
        }
    }
}

After authenticating and gaining access to the target SharePoint site, we create a ClientContext object and use it to execute two queries. The firs query’s the web. We use this query to grab the site’s title. The second query returns the list of lists in SharePoint. We read this list and insert it into the table.

Voila!

***

Inserting data into Word requires that you 1) retrieve the data from the data source and 2) insert into Word. In this sample, I inserted the data into a table by looping through the data and adding rows to the table. A similar strategy can be utilized with other Word objects. It depends on your business rules and document structure. It’s a topic worth investigating further… no doubt about it.

Available downloads:

This sample Word add-in was developed using Add-in Express for Office and .net:

Populate Word Data add-in (C#)

Word add-in development in Visual Studio for beginners:

• Part 1: Word add-in development – Application and base objects
• Part 2: Customizing Word UI – What is and isn’t customizable
• Part 3: Customizing Word main menu, context menus and Backstage view
• Part 4: Creating custom Word ribbons and toolbars
• Part 5: Building custom task panes for Word 2013 – 2003
• Part 6: Working with Word document content objects
• Part 7: Working with Word document designs, styles and printing
• Part 8: Working with multiple Microsoft Word documents
• Part 9: Using custom XML parts in Word add-ins
• Part 10: Working with Word document properties, bookmarks, content controls and quick parts
• Part 12: Working with Microsoft Word templates