<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute">
<mx:Script>
<![CDATA[
// 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
{
var fileData:ByteArray = fileReference.data;
var excelFile:ExcelFile = new ExcelFile();
var noOfRows:int;
var noOfColumns:int;
if(fileData!=null && fileData.length > 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<noOfRows;row++)
{
var cellObject:Object ={};
for(var col:int=0;col<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;
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<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 < 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
]]>
</mx:Script>
<mx:FormItem label="Do you want to import your items from Excel?" fontWeight="bold">
<mx:Form>
<mx:FormItem label="Browse you excel file" fontWeight="bold">
<mx:Button label="Browse" click="browseAndUpload()"/>
<mx:HBox>
<mx:ProgressBar id="progressBar" includeInLayout="false" visible="false"
indeterminate="true"/>
</mx:HBox>
</mx:FormItem>
<mx:DataGrid id="rebateScheduleDG" includeInLayout="false" visible="false"
dataProvider="{rebateDGDataProvider}" width="100%"/>
</mx:Form>
<mx:FormItem label="Export Datagrid items to Excel?" fontWeight="bold">
<mx:Form>
<mx:HBox width="100%" verticalAlign="middle">
<mx:DataGrid id="rebateByItemDG" includeInLayout="true" visible="true"
dataProvider="{ItemDGDataProvider}" width="100%" editable="true"/>
<mx:Button label="Export To Excel" click="exportToExcel();"/>
</mx:HBox>
</mx:Form>
</mx:FormItem>
</mx:FormItem>
</mx:Application>