Wednesday, April 08, 2009

Nice Flex export to Excel ActionScript file

THE ACTION SCRIPT
// ActionScript file
      import mx.controls.Alert;
      import mx.core.UIComponent;
      import mx.core.Container;
      import mx.events.ItemClickEvent;
      import mx.utils.ObjectProxy;
      import flash.errors.*;
      import flash.events.*;
     

import flash.external.*;           
      import flash.net.URLLoader;
      import flash.net.URLVariables;
      import flash.net.URLRequest;
      import mx.controls.DataGrid;


       
        //The location of the excel export file
        public var urlExcelExport:String = "./upload/excelexport.cfm";
 


        private function convertDGToHTMLTable(dg:DataGrid):String {
            //Set default values
            var font:String = dg.getStyle('fontFamily');
            var size:String = dg.getStyle('fontSize');


            var str:String = '';
            var colors:String = '';
            var style:String = 'style="font-family:'+font+';font-size:'+size+'pt;"';                       
            var hcolor:Array;
           
            //Retrieve the headercolor
            if(dg.getStyle("headerColor") != undefined) {
                  hcolor = [dg.getStyle("headerColor")];
            } else {
                  hcolor = dg.getStyle("headerColors");
            }                      
           
            //Set the htmltabel
            str+= '<table border="1" width="'+dg.width+'"><thead><tr width="'+dg.width+'">';
           
            //Set the tableheader data (retrieves information from the datagrid header                     
            for(var i:int = 0;i<dg.columns.length;i++) {
                  colors = dg.getStyle("themeColor");
                 
                  if(dg.columns[i].headerText == "Action"){
                        // skip it
                  }else

if(dg.columns[i].headerText == "Origin Carriers"){
                        // skip this one too
                  }else if(dg.columns[i].headerText == "Destination Carriers"){
                        // skip this one too
                  }else if(dg.columns[i].headerText == "Carrier Phone"){
                        // skip this one too
                  }else{
                        if(dg.columns[i].headerText != undefined) {
                        str+="<th "+style+">"+dg.columns[i].headerText+"</th>";
                        } else {
                              str+= "<th "+style+">"+dg.columns[i].dataField+"</th>";
                        }
                  }    
            }
            str += "</tr></thead><tbody>";
            colors = dg.getStyle("alternatingRowColors");
            //Loop through the records in the dataprovider and
            //insert the column information into the table
            for(var j:int =0;j<dg.dataProvider.length;j++) {                             
                  str+="<tr width=\""+Math.ceil(dg.width)+"\">";
                       
                  for(var k:int=0; k < dg.columns.length; k++) {
                       
                        //Do we still have a valid item?                                 
                        if(dg.dataProvider.getItemAt(j) != undefined && dg.dataProvider.getItemAt(j) != null) {
                             
                              if(dg.columns[k].labelFunction != undefined) {
                                    str += "<td width=\""+Math.ceil(dg.columns[k].width)+"\" "+style+">"+dg.columns[k].labelFunction(dg.dataProvider.getItemAt(j),dg.columns[k])+"</td>";
                                   
                              }else {
                                   
                                    str += "<td width=\""+Math.ceil(dg.columns[k].width)+"\" "+style+">"+dg.dataProvider.getItemAt(j)[dg.columns[k].dataField]+"</td>";
                              }
                        }
                  }
                  str += "</tr>";
            }
            str+="</tbody></table>";
       
            return str;
        }
       
            /**
             * offers the excel download to the user.
             * @params: dg Datagrid The Datagrid that will be loaded into Excel
             */
            private function loadDGInExcel(dg:DataGrid):void {

                  //Pass the htmltable in a variable so that it can be delivered
                  //to the backend script
                  var variables:URLVariables = new URLVariables();
                  variables.htmltable     = convertDGToHTMLTable(dg);
                 
                  //Setup a new request and make sure that we are
                  //sending the data through a post
                  var u:URLRequest = new URLRequest(urlExcelExport);
                  u.data = variables;
                  u.method = URLRequestMethod.POST;

                  //Navigate to the script
            navigateToURL(u,"_blank");
        }      

MY COLDFUSION PAGE
<cfset thsNum = randRange(50,999)>
<cfset thsNum2 = randRange(50,999)>
<cfset thsFile = "PCMSExport" & thsNum & thsNum2 & ".xls">
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=#thsFile#">
<cfoutput>
#form.htmltable#
</cfoutput>

AND ADDED TO MY MXML
I HAVE A DATAGRID CALLED carrierRes
include "excel.as";
<mx:Button label="Export To Excel

" click="loadDGInExcel(carrierRes)" fontSize="
9" color="0x5970a0" right="4"/>

3 comments:

Thallian said...

Thank you for that solution it really helped me develop one in flex.

B. Reese said...

Fantastic...this saved me a lot of time. Thanks.

Flexicious.com said...

We've created a datagrid component that internally ecapsulates excel, word, html, text, pdf export in addition to filter,pager footer, options persistence, and a lot more! Please feel free to check out http://www.flexicious.com

Wiredwizrd

Morgan Todd Lewistown, PA

Experienced Information Technology Manager with a strong knowledge of technical guidance, IT best practices, security protocols, team leadership, and analyzing business requirements.
Google