Posted in ColdFusion | Posted on 09-04-2007 | 9,910 views
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:
2 <cfargument name="page" type="numeric" required="true">
3 <cfargument name="size" type="numeric" required="true">
4 <cfargument name="sortcol" type="string" required="true">
5 <cfargument name="sortdir" type="string" required="true">
6 <cfset var q = "">
7
8 <cfquery name="q" datasource="cfartgallery">
9 select *
10 from art
11 <cfif len(arguments.sortcol)>
12 order by #arguments.sortcol#
13 <cfif len(arguments.sortdir)>
14 #arguments.sortdir#
15 </cfif>
16 </cfif>
17 </cfquery>
18
19 <cfreturn queryConvertForGrid(q, arguments.page, arguments.size)>
20</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:
2<cfgrid name="reportsGrid" format="html" pageSize="10" stripeRows="true"
3 bind="cfc:test.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
4>
5 <cfgridcolumn name="artname" header="Name">
6 <cfgridcolumn name="price">
7</cfgrid>
8<cfinput type="button" name="Download" value="Download" onClick="doDownload()">
9</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.
2function doDownload() {
3 var mygrid = ColdFusion.Grid.getGridObject('reportsGrid');
4 var mydata = mygrid.getDataSource();
5 var params = mydata.lastOptions.params;
6 var sort = params.sort;
7 var dir = params.dir;
8 page = params.start/params.limit+1;
9 document.location.href='download.cfm?page='+page+'&sort='+sort+'&dir='+dir+'&size='+params.limit;
10}
11</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:
2<cfparam name="url.limit" default="10">
3<cfparam name="url.sort" default="">
4<cfparam name="url.dir" default="">
5
6<cfinvoke component="test" method="getData" page="#url.page#"
7 size="#url.size#" sortcol="#url.sort#" sortdir="#url.dir#"
8 returnVariable="result">
9
10<cfheader name="Content-Disposition" value="inline; filename=download.pdf">
11<cfdocument format="pdf">
12<table>
13 <tr>
14 <th>Name</th>
15 <th>Price</th>
16 </tr>
17 <cfoutput query="result.query">
18 <tr>
19 <td>#artname#</td>
20 <td>#price#</td>
21 </tr>
22 </cfoutput>
23</table>
24</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.
Just had a look and it's there:
params.totalLength
mydata.totalLength
:)
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!
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!
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
How do i know if the grid is populated or not..in the sense, if the Query returned records or not?
Updated code:
function doDownloadAll() {
var mygrid = ColdFusion.Grid.getGridObject('reportsGrid');
var mydata = mygrid.getDataSource();
var params = mydata.lastOptions.params;
var sort = params.sort;
var dir = params.dir;
var page = params.start+1;
var size = mygrid.getDataSource().totalLength;
document.location.href='download2.cfm?page='+page+'&sort='+sort+'&dir='+dir+'&size='+size;
}
Update:
document.location.href='download2.cfm?page='+page+'&sort='+sort+'&dir='+dir+'&size='+size+'&filtercolumn='+filtercolumn+'&filter='+filter;
For some reason the following line of code sets the variable params to "undefined",
var params = mydata.lastOptions.params;
The code you suggested prior to that line works just fine. I have a cfgrid and I just want to know if the user sorted the list by some column before exporting to Excel (I want to preserve that sorted cfgrid list in Excel)
"lastOptions.params; " is null or not an object
The code for mygrid = ColdFusion.Grid.getGridObject('InOutStatus'); is used in another function - would that be a cause. When searching on google, this page is the main result. Thanks for your help.
var mydatad = mygridd.getDataSource().totalLength;
Thanks
i have a problem with my cfgrid. I want to get the result of a query over a ajax request. It means, that I push a button and the query runs in the background. Its working but not with cfgrid... I just make this code of the request Site:
<cfoutput>
<cfquery datasource="#data_source#" name="test">
#PreserveSingleQuotes(test)#
</cfquery>
</cfoutput>
<cfform>
<cfgrid name = "FirstGrid" format="Flash"
height="320" width="1200"
font="Tahoma" fontsize="12"
query = "test">
</cfgrid>
</cfform>
Can you get me an answer?
second.cfm. This is the button: (hdnr is one of the entries)
var sUrl = ("#application.pfad_root#internal/second.cfm?hdnr="+document.test.number.value + "&.......);
<button name="btnSearch" type="submit" value="startsql" onClick="startajax('divposition','',sUrl,'#ajaxpicture#');return false;">startSQL</button>
----------------------------------------------------------
second.cfm:
<cfset test = "Select......where hdnr='#hdnr#'">
<cfoutput>
<cfquery datasource="#data_source#" name="test">
#PreserveSingleQuotes(test)#
</cfquery>
</cfoutput>
<cfform>
<cfgrid name = "FirstGrid" format="html"
height="320" width="1200"
font="Tahoma" fontsize="12"
query = "test">
</cfgrid>
</cfform>
-----------------------------------------------------------
These is the function ajax
<cfoutput>
<script>
function getXmlObject() {
var http_request;
if (window.XMLHttpRequest) {
http_request = new XMLHttpRequest();
} else {
if (window.ActiveXObject) {
http_request = new ActiveXObject("Microsoft.XMLHTTP");
}
}
return http_request;
}
function startajax(sDiv, sText, sAufruf, sBild) {
document.getElementById(sDiv).innerHTML = '<span class="Stil6"><img src="' + sBild + '" /> ' + sText + '</span>';
var http_request_dat = getXmlObject();
http_request_dat.onreadystatechange = function() {
try {
if (http_request_dat.readyState == 4) {
if (http_request_dat.status == 200) {
document.getElementById(sDiv).innerHTML = http_request_dat.responseText;
}
}
return true;
} catch(e) {
//nichts
}
};
http_request_dat.open("POST", "#application.http_pfad#" + sAufruf + "#pageVar.URLDefaultParam#");
http_request_dat.setRequestHeader('Content-Type','application/x-www-form-urlencoded; charset=ISO-8859-1');
http_request_dat.send('parameter=value¶meter2=wert2');
}
</script>
</cfoutput>
the ajax js is on the first page. The grid is on the second page because I want only show the grid if somebody click the button.
thanking you in anticipation
Thanks again
<cfoutput>
<cfajaximport tags="cfgrid">
<a href="JavaScript:ColdFusion.Window.create('ImageWindow','Butterfly Image','page2.cfm',{width:450, height:375})">
<h3 style="color:RosyBrown">
<b>click</b>
</h3>
</a>
</cfoutput>
I know, that is not correct:
<a href="JavaScript:ColdFusion.Window.create....
But what is correct?
The JS would look like this:
var column = ColdFusion.getElementValue ('column1');
var filter = ColdFusion.getElementValue ('filter1');
var searchd = ColdFusion.getElementValue ('search1');
document.location.href='export.cfm?page='+page+'&sort='+gdsort+'&dir='+dir+'&size='+params.limit+'&column='+column+'&filter='+filter+'&search='+searchd;
Thanks Ray! Not because you gave me the answer, but because reading all your blogs has helped me learn so much about CF that I was able to figure this out on my own.
- Ron
[Add Comment] [Subscribe to Comments]