Export DataGrid to Excel via Clipboard

Sep 09, 2005 - Export DataGrid to Excel via Clipboard
Read Flex DataGrid, build HTML and put on clipboard for export to Excel or wherever

Clever solution to the "How can I export to Excel?" problem, by bdeen, as posted on MM Flex forum.

I know we all have users asking "Can I Export/Import to Excel?". Apparently Excel is the best thing to come along to the user community since large amounts of RAM. I personally have used Excel maybe 3 times in my life, but I always try to help out the user. So this is about exporting DataGrid information into Excel.

A few things to realize about this solution.
1) It should be able to run in all the Flash Players.
2) I only tested the output in Excel 2003.
3) I added IE specific javascript (which you don't need to have to still get this functionality) which will open Excel and populate it with the data in the DataGrid.
4) This solution uses basic html styles and table.
5) I populate the styles with background colors however Excel only has a handful of allowable colors and it might assign odd colors to an unodd color in Flash. so light blue might come out as lavendar. soft shades of gray/grey come out as white. Dark colors are seen as purple. However if you paste the result of this into an html file and have IE or FireFox render it, it comes out very much like the look and feel you have in Flex.
6) Font's seem to translate well, as well as sizes of fonts. I didn't do anything other than font family and font size, if you want more you can add it yourself, it's rather easy.
7) I added widths (which again looks great in a Browser) but Excel seems to ignore them.
8) I use labelFunctions and then columnNames, if you define the labelFunction then the copied content will reflect what the labelFunction produces, as well as the String version of the columnName

Code:

// ACTION SCRIPT FOR YOUR FLEX APP

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);
}

function handleOnKeyUp()
{
	if(Key.isDown(Key.CONTROL) &&Key.getCode() ==67) 
	{
		mx.managers.CursorManager.setBusyCursor();
		doCopy(grid);  
                  // exchange 'grid' with the id of your datagrid you want copied
		mx.managers.CursorManager.removeBusyCursor();
	}
}
// only register interest if you want ctrl-c to process a copy of a datagrid. 
// it's also ctrl + c (on key up, not down)
var obj:Object;
function registerKeyInterest()
{
  if(obj == undefined)
 {
   obj = new Object();
   obj.onKeyUp = mx.utils.Delegate.create(this,handleOnKeyUp);
}
   Key.addListener(obj);
}

function removeKeyInterest()
{
   Key.removeListener(obj);
}

function copyAndOpen(grid)
{
  doCopy(grid);
  getUrl("javascript:openExcel();");
}

// JAVASCRIPT for your HTML PAGE
<SCRIPT Language="JavaScript1.2">
var excel = null;
function openExcel()
{
	try
	{

		if(excel == null)
		{	
		 excel = new ActiveXObject("Excel.Application");

		}
		var workbook = excel.Workbooks.Add();

		workbook.Activate();
		var worksheet = workbook.Worksheets("Sheet1");
		worksheet.Activate();

		worksheet.Paste();
		excel.visible=true;



	}catch(exception)
	{
		window.alert("Now you may Paste into an Excel SpreadSheet");
	}
}
</SCRIPT>

Created On	Sep, 09, 2005 by Tracy Spratt
Last Modified On	Aug, 16, 2006 by Tariq Ahmed
Group:	Tips and Articles
Flex Versions:	All
Category:	General
Type:	Complete Lesson
Difficulty:	Beginner
Keywords:	Excel HTML export DataGrid

August 16, 2006 12:21PM - Tom Ortega
Here's a modified version of the doCopy function for Flex 2. I've stripped some of the styes, typed some vars and stripped duplicate var declarations to eliminate some warnings: public function exportDataToClipboard(dg:DataGrid):void { var font:String = dg.getStyle('fontFamily'); var size:Number = dg.getStyle('fontSize'); var hcolor:Array ; if(dg.getStyle("headerColor") != undefined) hcolor = [dg.getStyle("headerColor")]; else hcolor = dg.getStyle("headerColors"); var str:String = ''; for(var i:Number=0;i"+dg.columns[i].headerText+""; } else { str+= ""; } } str += ""; for(var j:Number=0;j"; for(var k:Number=0;k"+dg.columns[k].labelFunction(dg.dataProvider.getItemAt(j),dg.columns[k])+""; else str += ""; } str += ""; } str+="

"+dg.columns[i].columnName+"
"+dg.dataProvider.getItemAt(j)[dg.columns[k].dataField]+"

"; System.setClipboard(str); }

February 05, 2007 03:05AM - . .
How can i get the full flex 2.0 code ?

February 05, 2007 09:10PM - Tracy Spratt
From Adobe....

March 07, 2007 07:57AM - Sasa Radovanovic
Using Tracy's and Toms code I adjusted it so that it know works for Flex 2. Some adjustments are also that I'm not clipboarding the data and then using javascript, but instead I send the data to an external script which offers the user a download. For code see my blog on: http://www.saskovic.com/blog/

December 29, 2009 01:19PM - Terry Jahelka
Need some help. Trying to add export to excel logic to a flex component that will be packaged as a SWC and used by our business layers. I don't know where to put the javascript to launch excel so that it will be included in the SWC and available to all users of this component.

December 31, 2009 11:15AM - Tracy Spratt
ExternalInterface is very powerful for integrating with a browser. I do not have a full solution, But suggest you look into using externalInterface to "inject" the javascript needed into the wrapper.

May 19, 2010 03:42PM - Flexicious Flexicious
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