Upload Excel file into Flex datagrid

[Former Member]

Hi Experts,

I need to import data from excel file into flex datagrid. If anyone did this before?

Regards

Basheer

[Former Member · Accepted solution]

Hi Basheer,

Due to security concern Flex does not allow to import local excel files directly in flex application though it can be done in AIR application.

In Flex we can go with one other way round. copy the excel content and paste in on a textArea.

Using the normal tab and line seperators viz. \t and \n you can idenfy different lines and columns content.

Then it's easy to break entire string into array of substring to set as dataprovider to datagrid.

It's really an easy process and i have implemented it in my applications easily.

though i'm replying after long time to your post but atleast this will help others having same requirement.

Regards,

Vivek

[naimkhans_babi]

Dear Basheer

Please look the below code.

	<mx:Script>
		<![CDATA[
			import mx.charts.AreaChart;
			import mx.collections.ArrayCollection;
			import com.as3xls.xls.Sheet;
			import com.as3xls.xls.ExcelFile;
			import flash.net.FileReference;
			import mx.controls.Alert;
			
			public var file:File;
			private var stream:FileStream;

			private function loadXLSFile():void
			{
				//Alert.show("success");
				main.visible=true;
			}
						
			public function browseFileSystem():void {
                file = new File();
                file.addEventListener(Event.SELECT, selectHandler);
                file.addEventListener(IOErrorEvent.IO_ERROR,ioErrorHandler);
                file.addEventListener(Event.COMPLETE,completeHandler);
                file.browse();
            }
			private function selectHandler(event:Event):void
			{
				txtfilename.text=event.currentTarget.name;
              	loadFile(event.currentTarget as File)
            }
            
             private function loadFile(file:File):void
             {
				var byteA:ByteArray=new ByteArray();
		        stream = new FileStream();
		        stream.open(file, FileMode.READ);
		        stream.readBytes(byteA);
		        stream.close();
		       	loadByteExcel(byteA);
		     }
            
            private function ioErrorHandler(event:IOErrorEvent):void {
                trace("ioErrorHandler: " + event);
            }
   
           /*  private function progressHandler(event:ProgressEvent):void {
                var file:FileReference =FileReference(event.target);
                trace("progressHandler: name=" + file.name + "bytesLoaded=" + event.bytesLoaded + " bytesTotal=" +event.bytesTotal);
            } */
   
            private function completeHandler(event:Event):void {
                 Alert.show(event.currentTarget.data);
            }

		/* 	private function saveFile():void{
        var myPattern:RegExp = / /g;
        var newFileName:String = fileName.text.replace('.txt','');
        if(newFileName.length > 1){
            var file:File = File.desktopDirectory.resolvePath("Files/" + newFileName.replace(myPattern,'_') + ".txt");
            var stream:FileStream = new FileStream()
            stream.open(file, FileMode.WRITE);
            var str:String = contents.text;
            str = str.replace(/\r/g, File.lineEnding);
            stream.writeUTFBytes(str);
            stream.close();
            fdg.directory = File.desktopDirectory.resolvePath("Files/");
            fileName.text = "";
            contents.text = "";
          } else {
              mx.controls.Alert.show("File name is required", "Error Saving File");
          } 
    }*/
	
		private var gridArraycollection:ArrayCollection=new ArrayCollection();
		private var columArray:Array=new Array();
		private var rowArray:Array=new Array(); 	
		private function loadByteExcel(myByteArray:ByteArray):void
		{
			var xls:ExcelFile = new ExcelFile();
			xls.loadFromByteArray(myByteArray);
			var sheet:Sheet = xls.sheets[0];
			for(var i:int=1;i<sheet.rows;i++)
			{
				var obj:Object=new Object();
				for(var j:int=0;j<sheet.cols;j++)
				{
					obj[sheet.getCell(0,j)]=sheet.getCell(i,j).value;
				}
				gridArraycollection.addItem(obj);
			}
		}
				
		
	
	
		
			
			
		
		
	]]>

