Tuesday 17 July 2012

Export Data In Excel using Flex



How to export data in Excel Using Flex
To export data in Excel all you need is as3xls.swc.

Download as3xls Here
Drag and Drop This  as3xls.swc file in your Lib folder.


Refer The Following Code To Create Excel File From Your Flex Application.



<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009"
  xmlns:s="library://ns.adobe.com/flex/spark"
  xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600" xmlns:local="*" viewSourceURL="srcview/index.html">
<fx:Declarations>
<!-- Place non-visual elements (e.g., services, value objects) here -->
</fx:Declarations>
<fx:Script>
<![CDATA[
import com.as3xls.xls.Cell;
import com.as3xls.xls.ExcelFile;
import com.as3xls.xls.Sheet;

import mx.collections.ArrayCollection;
import mx.controls.Alert;

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

[Bindable]
public var ItemDGDataProvider:ArrayCollection = new
ArrayCollection([
{name:"Ajay",salary:"25000",jDate:"5-Jun-2010",desg:"Soft Engineer"},
{name:"Mayur",salary:"28000",jDate:"13-Sep-2010",desg:"Soft Tester"},
{name:"Ashish",salary:"34500",jDate:"5-Jan-2011",desg:"Project manager"},
{name:"Deepak",salary:"28000",jDate:"17-Feb-2011",desg:"Business Analyst"},
{name:"Ravi",salary:"22400",jDate:"7-Mar-2010",desg:"Graphics Designer"},
{name:"Summet",salary:"18000",jDate:"5-Jun-2010",desg:"Trainee"},
{name:"Sanket",salary:"35000",jDate:"18-July-2011",desg:"QA Lead"},
{name:"Arun",salary:"15000",jDate:"4-Apr-2012",desg:"Graphics Designer"},
{name:"Dinesh",salary:"25000",jDate:"5-Jun-2010",desg:"Soft Engineer"},
{name:"Mrunal",salary:"28000",jDate:"13-Sep-2010",desg:"Soft Tester"},
{name:"Sanjay",salary:"34500",jDate:"5-Jan-2011",desg:"Project manager"},
{name:"Abhishek",salary:"28000",jDate:"17-Feb-2011",desg:"Business Analyst"},
{name:"Shiv",salary:"22400",jDate:"7-Mar-2010",desg:"Graphics Designer"},
{name:"Raja",salary:"18000",jDate:"5-Jun-2010",desg:"Trainee"},
{name:"Pratic",salary:"35000",jDate:"18-July-2011",desg:"QA Lead"},
{name:"Vishal",salary:"15000",jDate:"4-Apr-2012",desg:"Graphics Designer"}

]);

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

private function fileReference_Select(event:Event):void
{
fileReference.addEventListener(Event.COMPLETE,fileReference_Complete);
fileReference.load();  
}



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
}  
}
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,"Name");
sheet.setCell(0,1,"Salary");
sheet.setCell(0,2,"Joining Date");
sheet.setCell(0,3,"Designation");

for(var r:int=0;r<rowCount;r++)
{
sheet.setCell(r+1,0,String(ItemDGDataProvider[r].name));
sheet.setCell(r+1,1,String(ItemDGDataProvider[r].salary));
sheet.setCell(r+1,2,String(ItemDGDataProvider[r].jDate));
sheet.setCell(r+1,3,String(ItemDGDataProvider[r].desg))
}

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);
sheet.setCell(row,c,record.salary);
sheet.setCell(row,c,record.jDate);
sheet.setCell(row,c,record.desg);
}
}
]]>
</fx:Script>
<mx:DataGrid id="rebateByItemDG"
includeInLayout="true"
visible="true"
dataProvider="{ItemDGDataProvider}" width="100%"
editable="true" x="0" y="26"/>
<mx:Button label="Export To Excel"
  click="exportToExcel();" width="128" height="24"/>
</s:Application>



5 comments:

  1. Hello my friend

    i have a problem with accents,

    ReplyDelete
  2. in the above code what is the function of the below line
    sheet.resize(rowCount+4,10);

    Please name the arguments of resize() function...

    ReplyDelete
  3. Hi frnds, i have problem with arabic data while exporting can any one explain the solution for it.

    ReplyDelete
  4. Hi Shabbir, check this fork from the same library https://github.com/djw/as3xls/tree/master/

    ReplyDelete
  5. Hello, how we can apply date format when exporting the cell.

    and is it possible to apply formatting. If yes how we can apply the same?

    ReplyDelete