POI Examples - Part II (Drawing Shapes)

So I was happily translating some java examples from the POI Quick Guide, when a blogger named Dave comments that he had already done that ... four years ago! Now while I am not familiar with his site, that did dampen my enthusiasm a bit ;-) Though I cannot say I am surprised. People learn about different topics at varying times and rates. So some things that are new to me, may well be 'old hat' to other developers and vice versa. On the up side, at least someone is learning something new.

Anyway, since POI Quick Guide was updated in the last four years, I decided to post a few of the newer examples. Now, if someone else has already done that too .. oh well. Just file it under the 'more information is better' category and move on to the next blog ;-)

applyFont .. sometime this year?
I did have one problem with the styled text example. The sample code worked without error. However, when I tried to open the file and MS Excel stopped responding. Yegor Kozlov mentioned this may be a bug with HSSFRichTextString.applyFont. So I added the suggested fix to my code which seemed to resolved the problem. All is well that ends well.

Drawing Shapes
Pre-requisites: The following examples use the JavaLoader.cfc. See How to install POI on ColdFusion using the JavaLoader.cfc.

<h1>Drawing Shapes - Simple Shape</h1>
<cfscript>
   pathToOutputFile = "c:/drawingSimpleShape.xls";

   // get a reference to the javaLoader stored in the server scope
   javaLoader = server[ application.myJavaLoaderKey ];
  
   try {
       // create a new workbook and add a sheet
       wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
       sheet = wb.createSheet("new sheet");
      
       // WARNING:: This will clear out any existing shapes for the worksheet.
       patriarch = sheet.createDrawingPatriarch() ;

       // example 1: create a simple shape
       HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
       a = HSSFClientAnchor.init( 0, 0, 1023, 255, 1, 0, 1, 0 );
       shape1 = patriarch.createSimpleShape( a );
       HSSFSimpleShape = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFSimpleShape");
       shape1.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );

       // create an output stream for saving the file
       fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
       // save the workbook to disk
       wb.write( fileOut );
       WriteOutput("Finished!");
   }
   catch (Any e) {
       WriteOutput("ERROR: "& e.message &" "& e.detail);
   }
   // always close the output stream
   if ( structKeyExists(variables, "fileOut") ) {
       fileOut.close();
   }
</cfscript>  


<h1>Drawing Shapes - Simple Textbox</h1>
<cfscript>
   pathToOutputFile = "c:/drawingSimpleTextBox.xls";

   // get a reference to the javaLoader stored in the server scope
   javaLoader = server[ application.myJavaLoaderKey ];
  
   try {
       // create a new workbook and add a sheet
       wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
       sheet = wb.createSheet("new sheet");
      
       // WARNING:: This will clear out any existing shapes for the worksheet.
       patriarch = sheet.createDrawingPatriarch() ;

       //create a text box
       HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
        textbox1 = patriarch.createTextbox( HSSFClientAnchor.init(0,0,0,0,1,1,2,2) );
       HSSFRichTextString = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFRichTextString");
       textbox1.setString( HSSFRichTextString.init("This is a test") );
      
       // create an output stream for saving the file
       fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
       // save the workbook to disk
       wb.write( fileOut );
       WriteOutput("Finished!");
   }
   catch (Any e) {
       WriteOutput("ERROR: "& e.message &" "& e.detail);
   }
   // always close the output stream
   if ( structKeyExists(variables, "fileOut") ) {
       fileOut.close();
   }
  
</cfscript>


<h1>Drawing Shapes - Styled Textbox</h1>
<cfscript>
   pathToOutputFile = "c:/drawingStyledTextBox.xls";

   // get a reference to the javaLoader stored in the server scope
   javaLoader = server[ application.myJavaLoaderKey ];
  
   try {
       // create a new workbook and add a sheet
       wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
       sheet = wb.createSheet("new sheet");
      
       // WARNING:: This will clear out any existing shapes for the worksheet.
       patriarch = sheet.createDrawingPatriarch() ;

       //use different fonts to style parts of the text in the textbox
       HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
        textbox = patriarch.createTextbox( HSSFClientAnchor.init(0,0,0,0,1,1,2,2) );
       font = wb.createFont();
       font.setItalic( true );
       HSSFFont = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFFont");
       font.setUnderline( HSSFFont.U_DOUBLE );
       HSSFRichTextString = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFRichTextString");
       string = HSSFRichTextString.init("Woo!!!");
       // this line is bug fix: http://www.mail-archive.com/user@poi.apache.org/msg01129.html
       string.applyFont( 0 );
       string.applyFont( 2, 5, font );
       textbox.setString( string );

       // create an output stream for saving the file
       fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
       // save the workbook to disk
       wb.write( fileOut );
       WriteOutput("Finished!");
   }
   catch (Any e) {
       WriteOutput("ERROR: "& e.message &" "& e.detail);
   }
   // always close the output stream
   if ( structKeyExists(variables, "fileOut") ) {
       fileOut.close();
   }
  
