Flex:Not able to load Excel files from folder and update them (delete )

Question

I am trying to delete a line from 50 excel files and for that I am making a tool in flex to go through a folder, find xls files and then perform operation on it.I am facing two issues here: 1. I have a loop which iterates on the folder and once a xls is found it should call a function to delete the line and come back to the for loop and continue with the other excel files in the folder. But it is not doing so.

2.I am not able to delete and row in excel .I am using as3xls to work with excel.In this I am setting a value to of the row which I don't require as blank as I don't know how to delete it.

I am new to flex so please help.

enter code here

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
  xmlns:mx="library://ns.adobe.com/flex/mx" layout="absolute"
  creationComplete="onCreate(event)">


       <fx:Script>

              <![CDATA[
              import com.as3xls.xls.Cell;
              import com.as3xls.xls.ExcelFile;
              import com.as3xls.xls.Sheet;

              import flash.filesystem.FileMode;
              import flash.utils.Timer;
              //     import flash.events.TimerEvents;



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

              private var sheet:Sheet; 
              private var  interval: uint;
              private var loadedFile:ByteArray;

                     function trigger():void { setTimeout(doIt, 10000); }
                     function doIt():void    { }


              private function onCreate(e:Event):void
              {

                     var fileDirectry:File = File.documentsDirectory.resolvePath("D:/temp");
                     var excelFile:File = null;
                     var files:Array = fileDirectry.getDirectoryListing();
                     for(var i:int = 0; i < files.length; i++){
                           var temp:File = files[i];
                           if(temp.extension == "xls"){
                                  excelFile = temp;
                                  break;

                                  //var request:URLRequest = new URLRequest(excelFile.nativePath);
                                  //var urlLoader:URLLoader = new URLLoader(request);
                                  //urlLoader.addEventListener(Event.COMPLETE, onURLLoaderComplete); // Once file loaded, function call onURLLoaderComplete
                                  //urlLoader.dataFormat = URLLoaderDataFormat.BINARY; // to Read Data in Binary Format
                                  //urlLoader.load(request);
                                  //trigger();
                     //     interval=setTimout( onCreate(event) ,200);


                           }
                     }
                     var request:URLRequest = new URLRequest(excelFile.nativePath);
                     var urlLoader:URLLoader = new URLLoader(request);
                  urlLoader.addEventListener(Event.COMPLETE, onURLLoaderComplete); // Once file loaded, function call onURLLoaderComplete
                     urlLoader.dataFormat = URLLoaderDataFormat.BINARY; // to Read Data in Binary Format
                     urlLoader.load(request);


              }
              private function onURLLoaderComplete(event:Event):void
              {
              loadedFile = event.target.data; 
              var excelFile:ExcelFile = new ExcelFile(); 
              excelFile.loadFromByteArray(loadedFile);
              sheet = excelFile.sheets[0]; // Reads sheet1
              //trace(sheet.getCell(1,1).value);
              //Alert.show(sheet.getCell(0,0).value)// getCell(Row, Col)

              var rows:int = sheet.rows;
              var cols:int = sheet.cols;
              for(var i:int = 0; i < rows; i++){
                     for(var j:int = 0; j < cols; j++){
                           if(sheet.getCell(i,j).toString() == "second" || sheet.getCell(i,j).toString() == "Second" )
                           {
                                  Alert.show(sheet.getCell(i,j-1).value)
                                         for (var k:int =0;k< cols;k++){
                                         sheet.setCell(i,k,'');



                                         }

                                         excelFile.sheets.addItem(sheet);
                                         var ba:ByteArray = excelFile.saveToByteArray();
                                         var fr:FileReference = new FileReference();
                                         fr.save(ba,"SampleExport1.xls");


                                         //sheet.
                                  //TypeLib name and TypeDef Id
                           }
                     }
              }             
              //Alert.show(sheet.getCell(0,0).value)
              //return ;
              //DG.dataProvider=sheet.values; // Imports all excel cells to Datagrid
              }





              ]]>
       </fx:Script>




       <fx:Declarations>
              <!-- Place non-visual elements (e.g., services, value objects) here -->
       </fx:Declarations>
</mx:WindowedApplication>

Answer 1

The problem is that loading is an asynchronous operation, so you need to create some kind of system to pause the processing of the loop until the load is complete. Something like this should work:

public class ProcessFilesCommand
{
    private var _files:Array;

    private var _index:int = 0;

    public function processFiles(path:String):void
    {
        _index = 0;
        var fileDirectry:File = File.documentsDirectory.resolvePath(path);
        _files = fileDirectry.getDirectoryListing();

        if(!_files || _files.length == 0)
            return; //or dispatch a complete event

        processFileAt(0);
    }

    private function cleanFile(data:Object):void
    {
        //do your excel stuff here
    }

    private function processFileAt(index:int):void
    {
        trace("ProcessFilesCommand.processFileAt(" + index + ")");

        if(index >= _files.length)
        {
            //maybe a good spot to dispatch a complete event...
            return;
        }

        var file:File = File(_files[_index]);

        if(file.isDirectory || file.extension == null || file.extension.toLowerCase() != "xls")
        {
            processFileAt(++_index);
        }
        else
        {
            var request:URLRequest = new URLRequest(file.nativePath);
            var loader:URLLoader = new URLLoader(request);
            loader.addEventListener(Event.COMPLETE, loader_completeHandler); // Once file loaded, function call onURLLoaderComplete
            loader.dataFormat = URLLoaderDataFormat.BINARY; // to Read Data in Binary Format
            loader.load(request);
        }
    }

    private function loader_completeHandler(event:Event):void
    {
        trace("ProcessFilesCommand.loader_completeHandler(event)");

        cleanFile(event.target.data);
        processFileAt(++_index)
    }
}