Using a word 2003 XML template with SharePoint List data

So how do you generate a word formatted document from a template doc, that substitutes placeholders with SharePoint list data?

Say for example you have a standard agreement or contract style document that you want to generate for new clients. You have your standard word 2003 template (doc) where you’d like to automatically populate data such as clients name, address, phone number etc. from your data already contained inside a SharePoint list.

Well there are a couple of approaches you could take:

1.       Use InfoPath – this is easy to connect to SharePoint data, however not as flexible to modify text etc if the contract wording changes

2.       Use Word 2007 – there is automatic ‘Document Property’ fields which you can add as placeholders, and if your template is uploaded to a library on your SharePoint site you can connect these fields to your library or content type fields.

3.       Or, if you want to save your output file as .doc and not .docx for compatibility reasons, you can use word 2003 and save it as a word xml file. This is what I’ll be looking at…

Open your word 2003 file (.doc) of the document you want to use as the template contract. (and yes I know I’m using word 2007, but you can achieve the same thing using word 2003) eg.

wordxml2003_1.jpg

So you want to fill in data where it I’ve currently got underlines. Replace the underlines with your unique placeholder characters (I use curly braces) with the name of the placeholder you will find and replace in your code later.

What I do is use a format of {ListName.FieldName} so that I can automatically loop through my list fields and replace it in the template when I have found a match. This means I can add a new field placeholder in my contract later down the track without having to rewrite code.

wordxml2003_2.jpg 

When you are happy, go Save As… and select Word (2003) XML file. You then have a new xml document that can be opened in word. If you select open with notepad, you can see the xml nodes that it has generated.

wordxml2003_3.jpg 

If you now search for your field placeholders, you will find they are inside the document.

 wordxml2003_4.jpg

Now upload this file to your template library of a SharePoint site.

 wordxml2003_5.jpg

You now need to develop some code that will generate the contract document from your template. You may wish to generate the document on an event handler, such as when a client is added/updated, or perhaps create a webpart that you can manually push a button to generate the document. I’ll demo the webpart method.

Create a new webpart in VS, and add a button that will trigger the generating of the document.

On the click event on the button, loop over your SP lists for the current list item and associated lookup list items, and add code to:

·         Generate a generics list of type strings that contains the tag names (eg.  {ClientList.Client Name})

·         Generate a hash table containing the tag names and associated value for that list item (eg. tagValuesHT.Add(tagName, tagValue) )

Create a function returning a byte array that will open the template file as a bite array, and convert to a string.

You can then do a regex match through the template file for matching tags (your tag list), and replace that match with the hashtable tag value.

The function will return a byte array which you can then save as your new doc file into your document library of client contracts.

Below is a snippet of some of the code required in the webpart, I’ll try update more on the webpart later when I have more time…

// Web part create child controls function to render button
 protected override void CreateChildControls()
        {
            base.CreateChildControls();

            HtmlGenericControl div = new HtmlGenericControl(“div”);
            HtmlGenericControl p = new HtmlGenericControl(“p”);
            p.InnerText = “Here you can generate contract documents based on the template file:”;
            div.Controls.Add(p);
            this.Controls.Add(div);

            HtmlTable table = new HtmlTable();
            HtmlTableRow row = new HtmlTableRow();
            HtmlTableCell cell = new HtmlTableCell();

            Button btn = new Button();
            btn.Text = “Create Contract”;
            btn.Click += new EventHandler(btn_Click);
            cell.Controls.Add(btn);
            row.Controls.Add(cell);
            table.Controls.Add(row);
            this.Controls.Add(table);

 }

//Definition for tagList, use function to loop over lists to get List and field names to generate tags
// such as {ClientList.Client Name}
List<string> tagList = new List<string>();
// Definition for Hashtable list of keys and values of tags, use function to loop over tag names and
// get values for tags
Hashtable tagValueList = new Hashtable();

void btn_Click(object sender, EventArgs e)
        {
            try
            {
                using (SPSite site = new SPSite(“http://site/“))
                {
                    using (SPWeb documentWeb = site.OpenWeb())
                    {
                        if (documentWeb.Exists)
                        {
   //Write function to loop over lists to set tags and values for list
                            SetTagsList(documentWeb.ParentWeb, agreementListId);
                         //Generate new document from template
   byte[] newDoc = GetProcessedTemplate(“My Contract file.xml”, documentWeb);
   //Open output document library
                            SPDocumentLibrary docLib = (SPDocumentLibrary)documentWeb.Lists[“Contract Documents”];
//Save output byte array as doc file in new library
   docLib.RootFolder.Files.Add(“generated document.doc”, newDoc, true);
                            docLib.Update();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }

        public byte[] GetProcessedTemplate(string templateFilename, SPWeb web)
        {
            string outputFileText = String.Empty;
            UTF8Encoding encoding = new UTF8Encoding();
 //Open template file as byte array
            byte[] templateFileBytes = this.GetTemplate(templateFilename, web);
            string templateFileText = encoding.GetString(templateFileBytes);

 //Loop over list of tags and to regex search to find and replace any matches
            foreach (string tag in tagList)
            {
                int index = 0;
                string tagValue = Convert.ToString(tagValueList[tag]);
                Regex regex = new Regex(tag);
                Match match = regex.Match(templateFileText);
                while (match.Success)
                {
                    outputFileText = regex.Replace(templateFileText, tagValue);
                    templateFileText = outputFileText;
                    match = match.NextMatch();
                    index++;
                }
            }
            outputFileText = templateFileText;
 //return byte array where placeholders have been replaced by list values
            return encoding.GetBytes(outputFileText);
        }

// Set List of tags and values
        private void SetTagsList(SPWeb web, int listItemId)
        {
            tagValueList.Clear();
            tagList.Clear();
            try
            {
                using (SPWeb myWeb = web)
                {
           SPList list = myWeb.Lists[ListName];
           SPListItem listItem = list.Items.GetItemById(listItemId);

                    foreach (SPField field in list.Fields)
                    {
                       CreateTagEntry(field, listItem);
              }
                }
            }
            catch (Exception ex)
            {
            }
        }

        private void CreateTagEntry(SPField field, SPListItem listItem)
        {
            if (!field.Hidden && field.Type != SPFieldType.Computed)
            {
                string newTagName = “{” + listItem.ParentList.Title + “.” + field.Title + “}”;
                if (!tagValueList.Contains(newTagName))
                {
                    tagValueList.Add(newTagName, listItem[field.Id]);
   tagList.Add(newTagName);
                }
            }
        }

Advertisements
Posted in MOSS. 1 Comment »

One Response to “Using a word 2003 XML template with SharePoint List data”

  1. Michelle Says:

    When i create the template and then view it in notepad my palceholders are split up with syntax such as {ClientList.Title
    so my curly bracket is split from the rest of the palceholder and then the match logic doesn’t work any idea how i can fix this? I have followed instructions as outlined. Thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: