How to exclude or reorder fields from featureLayer (pushed into an ArrayCollection)?

[DavidBoiano]

Hello,

I have a seemingly difficult request to tweak something minor, but perhaps someone will have an idea if this is possible.

I have a function exportToExcel() that takes myFeatureLayer.graphicProvider and saves it as an Array Collection so that it can be exported to Excel as a .xls (not csv!) The featureLayer has 9 outFields set, and they show up in the Attribute Table, with the alias names as defined in my feature service, in the order as set in the outFields property.

Now, when the Array Collection is populated by clicking the a button to trigger the exportToExcel() function, the export runs but the issue is my boss wants it to match how the Attribute Table looks exactly.  This means, I need to figure out how to make the headers as the alias and the order of the columns needs to match.  For some reason, when the Array Collection is created, the fields are arranged in alphabetical order, instead of by fields order. Is there a way to list of the column titles in order by fields? How about a way to exlude a field? (For example, the ObjectID field which does not show up in the Attribute Table, but does show up from the graphicProvider)

I am thinking I will need to write some loop or conditional to grab the column title and skip over it when exporting, I'm just not sure if this is possible.

Any help as always is appreciated! Thanks.

David


For reference, here is my exportToExcel() function:

   private function exportToExcel():void    {     var featureCollection:ArrayCollection = myFeatureLayer.graphicProvider as ArrayCollection;     var exportSet:Array = [];          for each (var graphic:Graphic in featureCollection){                                              if (myMap.extent.intersects(graphic.geometry))      {       exportSet.push(graphic.attributes);      }     }          exportData = new ArrayCollection(exportSet);          sheet = new Sheet();     var dataProviderCollection:ArrayCollection = ssoDataGrid.dataProvider as ArrayCollection;     var rowCount:int = dataProviderCollection.length;     sheet.resize(rowCount + 1,ssoDataGrid.columnCount);     var columns:Array = ssoDataGrid.columns;     var i:int = 0;     for each (var field:DataGridColumn in columns){      fields.push(field.dataField.toString());      sheet.setCell(0,i,field.dataField.toString());      i++;     }          for(var r:int=0; r < rowCount; r++)     {      var record:Object = dataProviderCollection.getItemAt(r);      /*insert record starting from row 2 else      headers will be overwritten*/      insertRecordInSheet(r+1,sheet,record);     }     var xls:ExcelFile = new ExcelFile();     xls.sheets.addItem(sheet);          var bytes: ByteArray = xls.saveToByteArray();     var fr:FileReference = new FileReference();     fr.save(bytes,"SSO_Table_Export.xls");    }    private function insertRecordInSheet(row:int,sheet:Sheet,record:Object):void    {     var colCount:int = ssoDataGrid.columnCount;     for(var c:int; c < colCount; c++)     {      var i:int = 0;      for each(var field:String in fields){       for each (var value:String in record){        if (record[field].toString() == value)         sheet.setCell(row,i,value);       }       i++;      }     }    }

And how myFeatureLayer is defined:

  <esri:FeatureLayer id="myFeatureLayer"          mode="snapshot"          outFields="[ADDRESS,NEIGHBORHOOD,EST_SPILL_START_DATE,EST_SPILL_END_DATE,EST_SPILL_START_TIME,EST_SPILL_END_TIME,SPILL_VOLUME_GAL,CAUSE,CORRECTIVE_ACTION]"          definitionExpression="{updateDefExp}"          selectionColor="cyan"          url="http://ntts10/ArcGIS/rest/services/Maps/sso_spill_type/MapServer/0"/>

This is a screenshot of the attribute table and how it needs to look in Excel:
[ATTACH=CONFIG]25837[/ATTACH]

This is a screenshot of how it looks when exported into Excel:
[ATTACH=CONFIG]25838[/ATTACH]

[RobertScheitlin__GISP]

David,

   So for the alias issue use this line:

sheet.setCell(0,i,field.headerText.toString());

instead of:

sheet.setCell(0,i,field.dataField.toString());

As for you question excluding a field: removing an item from an arraycollection is basic actionscript stuff. Just use code like this:

