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 <
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>
::
5
comments
Posts
