Contact Us

 Admin User     Feb,08 2017

Export Only Current Page of CFGRID to Excel

If you want to export only the current page of your CFGRID to Excel with also the gridsortcolumn and the gridsortdirection, then let's take a look at the solution below:

First of all, 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" DEFAULT="1"><CFARGUMENT NAME="pageSize" DEFAULT="30"><CFARGUMENT NAME="gridsortcolumn" DEFAULT="departmentName"><CFARGUMENT NAME="gridstartdirection" DEFAULT="asc"><cfset var qList = ""><cfquery name="qList" datasource="myDatabase">SELECT *FROM DepartmentORDER BY#arguments.gridsortcolumn##arguments.gridstartdirection#</cfquery><cfreturn queryConvertForGrid(q, arguments.page, arguments.size)></cffunction>

Now let's take a look at my form:

<cfform name="frmDepartment"><cfgrid name="departmentGrid" format="html" pageSize="10" stripeRows="true"bind="cfc:test.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"><cfgridcolumn name="departmentName" header="Department Name"><cfgridcolumn name="departmentType" header="Department Type"></cfgrid><cfinput type="button" name="btnExport" value="Export To Excel" onClick="ExportToExcel()"></cfform>

Ok - so far nothing special, Only I did add a 'Export To Excel' button that runs a JavaScript function named ExportToExcel
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.
There was no API for the Grid (over at Ext's docs) that specifically returned the current sort or page.
So let's take a look at the complete function and I'll explain what each line is doing.

<script>function ExportToExcel() {var mygrid = ColdFusion.Grid.getGridObject('departmentGrid');var mydata = mygrid.getDataSource(); //For Coldfusion 8//var mydata = mygrid.getStore(); //For Coldfusion 9var params = mydata.lastOptions.params;if(params.sort){var sort = params.sort;}else{var sort = 'departmentName';}var dir = params.dir;var page = params.start/params.limit+1;var pageSize = params.limit;var totalRecord = mydata.totalLength;window.open('Export_department.cfm?page='+page+'&sort='+sort+'&dir='+dir+'&pagesize='+pageSize);}</script>

The first thing I have use the ColdFusion.Grid.getGridObject function to get the Ext grid object. Then I get the DataSource object for the grid. Within this object there was a 'lastOptions.params' key that stored all the values I needed. This includes the sort which will give the value of gridsortcolumn and dir which is the sort direction, and a start and limit value that lets me get the current page no. So I just pass the all values in url to the Export_department.cfm.

Edited On : 25th March

Now Let see the content of Export_department.cfm Page.

<cfinvoke component="cfc.test" method="getData" returnvariable="DeptList"><cfinvokeargument name="page" value="#url.page#"><cfinvokeargument name="pageSize" value="#url.pageSize#"><cfinvokeargument name="gridsortcolumn" value="#url.sort#"><cfinvokeargument name="gridstartdirection" value="#url.dir#"></cfinvoke><cfset qDeptList = DeptList.query><CFOUTPUT><cfsavecontent variable="exportList"><style>br {mso-data-placement:same-cell;}</style><table border="1" cellpadding="2" cellspacing="0" width="100%"><tr><td align="left">Department Name</td><td align="left">Department Type</td></tr><cfif qDeptList.recordcount GT 0><cfloop query="qDeptList"><cfif val(qDeptList.DepartmentID) GT 0><tr valign="top"><td>#qDeptList.departmentName#</td><td>#qDeptList.departmentType#</td></tr></cfif></cfloop><cfelse><tr><td align="center" colspan="2">Record not found</td></tr></cfif></table></cfsavecontent></cfoutput><CFSET ExcelFileName = "#DateFormat(Now(), 'mmddyyyy')#.xls"><CFCONTENT TYPE="application/msexcel"><CFHEADER NAME="Content-Disposition" VALUE="filename=Department_List_#ExcelFileName#"><CFOUTPUT>#exportList#</CFOUTPUT>