</cfscript>

<h1>Drawing Shapes - Grouped Shapes</h1>
<cfscript>
   pathToOutputFile = "c:/drawingGroupedShapes.xls";

   // get a reference to the javaLoader stored in the server scope
   javaLoader = server[ application.myJavaLoaderKey ];
  
   try {
       // create a new workbook and add a sheet
       wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
       sheet = wb.createSheet("new sheet");
      
       // WARNING:: This will clear out any existing shapes for the worksheet.
       patriarch = sheet.createDrawingPatriarch() ;

       //example 4: group shapes together
       // WARNING: Any group you create should contain at least two other shapes or subgroups.

       // Create a shape group.
       HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
       HSSFShapeGroup = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFShapeGroup");
       group = patriarch.createGroup( HSSFClientAnchor.init(0,0,900,200, 2,2,2,2) );

       // Create a couple of lines in the group.
       HSSFChildAnchor = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFChildAnchor");
       HSSFSimpleShape = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFSimpleShape");
       shape1 = group.createShape( HSSFChildAnchor.init(3,3,500,500) );
       shape1.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );
       shape1.getAnchor().setAnchor( 3,3,500,500 );
       shape2 = group.createShape( HSSFChildAnchor.init( 1,200,400,600) );
       shape2.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );
      
      

       // create an output stream for saving the file
       fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
       // save the workbook to disk
       wb.write( fileOut );
       WriteOutput("Finished!");
   }
   catch (Any e) {
       WriteOutput("ERROR: "& e.message &" "& e.detail);
   }
   // always close the output stream
   if ( structKeyExists(variables, "fileOut") ) {
       fileOut.close();
   }
  
</cfscript>

...Read More

POI Examples - Part I

Update (02/13/2009): The POI 3.5 beta now supports Excel 2007 format. See also the updated examples for xlsx format.

The POI Quick Guide, on poi.apache.org, has some great introductory examples on using POI. Of course the examples are in java. Since I have seen several POI questions lately, I thought a ColdFusion translation of some of the basics might be helpful for those new to using POI with ColdFusion.

On a side note..
Interestingly, a houseoffusion.com subscriber named Tyler Fitch mentioned there is a version of POI bundled with ColdFusion 8. It might also be bundled with MX7. But I am not certain about that. In any case, this means it is possible to use POI under ColdFusion 8 without installing any new jars.

But before you get too excited, the version that ships with ColdFusion 8 is not the latest. An adobe forum member named BKBK recently pointed out that the built-in jar is version 2.5.1. It is also a partial version only. It does not contain some packages like HWPF (Horrible Word Processor Format). HWPF is used to work with MS Word Files. Still, it is neat to know that you can use POI right out of the box.

If you plan on doing any serious work with POI, you will probably want to use the latest version. In which case you can use the JavaLoader.cfc. It allows you load a newer version of POI without breaking ColdFusion. See How to install POI on ColdFusion using the JavaLoader.cfc


Anyway, here are the first few examples from the Quick Guide. I will add more as I get time.

Source: Apache Busy Developers' Guide to HSSF Features
HSSF Examples:

• New Workbook


• New Sheet


• Creating Cells


• Creating Date Cells


• Working with different types of cells

New Workbook

<h1>New Workbook</h1>
<cfscript>
pathToOutputFile = "c:/newWorkbook.xls";

