AdvancedDataGrid CSV Export Utility Class

Recently, I needed a way to export data from Adobe Flex’s AdvancedDataGrid control to CSV-formatted files for use in Microsoft Excel.  Since Flex did not provide a native method, I looked into some existing third-party libraries.  Those did not work out, so I ended up building my own AdvancedDataGrid CSV export utility class.

I first experimented with the excellent AlivePDF library.  I had used AlivePDF in the past to…well…create PDFs.  One of the things I remembered from using it was that there was CSV export functionality.  But after spending some time experimenting, I was disappointed to find it required quite a bit of overhead and would not be able to fully satisfy my needs.  Next, I turned my attention towards the AS3XLS library.  From what I could gather, the library seems like what you’d want if you were trying to read & write native Excel files, but it didn’t provide CSV support.

So, I started searching the intertubes for another option.  I found a great  post by Abdul Qabiz, demonstrating a home baked solution he had come up with.  The code he presented was quite dated (it was written for Flex 2, targeting the DataGrid control) and was missing some features I needed.  However, it was exactly what I needed as a starting point.  Learning from what he provided, I rolled my own solution that provided everything I was looking for.

Below is the AdvancedDataGrid CSV export utility class I came up with.  It compiles in Flex 3.5, Flex 3.6 and Flex 4.5, handles grouped column headers, can double-quote values, and is capable of saving the CSV data of any language to a file (through the use of UTF-16 encoding).  I am posting this simple class in case somebody else might find it useful.  If you do, please leave a comment.  Also, let me know if you find ways to improve it! 

Note: The final version of the code uses a tab as the default CSV delimiter.  This is due to a limitation in Excel when dealing with a comma-separated value file in UTF-16/Unicode encoding.  Despite the use of a tab character, the format of the file is still viewed as a “CSV”.  🙂

Download the code.

CSVUtil.as:

package net.onyxmueller.util
{
import flash.net.FileReference;
import flash.utils.ByteArray;

import mx.collections.ICollectionView;
import mx.collections.IViewCursor;
import mx.controls.AdvancedDataGrid;
import mx.controls.advancedDataGridClasses.AdvancedDataGridColumn;
import mx.controls.advancedDataGridClasses.AdvancedDataGridColumnGroup;

public class CSVUtil
{
private var _csvSeparator:String;
private var _lineSeparator:String;
private var _doubleQuoteValues:Boolean;
private var _doubleQuoteRexExPattern:RegExp = /\"/g;
private var _encoding:String;

public function CSVUtil(csvSeparator:String = "\t", lineSeparator:String = "\n", doubleQuoteValues:Boolean = true)
{
_csvSeparator = csvSeparator;
_lineSeparator = lineSeparator;
_doubleQuoteValues = doubleQuoteValues;
}

public function formatAsCSVString(items:Array):String
{
if (_doubleQuoteValues)
{
// escape any existing double quotes then place double quotes around values
for (var i:int = 0; i < items.length; i++) { items[i] = items[i] ? items[i].replace(_doubleQuoteRexExPattern, "\"\"") : ""; items[i] = "\"" + items[i] + "\""; } } return items.join(_csvSeparator) + _lineSeparator; } public function advancedDataGridToCSVString(dg:AdvancedDataGrid):String { var headerCSV:String = ""; var headerItems:Array; var dataCSV:String = ""; var dataItems:Array; var columns:Array = dg.groupedColumns ? dg.groupedColumns : dg.columns; var column:AdvancedDataGridColumn; var headerGenerated:Boolean = false; var cursor:IViewCursor = (dg.dataProvider as ICollectionView).createCursor(); // loop through rows while (!cursor.afterLast) { var obj:Object = null; obj = cursor.current; dataItems = new Array(); headerItems = new Array(); // loop through all columns for the row for each (column in columns) { // if the column is not visible or the header text is not defined (e.g., a column used for a graphic), // do not include it in the CSV dump if (!column.visible || !column.headerText) continue; // depending on whether the current column is a group or not, export the data differently if (column is AdvancedDataGridColumnGroup) { for each (var subColumn:AdvancedDataGridColumn in (column as AdvancedDataGridColumnGroup).children) { // if the sub-column is not visible or the header text is not defined (e.g., a column used for a graphic), // do not include it in the CSV dump if (!subColumn.visible || !subColumn.headerText) continue; dataItems.push(obj ? subColumn.itemToLabel(obj) : ""); if (!headerGenerated) headerItems.push(column.headerText + ": " + subColumn.headerText); } } else { dataItems.push( obj ? column.itemToLabel(obj) : ""); if (!headerGenerated) headerItems.push(column.headerText); } } // append a CSV generated line of our data dataCSV += formatAsCSVString(dataItems); // if our header CSV has not been generated yet, do so; this should only occur once if (!headerGenerated) { headerCSV = formatAsCSVString(headerItems); headerGenerated = true; } // move to our next item cursor.moveNext(); } // set references to null: headerItems = null; dataItems = null; columns = null; column = null; cursor = null; // return combined string return headerCSV + dataCSV; } public function saveAdvancedDataGridAsCSVFile(dg:AdvancedDataGrid, fileName:String, encoding:String = "utf-16"):void { var csvString:String = advancedDataGridToCSVString(dg); var bytes:ByteArray = new ByteArray(); // if using UTF-16, prefix file with BOM (little-endian) if (encoding == "utf-16") { bytes.writeByte(0xFF); bytes.writeByte(0xFE); bytes.writeMultiByte(csvString, encoding); } else bytes.writeMultiByte(csvString, encoding); // prompt the user with a save location // note: FileReference requires a minimum flash player version of 10 var fileReference:FileReference = new FileReference(); fileReference.save(bytes, fileName); fileReference = null; } } }