Generating a CSV file and forcing download in ColdFusion

The following code uses Ben Nadel's QueryToCSV method to generate a CSV file from query data and force a download using ColdFusion. I'm capturing this here for my own future reference, but it may be useful to others. If you have a better solution please let me know.

<cffunction name="QueryToCSV" access="public" returntype="string" output="false" hint="I take a query and convert it to a comma separated value string."> <cfargument name="Query" type="query" required="true" hint="I am the query being converted to CSV."> <cfargument name="Fields" type="string" required="true" hint="I am the list of query fields to be used when creating the CSV value."> <cfargument name="CreateHeaderRow" type="boolean" required="false" default="true" hint="I flag whether or not to create a row of header values."> <cfargument name="Delimiter" type="string" required="false" default="," hint="I am the field delimiter in the CSV value."> <cfset var LOCAL = {}> <cfset LOCAL.ColumnNames = {}> <cfloop index="LOCAL.ColumnName" list="#ARGUMENTS.Fields#" delimiters=","> <cfset LOCAL.ColumnNames[ StructCount( LOCAL.ColumnNames ) + 1 ] = Trim( LOCAL.ColumnName )> </cfloop> <cfset LOCAL.ColumnCount = StructCount( LOCAL.ColumnNames )> <cfset LOCAL.Buffer = CreateObject( "java", "java.lang.StringBuffer" ).Init()> <cfset LOCAL.NewLine = (Chr( 13 ) & Chr( 10 ))> <cfif ARGUMENTS.CreateHeaderRow> <cfloop index="LOCAL.ColumnIndex" from="1" to="#LOCAL.ColumnCount#" step="1"> <cfset LOCAL.Buffer.Append( JavaCast( "string", """#LOCAL.ColumnNames[ LOCAL.ColumnIndex ]#""" ) )> <cfif (LOCAL.ColumnIndex LT LOCAL.ColumnCount)> <cfset LOCAL.Buffer.Append( JavaCast( "string", ARGUMENTS.Delimiter ) )> <cfelse> <cfset LOCAL.Buffer.Append( JavaCast( "string", LOCAL.NewLine ) )> </cfif> </cfloop> </cfif> <cfloop query="ARGUMENTS.Query"> <cfloop index="LOCAL.ColumnIndex" from="1" to="#LOCAL.ColumnCount#" step="1"> <cfset LOCAL.Buffer.Append( JavaCast( "string", """#ARGUMENTS.Query[ LOCAL.ColumnNames[ LOCAL.ColumnIndex ] ][ ARGUMENTS.Query.CurrentRow ]#""" ) )> <cfif (LOCAL.ColumnIndex LT LOCAL.ColumnCount)> <cfset LOCAL.Buffer.Append( JavaCast( "string", ARGUMENTS.Delimiter ) )> <cfelse> <cfset LOCAL.Buffer.Append( JavaCast( "string", LOCAL.NewLine ) )> </cfif> </cfloop> </cfloop> <cfreturn LOCAL.Buffer.ToString()> </cffunction>  <cfset query = new query()> <cfset result = query.execute( sql="select artist_id, artist_title from Artists" )> <cfset result = result.getResult()>  <cfheader name="Content-disposition" value="attachment;filename=export.csv"> <cfcontent type="text/csv"><cfoutput>#QueryToCSV( result, "id,name,postcode,description" )#</cfoutput>

Comments

Be the first to add a comment!

Xindi CMS

About Me

Wednesday, July 11, 2012 Generating a CSV file and forcing download in ColdFusion

Tags

Share

Comments

Popular Posts

What I've Been Reading