#Value! error when using builtin POI in ColdFusion MX7

CF 7 (and 8, I think) has the java POI library builtin which means that (amongst other things) you can create native Excel spreadsheet files. cfSearching and Ben Nadel’s great blogs have more details.

I used Ben’s great POI Utility which is a set of custom tags that allows easy creation of XLS files with CSS styles for formatting. Fantastic stuff. However, when I created a cell containing the Excel COUNTIF() function, the cell displayed the #Value! error even though there was no error. By double clicking the cell then pressing enter, the error went away and the correct result was displayed.

After some googling I discovered that there was a bug in earlier versions of the POI java library that is shipped with CF7 (and 8??). So, nothing to do with the POI Utility, everything to do with the underlying Java.

Time to get the latest version of the POI Java jars and use Mark Mandel’s brilliant JavaLoader.cfc to load them into CF. If you need to know how to do that, see cfSearching’s post on that.

Once installed, the question was then how to change Ben’s POI Utility custom tags to use the new version of the jars instead of the builtin version. What I did was the following which solved the problem. I should say at the outset that I am not fluent in cf custom tags (I use objects instead) so there might be a more elegant way to acheive this.

First, add the following function to the POI/document.cfm page:

<cffunction name="getJavaClass" returntype="any" hint="" access="public">
	<cfargument name="javaLoader" type="any" required="yes" hint="">
	<cfargument name="className" type="string" required="yes" hint="">
 <cfscript>
	if (isObject(arguments.javaLoader)) {
		return arguments.javaLoader.create(arguments.className);
	} else {
		return CreateObject("java", arguments.className);
	}
</cfscript>
</cffunction>

This function will be used to load the java jars using the javaLoader if it is passed in, or the normal CreateObject if there is no javaLoader passed in.

Then, in poi/document.cfm:
after

		<cfparam
			name="ATTRIBUTES.Style"
			type="string"
			default=""
			/>

add the following attribute and variables declaration:

		<cfparam
			name="ATTRIBUTES.javaLoader"
			type="any"
			default=""
			/>

		 <cfset variables.javaLoader = ATTRIBUTES.javaLoader>

Then, change all occurrances of

CreateObject( "java",
eg
<cfset VARIABLES.WorkBook = CreateObject( "java", "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).Init(

to

getJavaClass(ATTRIBUTES.javaLoader,
eg
<cfset VARIABLES.WorkBook = getJavaClass(ATTRIBUTES.javaLoader, "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).Init(

Now, in poi/cell.cfm, change:

CreateObject( "java", "org.apache.poi.hssf.util.Region" ).Init(

to

VARIABLES.DocumentTag.getJavaClass(VARIABLES.DocumentTag.javaLoader, "org.apache.poi.hssf.util.Region" ).Init(

That fragment uses the function and variables that are located on the document.cfm page.

Finally, when you use the poi:document tag on your page, pass the instance of your javaLoader, eg:

<poi:document
	javaLoader="#variables.javaLoader#"
	name="REQUEST.ExcelData"
	file="#xlsfname#"
	style="font-family: verdana ; font-size: 10pt ; color: black ; white-space: nowrap ;">

After those small changes, the #Value! error went away! Yay!!

Thanks again to Mark, Ben and Leigh for making all this possible in the first place.

About these ads

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