Onyx Mueller

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