Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13394
  • Last Modified:

export flex 3 datagrid to excel

What is the simpliest way to export the data inside a flex 3 datagrid to an Excel spreadsheet?
0
COwebmaster
Asked:
COwebmaster
  • 8
  • 5
1 Solution
 
mplordCommented:
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/
0
 
COwebmasterAuthor Commented:
I want to use a button in flex to call the function.  what would I call?  Also, do you know of a coldfusion solution?
0
 
lexxwernCommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
COwebmasterAuthor Commented:
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.
0
 
lexxwernCommented:
Okay. It is fairly simple.

1. From the link above, you can download a component in the form of an SWC file.

2. This must be included into your Flex project by putting it in the libs folder.

3. Then, you can write the following in your ActionScript:


var xls:ExcelFile = new ExcelFile(); // if you are using flex builder, typing out this line will automatically import the required libraries

var sheet:Sheet = xls.sheets[0]; // creating a new worksheet

sheet.setCell(0, 0, "Today's date:"); // writing values into the worksheet

// now its just a matter of looping thru the dataProvider of the dataGrid, one row at a time and writing individual values into the worksheet

4. Will be the interesting part, you can convert the Excel object into a ByteArray ..

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

And this ByteArray needs to be sent to the web service which will provide it for download for the user.

// ...

If you are using Adobe AIR then you can directly write this into a file.
0
 
COwebmasterAuthor Commented:
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?
0
 
lexxwernCommented:
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.
0
 
COwebmasterAuthor Commented:
lexxwern, would I create a .as file to hold the actionscript or do I place the code inside the mxml file?
0
 
COwebmasterAuthor Commented:
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?
0
 
lexxwernCommented:
>> 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.. ;-)
0
 
lexxwernCommented:
>> 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.
0
 
COwebmasterAuthor Commented:
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);
}

Open in new window

0
 
COwebmasterAuthor Commented:
lexxwern, do you have a step by step?
0
 
COwebmasterAuthor Commented:
Thx!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now