Querying Excel files in ColdFusion Part II
After penning my first blog entry about Querying Excel files in ColdFusion, a friend pinged and asked if this would work with arbitrary files that users could upload. Since I was using an ODBC datasource that pointed directly to the Excel file, I told him that really wouldn't work. I then remembered that Ben Nadel had created a ColdFusion wrapper for the Apache POI utility that can read and write Excel files.
After discussing this with my friend, I decided to explore the POI Utility myself. It turns out that Ben made things quite easy. The code to get a ColdFusion query of the Excel worksheet with my data is as simple as this:
<cfset strFilePath = ExpandPath( "./rooms.xls" ) />
<!--- grab the second Excel worksheet (use "1" b/c it is zero-based) --->
<cfset RoomSheet = objPOIUtility.ReadExcel(FilePath = strFilePath,HasHeaderRow = true, SheetIndex = 1) />
<!--- get the ColdFusion query object --->
<cfset qExcel=RoomSheet.query>
There are two huge benefits to doing it this way:
- No need for an ODBC connection to the Excel sheet
- Which means I don't have to run this on Windows (I develop on Windows, but my production server is Linux)
There is one downside that I found: the utility creates the ColdFusion query with generic column names rather than the names I used in the spreadsheet. So I have to refer to the fields using "Column1", "Column2", etc. It's a little thing that I can get over, but it would be nicer to use the column headers instead.
One last item to note. When using the ODBC connection, I had to close the Excel file before reading it. With the POI Utility, I can read files that are currently open in Excel. Nice!
Now I don't have to buy that anymore
Thanks
No, it can only read up to Office 2003 format.