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”. 🙂
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;
}
}
}