Export to Excel with Flex 2.0 and ColdFusion
I didn't really find a suitable solution for exporting to Excel after a bit of searching through forums and blogs, so I came up with the following. I wanted to achieve seamless "click to generate a report" from Flex and have the file option dialog popup in the browser allowing me to open or save. Here's what I came up with. While I don't see any issues with the solution, I realize it's a bit of a hack, and would appreciate any CF wizards letting me know if this is going to create problems.
From Flex, I'm calling a CFC function with my data being passed back as an array. The CFC takes the data and stores it to an application variable in CF. I then send a trivial result back to Flex. On the CFC result inside Flex, I make a call to a template CFM file which uses the cfcontent tag to generate the excel file from an html table structure I build with the saved application variable data.
CFC
<cffunction name="exportToExcel" access="remote" returntype="any" output="true">
<cfargument name="data" type="array" required="yes" default="" />
<cfset session.exportData = #ArrayNew(1)#>
<cfreturn 'true'>
</cffunction>
Flex
if(event.result == 'true')
{
var request:URLRequest = new URLRequest(URLToCFTemplate);
flash.net.navigateToURL(request, "_self");
}
Brendan
Updated: Download the source files here - Download
I can´t make it run, i used your codes but is not working. Could you pass me a guid pass to pass?
For you information, i´m getting a follow problem from Cold Fusion: [coldfusion.runtime.ScopeCastException : You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.]
There´s some wrong, i can´t find... I need your help... I´m looking for the solution since yesterday all day and night.
Thanks a lot,
Michel
about 9 years ago I realized that almost all the examples for this from Allaire, MM and adobe and other individual developer were taking the wrong approach to exporting to Excel from CF or a query and developed my own version of doing this. In a nut shell almost all these examples either re-run the same query, have issues with placing the content in one cell, or can't get the headers right. I hadn't used this in a while until last week when applying it to a report.
Because using the columlist option of a query in CF outputs in Alphabetical order the only input to this is an ordered list of the columns.
This was before the days of CFC's so I have since re-written it in a simple cfc.
pass the query and ordered column list.
Note this actually writes to a file i.e. not using cfcontent, but this can be easily achieved.
Just getting into FLEX so it I get time I will try to integrate the two, but all the functionality is in the CFC itself.
Contact me via my email address if you think this would help
<cffunction name="exportToExcel" access="remote" returntype="any" output="true">
<cfargument name="someparameter" type="String" >
<cfargument name="connection_string" type="String">
<cfstoredproc procedure="someprocedure" datasource="#arguments.connection_name#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="vcParameter" value="#arguments.someparameter#">
<cfprocresult name="datasetResults">
</cfstoredproc>
<!--- here is the important part --->
<!--- the header and content will force IE/Firefox to open a download prompt --->
<cfoutput>
<cfheader name="Content-Disposition" value="inline; filename=InvalidCourseCodes.xls">
<cfcontent type="application/x-unknown-content-type">
<cfdump var = "#datasetResults#">
</cfoutput>
</cffunction>
and from the flex side (in actionscript that is)
private function somefunction:void {
fr = new FileReference();
fr.addEventListener(IOErrorEvent.IO_ERROR,ioErrorHandler);
startdownload('http://address.to.your.cfc?method=methodname¶meters','FileToSaveTo.xls');
}
startDownload(sFileURL:String,sFileName:String):void {
var request:URLRequest = new URLRequest();
request.url = s.FileURL;
fr.download(request,sFileName);
}
You will also need an error handler. Anything missing I am sure you can figure it out. I am a 8 week actionscript/Flex developer coming from some technologies I do not even wish to mention..
I have been needing a CFC-based solution to work into my security model and this will do just that...
Thanks, you are a genius...