yourArrayCollection.removeItemAt(yourArrayCollection.getItemIndex(itemToRemove));

Looping through the columns and skipping one is definitely possible.

You could use a block of code like this (untested handwritten) to get the columns in the order that the Featurelayer has them

for each(var fldName:String in myFeatureLayer.outFields){
    fields.push(fldName);
    for each (var field:DataGridColumn in columns){
        if(field.dataField.toString() == fldName){
            sheet.setCell(0,i,field.headerText.toString());
            i++;
            break;
        }
    }
}

[Anonymous User]

Original User: dboi517

Robert,

Thank you very much for your response, it was a great jumping off point for me.  I still don't fully understand the headerText v. dataField property as it produced the same results--I think because it was from a manipulated array collection, and not directly from the featureLayer with access to the alias property?

Lastly, your handwritten code for looping through the new array was perfect and works great! The myFeatureLayer.outFields was exactly what I needed to make the export array in the same order as my Attribute Table.  I ended up using a separate array with the headers explicitly written like the aliases, and a distinct for loop to overwrite the first line of the Excel output after the records had been added in the correct order.  This is now my code:

   //Create function to export attribute data to excel
   private function exportToExcel():void
   {
    var featureCollection:ArrayCollection = myFeatureLayer.graphicProvider as ArrayCollection;
    var exportSet:Array = [];
    
    for each (var graphic:Graphic in featureCollection){                                        
     if (myMap.extent.intersects(graphic.geometry))
     {
      exportSet.push(graphic.attributes);
     }
    }
    
    exportData = new ArrayCollection(exportSet);
    
    sheet = new Sheet();
    var dataProviderCollection:ArrayCollection = ssoDataGrid.dataProvider as ArrayCollection;
    var rowCount:int = dataProviderCollection.length;
    sheet.resize(rowCount + 1,ssoDataGrid.columnCount);
    
    var columns:Array = ssoDataGrid.columns as Array;
    columns.splice(8,1);
     
    var i:int = 0;
    for each(var fldName:String in myFeatureLayer.outFields){
     fields.push(fldName);
     for each (var field:DataGridColumn in columns){
      if(field.dataField.toString() == fldName){
       sheet.setCell(0,i,field.headerText.toString());
       i++;
       break;
      }
     }
    }    
    
    for(var r:int=0; r < rowCount; r++)
    {
     var record:Object = dataProviderCollection.getItemAt(r);
     /*insert record starting from row 2 else
     headers will be overwritten*/
     insertRecordInSheet(r+1,sheet,record);
    }
    
    //Create headers array to overwrite in correct format
    var headers:Array = ["Address","Neighborhood","Start Date","End Date","Start Time","End Time","Spill Volume (Gal)","Cause","Corrective Action"];
    
    //Loop to set headers on Xls sheet
    for (var h:int=0; h<9; h++)
    {
     sheet.setCell(0,h,headers);
    }
    
    //Excel export code
    var xls:ExcelFile = new ExcelFile();
    xls.sheets.addItem(sheet);    
    var bytes: ByteArray = xls.saveToByteArray();
    var fr:FileReference = new FileReference();
    fr.save(bytes,"SSO_Export.xls");
   }
   private function insertRecordInSheet(row:int,sheet:Sheet,record:Object):void
   {
    var colCount:int = ssoDataGrid.columnCount;
    for(var c:int; c < colCount; c++)
    {
     var i:int = 0;
     for each(var field:String in fields){
      for each (var value:String in record){
       if (record[field].toString() == value)
        sheet.setCell(row,i,value);
      }
      i++;
     }
    }
   }

It works almost perfectly. The only thing I am left debugging that I've discovered is (and I'm not sure if this is the actual reason or not, it just seems to be) when there are too many records (like more than 50-ish) when I click the export button to run the code, it breaks somewhere along the way and the File Reference never pops up for me to save and export. Any idea where in the code this might be caused to happen? I am guessing a loop somewhere?

[RobertScheitlin__GISP]

