ColdFusion Custom Tag - Converts database query object to XLS/XLSX Excel file w/optional formatting

Excel.cfm

<CFSETTING ENABLECFOUTPUTONLY="YES">
<!---
Excel.cfm
	ColdFusion Custom Tag - Converts database query object to
	XLS/XLSX Excel file w/optional formatting
	(Based on a defunct CFX_Excel tag that we used with ColdFusion 5)
Author: James Moberg james at ssmedia dot com
Requirements: ColdFusionF 9+

	<CF_Excel query="#GetResults#" filename="Products_#DateFormat(Now(),'yyyymmdd')#.xls">

	<CF_Excel Query="#GetResults#"
	    	directory="#FDir#"
	    	filename="#Fname#.xls"
	    	DateFields="TravelDate,DateCheckOut,DateReported,DateVerified"
	    	ColumnsToFormatAsText="VID"
		Download="false">
--->
<cfparam name="Attributes.Directory" default="#GetTempDirectory()#">
<cfparam name="Attributes.Download" default="true">
<cfparam name="Attributes.Query" default="">
<cfparam name="Attributes.DateFields" default="">
<cfparam name="Attributes.ColumnsToFormatAsText" default="">
<cfparam name="Attributes.filename" default="Export_#GetTickCount()#.xls">
<cfparam name="Attributes.FreezeRows" default="">
<cfparam name="Attributes.HighlightColNames" default="">
<cfparam name="Attributes.RightBorderColNames" default="">
<cfparam name="Attributes.ColumnWidths" default="#StructNew()#">
<cfparam name="Attributes.ColumnAlignment" default="#StructNew()#">
<cfparam name="Attributes.CellFormat" default="#StructNew()#">
<!--- To generate Excel 2007 XLSX files, use "xlsx" extension in filename --->

<CFIF NOT ISQuery(Attributes.Query)><CFSETTING ENABLECFOUTPUTONLY="No"><CFEXIT></CFIF>

<CFIF NOT Right(Attributes.Directory, 1) IS "\">
    <CFSET Attributes.Directory = Attributes.Directory & "\">
</CFIF>

<cfscript>
function getQueryColumns(theQuery){
	if (IsQuery(theQuery)) {
		return #arraytoList(theQuery.getMetaData().getColumnLabels())#;
	} else {
		return '';
	}
}
</cfscript>

<CFIF listlast(Attributes.filename,".") IS "xlsx">
	<CFSET sObj = SpreadsheetNew("true")>
<CFELSE>
	<CFSET sObj = SpreadsheetNew()>
</CFIF>
<CFSET ColumnList = getQueryColumns(Attributes.Query)>
<CFSET SpreadsheetAddRow(sObj, ColumnList)>
<CFSET SpreadsheetFormatRow(sObj, {bold=TRUE}, 1)>
<CFSET SpreadsheetAddRows(sObj, Attributes.Query)>

<!--- Set ColumnWidths --->
<CFIF IsStruct(Attributes.ColumnWidths) AND StructCount(Attributes.ColumnWidths)>
	<CFSET Col = 0>
	<CFLOOP LIST="#ColumnList#" INDEX="thisColumn"><CFSET Col = Col + 1>
		<CFIF StructkeyExists(Attributes.ColumnWidths, thisColumn) AND IsSimpleValue(Attributes.ColumnWidths[thisColumn]) AND VAL(Attributes.ColumnWidths[thisColumn])>
			<CFSET SpreadSheetSetColumnWidth(sObj, Col, VAL(Attributes.ColumnWidths[thisColumn]))>
		</CFIF>
	</CFLOOP>
</CFIF>

<!--- Set CellFormat (by column) --->
<CFIF IsStruct(Attributes.CellFormat) AND StructCount(Attributes.CellFormat)>
	<CFSET Col = 0>
	<CFLOOP LIST="#ColumnList#" INDEX="thisColumn"><CFSET Col = Col + 1>
		<CFIF StructkeyExists(Attributes.CellFormat, thisColumn) AND isStruct(Attributes.CellFormat[thisColumn])>
			<CFSET SpreadsheetFormatColumns(sObj, Attributes.CellFormat[thisColumn], Col)>
		</CFIF>
	</CFLOOP>
</CFIF>

