News and Views
Friday, January 07, 2005 8:45:31 AM
I'm sure this has been done before, but I wrote a couple of simple ColdFusion custom tags last night that you might find useful. The first is for downloading a CSV file generated from a database, of which I get a lot of code requests for. You pass a recordset to the tag and it will spit out a CSV file download with column headings. It will dynamically adjust to any sql statement with any number of columns.
It came about because I was playing around with a way to loop over a query and dynamically create tables, csv files, and other types of output from a query. My output had to be dynamic (different column names for different types of downloads) so I decided to use array notation to grab the contents of each field dynamically:
<cfquery name="rs" datasource="northwind">
SELECT * FROM Products
<cfloop list="#rs.columnlist#" index="f">
A ColdFusion query result is basically an array of arrays, so in this case instead of using #rs.fieldname#, I use the array of field names and the current row number in the loop.
Use the custom tag like this:
1. Put it in a directory where custom tags are stored. I usually just create a folder called tags and put all my tags inside. Save it as csvfile.cfm.
2. On the page where you want the download, add an import statement to the top of the file:
<cfimport prefix="mytag" taglib="tags">
3. Create your query:
<cfquery name="blah" datasource="#mydsn#">
SELECT field1, field2, etc FROM mytable
4. Put the tag on the page, passing in the results of the query:
If you browse the page, the download should start immediately. I usually put this on a page by itself and link to it, or surround it with a CFIF statement on the calling page.
There are two optional attributes: quotes and filename. The quotes attribute is set to false as default. If you pass true, quotes will be placed around the fields. The filename attribute is set to download.csv by default. If you pass in a filename, it will be the filename that is in the Save prompt:
<mytag:csvfile rs=#blah# quotes=true filename="myNewFilename.csv" >
Alternatively, you can avoid the import statement and use old-style cf_ syntax:
<cf_csvfile rs=#blah# quotes=true filename="myNewFilename.csv" >
The second tag does the same thing, but outputs an HTML table (like the <cftable> tag, only faster). In it's simplist form, call it like this:
If you want sortable column headings, call it like this:
<mytag:table rs="#blah#" sort="true"/>
If you want to supply your own column heading names, call it like this:
<mytag:table rs="#blah#" sort="true" columnnames="First,Last,Address"/>
Before posting comments or trackbacks, please read the posting policy.