Flex 2 – Export Datagrid To Excel

I recently came across the problem of exporting Datagrid data to an Excel sheet.

Research on the internet brought me to a post on cflex where an solution was presented for Flex < 2.
Together with a (half-visual) comment posted there I refactored the code so that it would be applicable to Flex 2 (And didn’t need to work through the clipboard, since in my opinion this would work only half the time (browser security)).

The following Actionscript code is used for converting the Datagrid to an Excel file and then passing it on to an external Excel exporter:

<mx:Script>
<![CDATA[
/**
* Simple script to convert a Datagrid to a HTML table and then
* pass it on to an external excel exporter
*
* @author: S.Radovanovic (With help of Tracy Spratt through the post on
* http://www.cflex.net/showFileDetails.cfm?ObjectID=298&Object=File&ChannelID=1)
*/
//Libs that are mostly used
//(only a number are necessary for the datagrid conversion and export)
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;
//The location of the excel export file
public var urlExcelExport:String = "/yourpath/excelexport.php";
/**
* Convert the datagrid to a html table
* Styling etc. can be done externally
*
* @param: dg Datagrid Contains the datagrid that needs to be converted
* @returns: String
*/
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 based upon knowlegde from the datagrid
str+= '<table width="'+dg.width+'"><thead><tr width="'+dg.width+'" style="background-color:#' +Number((hcolor[0])).toString(16)+'">';
//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 != 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) {
//Check to see if the user specified a labelfunction which we must
//use instead of the dataField
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].dataField)+"</td>";
} else {
//Our dataprovider contains the real data
//We need the column information (dataField)
//to specify which key to use.
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;
}
/**
* Load a specific datagrid into Excel
* This method passes the htmltable string to an backend script which then
* offers the excel download to the user.
* The reason for not using a copy to clipboard and then javascript to
* insert it into Excel is that this mostly will fail because of the user
* setup (Webbrowser configuration).
*
* @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; //Pass the variables
u.method = URLRequestMethod.POST; //Don't forget that we need to send as POST
//Navigate to the script
//We can use _self here, since the script will through a filedownload header
//which results in offering a download to the user (and still remaining in you Flex app.)
navigateToURL(u,"_self");
}
]]>
</mx:Script>
Download this code: flexdgtoexcel.txt

Download code

The code to export the data to Excel is fairly simple.
I’ve written it in PHP, but it can be written in any language.

<?php
/**
* Export data, delivered in the POST, to excel.
*
* @author S.Radovanovic
* @version $Id$
*/
header('ETag: etagforie7download'); //IE7 requires this header
header('Content-type: application/octet_stream');
header('Content-disposition: attachment; filename="rapportage.xls"');
//Add html tags, so that excel can interpret it
echo "<html>
<body>
".stripslashes($_POST["htmltable"])."
</body>
</html>
";
?>
Download this code: exportexcel.txt

Download code

This entry was posted on Wednesday, March 7th, 2007 at 11:52 am and is filed under AS3, Flex 2. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

60 Responses to “Flex 2 – Export Datagrid To Excel”

Jason says:

June 10, 2009 at 8:20 pm

UPDATE: POST variable to big resolved

I increased the post variable limit and the script memory use limit in my php.ini file and could then export a 5,000 record dataset.
Garra says:

June 12, 2009 at 8:35 pm

Jason;
could you tell where did you modified php.ini for many rows?
thanks.

do yoy know if the version of the code here in site is the last one?

thanks to tell me where could i find the last version of the code.
thanks a lot
regards
Garra
Garra says:

June 12, 2009 at 9:50 pm

For utf-8 charset you can do:

/*
echo ‘

‘.stripslashes($_POST["htmltable"]).’

‘;
*/
Garra says:

June 12, 2009 at 9:52 pm

sorry, i think that last comment did not appear in good form, because of PHP code; but you can use content type for utf-8 charset
Chetan says:

August 19, 2009 at 11:26 am

Hello,
Thanks a lot for the code!

I was able to extend this solution for AdvancedDataGrid, will try to post it after some cleanup.
mohd says:

August 27, 2009 at 6:59 pm

Hi everyone

I tried using the given code with AdvanceDatagrid ….I received the follwing error
”
Property getItemAt not found on mx.collections.HierarchicalCollectionView and there is no default value.”

When I debug the program it seems like there is no property by name .getItemAt() for dataProvider of Advance DataGrid…………..

Any one Pls……………..Help……….
mohd says:

August 27, 2009 at 7:01 pm

Hi Chetan

If you can pls upload the code ………..Thanks
Sanjay says:

August 28, 2009 at 5:54 am

@Chetan,

Not really the code, but could u just give an idea as of how u did extend it for ADG .. obviously the code will save my efforts in case u publish the same. Thanks in advance.
Import, Export, Copy & Paste Flex DataGrid « Hillel Coren says:

September 18, 2009 at 8:30 am

[...] In Flex 3, in order to upload a file you need a small script on the server (in Flex 4/Flash 10 the client is able to handle this locally). Included in the source you’ll find two PHP files: csvImport.php and csvExport.php. To support exporting the data from the DataGrid I’m leveraging a nice algorithm written by Sasa Radovanovic explained in this post. [...]
Iwan P says:

December 7, 2009 at 7:41 am

hi Chetan,

how we modify

/*
echo ‘

‘.stripslashes($_POST["htmltable"]).’

‘;
*/

to classic asp…
pls, thank

Saskovic’s Blog

Flex 2 – Export Datagrid To Excel

60 Responses to “Flex 2 – Export Datagrid To Excel”

Leave a Reply