Export spreadsheet data using spreadsheet object from a query object in ColdFusion

In one of my previous post for exporting data from cfgrid I had used table formatting method for a spreadsheet file you can see here.
Export cfgrid Data or Table Data in Excel, Pdf and CSV Format(ColdFusion - 9)

But, I found another efficient way to export a query object to excelsheet rather than using that table formatting.

Here, in the above code first I am creating a spread sheet object in ColdFusion then I am adding the required header names for each column then I am adding data into the spreadsheet object using the query object.

What are the Benefits of using this method rather than using table formatting like in my previous posts.

1. In first case we were looping over the query object and creating each row. So, if our query object will contain more records then we will end with looping over the query object. Which is not present here. Here we are creating spreadsheet object from the query object directly.
2. If we will edit the spreadsheet file downloaded in NotePad++( http://notepad-plus-plus.org/ ) then we will find the <table> and <tr> tags but practically a spreadsheet file doesn't contain such data. So, the formatting is wrong. When we will export spreadsheet file using spreadsheet object then we can see the correct format.
3. Another benefit is we can create multiple sheets within a single excel file for different query objects.

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.