Exporting from CFGRID
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.
Comments
I really meantmydata.totalLength
:)
This is great stuff, Ray. I got you something small off your wishlist, not for anything in particular, just as a token of support for all you've done to help everyone with CF8
Thanks. After the day I had (will blog later) it is appreciated.
is it possible to access these properties, and the totalLength mentioned by doug, using a ajaxonload() function or other similar way? i.e. without having to click a button?i have tried to use <cfset ajaxonload('myfunction')>, but it always returns 'undefined' for totalLength... it seems to fire before the grid is fully loaded, since my test alert pop up over an empty grid shell...
also, any ideas on how to re-render a grid on the client's side only, but with different default options for, say, pageSize? that would allow to add a custom 'Show X records per page' control, without hitting the db with every change in X...
Thanks!
how do i get the values of 'name' and 'price' in a selected row using a javascript?like for instance on your doDownload(), instead of generating an exported pdf data, i just would like to get the selected value of 'name' and 'price' and of course the 'id' of the row.
thanks a lot!
Ray,Do you know if there is a similar call in jquery like the CF call [ColdFusion.Grid.getGridObject]? We are using a jquery plugin (jqgrid) and need to dump the query to pdf or excel.
thanks, Jim
You would need to check with the plugin, not jQuery. The plugin should have it's own API.
Hi Ray, is there a way to use this method and print all records in the grid but with the sort parameters specified by the user? It seems like there should be but I can't figure it out.
Can you be more specific? Are you saying it isn't exporting per the current sort?
it's exporting, but only the current page. I want all the pages returned and displayed in a single document, not just the current page.
Then you have to modify my code. My code gets the page and sort and runs a CFM. Your code would not worry about the page. Should be easy enough.
I guess what I'm trying to get at, and I'm sorry I'm kind of new at this, is that since the page and pagesize are required arguments, how do I specify them so that they show all. I can get it to display more records just by bumping up the default numbers for page and pagesize but I can't get it to just show all of them. thanks again.
I may not be getting you still. My code gets the current page and dir from the grid, and sends it to a new CFM. THe last script block on the blog post. All you have to do is NOT use the page and size values. If your method, getData(), requires it, simply modify it to make them optional, or, worse comes to worse, if getData() is used to power the grid, make a new method, getAll() (but that's really poor design though).
The mydata.totalLength gives the length of the grid records size/page.How do i know if the grid is populated or not..in the sense, if the Query returned records or not?
Search my blog for cfgrid - I know I covered this in the past 4 weeks (but a bit too busy to searhc myself).
Is there a way to export cfgrid to Excel either by hard coding or using a plugin or is there a better way to get the data into Excel such as taking the data directly from the DB via the query?
You would just modify my code to output an HTML table. That's the cheesy/simple way to do it. You have to modify the cfcontent/cfheader tag, but that's really it.
Here's a pretty slick solution to export all records to Excel
Just had a look and it's there:
params.totalLength