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>