Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's Blog: Custom tags for CSV file download and sortable tableTom Muck's Blog

News and Views

Custom tags for CSV file download and sortable table

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
</cfquery>

<cfoutput query="rs">
  <cfloop list="#rs.columnlist#" index="f">
    #rs[f][rs.currentrow]#
  </cfloop><br>
</cfoutput>

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
</cfquery>

4. Put the tag on the page, passing in the results of the query:

<mytag:csvfile rs=#blah#>

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" >

I hope you find it useful. View the source here or download it here.

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:

<mytag:table rs="#blah#"/>

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"/>

View the source here or download it here.

Tom

Category tags: Dreamweaver, ColdFusion

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2024 Tom Muck | Dreamweaver Extensions