Team Ravenglass

Coldfusion, jQuery, Mobile, SQL

Reading an XLS file using CFQUERY

Sep 9

posted by Bridget Maloney in excel, Coldfusion, SQL | 3 Comments Bookmark and Share

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:

[More]

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.

Create an Excel document from any ColdFusion query object

Jan 19

posted by Jim Jurista in excel, Coldfusion, SQL | 4 Comments Bookmark and Share

Here's an issue that has come up many times in my years of working with ColdFusion: a client wants a one-off report in Excel. I created a function that does just that, taking any CFQUERY as input and outputting the results as an Excel file that can be saved or opened locally by the user. Here's the function:

view plain print about
1<cffunction name="queryToExcel" returnType="any" output="true" hint="Given a query and an optional output file name, output the query as an Excel document using the query columns as columns in Excel.">
2&nbsp;&nbsp;&nbsp;&nbsp;<cfargument name="queryToConvert" type="query" required="true" hint="Any valid CF Query Object" />
3&nbsp;&nbsp;&nbsp;&nbsp;<cfargument name="sFileBase" type="string" required="false" default="yourfile" hint="Desired base file name. Defaults to 'yourfile'. Output filename will be the value of this variable with .xls appended." />
4&nbsp;&nbsp;&nbsp;&nbsp;<cfheader name="Content-Disposition" value="inline; filename=#sFileBase#.xls">
5&nbsp;&nbsp;&nbsp;&nbsp;<cfcontent type="application/vnd.ms-excel">
6&nbsp;&nbsp;&nbsp;&nbsp;<table border=1>
7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<tr>
8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<cfloop list="#arrayToList(queryToConvert.getColumnList())#" index="sColName">
9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<th><cfoutput>#sColName#</cfoutput></th>
10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</cfloop>
11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</tr>
12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<cfloop query="queryToConvert">
13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<tr>
14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<cfloop list="#arrayToList(queryToConvert.getColumnList())#" index="sColName">
15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<td><cfoutput>#queryToConvert[sColName][queryToConvert.currentRow]#</cfoutput></td>
16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</cfloop>
17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</tr>
18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</cfloop>
19&nbsp;&nbsp;&nbsp;&nbsp;</table>
20&nbsp;&nbsp;&nbsp;&nbsp;<cfreturn true>
21</cffunction>

Ideally you'll create your query with column names that make sense to the user. Here's an example of an invocation of the above function from a CFM page with an employee query. Our database is SQL Server, so your mileage may vary when creating user-friendly column names.

view plain print about
1<cfquery name="qEmpDetails" datasource="myDSN" >
2&nbsp;&nbsp;&nbsp;select
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;emp_id AS [Employee ID],
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;emp_fname AS [First Name],
5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;emp_lname AS [Last Name],
6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;emp_email AS [Employee Email]
7&nbsp;&nbsp;&nbsp;from employees
8&nbsp;&nbsp;&nbsp;order by emp_lname,emp_fname,emp_id
9</cfquery>
10
11<cfscript>
12&nbsp;&nbsp;&nbsp;&nbsp;// Note my components are stored in the folder /shared/components;
13&nbsp;&nbsp;&nbsp;&nbsp;// The component name is 'Utils.cfc'
14&nbsp;&nbsp;&nbsp;&nbsp;utilsObj = createObject('component', 'shared.components.Utils');
15&nbsp;&nbsp;&nbsp;&nbsp;utilsObj.queryToExcel(qSupDetails,'supplier_details');
16</cfscript>

I hope this helps somebody who needs to do occasional Excel exports. Note that Excel has a limit of 65,536 rows per sheet; a nice exercise would be to adapt this tool to check the number of rows in the query and be more flexible in its output options.

This entry was posted on January 19, 2009 at 12:50 PM and has received 2920 views. There are currently 4 comments. Print this entry.