Ty Anderson

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.

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.

Adding required references

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:

The classes 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:

The table pupulated with data from an Access database

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.

Contact information imported from Outlook

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.

The table pupulated with data imported from SharePoint Online

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:

4 Comments

  • https://secure.gravatar.com/avatar/2233c2fa46aef5b4987a46f8cac1d7bc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G cddesigner says:

    I am forms designer and I work with vendors that provide forms development/coding services for our company. They are using Word automation with VB.net and we constantly get back test print with large gaps of white space or nested tables that don’t align. We end up going back and forth on the formatting and they seem to have little knowledge of how to control the spacing consistently. I have very little experience with VB and don’t have insight to exactly what they are doing or how so I feel like I am stuck with their results which at times is poor.
    So, my main question right now is with tables. I have created a template that has multiple tables. One of the tables lets say has 4 rows. The 4 rows have various cells that will fill with data. At run time the table would need to repeat a different number of times for each record. So record 1 may only need the table once, but record 2 may need the table repeated 4 times. The cells within the specific table is always consistent and I have designed the table to have fixed cell widths based on the data length and have set the cells to “stay with next” in order to control page flow, etc.

    With all the above information what is the best method for repeating these tables in the form? What can I do as the forms designer to help the developer more easily accomplish or code this type of template? Any thoughts would be appreciated.

  • Could you send me the template and examples of what you get and what you need to achieve? Please use the contact form at https://www.add-in-express.com/support/askus.php; it allows sending attachments.

    > We end up going back and forth on the formatting and they seem to have little knowledge of how to control the spacing consistently.

    Note that the formatting of a Word document depends on the default printer that you use and the paper settings.

    > what is the best method for repeating these tables in the form?

    the form? Are you talking about Word? If so, you can use the Macro Recorder to record a VBA macro while performing the required steps in the Word UI.

    Hope your email will help me understand your problem.

  • https://secure.gravatar.com/avatar/2233c2fa46aef5b4987a46f8cac1d7bc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G cddesigner says:

    Thank you so much for your response. I will upload the template and example which relates directly to the table question. In general this particular form test print looked really good except I noticed the table for the driver info was slightly off and when reviewing his template once coded I saw he had changed to a nested table. When I asked could he create those in some other manner (I really dislike nested tables) his response was, “I will have to recreate the driver table every time for a new driver in the code, which means going from having 1 cell to splitting it to 7 cells, and then merging them back to 2 cells. And when doing that, I would have to set the width of cells in the code.
    With the nested table, I’m just copying the original driver table and pasting it for all the extra drivers. Then filling all those tables with the driver’s info.”

    I just want to know what others may think is the best way to code this or is there something else I can do in the form with the design. I am hoping to gain a better understanding. We are using Word, saving in 97 format. They convert to PDF and send as test print. I also ask for the Word doc before converting to PDF since that helps me see how they are populating or creating the final document. I also have recorded many macros but don’t know how that can help me in this case. Thanks again.

  • Please understand that I sit at a different desk looking at a different monitor; I just can’t see what you see. I don’t understand whether this is a programming issue or not.

Post a comment

Have any questions? Ask us right now!