Export cfgrid Data or Table Data in Excel, Pdf and CSV Format(ColdFusion - 9)
Below I have posted the sample code for exporting a table data in Excel/Pdf/CSV format. The code contains the comment line for each of the action. I think it will help you to understand.
<!--- The format to Which you want to Export the Data --->
<cfparam name="url.format" default="csv" />
<cftry>
<cfset request.qryGetData = queryNew("") /><!--- The Query Object. To Which we will Export --->
<cfset request.queryResult = structNew() /><!--- Result set of the Query Object --->
<!--- Query to get data from the specified DSN. This DSN created when the ColdFusion is installed in your machine--->
<cfquery name="request.qryGetData" datasource="cfdocexamples" result="request.queryResult">
SELECT * FROM EMPLOYEES
</cfquery>
<!--- Check the format Requested to download and Prepare the Document Accordingly --->
<cfif url.format EQ "csv">
<!--- Preparing the CSV Document --->
<cfset request.strCSVString = "" />
<!--- Storing the comma separated column name in a string Object and Appending chr(13) and chr(10) for line break and carriage return--->
<cfset request.strCSVString = request.strCSVString & "#request.qryGetData.columnList#" & chr(13) & chr(10) />
<cfset request.rowArray = arrayNew(1) />
<cfloop query="request.qryGetData">
<cfset request.rowArray = arrayNew(1) />
<cfset request.columnCntr = 1 />
<!--- Stroing the row of a query object into an array--->
<cfloop list="#request.qryGetData.columnList#" index="colname">
<cfset request.rowArray[request.columnCntr] = request.qryGetData[colname][request.qryGetData.currentRow]>
<cfset request.columnCntr += 1 />
</cfloop>
<!--- Converting the array into a list. So that the entire row will be converted into a comma separated list--->
<cfset request.strCSVString = request.strCSVString & arrayToList(request.rowArray) & chr(13) & chr(10) />
</cfloop>
<!--- Convert the comma separated string content into a CSV file(download.csv) and this file will be downloaded.--->
<!--- Preparing the Query Object into tabular format --->
<cfsavecontent variable="request.exportContent">
<table>
<tr>
<cfoutput>
<!--- Writting all Column Names as table header--->
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<th>#columnName#</th>
</cfloop>
</cfoutput>
</tr>
<!---Loop over the query object and get the column name dynamically from the result set and fetch the cell
value of table and write it in the string object--->
<cfoutput query="request.qryGetData">
<tr>
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<td>#evaluate("request.qryGetData.#columnName#")#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfsavecontent>
<!--- Convert the tabular string data into an excel document(download.xls)--->
<!--- Preparing the Query Object into tabular format --->
<cfsavecontent variable="request.exportContent">
<table>
<tr>
<cfoutput>
<!--- Writting all Column Names as table header--->
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<th>#columnName#</th>
</cfloop>
</cfoutput>
</tr>
<!---Loop over the query object and get the column name dynamically from the result set and fetch the cell
value of table and write it in the string object--->
<cfoutput query="request.qryGetData">
<tr>
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<td>#evaluate("request.qryGetData.#columnName#")#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfsavecontent>
<!--- Convert the tabular string data into a pdf document(download.pdf)--->
Error in Preparing the Document.
</cfif>
<cfcatch>
Error In Preparing the Document.
</cfcatch>
</cftry>
By using the above code we can export a Query Object Data into different format.
Then how we will export cfgrid data with a particular page no??
Ans:
Just you have to pass two another parameter in the URL. i.e
<cfparam name="url.pageNo" default="1" />
<cfparam name="url.pageCount" default="100" />
Now, when we are looping over the query object for creating CSV/Tabular string we have to set the start and end value. Like the below:
Hope you will enjoy with the code. :)
Other Grid related topics:
1. Starting with CFGRID( part - 1 )
2. Starting with CFGRID( part - 2 )(Auto Refreshing CFGRID)
3. Conditionally Change the Color Of a Cell Text In cfgrid
4. Search Functionality in CFGRID
5. Export Query Object in Spreadsheet using spreadsheet object.
<cfparam name="url.format" default="csv" />
<cftry>
<cfset request.qryGetData = queryNew("") /><!--- The Query Object. To Which we will Export --->
<cfset request.queryResult = structNew() /><!--- Result set of the Query Object --->
<!--- Query to get data from the specified DSN. This DSN created when the ColdFusion is installed in your machine--->
<cfquery name="request.qryGetData" datasource="cfdocexamples" result="request.queryResult">
SELECT * FROM EMPLOYEES
</cfquery>
<!--- Check the format Requested to download and Prepare the Document Accordingly --->
<cfif url.format EQ "csv">
<!--- Preparing the CSV Document --->
<cfset request.strCSVString = "" />
<!--- Storing the comma separated column name in a string Object and Appending chr(13) and chr(10) for line break and carriage return--->
<cfset request.strCSVString = request.strCSVString & "#request.qryGetData.columnList#" & chr(13) & chr(10) />
<cfset request.rowArray = arrayNew(1) />
<cfloop query="request.qryGetData">
<cfset request.rowArray = arrayNew(1) />
<cfset request.columnCntr = 1 />
<!--- Stroing the row of a query object into an array--->
<cfloop list="#request.qryGetData.columnList#" index="colname">
<cfset request.rowArray[request.columnCntr] = request.qryGetData[colname][request.qryGetData.currentRow]>
<cfset request.columnCntr += 1 />
</cfloop>
<!--- Converting the array into a list. So that the entire row will be converted into a comma separated list--->
<cfset request.strCSVString = request.strCSVString & arrayToList(request.rowArray) & chr(13) & chr(10) />
</cfloop>
<!--- Convert the comma separated string content into a CSV file(download.csv) and this file will be downloaded.--->
<cfcontent type="application/csv">
<cfheader name="content-disposition" value="inline; filename=download.csv">
<cfoutput>#request.strCSVString#</cfoutput>
<cfelseif url.format EQ "excel"><!--- Preparing the Query Object into tabular format --->
<cfsavecontent variable="request.exportContent">
<table>
<tr>
<cfoutput>
<!--- Writting all Column Names as table header--->
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<th>#columnName#</th>
</cfloop>
</cfoutput>
</tr>
<!---Loop over the query object and get the column name dynamically from the result set and fetch the cell
value of table and write it in the string object--->
<cfoutput query="request.qryGetData">
<tr>
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<td>#evaluate("request.qryGetData.#columnName#")#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfsavecontent>
<!--- Convert the tabular string data into an excel document(download.xls)--->
<cfcontent type="application/msexcel">
<cfheader name="content-disposition" value="inline; filename=download.xls">
<cfoutput>#request.exportcontent#</cfoutput>
<cfelseif url.format EQ "pdf"><!--- Preparing the Query Object into tabular format --->
<cfsavecontent variable="request.exportContent">
<table>
<tr>
<cfoutput>
<!--- Writting all Column Names as table header--->
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<th>#columnName#</th>
</cfloop>
</cfoutput>
</tr>
<!---Loop over the query object and get the column name dynamically from the result set and fetch the cell
value of table and write it in the string object--->
<cfoutput query="request.qryGetData">
<tr>
<cfloop index="columnName" list="#request.queryResult.COLUMNLIST#">
<td>#evaluate("request.qryGetData.#columnName#")#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfsavecontent>
<!--- Convert the tabular string data into a pdf document(download.pdf)--->
<cfheader name="content-disposition" value="inline; filename=download.pdf">
<cfdocument format="pdf">
<cfoutput>#request.exportcontent#</cfoutput>
</cfdocument>
<cfelse>Error in Preparing the Document.
</cfif>
<cfcatch>
Error In Preparing the Document.
</cfcatch>
</cftry>
By using the above code we can export a Query Object Data into different format.
Then how we will export cfgrid data with a particular page no??
Ans:
Just you have to pass two another parameter in the URL. i.e
<cfparam name="url.pageCount" default="100" />
Now, when we are looping over the query object for creating CSV/Tabular string we have to set the start and end value. Like the below:
<cfset request.start = ((url.pageNo - 1) * url.pageCount) + 1 />
<cfset request.end = (request.start - 1) + url.pageCount />
<cfloop
query = "query name" //request.qryGetData
startRow = "row number" // request.start
endRow = "row number" //request.end
</cfloop>query = "query name" //request.qryGetData
startRow = "row number" // request.start
endRow = "row number" //request.end
Hope you will enjoy with the code. :)
Other Grid related topics:
1. Starting with CFGRID( part - 1 )
2. Starting with CFGRID( part - 2 )(Auto Refreshing CFGRID)
3. Conditionally Change the Color Of a Cell Text In cfgrid
4. Search Functionality in CFGRID
5. Export Query Object in Spreadsheet using spreadsheet object.
No comments:
Post a Comment