Exporting from CFGRID

September 4, 2007 coldfusion

(This post is more than 2 years old.)

A user asked me today if it was possible to export data from the new HTML-based grid in ColdFusion 8. While there is no direct support, you can roll your own. Let's take a look at one solution to this problem.

The first thing I'll do is share the base code that will power my grid. I have one cfc, test, with a getData method that looks like so:


<cffunction name="getData" access="remote" returnType="struct" output="false">
	<cfargument name="page" type="numeric" required="true">
	<cfargument name="size" type="numeric" required="true">
	<cfargument name="sortcol" type="string" required="true">
	<cfargument name="sortdir" type="string" required="true">
	<cfset var q = "">
<cfquery name="q" datasource="cfartgallery">
select	*
from	art
<cfif len(arguments.sortcol)>
order by #arguments.sortcol#
<cfif len(arguments.sortdir)>
#arguments.sortdir#
</cfif>
</cfif>
</cfquery>

<cfreturn queryConvertForGrid(q, arguments.page, arguments.size)> </cffunction>

There isn't anything special about this method - except for the fact that it was specifically built to handle CFGRID ajax calls. Note the 4 attributes and the queryConvertForGrid used in the return. Now let's take a look at my form:


<cfform name="myform">
<cfgrid name="reportsGrid" format="html" pageSize="10" stripeRows="true"          
		bind="cfc:test.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
>
	<cfgridcolumn name="artname" header="Name">
	<cfgridcolumn name="price">
</cfgrid>
<cfinput type="button" name="Download" value="Download" onClick="doDownload()">
</cfform>

Ok - so far nothing special, but I did add a download button that runs a JavaScript function named doDownload.

At this point I figured out what solution I would use. I would simply look at the grid - get the current sort column, sort direction, and page, and make a call to a page that would run the same call my grid was. Turns out though that was harder said then done. Believe it or not, there was no API for the Grid (over at Ext's docs) that specifically returned the current sort or page. My buddy Todd Sharp did some digging and found that properties existed for these values, you just had to digg a bit. So let's take a look at the complete function and I'll explain what each line is doing.


<script>
function doDownload() {
	var mygrid = ColdFusion.Grid.getGridObject('reportsGrid');
	var mydata = mygrid.getDataSource();
	var params = mydata.lastOptions.params;
	var sort = params.sort;
	var dir = params.dir;
	page = params.start/params.limit+1;
	document.location.href='download.cfm?page='+page+'&sort='+sort+'&dir='+dir+'&size='+params.limit;
}
</script>

The first thing I do is get the Ext grid object using the ColdFusion.Grid.getGridObject function. This is documented in the CF Reference. I then get the DataSource object for the grid. What is that? I'm not quite sure. The API docs mention the function, but don't fully explain what the DataSource is. Todd found that within this object there was a 'lastOptions.params' key that stored all the values I needed. This includes the sort and dir (although the values are undefined if you don't click anything), and a start and limit value that lets me get the current page. At this point I have everything ColdFusion had when it made its Ajax request. So I just duplicate it a bit and push the user to download.cfm. Here is the code for that page:


<cfparam name="url.page" default="1">
<cfparam name="url.limit" default="10">
<cfparam name="url.sort" default="">
<cfparam name="url.dir" default="">
<cfinvoke component="test" method="getData" page="#url.page#"
size="#url.size#" sortcol="#url.sort#" sortdir="#url.dir#"
returnVariable="result">

<cfheader name="Content-Disposition" value="inline; filename=download.pdf"> <cfdocument format="pdf"> <table> <tr> <th>Name</th> <th>Price</th> </tr> <cfoutput query="result.query"> <tr> <td>#artname#</td> <td>#price#</td> </tr> </cfoutput> </table> </cfdocument>

All I've done here is hit the same CFC my grid was hitting. I output the result within cfdocument to create a simple page.