Write a Excel file with Query output

				
//**************************************
// Name: Write a Excel file with Query output
// Description:If you are like me, my bosses all ask for reports. This is how I give them the reports. I use an advanced query to pull the appropriate data then write it all in an excel spread sheet.
This uses the CFFILE tag to write the spread sheet file. Granted it comes out as a CSV file, but Excel will open it. If your host has disallowed CFFILE, check out my other post titled "HTML to EXCEL"
// By: Tim Garver
//
// Inputs:All the colums from your query.
//
// Returns:Returns a text CSV file that can be opened in Excel.
//
// Assumes:You must have CFFILE enabled on your server, or this will cause an error.
If you do not have this capability, take a look at my other post titled "HTML to Excel" its much easier..
//
// Side Effects:Make sure you fix your data so that no ","'s are in the data. This will cause your colums to shift over. I use the #REPLACE()# function to rip out commas.
You will also want to surround any numeric fields that start with a leading Zero with single qoutes.
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=27&lngWId=9//for details.//**************************************

<cfquery name="daily" datasource="#mydatasource#" dbtype="ODBC" >
SELECT *
FROM Customers
Where company <> ''
</cfquery>
<!--- make some variables to store the dirctory and file name. --->
<cfset f_dir = "C:\reports\">
<!--- I like to use the date and time for a file name, but you can name it anything you like by changing the value below. --->
<cfset f_name = "#dateformat(now(), 'mmddyy')##timeformat(now(), 'hhmm')#.csv">
<!--- Lets make the file, and put the first row of Column headings in --->
<cffile action="WRITE" file="#f_dir##f_name#"
 output="Company, Sales Area, Sales Person, Site Contact, Contact Phone, Contact Email, Contact Title" addnewline="Yes">
<!--- Now lets loop over the RecordSet and fill in the data --->
<cfloop query="daily">
<cffile action="APPEND" file="#f_dir##f_name#" 
output="#REPLACE(company, ",", "","AlL")#, #region#, #salesperson#, #firstname# #lastname#, #contactphone# #contactphoneext#, #contactsemail#, #contactsTitle#" 
addnewline="Yes">
<!--- End the loop here --->
</cfloop>
<br>
< a href="/reports/#f_name#">Here is the file</a>