Here's some sample code that I used to output sales leads to an Excel spreadsheet.
This works best if the user is browsing with Internet Explorer because IE will
automatically launch Excel and populate the spreadsheet with no extra lines whereas if
you simply saved the file, you'd end up with extra lines. By the way, no one who knew
anything about Excel had a problem with the extra lines because they knew they could
sort the records and get rid of them. By the way, there are no indents, extra lines,
or extra spaces in this code for a reason.
---mark
PS - Please don't bitch about the syntax people. I know! It's OLD code. But it
works! Update it to current standards as you wish.
--- begin snippet ---
<CFSETTING ENABLECFOUTPUTONLY="YES">
<cfparam name="total_records" default="0">
<cfif #go# neq "no">
<cfif #total_records# eq "0">
Your search returned no records.
<cfelse>
<CFCONTENT TYPE="text/x-excel-csv">
<CFSETTING ENABLECFOUTPUTONLY="NO">Log Date Lead ID Contact Last Name
Contact First Name Company Name Product Interest Status Latest Update
Lead Owner Lead Owner Company
<CFSETTING ENABLECFOUTPUTONLY="YES">
<cfloop query="get_leads">
<cfif #get_leads.atl_contact_id# neq "0">
<cfquery name="get_lead_owner" datasource="#dsn#">
SELECT FIRST_NAME,LAST_NAME,COMPANY_NAME
FROM CONTACTS_ATL_EMPLOYEES
WHERE ATL_CONTACT_ID = #GET_LEADS.ATL_CONTACT_ID#
</cfquery>
</cfif>
<cfif #get_leads.outside_sales_rep_id# neq "0">
<cfquery name="get_lead_owner" datasource="#dsn#">
SELECT FIRST_NAME,LAST_NAME,COMPANY_NAME
FROM CONTACTS_OUTSIDE_SALES_REPS
WHERE OUTSIDE_SALES_REP_ID = #GET_LEADS.OUTSIDE_SALES_REP_ID#
</cfquery>
</cfif>
<cfif (#get_leads.outside_sales_rep_id# eq "0") AND (#get_leads.atl_contact_id# eq
"0")>
<cfset #lead_owner_first_name# = "None">
<cfset #lead_owner_last_name# = "">
<cfset #lead_owner_company_name# = "None">
<cfelse>
<cfset #lead_owner_first_name# = "#get_lead_owner.first_name#">
<cfset #lead_owner_last_name# = "#get_lead_owner.last_name#">
<cfset #lead_owner_company_name# = "#get_lead_owner.company_name#">
</cfif>
<cfquery name="get_status_bgcolor" datasource="#dsn#">
SELECT DISTINCT STATUS_CODE_BGCOLOR
FROM LEADS_STATUS_CODES
WHERE STATUS_CODE_NAME = '#get_leads.STATUS_NAME#'
</cfquery>
<cfoutput>#DateFormat((get_leads.log_date), "MM/DD/YYYY")#
#NumberFormat(get_leads.Lead_ID, "99999999999")# #get_leads.Last_Name#
#get_leads.First_Name# #get_leads.Company_Name# #get_leads.product_name#
#get_leads.Status_name# #DateFormat((get_leads.log_update_date), "MM/DD/YYYY")#
#lead_owner_first_name# #lead_owner_last_name# #lead_owner_company_name#
</cfoutput>
</cfloop>
</cfif>
</cfif>
---end snippet---
--------------------------------------------------------------
Mark Warrick
Phone: (714) 547-5386
Efax.com Fax: (801) 730-7289
Personal Email: [EMAIL PROTECTED]
Personal URL: http://www.warrick.net
Business Email: [EMAIL PROTECTED]
Business URL: http://www.fusioneers.com
ICQ: 346566
--------------------------------------------------------------
> -----Original Message-----
> From: S.E.Fling [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 29, 2000 5:09 PM
> To: [EMAIL PROTECTED]
> Subject: outputting query results to Excel
>
>
> Hi,
>
> I'd like to write the results of a query to an excel spreadsheet,
> then allow the user to download it. I think I can handle the
> download part, but I'm not sure how to output the query
> results to Excel. Appreciate any and all clues, TIA.
>
> sharon
>
> ------------------------------------------------------------------
> ------------
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
> _talk or send a message to [EMAIL PROTECTED] with
> 'unsubscribe' in the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