David,

   Hmm.. I thought that the columns array as from the ssoDataGrid.columns and that was the attribute grid you have pictured in your attachments. If so then the field.headerText should be the alias for the field as it appears in the attribute table column headers...

As far as it bombing out with more than 50 fields I don't know what to yell you without some sort of error message to go on... Are you using the Flash Player Debug version so that you can debug your code?

[Anonymous User]

Original User: dboi517

Hi Robert,

After lots of trial and error, I have identified the problem.  It doesn't have to do with the number of records at all, actually. The export fails when trying to export a record that has an empty field.  I have been doing research to try and figure out how to rectify this, but it seems like there is a big difference between a null value, "", and undefined.  I  am not sure of which is my case. 

For example, I have a record that has a value in every field except "Cause". In my data grid it shows up blank. When I check it in ArcCatalog directly from the shapefile source, it doesn't say <Null>, it is also a blank space.  Out of the 9 fields, there are only 3 that may not be filled in by field personnel - so I am thinking I need to come up with some conditional statement that checks the record for ____ (I'm not sure if it's null or "" or what) in those 3 fields where it may have a blank, and then force it to overwrite to a " " space so it still looks blank??  This is my code now:

   //Create function to export attribute data to excel
   private function exportToExcel():void
   {
    var featureCollection:ArrayCollection = myFeatureLayer.graphicProvider as ArrayCollection;
    var exportSet:Array = [];
    
    for each (var graphic:Graphic in featureCollection){                                        
     if (myMap.extent.intersects(graphic.geometry))
     {
      exportSet.push(graphic.attributes);
     }
    }
    
    exportData = new ArrayCollection(exportSet);
    
    sheet = new Sheet();
    var dataProviderCollection:ArrayCollection = ssoDataGrid.dataProvider as ArrayCollection;
    var rowCount:int = dataProviderCollection.length;
    sheet.resize(rowCount + 1,ssoDataGrid.columnCount - 1);
    
    var columns:Array = ssoDataGrid.columns as Array;
    columns.splice(8,1);
     
    var i:int = 0;
    for each(var fldName:String in myFeatureLayer.outFields){
     fields.push(fldName);
    }  
    
    for(var r:int=0; r < rowCount; r++)
    {
     var record:Object = dataProviderCollection.getItemAt(r);
     /*insert record starting from row 2 else
     headers will be overwritten*/
     insertRecordInSheet(r+1,sheet,record);
    }
    
    //Create headers array to match Attribute Table
    var headers:Array = ["Address","Neighborhood","Start Date","End Date","Start Time","End Time","Spill Volume (Gal)","Cause","Corrective Action"];
    
    //Loop to set headers on Xls sheet
    for (var h:int=0; h<9; h++)
    {
     sheet.setCell(0,h,headers);
    }
    
    //Excel export code
    var xls:ExcelFile = new ExcelFile();
    xls.sheets.addItem(sheet);    
    var bytes: ByteArray = xls.saveToByteArray(); 
    var fr:FileReference = new FileReference();
    fr.save(bytes,"SSO_Export.xls");
   }
   
   //Function to call for inserting records into Xls sheet
   private function insertRecordInSheet(row:int,sheet:Sheet,record:Object):void
   {
    var colCount:int = ssoDataGrid.columnCount - 1;
    for(var c:int; c < colCount; c++)
    {
     var i:int = 0;
     for each(var field:String in fields){
      for each (var value:String in record){
       if (record[field].toString() == value)
        sheet.setCell(row,i,value);
      }
      i++;
     }
    }
   }

In sum, I need to figure out how my blank spaces are defined, and then how I can access them in a conditional statement.  Do you have any reference on this?  Thank you.

[RobertScheitlin__GISP · Accepted solution]

David,

   In your records loop you just need to check for the null.
Something like this

                        for each (var value:String in record){                             var rvalue:String = (record[field] != null) ? String(record[field]) : "";                             if (rvalue == value)                             {                                 sheet.setCell(row,i,value);                             }                         }

[Anonymous User]

Original User: dboi517

It works like a charm. Thanks for all your help, Robert!