Reading Excel spreadsheet files with ColdFusion

A new project I started working on requires the ability to read values from an Excel file using ColdFusion. The last time I looked into doing this (a long time ago) the answer was to use COM. Sometime soon we will be moving our production server from Windows to Solaris so that's not a great solution. After a bit of searching I came across the Apache Jakarta POI project, a Java API to access Microsoft format files. A little bit more searching revealed that ColdFusion already has this installed. After some trial and error and help from Dave Ross' blog and the API docs I got it to work.

Here's some example code to read a spreadsheet and dump it into an HTML table.

<!--- Get the file --->
<cfset FileIn = createObject("java","java.io.FileInputStream").init("#ExpandPath('myfile.xls')#")/>
<cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(FileIn)/>
<cfset workbook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>
<!--- Get the first sheet --->
<cfset sheet = workbook.getSheetAt(0)/>
<!--- Get an iterator to loop over each row in the sheet --->
<cfset rows = sheet.rowIterator()>
<table border="1">
<!--- Process each row --->
<cfloop condition="#rows.hasNext()#">
 <tr>
 <!--- Get next row --->
 <cfset row = rows.next()>
 <!--- Get an iterator to loop over each cell in the row --->
 <cfset cells = row.cellIterator()>
 <!--- Process each cell --->
 <cfloop condition="#cells.hasNext()#">
  <!--- Get next cell --->
  <cfset cell = cells.next()>
  <!--- Get the cell value's format --->
  <cfset CellType = cell.getCellType()>
  <!--- Get the cell value --->
  <cfset CellValue = "">
  <cfswitch expression="#CellType#">
   <cfcase value="1,3">
    <cfset CellValue = cell.getStringCellValue()>
   </cfcase>
   <cfcase value="0">
    <cfset CellValue = cell.getNumericCellValue()>
   </cfcase>
  </cfswitch>
  <cfoutput><td>#CellValue# </td></cfoutput>
 </cfloop>
 </tr>
</cfloop>
</table>
<cfset FileIn.close()>