CODEWITHSUNDEEP
excelcoldfusion
user747291
user747291

Reputation: 821

Download excel sheet query result without writing file coldfusion

I have a simple query whose results I am looking to download into excel on click of a button without writing the file to the disk.A gist of what I am doing. 

<cfquery name="qtest" datasource="xyz">
select a,b,c from table
</cfquery>

<cfspreadsheet action="write" fileName="test.xls" query="the_jobfeed" overwrite=true />
<button>click here </button>

I have figured that its ok to write the file to the disk. Hence I am doing the above. But how do I conserve the query columns case on the excel sheet.

Upvotes: 3

Views: 3303

Answers (2)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Here's the stub of a SpreadSheetService.cfc that will convert a query to an excel file w/o writing it to a web-accessible folder.

The main function, createFromQuery takes 3 parameters:

  1. data (query object)
  2. xlsx (boolean, xls [false, default] or xlsx [true])
  3. fileName (string, uses a UUID if empty.)

This service uses the server's temp directory as the location to create, compile and read the file to the brower. Once the file has been read into memory, the physical file is deleted from the temp directory to ensure that no other process can access it.

The core of the code: 

<cftry>
    <cfspreadsheet action="write" filename="#config.full_temp_name#" query="config.q" />
    <cfspreadsheet action="read" src="#config.full_temp_name#" name="local.xls" />
    <cffile action="delete" file="#config.full_temp_name#" />

    <cfif len(arguments.fileName) GT 0>
        <cfheader name="content-disposition" value="attachment; filename=#arguments.fileName#.#config.extension#" />
    <cfelse>
        <cfheader name="content-disposition" value="attachment; filename=#config.temp_name#.#config.extension#" />
    </cfif>
    <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(local.xls)#" reset="true" />
    <cfcatch type="any">
        <cfdump var="#cfcatch#" output="console" />
    </cfcatch>
</cftry>

Test code: 

<cfset oSS = new SpreadsheetService() />

<cfquery name="testData" datasource="{your_dsn}">
    SELECT TOP 100 * FROM dbo.Some_Table ORDER BY id DESC
</cfquery>

<cfoutput>
    #oSS.createFromQuery(testData, false, "My Safe Excel File")#
</cfoutput>

Upvotes: 5

CFMLBread
CFMLBread

Reputation: 754

I have used variations of this snippet for a while:

<cfset sheet = SpreadSheetNew()>
<cfset SpreadsheetAddRow(sheet, "foo,bar")>

<!--- stream it to the browser --->
<cfheader name="Content-Disposition" value="inline; filename=foo.xls">
<cfcontent type="application/vnd.ms-excel" variable="#SpreadSheetReadBinary(sheet)#">

Upvotes: 3

Related Questions