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

The Joel Test

I've been a fan of the Joel Test for a while. Its a a quick test you can use to rate the quality of your development team (or a potential employer). If I apply the Joel test to my workplace I get a score of 3 or 4 out of 12. Hmm, obviously a bit of work still to do.

Being a web application developer I was very happy to come accross a great three part series on Drew McLellan's blog talking about applying The Joel Test to a web development setting.

Finding the most recent from multiple related records

I'm currently working on a membership database. In this system people can apply to become members for a specific period and then renew their membership when that period expires. The table holding the memberships looks like this:

CREATE TABLE membership
(
MembershipId INT PRIMARY KEY AUTO_INCREMENT,
MemberId INT,
PeriodId INT,
created DATE,
FOREIGN KEY MemberId REFERENCES members
FOREIGN KEY PeriodId REFERENCES periods
)

There are also related tables to hold the member's details and the membership periods.

One of the things that the system needs to do is to display a list of members and their latest membership. My first attempt looked like this:

SELECT *
FROM membership
WHERE MembershipId IN
(
SELECT MAX(MembershipId)
FROM membership
GROUP BY MemberId
)

Whilst this works its not ideal. In the membership table the primary key really should be a composite of the MemberId and PeriodId fields. Finding the latest membership period would then require a correlated subquery based on the maximum creation date. After a bit of searching on Google I ended up with this:

SELECT *
FROM membership
WHERE membership.created =
(
SELECT MAX(membership2.created)
FROM membership AS membership2
WHERE membership2.MemberId = membership.MemberId
)