Creating Excel files from Coldfusion

4. January 2007 05:55 by Mrojas in General  //  Tags:   //   Comments (0)
I  found a super interesting blog called Busy ColdFusion Developers' Guide to HSSF Features http://www.d-ross.org/index.cfm?objectid=9C65ECEC-508B-E116-6F8A9F878188D7CA

 
HSSF is part of an open source java project that provides FREE OPEN SOURCE libraries to create Microsoft Office Files like Microsoft Word or Microsoft Excel.

So I was wondering could it be possible with Coldfusion to use those libraries. Specially now that Coldfusion MX relies heavily in Java. So I found this wonderfull blog that explains it all.

 

It just went to the HSSF download site: 

 

http://www.apache.org/dyn/closer.cgi/jakarta/poi/

 

The project that holds these libraries is called POI.

When you enter in those links there is a release directory and a bin directory. I downloaded the zip file and you will find three files like: poi-Version-Date.jar; poi-contrib-Version-Date.jar and poi-scratchpad-Version-Date.jar

 

I downloaded the 2.5.1-final version. I renamed my jars to poi-2.5.1.jar, poi-2.5.1-contrib.jar and poi-2.5.1-scratchpad.jar because my memory cannot hold those complex names for a long time. I installed the library in C:\POI and put them in the Coldfusion Administrator classpath

 

 

 

And then I created a test file like the following:

 

Note: I used a recommendation from Cristial Cantrell http://weblogs.macromedia.com/cantrell/archives/2003/06/using_coldfusio.cfm

 

<cfscript>
    context = getPageContext();
    context.setFlushOutput(false);
    response = context.getResponse().getResponse();
 response.setContentType("application/vnd.ms-excel");
 response.setHeader("Content-Disposition","attachment; filename=unknown.xls");

    out = response.getOutputStream();
</cfscript>


<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset format = wb.createDataFormat()/>
<cfset sheet = wb.createSheet("new sheet")/>
<cfset cellStyle = wb.createCellStyle()/>
<!--- Take formats from: http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html --->
<cfset cellStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("0.00"))/>


<cfloop index = "LoopCount" from = "0" to = "100">
<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(javacast("int",LoopCount))/>

<!---  Create a cell and put a value in it --->
<cfset cell = row.createCell(0)/>
<cfset cell.setCellType( 0)/>
<cfset cell.setCellValue(javacast("int",1))/>

<!--- Or do it on one line. --->
<cfset cell2 = row.createCell(1)/>
<cfset cell2.setCellStyle(cellStyle)/>
<cfset cell2.setCellValue(javacast("double","1.223452345342"))/>
<cfset row.createCell(2).setCellValue("This is a string")/>
<cfset row.createCell(3).setCellValue(true)/>

</cfloop>


<cfset wb.write(out)/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>
<cfset out.flush()/>