Creating Excel files from Coldfusion

 
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()/>