export flex 3 datagrid to excel

I've used an approach based on this solution which works well for me:
http://flex-apollo.blogspot.com/2007/05/exporting-datagrid-data-to-excel.html

I can't take any credit for that.

My observations on this approach are:
a) Standard Flex in a web browser has no access to the desktop other than FileReference which won't help you here, so the only way to make anything happen with files that you control the content of on your desktop is via what is possible with the browser, in this case 'downloading' a csv file generated on the fly from a server PHP script containing the data you posted to it.
b) The downside is you have to send your data to the server only for it to return it to you as a download with modified headers to look like an excel file, so you might want to consider security and maybe an HTTPS connection, or some other sort of encryption.

If you want a completely desktop-local solution, you'll have to consider AIR. Then add to that the Flourine solution for system process interaction (Windows only) if you want to e.g. launch files as a desktop process, until Adobe include ability to work with system processes in AIR.

Flourine Aperture: http://aperture.fluorinefx.com/

I want to use a button in flex to call the function.  what would I call?  Also, do you know of a coldfusion solution?

It will not directly export a DataGrid to Excel, but a library called AS3XLS can be used to generate Excel files directly from Flex. See this: http://code.google.com/p/as3xls/

You can traverse through the dataProvider of the DataGrid and write to the cells of the Excel file using this component.

Sample Code:
Reading

   1. You need to get the Excel file into a ByteArray of some sort, whether by hook, crook, or Embed.
   2. Now you need to load it up like so:

var xls:ExcelFile = new ExcelFile();
xls.loadFromByteArray(myByteArray);

   1. The sheets are stored in an ArrayCollection as Sheet objects. These hold a bunch of Cell objects each of which has a value property. This returns a Number, a String, or a Date object depending on the contents. You can play with them like so:

var sheet:Sheet = xls.sheets[0];

var value:String = sheet.getCell(0, 0).value;

Each sheet also has a values ArrayCollection suitable for a DataGrid's dataProvider. To display a sheet in a DataGrid you'd do this:

myDataGrid.dataPovider = sheet.values;

Formulas

The result of evaluating a formula is available in the value property. To get the a string representation of the formula, like "SIN(5)" or "A5*SUM(B1:B28)" you use the Formula object:

sheet.getCell(0, 0).formula.formula.

If a cell doesn't contain a formula the formula property will be null.
Writing

First create a sheet and resize it. If you want to resize it after putting in values those values will not be deleted.

var sheet:Sheet = new Sheet();
sheet.resize(10, 10).

Now you can put in some values.

sheet.setCell(0, 0, "Today's date:");
sheet.setCell(0, 1, new Date());

Now put the sheet into an ExcelFile and save it.

var xls:ExcelFile = new ExcelFile();
xls.sheets.addItem(sheet);
var bytes:ByteArray = xls.saveToByteArray();

That ByteArray can be saved to disk and the resulting file will be loadable by any version of Excel.

I guess I'm looking for an easier way, if any.  Do you know of a downloadable app I can play with?  Again, I need to export data from a flex datagrid into an excel spreadsheet.  It is only for local use only and stored on my server for my users who have excel on their machines to download data.  I'm not really following the above.  I'm kind of a flex 3 newbie.

Lexxwern, I can tell you know your stuff well.  I'll try it out but is there a more simpler way that you know of?

I'm not aware of a direct way of DataGrid to Excel export. I know of AS3XLS library and I have worked on server side solutions to this problem in JSP and PHP.

I think ColdFusion has a very easy mechanism of DataGrid to Excel but again that is Server Side programming and not a nqative Flex/ActionScript solution.

lexxwern, would I create a .as file to hold the actionscript or do I place the code inside the mxml file?

I saw this site, http://www.cflex.net/showFileDetails.cfm?ObjectID=298&Object=File&ChannelID=1, but where doesn the code?  Also, how do I assign a button event?

>> lexxwern, would I create a .as file to hold the actionscript or do I place the code inside the mxml file?

Either it can be an AS file included in the MXML or ActionScript code within the MXML <mx:Script></mx:Script> tags.. ;-)

>> Also, how do I assign a button event?

In MXML assign it with the tag <mx:Button click="doSomething()"/>

In actionscript use the button.addEventListener() method.

Ok, well I'm using the following from the above site but I get an error saying..

Property column not found on mx.controls.DataGrid and there is no default value

What would I need to change?

function doCopy(dg)
{
	var font = dg.getStyle('fontFamily');
	var size = dg.getStyle('fontSize');
	var hcolor ;
	if(dg.getStyle("headerColor") != undefined) hcolor = [dg.getStyle("headerColor")];
	else hcolor = dg.getStyle("headerColors");
	var str:String = '<html><body><table width="'+dg.width+'"><thead><tr width="'+         dg.width+'" style="background-color:#' +Number((hcolor[0])).toString(16)+'">';
	for(var i=0;i<dg.__columns.length;i++)
	{
	var colors = dg.getStyle("themeColor");
	var style = 'style="font-family:'+font+';font-size:'+size+'pt;"';
			
		if(dg.__columns[i].headerText != undefined)
		{
			str+="<th "+style+">"+dg.__columns[i].headerText+"</th>";
		}
		else
		{
			str+= "<th "+style+">"+dg.__columns[i].columnName+"</th>";
		}
	}
		str += "</tr></thead><tbody>";
	var colors = dg.getStyle("alternatingRowColors");
	for(var j=0;j<dg.length;j++)
	{
		str+="<tr width=\""+Math.ceil(dg.width)+"\" style='background-color:#" +Number((colors[j%colors.length])).toString(16)+"'>";
		var style = 'style="font-family:'+font+';font-size:'+size+'pt;"';
			
		for(var i=0;i<dg.__columns.length;i++)
		{
			if(dg.getItemAt(j) != undefined && dg.getItemAt(j) != null)
				if(dg.__columns[i].labelFunction != undefined)
					str += "<td width=\""+Math.ceil(dg.__columns[i].width)+"\" "+style+">"+dg.__columns[i].labelFunction(dg.getItemAt(j),dg.__columns[i].columnName)+"</td>";
				else
					str += "<td width=\""+Math.ceil(dg.__columns[i].width)+"\" "+style+">"+dg.getItemAt(j)[dg.__columns[i].columnName]+"</td>";
			
		}
		str += "</tr>";
	}
	str+="</tbody></table></body></html>";
	System.setClipboard(str);
}

Select allOpen in new window

lexxwern, do you have a step by step?

Thx!