This is an expansion on Jim's blog post Create an Excel document from any ColdFusion query object. I had commented a while ago that, "One thing to note is that when you are sending a query that uses a date value, it won't print out right in the excel doc. I had to send my query through a loop and do a DateFormat() update to my date field and then send the modified query to the excel function.". Jamie has since asked how I did that, so I will expand in a new blog entry.

Basically, we want to create a temporary query object in Coldfusion and while transferring the data from the query used on the database we will re-format the fields that need it.

In my example, I am going to use the same basic query as Jim, but with 2 minor tweaks. I am adding a field, for the sake of having a date. Also, I'm going to use the variables' names until the end, because I was having difficulties getting this to work using the user-friendly names.

Here is the query we will use:

view plain print about
1<cfquery name="qEmpDetails" datasource="myDSN" >
2 select
3 emp_id,
4 emp_fname,
5 emp_lname,
6 emp_email,
7 emp_birthday
8 from employees
9 order by emp_lname,emp_fname,emp_id
10</cfquery>

Next, we will handle the meat of this entry - setting up the temporary query object and looping through the old query. Notice how when I set up qTempEmpDetails, the 2nd argument to QueryNew() is a list of the data types for the columns in the first argument. Also, note how I set the birthday field as a varchar as opposed to date. If I was to set it as date, even with the DateFormat(), it will display in timestamp format ({ts '2009-06-10 00:00:00'}).

view plain print about
1<cfset qTempEmpDetails = QueryNew('emp_id,emp_fname,emp_lname,emp_email,emp_birthday','varchar,varchar,varchar,varchar,varchar')>
2<cfloop query="qEmpDetails">
3    <cfscript>
4            queryaddrow( qTempEmpDetails );
5            QuerySetCell( qTempEmpDetails,"emp_id",qEmpDetails.emp_id );
6            QuerySetCell( qTempEmpDetails,"emp_fname",qEmpDetails.emp_fname );
7            QuerySetCell( qTempEmpDetails,"emp_lname",qEmpDetails.emp_lname );
8            QuerySetCell( qTempEmpDetails,"emp_email",qEmpDetails.emp_email );
9            QuerySetCell(qTempEmpDetails,"emp_birthday",DateFormat(qEmpDetails.emp_birthday,"mm/dd/yyyy") );
10            //DateFormat() to change the birthday
11    </cfscript>
12</cfloop>
You can alter any of the fields in any way in this manor (like make a field all upper case, replace a certain character or anything else you can think of).

As we want to have the user friendly column names, I am going to call on an entry by Ben Nadel to change the names of our columns, because apparently a query of queries doesn't like it when I use the [Employee ID] notation.

view plain print about
1<cfscript>
2    columns = qTempEmpDetails.GetColumnNames(); //Grab all the column names for this query
3    columnList = ArrayToList(columns); // turn them into a list
4    
5    for(i=1; i LTE listlen(columnList); i=i+1){ //loop through the list
6        columnName = ListGetAt(columnList,i); //grab the column name that is currently used
7        switch(columnName){ // use a switch statement to set the appropriate user friendly column name
8            case 'emp_id':
9                columns[i] = 'Employee ID';
10                break;
11            case 'emp_fname':
12                columns[i] = 'Employee First Name';
13                break;
14            case 'emp_lname':
15                columns[i] = 'Employee Last Name';
16                break;
17            case 'emp_email':
18                columns[i] = 'Employee Email';
19                break;
20            case 'emp_birthday':
21                columns[i] = 'Employee Birthday';
22                break;
23            default: //use a default of the original column name so we don't have any columns without names.
24                columns[i] = columnName;
25                break;
26                
27        };
28    }
29    qTempEmpDetails.SetColumnNames(columns);    
30</cfscript>

Lastly, we'll resend this query to Jim's excel function:

view plain print about
1<cfscript>
2 // Note my components are stored in the folder /shared/components;
3 // The component name is 'Utils.cfc'
4 utilsObj = createObject('component', 'shared.components.Utils');
5 utilsObj.queryToExcel(qTempEmpDetails,'employee_details');
6</cfscript>

Well, the first part is pretty straightforward. We just loop through a query and build a temporary query with the newly formatted data. The tough part was getting the column names to be user friendly again. Does anyone know if there is a better way to accomplish this when working with a query that we have built in Coldfusion?

Also, thanks Jaime for giving me this to run with in a blog entry!

'Til next time, Bridget

This entry was posted on June 10, 2009 at 12:43 PM and has received 1066 views. There are currently 7 comments. Print this entry.