you have to use as3xls.swc libaray which u can get by googled it.... please put this library in the libs folder,, i hope this will help you.

Regards

Naeem

[naimkhans_babi]

>

> Dear Basheer

>

> Please look the below code.

>

>

	<mx:Script>
> 		<![CDATA[
> 			import mx.charts.AreaChart;
> 			import mx.collections.ArrayCollection;
> 			import com.as3xls.xls.Sheet;
> 			import com.as3xls.xls.ExcelFile;
> 			import flash.net.FileReference;
> 			import mx.controls.Alert;
> 			
> 			public var file:File;
> 			private var stream:FileStream;
> 
> 			private function loadXLSFile():void
> 			{
> 				//Alert.show("success");
> 				main.visible=true;
> 			}
> 						
> 			public function browseFileSystem():void {
>                 file = new File();
>                 file.addEventListener(Event.SELECT, selectHandler);
>                 file.addEventListener(IOErrorEvent.IO_ERROR,ioErrorHandler);
>                 file.addEventListener(Event.COMPLETE,completeHandler);
>                 file.browse();
>             }
> 			private function selectHandler(event:Event):void
> 			{
> 				txtfilename.text=event.currentTarget.name;
>               	loadFile(event.currentTarget as File)
>             }
>             
>              private function loadFile(file:File):void
>              {
> 				var byteA:ByteArray=new ByteArray();
> 		        stream = new FileStream();
> 		        stream.open(file, FileMode.READ);
> 		        stream.readBytes(byteA);
> 		        stream.close();
> 		       	loadByteExcel(byteA);
> 		     }
>             
>             private function ioErrorHandler(event:IOErrorEvent):void {
>                 trace("ioErrorHandler: " + event);
>             }
>    
>            /*  private function progressHandler(event:ProgressEvent):void {
>                 var file:FileReference =FileReference(event.target);
>                 trace("progressHandler: name=" + file.name + "bytesLoaded=" + event.bytesLoaded + " bytesTotal=" +event.bytesTotal);
>             } */
>    
>             private function completeHandler(event:Event):void {
>                  Alert.show(event.currentTarget.data);
>             }
> 
> 		/* 	private function saveFile():void{
>         var myPattern:RegExp = / /g;
>         var newFileName:String = fileName.text.replace('.txt','');
>         if(newFileName.length > 1){
>             var file:File = File.desktopDirectory.resolvePath("Files/" + newFileName.replace(myPattern,'_') + ".txt");
>             var stream:FileStream = new FileStream()
>             stream.open(file, FileMode.WRITE);
>             var str:String = contents.text;
>             str = str.replace(/\r/g, File.lineEnding);
>             stream.writeUTFBytes(str);
>             stream.close();
>             fdg.directory = File.desktopDirectory.resolvePath("Files/");
>             fileName.text = "";
>             contents.text = "";
>           } else {
>               mx.controls.Alert.show("File name is required", "Error Saving File");
>           } 
>     }*/
> 	
> 		private var gridArraycollection:ArrayCollection=new ArrayCollection();
> 		private var columArray:Array=new Array();
> 		private var rowArray:Array=new Array(); 	
> 		private function loadByteExcel(myByteArray:ByteArray):void
> 		{
> 			var xls:ExcelFile = new ExcelFile();
> 			xls.loadFromByteArray(myByteArray);
> 			var sheet:Sheet = xls.sheets[0];
> 			for(var i:int=1;i<sheet.rows;i++)
> 			{
> 				var obj:Object=new Object();
> 				for(var j:int=0;j<sheet.cols;j++)
> 				{
> 					obj[sheet.getCell(0,j)]=sheet.getCell(i,j).value;
> 				}
> 				gridArraycollection.addItem(obj);
> 			}
> 		}
> 				
> 		
> 	
> 	
> 		
> 			
> 			
> 		
> 		
> 	]]>

>

>

> you have to use as3xls.swc libaray which u can get by googled it.... please put this library in the libs folder,, i hope this will help you.

