Why is it that we developers always wait until the last minute to get around to the ‘Export to Excel’ requirement? To be fair, all of the other requirements seem so much more interesting and we’ve exported to Excel 100 times before and can probably just copy some code from elsewhere. So we put it on the back burner.

Then, of course, we end up running short on time and we just whip up some simple server-side script that converts a dataset to CSV format. And sure, it meets the requirement, but it’s not pretty nor a native Excel file. To boot, if you’re developing in Flex you likely have all of the data client-side already so making a trip back to the server is frivolous and time consuming.

Well, never fear – as3xls (link: http://code.google.com/p/as3xls/) is here. This library was developed by some kind of Flex developer known only as Sigfrid3141 (link: http://code.google.com/u/Sigfrid3141/). It can read and write the XLS format from your Flex code so that you can create a pretty, native Excel file and prompt the user to save that file all from your Flex application.

For one of my projects, I whipped up a utility class which has one static method – dataGridToExcel. It takes a Flex DataGrid control and used the as3xls library to create an Excel file. Feel free to borrow. See the code below.

package com.gisinc.util
{
import com.as3xls.xls.ExcelFile;
import com.as3xls.xls.Sheet;
import flash.net.FileReference;
import flash.utils.ByteArray;
import mx.collections.ICollectionView;
import mx.collections.IViewCursor;
import mx.controls.Alert;
import mx.controls.DataGrid;
import mx.controls.dataGridClasses.DataGridColumn;

public class ExcelUtil
{
public static function dataGridToExcel(dataGrid:DataGrid, filename:String):void
{
var sheet:Sheet = new Sheet();
sheet.resize((dataGrid.dataProvider as ICollectionView).length + 1, dataGrid.columns.length + 1);

for(var i:int = 0; i < dataGrid.columns.length; i++) {
sheet.setCell(0, i, (dataGrid.columns[i] as DataGridColumn).headerText);
}

var cursor:IViewCursor = dataGrid.dataProvider.createCursor();
var rowCount:int = 1;

while(!cursor.afterLast)
{
for(var j:int = 0; j < dataGrid.columns.length; j++)
{
var col:DataGridColumn = dataGrid.columns[j] as DataGridColumn;
sheet.setCell(rowCount, j, col.itemToLabel(cursor.current));
}
rowCount++;
cursor.moveNext();
}

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

var bytes:ByteArray = xls.saveToByteArray();
var fileReference:FileReference = new FileReference();
fileReference.save(bytes, filename);
}
}
}