Export cfgrid Data or Table Data in Excel, Pdf and CSV Format(ColdFusion - 9)

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.--->

        <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.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:

<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>


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.