How to install the POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc

In a previous entry I wrote about the new POI 3.5 beta that supports ooxml files. Here are some updated instructions on installing POI (3.5 beta 4) on ColdFusion 8 with the JavaLoader.cfc.

You can download a sample Application.cfc file, and other CF + POI samples, using the file downloads widget on the top right menu.

How to install POI (3.5 beta4 or later)

1. Download POI (3.5 beta4 or later) from http://poi.apache.org/. Extract the files to the desired directory.

Example:
I extracted the POI files and copied the entire "poi-3.5-beta4" directory (including subfolders) beneath my webroot. Most of the jars and subfolders are needed to properly use POI. One exception is "\poi-3.5-beta4\docs\", which contains documentation.

Your directory structure should be similar to the list below:

c:\coldfusion8\wwwroot\poi-3.5-beta4\poi-3.5-beta4-20081128.jar
c:\coldfusion8\wwwroot\poi-3.5-beta4\poi-contrib-3.5-beta4-20081128.jar
c:\coldfusion8\wwwroot\poi-3.5-beta4\poi-ooxml-3.5-beta4-20081128.jar
c:\coldfusion8\wwwroot\poi-3.5-beta4\poi-scratchpad-3.5-beta4-20081128.jar
c:\coldfusion8\wwwroot\poi-3.5-beta4\docs\*.* (subdirectory)
c:\coldfusion8\wwwroot\poi-3.5-beta4\lib\*.* (subdirectory)
c:\coldfusion8\wwwroot\poi-3.5-beta4\ooxml-lib\*.* (subdirectory)
c:\coldfusion8\wwwroot\poi-3.5-beta4\LICENSE
c:\coldfusion8\wwwroot\poi-3.5-beta4\NOTICE

2. Download the JavaLoader.cfc, available at javaloader.riaforge.org. Extract the files and copy the entire javaLoader folder to your webroot.

Example:
I copied the entire JavaLoader folder (including subfolders) under the webroot.

C:\coldfusion8\wwwroot\javaloader\JavaLoader.cfc
C:\coldfusion8\wwwroot\javaloader\JavaProxy.cfc
C:\coldfusion8\wwwroot\javaloader\lib\*.* (subdirectory)

3. Instantiate the JavaLoader

Example:
Due to a java bug that causes a memory leak, I store my JavaLoader in the server scope. This is done inside my Application.cfc file. (To read more about the java issue see: Using a Java URLClassLoader in CFMX Can Cause a Memory Leak.)

Below is an example of my Application.cfc file. Since the new version of POI requires several jar files, I am using cfdirectory to generate the array of jar files for the JavaLoader, rather than typing out each path manually. Just update the cfdirectory path (if needed) and replace the #application.myJavaLoaderKey# value with your own UUID. Then you are ready to use the new POI beta.

Update 2009-01-15: Changed server scoped lock to named lock in Application.cfc

Usage

// get a reference to the javaLoader
<cfset javaLoader = server[application.myJavaLoaderKey]>
// create an object
<cfset wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook")>

Application.cfc
Do not forget to update the cfdirectory path (if needed) and replace the #application.myJavaLoaderKey# value with your own UUID.

<cfcomponent>
<cfset this.name = "POIBeta_Examples">
<cfset this.Sessionmanagement = true>
<cfset this.loginstorage = "session">

<cffunction name="onApplicationStart" output="false">

    <cfset var jarPaths = arrayNew(1)>
    <cfset var getJarFiles = "" >
    <cfset var getFilePaths = "" >
    <cfset var filePathList = "">

       <!--- use a unique hard coded key to store the javaLoader in the server structure ---->
       <!--- the xxxx is actually a hardcoded UUID value. replace this value with your own UUID ---->
      <cfset application.myJavaLoaderKey = "xxxxxx-xxxxxxxx-xxxxxxxxxx-xxxxxxxxxxx_javaloader">
 
    <!--- list all jars within the "poi-3.5-beta4" directory (and its subdirectories) --->
    <cfdirectory action="list" directory="#ExpandPath('/poi-3.5-beta4/')#" name="getJarFiles" recurse="true" filter="*.jar">

    <!--- construct the full file paths for all jar files --->
    <cfquery name="getFilePaths" dbtype="query">
        SELECT    Directory +'/'+ Name AS FilePath
        FROM    getJarFiles
    </cfquery>

    <!--- if the javaLoader was not created yet --->
    <cfif NOT structKeyExists(server, application.myJavaLoaderKey)>
        <!--- create an array of jar file paths --->
        <cfset filePathList = replace( valueList(getFilePaths.FilePath, "|"), "\", "/", "all")>
        <cfset jarPaths = listToArray(filePathList, "|")>

        <!---  create an instance of the JavaLoader and store it in the server scope --->
        <cflock name="#Hash(myJavaLoaderKey)#" type="exclusive" timeout="10">
            <!---  re-verify it was not created yet --->
            <cfif NOT structKeyExists(server, application.myJavaLoaderKey)>
                <cfset server[application.myJavaLoaderKey] = createObject("component", "javaloader.JavaLoader").init( jarPaths )>
            </cfif>
        </cflock>
    </cfif>

</cffunction>


</cfcomponent>