ColdFusion 8 + JavaLoader: IsInstanceOf (In this intance, NO)

No?

In a previous entry I mentioned the new POI beta supports ooxml files. I was exploring how to extract metadata from the new file format when I ran into a gotcha with ColdFusion's IsInstanceOf function.

Since my code required the latest version of POI, I used the JavaLoader.cfc to create my objects. But whenever I passed those objects into the IsInstanceOf function, it always returned false. At first I thought it was because I was using a different version of POI. But I got the same results when using the POI jar that is built into ColdFusion 8.

After re-checking the documentation, I realized it clearly says java objects must be "created by using the cfobject tag or createObject method". So I took that to mean I could not use it with objects created by the javaLoader.

Now, I do not know how CF's IsInstanceOf function is implemented. But I would assume it is similar to java's instanceof operator and is probably tied to the underlying classloader. As I understand it, createObject and the javaLoader.cfc use different classloaders. So I guess it would make sense that IsInstanceOf would not work with objects created by a different classloader.

Anyway, I just thought I would mention it in case anyone else falls into the same trap.

Are you sure?

Since it seemed I could not use IsInstanceOf, I decided to try java's isInstance method instead. (If you are not familiar with it, it is a method of java.lang.Class) Now, isInstance worked fine for the objects created with the JavaLoader. But I noticed something strange when using it with objects returned by createObject.

When I created an object, but did not instantiate it, java's isInstance method returned false. Only when I instantiated the object first did isInstance return true. Take this example. It creates a POI HSSFWorkbook object. Then checks if the object is an instance of HSSFWorkbook. Since I am comparing the object to itself, of course the result should be "yes". But the result is "no", unless the object is instantiated first.

<!---
 RETURNS "NO" when the object is NOT instantiated
--->
<cfset wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook") />
<cfoutput>
(NOTE instantiated) isInstance = #wb.getClass().isInstance(wb)#<br/>
</cfoutput>

<!---
 RETURNS "YES" when the object IS instantiated
--->
<cfset wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init() />
<cfoutput>
(IS instantiated)  isInstance = #wb.getClass().isInstance(wb)#<br/>
</cfoutput>

I am not sure why this happens. But I guess the lesson learned from this is be careful with your IsInstanceOf and isInstance checks. The answer might surprise you.

...Read More

POI 3.5 beta 4 - Supports Excel 2007 / XLSX Format

I was pleased to discover there is a new beta version of POI that supports the Excel 2007 xlsx format. The beta version introduces a new SS UserModel that supports both HSSF (xls files) and the new XSSF packages (xlsx files).

While I have only tested some of the updated examples in the Busy Developers' Guide to HSSF and XSSF Features, so far I am pleased. The syntax for most basic tasks seems the same. Except you now use the new XSSF classes when working with xlsx files. For example, using the JavaLoader.cfc, you simply change the workbook class:

<cfscript>
...
// XLS workbook
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();

// XLSX workbook
wb = javaLoader.create("org.apache.poi.xssf.usermodel.XSSFWorkbook").init();
</cfscript>

There are also some new helper classes that provide generic ways of performing common tasks with both formats. To open an existing workbook (of either format) you now use the new WorkBookFactory class. It automatically determines the file format, and returns a workbook object of the correct type:

<cfscript>
// Use the JavaLoader.cfc to read in either an XLS or XLSX workbook
WorkbookFactory = javaLoader.create("org.apache.poi.ss.usermodel.WorkbookFactory");
inp = createObject("java", "java.io.FileInputStream").init( pathToFile );
wb = WorkbookFactory.create(inp);
</cfscript>

Another new convenience class is CreationHelper. It is a useful class that lets you generically create objects (like RichTextStrings, HyperLinks, ecetera) . Meaning you can use the same code with both formats. The CreationHelper is accessible from any workbook object. Just call the new method: workbook.getCreationHelper().

<cfscript>
...
createHelper = workbook.getCreationHelper();
textString = createHelper.createRichTextString("this is a richTextString");
</cfscript>

There are obviously some differences in the formats. So I imagine certain areas will need separate code for each format. But there also some improvements in the new XSFF packages. For example, the old way of creating cell comments was a bit "klunky". The new XSFF method is much simpler. As described in the documentation, each Sheet has a list of its comments, and they can be added much like other cell properties.


Testing the Updated Examples
To get familiar with the new version, I tested many of the updated examples in the Busy Developers' Guide. For the most part, the new code worked with either format. Usually, a simple change of file extension and workbook class was all that was needed. However, I did run into a few issues.

To test compatibility, I used the Office 2007 converter to view all xlsx files. So some of the issues may have been due to problems with the converter. Though I would not rule out my own inexperience with the new XSSF packages either ;-)

Examples - Busy Developers' Guide to HSSF and XSSF Features

Code worked with both formats

• How to create a sheet
• How to create cells
• How to create date cells
• Working with different types of cells
• Getting the cell contents
• Aligning cells
• Working with borders
• Fills and color
• Merging cells
• Working with fonts
• Reading and writing
• Create user defined data formats
• Set page numbers on the footer of a sheet
• Shift rows
• Set the zoom magnification for a sheet
• Repeating rows and columns
• TextExtraction (Update 02/07/2009) Using the ExtractorFactory, the same code for both formats
  

Issues/Differences

• How to create a new workbook - Code worked with both formats. Though an error occurred unless one or more sheets was added to the new workbook.

• Text Extraction - Required HSSF and XSSF specific extractor objects

• Custom colors - Required HSSF and XSSF specific color objects

• Use newlines in cells - Code works with both formats. However, "\n" did not seem to work with CF, so #chr(10)# was used instead

• Fit Sheet to One Page - Code works with both formats. But the "Fit to One Page" setting did not work when the XLSX file was opened with the Office 2007 converter

• Set print area for a sheet - Code works for both formats. But "Print Area" did not seem to work for XLS files
  

• Create split and freeze panes - Code works for both formats. But the "pane" was not visible in Sheet 4, when the XLSX file was opened with the Office 2007 converter
  

• How to set cell comments - Required HSSF and XSSF specific code. Also, the Office 2007 converter would not open XLSX files with comments

I am still learning about the new format, so comments and corrections are welcome.

Related Links:

1. How to install the POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc
2. Download the Updated CF examples

...Read More

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>

...Read More