Flex to Excel or Excel to Flex, now that is the question!

Greetings,

Since my last challenge of getting Flex to create a PDF file using Flash Player 10 went so well, it was decided that I should tackle Excel files. This proved to be a challenge, since the library I decided to use doesn’t play well with itself and I thought I was doing something wrong.

Being the lazy person I am, I tried the Google search again and came across a number of different ways to handle Excel files. I came across Sasa Radovanovic’ Blog, where Sasa created an HTML file that could be imported into Excel. CFLEX.NET had an example for importing the DataGrid to Excel via the Clipboard. And finally, I found an example on the Adobe site that imported and exported data out. So I used the from Adobe as my example with a few tweaks here and there.

The example from Adobe used the as3xls Excel Library hosted on code.google.com. After doing some research, I found that the library has some issues with writing Excel files out. Excel can read the exported files, but the files are just not in the correct format. So to read the files back in, you have to open the file you exported in Excel, save the file in Excel (Excel was nice enough to correct the formatting errors), and then you can import the file back in using the library. Looking at some of the dates on the project, it does not look like the original author is updating the library. I did find Dan Wilson’s site, where he has taken a fork from the original library and updated it to fix a few problems. You can either use the original or Dan ‘s version. Either version will work for the sample below.

If the Excel file is not in the correct format when you try to read it in, you will get an error like this one. “TypeError: Error #1009: Cannot access a property or method of a null object reference.” Open the file you are trying to import in Excel, save it, and try re-importing and see if that fixes your problem.

Enjoy,

William Lorfing

<!--?xml version="1.0" encoding="utf-8"?-->
// Taken from http://cookbooks.adobe.com/post_Import_Export_data_in_out_of_a_Datagrid_in_Flex-17223.html
//
import com.as3xls.xls.Cell;
import mx.collections.ArrayCollection;

import com.as3xls.xls.Sheet;
import com.as3xls.xls.ExcelFile;
import mx.controls.dataGridClasses.DataGridColumn;

private var fileReference:FileReference;
private var sheet:Sheet;

[Bindable]
private var fields:Array = new Array();
private var ItemDGDataProvider:ArrayCollection = new ArrayCollection([
{name:"Item1",value:"21",qty:"3",cost:"12.21"},
{name:"Item2",value:"20",qty:"4",cost:"12.22"},
{name:"Item3",value:"22",qty:"5",cost:"12.23"},
{name:"Item4",value:"23",qty:"2",cost:"12.24"}
]);

[Bindable]
private var rebateDGDataProvider:ArrayCollection = new ArrayCollection();

private function browseAndUpload():void
{
fileReference = new FileReference();
fileReference.addEventListener(Event.SELECT,fileReference_Select);
fileReference.addEventListener(Event.CANCEL,fileReference_Cancel);
fileReference.browse();
}
private function fileReference_Select(event:Event):void
{
fileReference.addEventListener(ProgressEvent.PROGRESS,fileReference_Progress);
fileReference.addEventListener(Event.COMPLETE,fileReference_Complete);
fileReference.addEventListener(IOErrorEvent.IO_ERROR, onLoadError);
fileReference.load();
}
private function fileReference_Cancel(event:Event):void
{
fileReference = null;
}
private function fileReference_Progress(event:ProgressEvent):void
{
progressBar.visible = true;
progressBar.includeInLayout = true;
}
private function onLoadError():void
{
/*body not implemented*/
}
private function fileReference_Complete(event:Event):void
&amp;
#0160; {
var fileData:ByteArray = fileReference.data;
var excelFile:ExcelFile = new ExcelFile();
var noOfRows:int;
var noOfColumns:int;
if(fileData!=null &amp;&amp; fileData.length &gt; 0){
excelFile.loadFromByteArray(fileData);
var sheet:Sheet = excelFile.sheets[0];
if(sheet!=null)
{
noOfRows=sheet.rows;
noOfColumns = sheet.cols;
for(var row:int = 0; row&lt;noOfRows;row++)
{
var cellObject:Object ={};
for(var col:int=0;col&lt;noOfColumns;col++)
{
var cell:Cell = new Cell();
var cellValue:String = new String();
cell = sheet.getCell(row,col);
if(cell!=null)
{
cellValue =(cell.value).toString();
addProperty(cellObject,col,cellValue);
}
}// inner for loop ends

rebateDGDataProvider.addItem(cellObject);
} //for loop ends
} //if sheet
} //if filedata
progressBar.visible = false;
progressBar.includeInLayout =false;
rebateScheduleDG.includeInLayout = true;
rebateScheduleDG.visible = true;
fileReference = null;
}
private function addProperty(cellObject:Object,index:int,cellValue:String):void
{
if(index == 0)
cellObject.cost = cellValue;
else if(index == 1)
cellObject.name = cellValue;
else if(index == 2)
cellObject.qty = cellValue;
else if(index == 3)
cellObject.value = cellValue;
}

private function exportToExcel():void
{
sheet = new Sheet();
var dataProviderCollection:ArrayCollection = rebateByItemDG.dataProvider as ArrayCollection;
var rowCount:int = dataProviderCollection.length;

0; sheet.resize(rowCount+4,10);
sheet.setCell(0,0,"Item Name");
sheet.setCell(0,1,"Item Cost");
sheet.setCell(0,2,"Item Qty");
sheet.setCell(0,3,"Item Price");
var columns:Array = rebateByItemDG.columns;
var i:int = 0;
for each (var field:DataGridColumn in columns){
fields.push(field.dataField.toString());
sheet.setCell(0,i,field.dataField.toString());
i++;
}

for(var r:int=0;r&lt;rowCount;r++)
{
var record:Object = dataProviderCollection.getItemAt(r);
/*insert record starting from row no 2 else
headers will be overwritten*/
insertRecordInSheet(r+2,sheet,record);
}
var xls:ExcelFile = new ExcelFile();
xls.sheets.addItem(sheet);

var bytes: ByteArray = xls.saveToByteArray();
var fr:FileReference = new FileReference();
fr.save(bytes,"SampleExport.xls");
}

private function insertRecordInSheet(row:int,sheet:Sheet, record:Object):void
{
var colCount:int = rebateByItemDG.columnCount;
for(var c:int; c &lt; colCount; c++) { var i:int = 0; for each(var field:String in fields){ for each (var value:String in record){ if (record[field].toString() == value) sheet.setCell(row,i,value); } // if i++; } // for record } // for fields } // for colCount