As the list strips attachments:
<!---
NAME: CF_Query2Excel
AUTHOR: Ali Taleb
DATE: 3 March 2000
REV: 4/4/2001 Thomas Chiverton, [EMAIL PROTECTED]
Improved error handerling for blank fields in rows
Takes note of Headers setting and uses that to order columns
DESCRIPTION:
Cold Fusion custom tag to dump the result of an SQL select query
to a downloadable Microsoft Excel spreadsheet which can have alternating row
colors.
USAGE EXAMPLE (1):
<CF_Query2Excel Query="GetEmployees">
USAGE EXAMPLE (2):
<CF_Query2Excel Query="GetEmployees"
Headers="Firt Name, Last Name, Date of Birth"
AlternatColor = "ff0000">
USAGE EXAMPLE (3):
<CF_Query2Excel Query="GetEmployees"
AlternatColor = "green">
Where 'GetEmployees' is the query name.
ATTRIBUTES:
Query - (Required) Name of query to be converted to Excel.
Headers - (Optional) Comma delimited list of coloumn header names.
Defaults to all
query fields.
Message - (Optional) Prompt link message to download the Excel File.
Defaults to
'Download Results to Excel'.
AlternateColor - (Optional) The rows can have alternating colours, white and
a second colour
defined by this attribute. Default is 'White'.
IMPORTANT: - As this tag invokes a download dialogue window (and hence
not allow the actual page
calling it to be displayed), It is best to place it in a
..cfm page containing just
the query followed by the tag, and then linking to that
page with an HTML link.
- The 'AlternateColor' Attribute MUST NOT begin with the
number symbol '#', otherwise
an error will be generated. There is no means of rectifying
this in ColdFusion 4.01.
as the problem is giving the custom tag attribute variable
a value preceeding
with '#', rather than just deleting the symbol which is an
easy thing to do.
As an example, For red you can type: AlternateColor = "red"
or AlternateColor = "ff0000"
but NOT AlternateColor = "#ff0000".
--->
<cfif Not IsDefined("Attributes.Query")>
<cfoutput><div align="center"><b>Error! 'Query' attribute is required
in your custom tag.</b></div></cfoutput>
<cfabort>
</cfif>
<cfset QueryName = "Caller." & #Attributes.Query#>
<cfset NumRows = Evaluate(QueryName & ".RecordCount")>
<cfparam name="Attributes.Headers" default=#Evaluate(QueryName &
".ColumnList")#>
<cfset TheColumnList = #Attributes.Headers#>
<cfif ListLen(Evaluate(QueryName & ".ColumnList")) gt
ListLen(Attributes.Headers)>
<cfoutput><div align="center"><b>Error! There are more query fields
than the items in the 'Headers' list.</cfoutput>
<cfabort>
<cfelseif ListLen(Evaluate(QueryName & ".ColumnList")) gt
ListLen(TheColumnList)>
<cfoutput><div align="center"><b>Error! There are more values in the
'Headers' list than the fields returned by the query.</cfoutput>
<cfabort>
</cfif>
<cfparam name="Attributes.AlternateColor" default="ffffff">
<cfparam name="Attributes.Type" default="application/vnd.ms-excel">
<cfset Counter = 1>
<cfloop query="#QueryName#">
<cfset TheValueList = "">
<cfloop list="#TheColumnList#" index="TheValue">
<cfset TheValue = #Trim(TheValue)#>
<cfset TheValue = #Evaluate(QueryName & "." & TheValue)#>
<cfset TheValue = Replace(TheValue, ",", "��", "All")>
<cfset TheValueList = ListAppend(TheValueList,TheValue)>
<cfset temp = SetVariable("ValueList#Counter#",
"#TheValueList#")>
</cfloop>
<cfset Counter = Counter + 1>
</cfloop>
<cfset AlternateColor = #Attributes.AlternateColor#>
<cfcontent type="#Attributes.Type#">
<cfset CharWidth = 10>
<cfset numcols=0>
<table border="1">
<tr bgcolor="#C0C0C0">
<cfloop list="#Attributes.Headers#" index="TheColTitle">
<cfset TheString = "#TheColTitle#">
<cfset TheLength = Len(#TheString#) *
#charwidth#>
<cfoutput>
<th width="#TheLength#"><b>#TheString#</b></th>
</cfoutput>
<cfset numcols=numcols+1>
</cfloop>
</tr>
<cfset ColorList = "ffffff,#AlternateColor#">
<cfloop from="1" to="#NumRows#" index="n">
<cfif ColorList is "ffffff,#AlternateColor#">
<cfset ColorList = "#AlternateColor#,ffffff">
<cfelse>
<cfset ColorList = "ffffff,#AlternateColor#">
</cfif>
<cfloop list="#ColorList#" index="c">
<cfset CurrentColor = #ListGetAt(c,1)#>
</cfloop>
<cfset temp = SetVariable("CurrentList",
Evaluate("ValueList#n#"))>
<cfset CurrentList = Replace(CurrentList, ",,", ", ,",
"All")>
<cfset CurrentList = Replace(CurrentList, ",,", ", ,",
"All")>
<cfoutput>
<tr bgcolor="#CurrentColor#">
</cfoutput>
<cfset num=0>
<cfloop list="#CurrentList#" index="TheColValue">
<cfset num=num+1>
<cfset TheString "#TheColValue#">
<cfset TheLength = Len(#TheString#) *
#charwidth#>
<cfif Len(TheColValue) is 0>
<cfset TheString = " ">
</cfif>
<cfset TheString = Replace(TheString, "��", ",", "All")>
<cfoutput>
<td width="#TheLength#">#TheString#</td>
</cfoutput>
</cfloop>
<cfif #Evaluate(numcols - num)# neq 0>
<cfloop from="1" to ="#Evaluate(numcols - num)#"
index="i">
<td> </td>
</cfloop>
</cfif>
</tr>
</cfloop>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
