Updating a Query Field, while Retaining a Query Object
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:
select
emp_id,
emp_fname,
emp_lname,
emp_email,
emp_birthday
from employees
order by emp_lname,emp_fname,emp_id
</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'}).
<cfloop query="qEmpDetails">
<cfscript>
queryaddrow( qTempEmpDetails );
QuerySetCell( qTempEmpDetails,"emp_id",qEmpDetails.emp_id );
QuerySetCell( qTempEmpDetails,"emp_fname",qEmpDetails.emp_fname );
QuerySetCell( qTempEmpDetails,"emp_lname",qEmpDetails.emp_lname );
QuerySetCell( qTempEmpDetails,"emp_email",qEmpDetails.emp_email );
QuerySetCell(qTempEmpDetails,"emp_birthday",DateFormat(qEmpDetails.emp_birthday,"mm/dd/yyyy") );
//DateFormat() to change the birthday
</cfscript>
</cfloop>
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.
columns = qTempEmpDetails.GetColumnNames(); //Grab all the column names for this query
columnList = ArrayToList(columns); // turn them into a list
for(i=1; i LTE listlen(columnList); i=i+1){ //loop through the list
columnName = ListGetAt(columnList,i); //grab the column name that is currently used
switch(columnName){ // use a switch statement to set the appropriate user friendly column name
case 'emp_id':
columns[i] = 'Employee ID';
break;
case 'emp_fname':
columns[i] = 'Employee First Name';
break;
case 'emp_lname':
columns[i] = 'Employee Last Name';
break;
case 'emp_email':
columns[i] = 'Employee Email';
break;
case 'emp_birthday':
columns[i] = 'Employee Birthday';
break;
default: //use a default of the original column name so we don't have any columns without names.
columns[i] = columnName;
break;
};
}
qTempEmpDetails.SetColumnNames(columns);
</cfscript>
Lastly, we'll resend this query to Jim's excel function:
// Note my components are stored in the folder /shared/components;
// The component name is 'Utils.cfc'
utilsObj = createObject('component', 'shared.components.Utils');
utilsObj.queryToExcel(qTempEmpDetails,'employee_details');
</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
I was able to resolve my problem dealing with the date formatting issue thanks to your great contribution to this blog website.
I followed your instructions, applied the technique which is well documented, and was able to make it work for my application.
I just posted the updated link to my report for my users and now I will be able to sleep tonight.
Again, I really appreciate your help, thanks!
Query New can be built on the fly and the second argument isn't required (but then you leave it up to CF which does require extra processing power to determine the type). If you post a snippet of your code, I can try and help.
I noticed that when I do this function on a page that has any html on it other than the code to call the function it for some reason includes all the html of the page in the excel file. (Weird huh?!)
I was hoping to call the function on the bottom of a display page of my admin site. Any ideas why the HTML of the page is included in the file that it creates? I does contain the table with the data but also the header of the page and everything else...
I'm not sure what is going on for you - you can try to post your code if you want.
I did a couple of tests and didn't get any of the extra HTML on the page in my excel document that wasn't below my cfcontent tag (or if it was there, it wasn't showing) . Although, the extra HTML didn't show on the browser page either.
I'll bet the html not showing on the browser page is related to the HTML showing up in your excel doc - the cfcontent docs say "Sends the contents of a file, or of a variable that contains binary data, as the page output." - I wonder if "as the page output" means as the only page output, ignoring anything that isn't below the cfcontent tag?
This also coincides w/ my experience, that when errors are thrown while generating the doc, they show up in the excel doc and not on the browser page ... thoughts?
Thanks for the reply. I am using fusebox 3.0 as the framework for my site. I think I discovered the issue. It seems that when I call the code on a page that also contains other HTML it pulls all the html in including the styles and everything into the excel doc. I had a need to have the html display as well as the code to create the file but it appears that is not possible. I created another page that only contains the code and query to create thge excel file and it worked fine. I think it has to do with the code that creates the page header. ( <cfheader name="Content-Disposition" value="inline; filename=#sFileBase#.xls">
<cfcontent type="application/vnd.ms-excel">) I think that makes the page into a file (if you will) and anything else on the page seems to be included.
I appreciate your help and after a few hours of hitting my head on the keyboard if you will, I figured it out.
I tried a few other code modules that I found on the web related to converting a query to excel (all have similar code) with the same results when called from a display page. I'm sure there is a work around with this but for now I am good.
Have a good day,
Kris