10 Replies Latest reply on Jan 31, 2011 1:58 AM by Flexer_Student

    Import Excel-File (xls) to ArrayCollection

    Flexer_Student

      Hi folks,

       

      I´m trying to import an excel-file (*.xls) to offer the data in an arrayCollection for my dataProvider. Therefore, I´m using the as3xls-library and tried to implement it like in some posts - but it doesn´t work.

       

      Maybe someone can give me an advise to solve the problem.

       

      Here is my code for this part:

       

      import

       

       

      com.as3xls.xls.ExcelFile;

       

      import com.as3xls.xls.Sheet;

       

      import com.as3xls.xls.Cell;

       

      protected var carrr:ArrayCollection;

       

      private var loader:URLLoader;

       

      private function xlsToArrC():void {

           carrr =

      new ArrayCollection();

       

           var PATH:String = "../test.xls";

       

           var request:URLRequest = new URLRequest(PATH);

       

           var loader:URLLoader = new URLLoader();

           loader.dataFormat = URLLoaderDataFormat.BINARY;

           loader.addEventListener(Event.COMPLETE, completeHandler);

       

           try

           {

                loader.load(request);

           }

          

       

           catch (error:Error)

           {

       

                trace("Unable to load URL: " + error);

           }

      }

       

       

      private function completeHandler(event:Event):void {

       

      var xls:ExcelFile = new ExcelFile();

       

      var noOfRows:int;

       

      var noOfColumns:int;

       

      if(data!=null && data.length > 0){

           xls.loadFromByteArray(loader.data);

       

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

       

           if(sheet!=null) {

                noOfRows=sheet.rows;

                noOfColumns = sheet.cols;

       

                for(var row:int = 1; row<noOfRows;row++) {

       

                     var cellKW:Cell = new Cell();

       

                     var cellFZG:Cell = new Cell();

       

                     var cellBT:Cell = new Cell();

       

                     var cellTBT:Cell = new Cell();

       

                     var cellFBT:Cell = new Cell();

       

                     var valueKW:String = new String();

       

                     var valueFZG:String = new String();

       

                     var valueBT:Number = new Number();

       

                     var valueTBT:Number = new Number();

       

                     var valueFBT:Number = new Number();

                     valueKW = sheet.getCell(row,0).value;

                     valueFZG = sheet.getCell(row,1).value;

                     valueBT = sheet.getCell(row,2).value;

                     valueTBT = sheet.getCell(row,3).value;

                     valueFBT = sheet.getCell(row,4).value;

       

                     var o:Object = {car:valueFZG, bt:valueBT, tbt:valueTBT, fbt:valueFBT, week:valueKW};

                     carrr.addItem(o);

                }

           }

      }

      }

        • 1. Re: Import Excel-File (xls) to ArrayCollection
          miguel8312 Level 3

          i had similar problem in the past and i was able to solve like this.

          by following this forum. i think is pretty straight forward.

          try it and post back if you need more help.

          http://forums.sdn.sap.com/thread.jspa?threadID=1539919&tstart=0

          • 2. Re: Import Excel-File (xls) to ArrayCollection
            Flexer_Student Level 1

            Hey miguel8312,

             

            thanks for your fast reply. The problem that I need a webapplication which runs in FlashPlayer (not an AIR-appl.). Your post mentioned that u can do it in AIR. When I try to rebuild the posted code, I can´t import a File or Filestream datatype.

             

            So, is there no chance to import an excel-file in flex? I thought it should be possible with the as3xls-lib. This tutorial (http://code.google.com/p/as3xls/wiki/Tutorial) mentioned that i´ve to load the file first to an ByteArray - it it doesn´t work with my code.

             

            Thanks for every kind of help!

            • 3. Re: Import Excel-File (xls) to ArrayCollection
              miguel8312 Level 3

              flexer i apologize i should have noticed. in that case then you would like to follow this post.

              http://cookbooks.adobe.com/post_Import_Export_data_in_out_of_a_Datagrid_in_Flex-17223.html

               

              its pretty straight forward if youd need working example let me know. but i think you would get the basic idea...

              Miguel

              • 4. Re: Import Excel-File (xls) to ArrayCollection
                Flexer_Student Level 1

                Hey miguel8312,

                 

                thanks - that´s the code I found, too. I´ve oriented my code-snippet to that.

                 

                The problem is, I want to load an excel-file from a specific file path - not via browsing. Therefore I´ve seen I need an URLLoader, what I´ve done. I think my problem is that the addEventListener-method is not working / not called - but I don´t know why. The file (test.xls) is directly in the src-folder.

                 

                It would be great if u have any idea or a working example for this issue.

                 

                Thank u very much for ur help!!!

                • 5. Re: Import Excel-File (xls) to ArrayCollection
                  miguel8312 Level 3

                  alright a stupid questions but ill ask anyway... are users in this app going to load from their dekstop? when you mean a specific path do you mean a path on the server? or  is this specific path on the root of your project? do you have a sample project?

                  Miguel

                  • 6. Re: Import Excel-File (xls) to ArrayCollection
                    Flexer_Student Level 1

                    Hey,

                     

                    for trying to import the file is in the root of my project (in src folder). Later, in practical usage, the file has to be on a mapped drive on the server (same server like the application). But first I´m trying to import the file from my src-folder.

                     

                    If u have any time to spend some minutes on my code I can email it to u (if you send me your mail-address).

                     

                    Thanks for ur help!

                    • 7. Re: Import Excel-File (xls) to ArrayCollection
                      miguel8312 Level 3

                      Check Private messages.

                      Miguel

                      • 8. Re: Import Excel-File (xls) to ArrayCollection
                        miguel8312 Level 3

                        flexer_student i see where the problem is on  your code. Try this instead.

                         

                         

                           public var dataFormat:String = URLLoaderDataFormat.BINARY
                           public var strPath:String = 'test01.xls';
                           public var urlLoader:URLLoader = new URLLoader();
                           public var urlRequest:URLRequest

                        /*create loader to load file from project*/
                           private function init():void
                           {
                            urlLoader= new URLLoader();
                            urlLoader.dataFormat = dataFormat;
                            urlRequest= new URLRequest(strPath);
                            urlLoader.addEventListener(Event.COMPLETE,requestHandler);
                            urlLoader.load(urlRequest)
                           }
                          
                           private function requestHandler(event:Event):void
                           {       /*changed here for grabbing data from the current target url loader*/
                            var fileData:ByteArray  = event.currentTarget.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);
                               //rebateScheduleDG.dataProvider = rebateDGDataProvider
                              } //for loop ends
                             }  
                            }
                            progressBar.visible = false;
                            progressBar.includeInLayout =false;
                            rebateScheduleDG.includeInLayout = true;
                            rebateScheduleDG.visible = true;
                            fileReference = null;
                           }

                         

                         

                         

                        I will post the full project in a few minutes i couldn't get your project to compile so i made my own and change the code to use the  urlLoader class.

                        I will remind you that this library is buggy "as3xls" for exampple the sample file that you send me wont load i get an error that says "end of file expected"

                        however if i take that same file copy and pasted it to a clean excel sheet VOILA!!! no problems. Also i noticed you had formatting in your worksheet.. I hope you realize that "as3xls" will remove alot of your formatting so you better be careful in your solution.

                        anywho enjoy.

                         

                         

                         

                         

                        if this post answers your question please mark it answered

                        Thanks

                        Miguel

                        • 9. Re: Import Excel-File (xls) to ArrayCollection
                          miguel8312 Level 3

                          full project

                          http://miguelontheweb.com/ExcelTest.zip

                           

                           

                          if this post answers your question please mark it answered

                          Thanks

                          Miguel

                           

                           

                          Message was edited by: miguel8312

                          1 person found this helpful
                          • 10. Re: Import Excel-File (xls) to ArrayCollection
                            Flexer_Student Level 1

                            Hey,

                             

                            the project ist working now. A lot of thanks for your help miguel8312.

                             

                            One thing to tell all others: If you try to use the lib "as3xls" you need an unformatted xls-file.