Skip to content

Instantly share code, notes, and snippets.

@iknowkungfoo
Last active December 9, 2022 09:01
Show Gist options
  • Save iknowkungfoo/1298ef5a65275163c70ad737743c2adc to your computer and use it in GitHub Desktop.
Save iknowkungfoo/1298ef5a65275163c70ad737743c2adc to your computer and use it in GitHub Desktop.
<cfcomponent output="false" displayname="Spreadsheet Service">
<cffunction name="init" access="public" output="false" returntype="SpreadsheetService">
<cfreturn this />
</cffunction>
<cffunction name="createFromQuery" access="public" output="false" returntype="void">
<cfargument name="data" type="query" required="true" />
<cfargument name="xlsx" type="boolean" required="false" default="false" hint="File extension is xlsx (true) or xls (false)." />
<cfargument name="fileName" type="string" required="false" default="" hint="Final file name sent to the browser." />
<cfset var config = {
extension = "xls"
, temp_path = getTempDirectory()
, temp_name = createUUID()
, full_temp_name = ""
, file_name = arguments.fileName
, q = arguments.data
} />
<cfif arguments.xlsx>
<cfset config.extension = "xlsx" />
</cfif>
<cfset config.full_temp_name = config.temp_path & config.temp_name & "." & config.extension />
<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>
</cffunction>
</cfcomponent>
<cfcomponent output="false" displayname="Spreadsheet Service">
<cffunction name="init" access="public" output="false" returntype="SpreadsheetService">
<cfreturn this />
</cffunction>
<cffunction name="createFromQuery" access="public" output="false" returntype="void">
<cfargument name="data" type="query" required="true" />
<cfargument name="columnnames" type="string" required="false" default="" hint="Comma-delimited List.">
<cfargument name="downloadName" type="string" required="false" default="" hint="Final file name sent to the browser." />
<cfargument name="xlsx" type="boolean" required="false" default="false" hint="File extension is xlsx (true) or xls (false)." />
<cfargument name="excludeHeaderRow" type="boolean" required="false" default="false" hint="Show or hide the column header row." />
<cfset var config = {
, filename = getTempDirectory() & createUUID()
, columnnames = arguments.columnnames
, excludeHeaderRow = arguments.excludeHeaderRow
, downloadFilename = arguments.downloadName
} />
<cfif arguments.xlsx>
<cfset config.fullname &= "xlsx">
<cfif len(config.downloadName) GT 0>
<cfset config.downloadFilename &= "xlsx">
</cfif>
<cfelse>
<cfset config.fullname &= "xls">
<cfif len(config.downloadName) GT 0>
<cfset config.downloadFilename &= "xls">
</cfif>
</cfif>
<cfset config.full_temp_name = config.temp_path & config.temp_name & "." & config.extension />
<cftry>
<cfspreadsheet action="write"
filename="#config.filename#"
columnnames="#arguments.columnnames#"
excludeHeaderRow="#arguments.excludeHeaderRow#"
query="#arguments.data#" />
<cfspreadsheet action="read" src="#config.filename#" name="local.xls" />
<cffile action="delete" file="#config.filename#" />
<cfif len(arguments.downloadName) GT 0>
<cfheader name="content-disposition" value="attachment; filename=#config.downloadFilename#" />
<cfelse>
<cfheader name="content-disposition" value="attachment; filename=#config.filename#" />
</cfif>
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(local.xls)#" reset="true" />
<cfcatch type="any">
<cfdump var="#cfcatch#" output="console" />
</cfcatch>
</cftry>
</cffunction>
</cfcomponent>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment