Problem
This is a very common problem and unfortunately even after a lot of search on Google, I was not able to find a solution. This is the problem which is asked on almost every Flex blog. Actually that functionality is not provided by standard Flex. Then how can we import data from a datagrid from excel and how can we export data out of datagrid in Flex?
Solution
I would be using a as3xls library for import/export. The code I am showing here may not be very clean, as I wrote it for one of my projects and sharing it here for saving flex developers from their worst nightmares :D. You will need SDK 3.4 or higher and Flash Player 10 for this code to work.
Detailed explanation
<?xml version="1.0" encoding="utf-8"?>
<mx:VBox xmlns:mx="http://www.adobe.com/2006/mxml" width="100%"
height="100%"
paddingTop="5" paddingLeft="5" borderThickness="1" paddingRight="5"
paddingBottom="5" borderColor="#000000"
backgroundColor="#FFFFFF" borderStyle="solid" cornerRadius="10">
<mx:Script>
<![CDATA[
import com.as3xls.xls.Cell;
import mx.collections.ArrayCollection;
import com.as3xls.xls.Sheet;
import com.as3xls.xls.ExcelFile;
private var fileReference:FileReference;
private var xls:Class;
private var sheet:Sheet;
[Bindable]
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
}
}
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.name = cellValue;
else if(index == 1)
cellObject.value = cellValue;
else if(index == 2)
cellObject.qty = cellValue;
else if(index == 3)
cellObject.cost = 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");
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 = 4;
for(var c:int;c<colCount;c++)
{
sheet.setCell(row,c,record.name);
}
}
]]>
</mx:Script>
<mx:Style source="styles\styles.css"/>
<mx:VBox width="100%" height="100%">
<mx:Form>
<mx:FormHeading label="Excel Import/Export Demo"
fontWeight="bold" color="#2EC200"/>
<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>
<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:Form>
</mx:VBox>
</mx:VBox>
/*---------------------- Application File--------------------*/
<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
layout="absolute" xmlns:components="components.*">
<components:ExcelImportAndExportComponent/>
</mx:Application>
/* You may need to make some small chanage in the code depending upon your requirement. You can use the excel file in attachment for import purpose. For any other information email me at contacttoakhil22 at yahoomail*/
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
Report abuse
