I haven't really seen a lot of documentation on how to read an XLS file into Coldfusion. Luckily, one of my colleagues was able to point me in the right direction. I am now here to write up a blog post for future reference.

Once you set up the new datasource in the CF Administrator (see comments below), it's really simple (at least more simple than parsing through the other file formats that I've had to do far). Basically, we just read it in as a query:

view plain print about
1<cfquery name="qImportFile" datasource="dynamicXLS">
2        SELECT *
3        FROM [sheet1$]
4        IN '#ExpandPath("some_xls_file.xls")#' 'EXCEL 5.0;'
5    </cfquery>

Note the only real differences from a typical query are: the datasource and the FROM .. IN portion of the code. Also, you can pick and choose specific columns using their column header names from the XLS file in the SELECT clause. You can also add a WHERE clause after the end of the IN statement using those same column headers.

Isaac also had a function that he created to take care of special characters in the header names, but I'm going to leave that for another day (my fake file uses nicely formatted header names w/o spaces or special characters, he he).

And you would just utilize this in the same way that you would any query object. This includes doing a QoQ with it. I was able to union the query from my file with a query that I ran on a table in our database to find possible duplicates (if you do this, remember that QoQ is case sensitive).

When doing a little last minute research for this blog entry, I noticed that CF9 is going to have a new tag called CFSPREADSHEET. I guess not only will you be able to do the same thing that I did here but you'll be able to read it in as other formats (including HTML and CSV), write a single sheet to a new XLS file and add new sheets to existing XLS files.

'Til next time, Bridget

This entry was posted on September 9, 2009 at 8:39 AM and has received 1488 views. There are currently 3 comments. Print this entry.