Sunday, August 3, 2008

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;
// read in the image file
fis = new FileInputStream( path);
bos = new ByteArrayOutputStream( );
int c;
// copy the image bytes into the ByteArrayOutputStream
while ( (c = != -1)
bos.write( c );

// add the image bytes to the workbook
pictureIndex = wb.addPicture( bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG );
if (fis != null)
if (bos != null)
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()>

Local.index = 0;
Local.errorMessage = "";

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

Local.c =;
while ( Local.c neq -1 )
Local.bao.write( Local.c );
Local.c =;

// Note: Assumes image is PNG format. Adapt if needed
Local.index = arguments.workbook.addPicture( Local.bao.toByteArray(),
catch (Any e) {
Local.errorMessage = e.Message &" "& e.Detail;
// always close the input stream
if ( structKeyExists(Local, "fis") ) {

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

<cfreturn Local.index >

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>
// 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:
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", "").init( pathToOutFile );
book.write( outFile );


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>
<!--- JPEG format --->
<cfcase value="JPG,JPEG">
<cfset type = arguments.workbook.PICTURE_TYPE_JPEG>
<!--- Device independant bitmap --->
<cfcase value="BMP,DIB">
<cfset type = arguments.workbook.PICTURE_TYPE_DIB>
<!--- Mac PICT format --->
<cfcase value="EMF">
<cfset type = arguments.workbook.PICTURE_TYPE_EMF>
<!--- Mac PICT format --->
<cfcase value="PICT,PCT">
<cfset type = arguments.workbook.PICTURE_TYPE_PICT>
<!--- Windows Meta File --->
<cfcase value="WMF">
<cfset type = arguments.workbook.PICTURE_TYPE_WMF>

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

<cfreturn type>


Anonymous,  August 29, 2008 at 12:45 PM  

What version of POI are you using? I tried this on a CF7 and a CF8 server (they both are using POI 2.5) and they both give a "addPicture method was not found" error.

cfSearching August 29, 2008 at 1:11 PM  

I am using poi-3.1-FINAL-20080629.jar with the javaLoader.cfc. I doubt the code will run on CF8, because I am almost certain addPicture was introduced _after_ 2.5. So you will have to use a newer jar, with the javaLoader.

Erin May 21, 2009 at 11:40 AM  

Thanks for posting this code.

I'm using it to do a bit of a code-hack involving checkboxes on an Excel form. Instead of checking the checkbox (which is impossible with POI, apparently), I'm inserting an image of a checked or unchecked checkbox in place of the checkbox.

Pat IT Dude Blog Just a Web Developer's viewpoint May 11, 2010 at 5:44 AM  

Can one read a spreadsheet with images and data and write it to a pdf or powerpoint? I'm not having any luck getting the images (charts) out of the spreadsheet.

cfSearching May 11, 2010 at 1:27 PM  

Can one read a spreadsheet with images and data and write it to a pdf or powerpoint?

Unfortunately, the last I checked, it was not possible to extract charts with POI. At least not without doing some _very_ low level manipulation on your own. Since they are not stored as standard images ..


  © Blogger templates The Professional Template by 2008

Header image adapted from atomicjeep