try {
   // create a workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
   // create an output stream for saving the file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   // save the workbook to disk
   wb.write( fileOut );
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>             

New Sheet

<h1>New Sheet</h1>
<cfscript>
pathToOutputFile = "c:/newSheet.xls";

try {
    // create a workbook object
    wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    // add new worksheets
    sheet1 = wb.createSheet("new sheet");
    sheet2 = wb.createSheet("second sheet");
    // create an output stream for saving the file
    fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
    // save the workbook to disk
    wb.write( fileOut );
    WriteOutput("Finished!");
}
catch (Any e) {
    WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
    fileOut.close();
}
</cfscript>

Creating Cells

<h1>Creating Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingCells.xls";

try {
   // create a workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    sheet = wb.createSheet("new sheet");

   // Create a row and put some cells in it. Rows are 0 based.
   row = sheet.createRow( 0 );
   // Create a cell and put a value in it.
   cell = row.createCell( 0 );
   cell.setCellValue( 1 );

   // Or do it on one line.
   row.createCell( 1 ).setCellValue( 1.2 );
   row.createCell( 2 ).setCellValue( "This is a string" );
   row.createCell( 3 ).setCellValue( true );

   // Write the output to a file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   wb.write( fileOut );
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>

Creating Date Cells

<h1>Creating Date Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingDateCells.xls";

try {

   // create a new workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
   // add a new sheet
   sheet = wb.createSheet("new sheet");

   // Create a row and put some cells in it. Rows are 0 based.
   row = sheet.createRow( 0 );

   // Create a reusable date object
   JavaDate = createObject("java", "java.util.Date");

   // Create a cell and put a date value in it.
   // The first cell is not styled as a date.
   cell = row.createCell( 0 );
   cell.setCellValue( JavaDate.init() );

   // we style the second cell as a date (and time).  It is important to
   // create a new cell style from the workbook otherwise you can end up
   // modifying the built in style and effecting not only this cell but other cells.
   cellStyle = wb.createCellStyle();
   HSSFDataFormat = createObject("java", "org.apache.poi.hssf.usermodel.HSSFDataFormat");
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
   cell = row.createCell( 1 );
   cell.setCellValue( JavaDate.init() );
   cell.setCellStyle( cellStyle );

   // Write the output to a file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   wb.write(fileOut);
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>

Working with different types of cells

<h1>Working with different types of cells</h1>                
<cfscript>
pathToOutputFile = "c:/differentTypesOfCells.xls";

try {
    // create a workbook object
    wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    // create a new worksheet and add a row
    sheet = wb.createSheet("new sheet");
    row = sheet.createRow( 2 );

    // create different types of cells
    HSSFCell = createObject("java", "org.apache.poi.hssf.usermodel.HSSFCell");
    JavaDate = createObject("java", "java.util.Date");
    row.createCell( 0 ).setCellValue( 1.1 ); // numeric
    row.createCell( 1 ).setCellValue( JavaDate.init() ); // date (not formatted)
    row.createCell( 2 ).setCellValue( "a string" ); // string
    row.createCell( 3 ).setCellValue( true ); // boolean
    row.createCell( 4 ).setCellType( HSSFCell.CELL_TYPE_ERROR );

    // Write the output to a file
    fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
    wb.write(fileOut);
    WriteOutput("Finished!");
}
catch (Any e) {
    WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
    fileOut.close();
}
</cfscript>

...Read More

Adding an image to a worksheet with POI

I saw an interesting post on the adobe forums about POI. A member named MinOP mentioned having trouble adding an image to an Excel file . Here is the problematic section of their code:

<cfset patriarch = newSheet.createDrawingPatriarch()/>
<cfset patriarch.createPicture(
            createObject("java", "org.apache.poi.hssf.usermodel.HSSFClientAnchor").init(),
            loadPicture("asset/images/logo_e2eA.png", workSheet)
        )/>

The code is similar to one of the java examples in the POI Quick Guide: adding images to a worksheet. Now what is confusing about the java example is the use of a method called loadPicture. I remember having this question myself. But could not recall the cause or solution.
So I searched my jar files for loadPicture but found nothing. Eventually I found a thread which explained that loadPicture is a private convenience method contained in one of the example classes. The example classes are located in the org.apache.poi.hssf.usermodel.examples.* package. I checked my jar files. Sure enough the examples package was missing. But even if it were present, the loadPicture method is private. Meaning it cannot be used outside its parent class anyway. At least not without modifying the source code. So that meant I needed to create a ColdFusion function that mimics the loadPicture method.

If you look at the java source code for loadPicture, it is very simple. It reads in an image file then adds the raw bytes of the image to a workbook. As you can see the code uses a FileInputStream object to read the image file. Then the bytes of image are copied into a ByteArrayOutputStream object. Finally, the image bytes are converted to an array and added to the workbook using the HSSFWorkbook.addPicture method.

private static int loadPicture( String path, HSSFWorkbook wb ) throws IOException
{
int pictureIndex;
FileInputStream fis = null;
ByteArrayOutputStream bos = null;
try
{
   // read in the image file
    fis = new FileInputStream( path);
    bos = new ByteArrayOutputStream( );
    int c;
   // copy the image bytes into the ByteArrayOutputStream
    while ( (c = fis.read()) != -1)
        bos.write( c );

   // add the image bytes to the workbook
    pictureIndex = wb.addPicture( bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG );
}
finally
{
    if (fis != null)
        fis.close();
    if (bos != null)
        bos.close();
}
return pictureIndex;
}

So I created a ColdFusion function to do the same thing. It is worth noting the java example assumes the image is a PNG. It should be adapted it to accept other image types as needed.

<cffunction name="loadPicture" access="public" returntype="numeric" output="false">
 <cfargument name="path" type="string"  required="true">
 <cfargument name="workbook" type="any" required="true">
 <cfset var Local = structNew()>

 <cfscript>
     Local.index = 0;
     Local.errorMessage = "";

     try {
           Local.fis = createObject("java", "java.io.FileInputStream").init( arguments.path );
           Local.bao = createObject("java", "java.io.ByteArrayOutputStream").init();

           Local.c = Local.fis.read();
           while ( Local.c neq -1 )
           {
               Local.bao.write( Local.c );
               Local.c = Local.fis.read();
           }
      
         // Note: Assumes image is PNG format. Adapt if needed
           Local.index = arguments.workbook.addPicture(     Local.bao.toByteArray(),
                                                           arguments.workbook.PICTURE_TYPE_PNG
                                                       );
     }
     catch (Any e) {
         Local.errorMessage = e.Message &" "& e.Detail;
     }
     // always close the input stream 
     if ( structKeyExists(Local, "fis") ) {
         Local.fis.close();
     }         
 </cfscript>

 <!--- if an error occurred, rethrow it --->
 <cfif len(Local.errorMessage)>
     <cfthrow message="#Local.errorMessage#" type="UnableToLoadPicture">
 </cfif>

 <cfreturn Local.index >
</cffunction>

Let us try this again ..
Now after examining at the java code again, I realized there is a much simpler method. The bulk of the java code just reads in the bytes of a file. In ColdFusion 8 you could simply use the FileReadBinary() function to do this, or for MX7 use <cffile action="readBinary" ...>. It is possible they may do the same thing behind the scenes. But I like the simplicity of using a single function or tag. So the code for loadPicture was simplified to:

 imageBytes = FileReadBinary( pathToImage );
 pictureIndex = book.addPicture( imageBytes, book.PICTURE_TYPE_PNG );

Here is the complete example below. For convenience I threw together a function called getPOIPictureType. It maps a file extension like "jpg" or "png" to a POI picture type. It is not heavily tested, so no guarantees it is not missing file extensions. Use it at your own risk ;)

Add image to worksheet example
How to install POI on ColdFusion 8 using JavaLoader.cfc

<h1>Adding an image to a worksheet with POI </h1>
<cfscript>
 // get a reference to the javaLoader stored in the server scope
 javaLoader = server[application.myJavaLoaderKey];

 pathToOutFile = "c:\excelFileWithImage.xls";
 pathToImage = expandPath("c:\myImage.JPG");

 //create a new workbook and worksheet
 book = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFWorkbook").init( );
 sheet = book.createSheet("Image Test");

 // extract the image bytes
 // FileReadBinary() is ColdFusion 8 specific. For MX 7 use CFFILE action="readBinary"
 imageBytes = FileReadBinary( pathToImage );

 // add the image bytes to the workbook
 fileExtension = listLast( pathToImage, "." );
 pictureType = getPOIPictureType( fileExtension,  book );
 pictureIndex = book.addPicture( imageBytes, pictureType );

 // WARNING: This will clear out any existing shapes for the worksheet.
 patriarch = sheet.createDrawingPatriarch() ;

 // IMAGE1: add the picture at top left
 anchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
 picture = patriarch.createPicture( anchor.init() , pictureIndex );
 // set anchor to actual width and height
 preferredSize = picture.getPreferredSize();
 picture.setAnchor( preferredSize );

 // IMAGE2: add the picture at specific location. For details see:
 // http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFClientAnchor.html
 anchor = anchor.init( 0, 0, 0, 255, 4, 5, 4, 7);
 pictureIndex = book.addPicture( imageBytes, pictureType );
 picture = patriarch.createPicture( anchor , pictureIndex );
 preferredSize = picture.getPreferredSize();
 picture.setAnchor( preferredSize );


 //write the file to disk
 outFile = createObject("java", "java.io.FileOutputStream").init( pathToOutFile );
 book.write( outFile );
 outFile.close();

 WriteOutput("Done!");
</cfscript>

<!---
 WARNING:  File extension were not heavily tested
--->
<cffunction name="getPOIPictureType" access="public" returntype="numeric" output="false">
 <cfargument name="fileExtension" type="string">
 <cfargument name="workbook" type="any" required="true">

 <cfset var type = "" >
 <cfswitch expression="#arguments.fileExtension#">
     <!--- PNG format --->
     <cfcase value="PNG">
         <cfset type = arguments.workbook.PICTURE_TYPE_PNG>
     </cfcase>
     <!--- JPEG format --->
     <cfcase value="JPG,JPEG">
         <cfset type = arguments.workbook.PICTURE_TYPE_JPEG>
     </cfcase>
     <!--- Device independant bitmap --->
     <cfcase value="BMP,DIB">
         <cfset type = arguments.workbook.PICTURE_TYPE_DIB>
     </cfcase>
     <!--- Mac PICT format --->
     <cfcase value="EMF">
         <cfset type = arguments.workbook.PICTURE_TYPE_EMF>
     </cfcase>
     <!--- Mac PICT format --->
     <cfcase value="PICT,PCT">
         <cfset type = arguments.workbook.PICTURE_TYPE_PICT>
     </cfcase>
     <!--- Windows Meta File --->
     <cfcase value="WMF">
         <cfset type = arguments.workbook.PICTURE_TYPE_WMF>
     </cfcase>

     <cfdefaultcase>
         <cfthrow message="Invalid or unhandled file extension [#arguments.fileExtension#]">
     </cfdefaultcase>
 </cfswitch>

 <cfreturn type>
</cffunction>

...Read More

How to install POI on ColdFusion 8 using JavaLoader.cfc

Update (02/07/2009):

The new POI 3.5 beta also supports ooxml files (xlsx,docx,...). For updated instructions see: How to install the POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc

Update (01/15/2010): Changed server scoped lock to named lock in Application.cfc example

I decided to write up a quick set of instructions for using POI with ColdFusion 8 as a reference. If you are not familiar with the JavaLoader.cfc it is a great tool, written by Mark Mandel, that allows you to dynamically load jar files.


Instructions:
1. Download the latest version of POI from http://poi.apache.org/. Extract the jars into the desired directory.

Example:
I created a directory under the webroot named "poi" and placed the jar files there. You can use a different location. So long as ColdFusion has permission to access whatever directory you select. Please note the jar file names tend to be based on version and date. So your file names may differ.

Jar Locations:
c:\coldfusion8\wwwroot\poi\poi-3.1-FINAL-20080629.jar
c:\coldfusion8\wwwroot\poi\poi-contrib-3.1-FINAL-20080629.jar
c:\coldfusion8\wwwroot\poi\poi-scratchpad-3.1-FINAL-20080629.jar

2. Download and install the JavaLoader.cfc, available at javaloader.riaforge.org

Example:
I installed the JavaLoader under the webroot

JavaLoader Location:
C:\coldfusion8\wwwroot\javaloader\JavaLoader.cfc
C:\coldfusion8\wwwroot\javaloader\JavaProxy.cfc
C:\coldfusion8\wwwroot\javaloader\lib\*.*

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.

Usage

// get a reference to the javaLoader
<cfset javaLoader = server[application.myJavaLoaderKey]>

Application.cfc

<cfcomponent>

<cfset this.name = "POIExamples">
<cfset this.sessionManagement = true>
<cfset this.loginStorage = "session">

<cffunction name="onApplicationStart">
<!--- use a unique hard coded key to store the javaLoader in the server structure ---->
<!--- the xxxx is actually a hardcoded UUID value ---->
<cfset var myJavaLoaderKey = "xxxxxx-xxxxxxxx-xxxxxxxxxx-xxxxxxxxxxx_javaloader">
<cfset var jarPaths = arrayNew(1)>

<!--- if the javaLoader was not created yet --->
<cfif NOT structKeyExists(server, myJavaLoaderKey)>

<!--- these are absolute paths to the POI jar files --->
<cfset arrayAppend( jarPaths, expandPath("./poi-3.1-FINAL-20080629.jar")) >
<cfset arrayAppend( jarPaths, expandPath("./poi-contrib-3.1-FINAL-20080629.jar")) >
<cfset arrayAppend( jarPaths, expandPath("./poi-scratchpad-3.1-FINAL-20080629.jar")) >

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

<!--- store the key in the appliation scope for easy access by other pages --->
<cfset application.myJavaLoaderKey = myJavaLoaderKey>
</cffunction>

</cfcomponent>

...Read More