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.

There seems to be a potential bug in ColdFusion 2021 Query of Queries.

Today I ran into this error:

Index 5 out of bounds for length 5 null

The index bounds will change based on the number of columns in the query as explained below.

This error was being thrown in a very basic QoQ:

<cfquery name="qUser" dbtype="query">
SELECT	*
FROM	qUsers
WHERE	User_ID = <cfqueryparam value="#user_id#" cfsqltype="cf_sql_numeric">
</cfquery>

While Investigating the source for the original qUsers query I noticed two columns with the same name:

Looking a little deeper I found that the original qUsers query was being ordered via a QoQ prior to the QoQ throwing the error:

<cfquery name="qUsers" dbtype="query">
SELECT	  *
FROM	  qUsers
ORDER BY  payroll_name
</cfquery>

So essentially a query of a query of a query.

The problem appears to be that ColdFusion is case-sensitive and for some reason adds the ORDER BY column to the query resulting in a query that has two columns with the same name.

The solution was to change the ordering QoQ to match the case of the original qUsers query:

<cfquery name="qUsers" dbtype="query">
SELECT	  *
FROM	  qUsers
ORDER BY  PAYROLL_NAME
</cfquery>

I recently installed Nodeclipse for developing Node.js. Nice little editor with some code hinting and quick Node run command.

Due to my development requirements (and laziness) I wanted to work with the remote JS files using FTP. Nodeclipse does not support FTP out of the box. Enter “Remote System Explorer” for Eclipse.

The plugin installed easily. I entered my connection details. The console started showing my connection status and directory listings. However, attempting to explore the FTP site resulted in a generic “file system input or output error” and/or “java.net.SocketException”.

After some searching I found that the issue actually lies with the underlying Java VM used by Eclipse (Nodeclipse). I’d upgraded to JRE7 and there seems to be a bug in this release that causes an incompatibility with Eclipse.

I changed my shortcut to force Nodeclipse to use an install of JRE6 BIN folder.

eclipse.exe -vm "C:\Program Files\Java\jre6\bin"

NOTE: Ensure you are using the appropriate architecture (32-bit OR 64-bit) when matching JRE6 to Eclipse

Went digging around for ways to view the active connections in Microsoft SQL 2008. To my surprise Microsoft moved the Activity Monitor. Apparently they did this way back in SQL 2005. I skipped 2005 so I missed that. To view the open connection:

1. Open Management Studio.
2. On the standard toolbar is a little graph icon. That is the Activity Monitor (CTRL + ALT + A).
3. Click on Processes

Simple as that!