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>