ColdFusion Word Mail Merge using OOXML

If you’ve ever needed to use ColdFusion to manipulate Word documents, you might have tried a 3rd party library like Doc4J or Apache POI.

While these libraries are very robust I found them to be limiting in different ways. Apache POI lacks built-in mail merge capability (which to me seems very odd given the information below) and Doc4j threw an odd Jakarta error that I was not able to fix.

I also looked at paid for libraries like Apose, but the licensing costs were just too prohibitive

Finally I decided on direct OOXML manipulation.

This turned out to be much easier than I anticipated, once I learned that Office files like Word docx files are actually Zip archives!

Who knew!?!?!

So it’s possible to use the native cfzip tag to “open” the file.

<cfzip action="unzip" file="{Your Office Document File Path}" destination="{A temporary folder}" recurse="yes"/>

Once unzipped to a folder you will have a directory structure like this:

Within the Word subfolder there are several XML files. The document I am manipulating for the mail merge is document.xml

This file can be read into ColdFusion using xmlParse() and from there can be manipulated like any other XML object using ColdFusion’s native tags and commands.

I couldn’t find any “standard” way for altering the XML to merge data into the various template fields.

To get an idea of how Word does it I created a test Word document with a simple and complex mail merge field: Word Merge Fields.docx

Running this through a Word mail merge and then unzipping the resulting file and reviewing the document.xml for the “merged” document I found that simple merge fields (fldSimple) are completely replaced with the merged value while complex fields have a begin and end XML node delimiter that must be parsed and manipulated in specific ways.

In order to properly merge complex fields it’s necessary to determine if there is a separator XML node within the field. If so, the the node between the separator node and the end node are used as the value. If no separator node is found then the entire field is replaced with the merged value just as simple merge fields.

After updating the parsed XML it must be written back to the document.xml file:

<cffile action="write" file="{Path to document.xml}" output="#toString(CF XML object)#" charset="utf-8">

Once the document.xml file has been saved we need to rezip everything back into a Word docx file:

<cfzip action="zip" file="{Full path to resulting docx file}" source="{Folder containing unzipped contents}" recurse="yes"/>

Fully merged document:
Word Merge Fields – Merged.docx

So far I’ve only used this for mail merges, but I’m sure that is only scratching the surface.

I do most of my development locally (using my local IIS webserver). My local webserver makes use of host headers to host multiple development sites on a single IP. After reading several articles and blog entries I was unable to find a good solution to allow using host headers with Android. The emulator can access the host machine out of the box on 10.0.2.2, but that will only show you the default IIS site.

Tom Deryckere’s article (http://www.mobiledrupal.com/content/using-virtual-hosts-android-emulator) explains how to get host headers working on the emulator, but I didn’t want to run terminal commands every time I launched the emulator or keep updating my host file across mulitple emulator images (I’m really lazy.)

The solution was pretty simple and I’m embarrassed that I didn’t think of it before. I simply added another binding entry to a new port:

In IIS Manager:

1. Select the site you want to access
2. Click ‘Bindings’ in the right side Actions pane
3. Click ‘Add…’ on the Site Binding dialog that is displayed
4. Give the site a any valid port number that is not currently in use (example 9191)

Now in the Android emulator you can access your site using http://10.0.2.2:9191

I began using FCKEditor long before it was included with Coldfusion. I’ve continued to use my own implemenation even after the release of Coldfusion 8.

There was a vulnerability found in the included FCKEditor:
http://www.adobe.com/support/security/bulletins/apsb09-09.html

I applied the patch to my server and it also broke my own FCKEditor instance. I received “403 Access Denied” errors when using the FCKEditor file browser. After much time and effort the solution is pretty simple:

Add this to your JVM args in jvm.config:

-Dcoldfusion.fckupload=true

***CAREFUL about copying and pasting the text above. Hidden carriage returns will cause CF to fail on startup.***

Even if you are not using the Coldfusion FCKEditor you still need to enable FCKEditor uploads. The updated JAR must have a built-in request filter for “filemanager”.

Thanks to Tim Jackson for bringing this one to my attention. Since I’ve worked in Coldfusion I’ve always used code similar to this:

<cfset variables.protocol = iif(CGI.SERVER_PORT EQ 443, de("https"), de("http"))>

So that I could avoid the “This page contains unsecure items.” message when embedding Flash, Javascript, images, etc.

<img src="#variables.protocol#://www.foo.com/linkedimage.jpg"/>

Tim advised me that he had found a new way to avoid this without using server-side code:

<img src="//www.foo.com/linkedimages.jpg"/>

Apparently the browser retains the current protocol. I’ve tested this in IE7 and Firefox using images, Flash, anchors, and JavaScript.