> Regards

> Naeem

<mx:Script>

<![CDATA[

import mx.charts.AreaChart;

import mx.collections.ArrayCollection;

import com.as3xls.xls.Sheet;

import com.as3xls.xls.ExcelFile;

import flash.net.FileReference;

import mx.controls.Alert;

public var file:File;

private var stream:FileStream;

private function loadXLSFile():void

{

//Alert.show("success");

main.visible=true;

}

public function browseFileSystem():void {

file = new File();

file.addEventListener(Event.SELECT, selectHandler);

file.addEventListener(IOErrorEvent.IO_ERROR,ioErrorHandler);

file.addEventListener(Event.COMPLETE,completeHandler);

file.browse();

}

private function selectHandler(event:Event):void

{

txtfilename.text=event.currentTarget.name;

loadFile(event.currentTarget as File)

}

private function loadFile(file:File):void

{

var byteA:ByteArray=new ByteArray();

stream = new FileStream();

stream.open(file, FileMode.READ);

stream.readBytes(byteA);

stream.close();

loadByteExcel(byteA);

}

private function ioErrorHandler(event:IOErrorEvent):void {

trace("ioErrorHandler: " + event);

}

/* private function progressHandler(event:ProgressEvent):void {

var file:FileReference =FileReference(event.target);

trace("progressHandler: name=" + file.name + "bytesLoaded=" + event.bytesLoaded + " bytesTotal=" +event.bytesTotal);

} */

private function completeHandler(event:Event):void {

Alert.show(event.currentTarget.data);

}

/* private function saveFile():void{

var myPattern:RegExp = / /g;

var newFileName:String = fileName.text.replace('.txt','');

if(newFileName.length > 1){

var file:File = File.desktopDirectory.resolvePath("Files/" + newFileName.replace(myPattern,'_') + ".txt");

var stream:FileStream = new FileStream()

stream.open(file, FileMode.WRITE);

var str:String = contents.text;

str = str.replace(/\r/g, File.lineEnding);

stream.writeUTFBytes(str);

stream.close();

fdg.directory = File.desktopDirectory.resolvePath("Files/");

fileName.text = "";

contents.text = "";

} else {

mx.controls.Alert.show("File name is required", "Error Saving File");

}

}*/

private var gridArraycollection:ArrayCollection=new ArrayCollection();

private var columArray:Array=new Array();

private var rowArray:Array=new Array();

private function loadByteExcel(myByteArray:ByteArray):void

{

var xls:ExcelFile = new ExcelFile();

xls.loadFromByteArray(myByteArray);

var sheet:Sheet = xls.sheets[0];

for(var i:int=1;i<sheet.rows;i++)

{

var obj:Object=new Object();

for(var j:int=0;j<sheet.cols;j++)

{

obj[sheet.getCell(0,j)]=sheet.getCell(i,j).value;

}

gridArraycollection.addItem(obj);

}

}

]]>

</mx:Script>

<mx:VBox id="main" showEffect="Fade" visible="false" hideEffect="Fade" height="100%" width="100%" horizontalAlign="center" verticalAlign="middle">

<mx:HBox height="100%" width="100%" horizontalAlign="center" verticalAlign="middle" paddingBottom="2" paddingLeft="2" paddingRight="2" paddingTop="2">

<mx:TextInput id="txtfilename"/>

<mx:Button label="Browse" click="browseFileSystem()"/>

</mx:HBox>

<mx:DataGrid dataProvider="" height="100%" width="100%"/>

</mx:VBox>

Edited by: Naim Khan S Babi on Feb 11, 2010 12:28 AM

[naimkhans_babi]

I am sorry for this strange look of code.. i tried to provide u formatted look... please give me your email address so i can send u entire code and library as well,,,,

Regards

Naeem

[Former Member]

Dear Naeem,

I already done it with as3xls only.

Regards

Basheer

[Former Member]

hello basheer can you please post the code for uploading excel file into flex datagrid