<!--- Set RightBorderColNames,  HighlightColNames, ColumnWidths --->
<CFIF LEN(Attributes.RightBorderColNames) OR LEN(Attributes.HighlightColNames) OR (IsStruct(Attributes.ColumnAlignment) AND StructCount(Attributes.ColumnAlignment))>
	<CFSET Col = 0>
	<CFLOOP LIST="#ColumnList#" INDEX="thisColumn"><CFSET Col = Col + 1>
		<CFSET CFormat = StructNew()>
		<CFIF ListFindNocase(Attributes.RightBorderColNames, thisColumn)>
			<CFSET CFormat.rightborder = "thick">
			<CFSET CFormat.rightbordercolor = "black">
		</CFIF>
		<CFIF ListFindNocase(Attributes.HighlightColNames, thisColumn)>
			<CFSET CFormat.fgcolor="yellow">
		</CFIF>
		<CFIF StructKeyExists(Attributes.ColumnAlignment, thisColumn)>
			<CFSET CFormat.alignment = Attributes.ColumnAlignment[thisColumn]>
		</CFIF>
		<CFIF StructCount(CFormat)>
			<CFSET SpreadsheetFormatColumn(sObj, CFormat, Col)>
		</CFIF>
	</CFLOOP>
</CFIF>

<!--- ColumnsToFormatAsText http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text --->
<CFIF LEN(Attributes.ColumnsToFormatAsText)>
	<CFLOOP LIST="#Attributes.ColumnsToFormatAsText#" INDEX="ColumnName">
		<CFSET ThisColumn = ListFind(ColumnList, ColumnName)>
		<CFIF ThisColumn>
			<CFSET SpreadSheetFormatColumn(sObj, {dataformat="text"}, ThisColumn)>
			<CFQUERY NAME="ThisTable" DBTYPE="query">SELECT #ColumnName# AS TheColumn FROM Attributes.Query</CFQUERY>
			<CFLOOP QUERY="ThisTable">
				<CFSET SpreadsheetSetCellValue(sObj, TheColumn, CurrentRow+1, ThisColumn)>
			</CFLOOP>
		</CFIF>
	</CFLOOP>
</CFIF>


<!--- DateFields http://www.bennadel.com/blog/308-Ask-Ben-Finding-The-SQL-Data-Type-Of-A-ColdFusion-Query-Column.htm --->
<CFSET DateFields = ArrayNew(1)>
	<CFTRY>
		<CFLOOP ARRAY="#getMetaData(GetLists)#" INDEX="this"><CFIF This.TypeName IS "DATE">
			<CFSET ArrayAppend(DateFields, This.Name)>
		</CFIF></CFLOOP>
	<CFCATCH></CFCATCH>
	</CFTRY>
<CFSET DateFields = ArrayToList(DateFields)>


<!--- FreezeRows, allow col/row to be specified --->
<CFIF LEN(Attributes.FreezeRows) AND ListLen(Attributes.FreezeRows) IS 2>
	<CFSET SpreadSheetAddFreezePane(sObj, ListFirst(Attributes.FreezeRows), ListLast(Attributes.FreezeRows))>
<CFELSEIF YesNoFormat(Attributes.FreezeRows)>
	<CFSET SpreadSheetAddFreezePane(sObj, 1, 1)>
</CFIF>

<!--- Always save to HD first --->
<cfspreadsheet action="write" filename="#Attributes.Directory##Attributes.filename#" name="sObj" overwrite="true">

<CFIF YesNoFormat(Attributes.Download)>
	<CFIF CGI.Server_Port IS 80>
		<CFSET LastModified = DateFormat(Now(), "dd mmm yyyy") & " " & TimeFormat(Now(), "hh:mm:ss") & " GMT-5">
		<CFHEADER NAME="Pragma" VALUE="no-cache">
		<CFHEADER NAME="Cache-Control" VALUE="no-cache, must-revalidate">
		<CFHEADER NAME="Last-Modified" VALUE="#LastModified#">
		<CFHEADER NAME="Expires" VALUE="Mon, 26 Jul 1997 05:00:00 EST">
	</CFIF>
	<cfheader name="content-disposition" value="attachment; filename=#Attributes.filename#">
	<cfcontent type="application/unknown" file="#Attributes.Directory##Attributes.filename#" deletefile="yes">
	<CFABORT>
</CFIF>
<CFSETTING ENABLECFOUTPUTONLY="No">