Iterating over datagrid columns/rows

[DeewendraShrestha]

Hi there, I wanted to export data from datagrid to excel but I am having difficulty accessing the datagrid items. Here is what I was trying:

<?xml version="1.0" encoding="utf-8"?> <viewer:BaseWidget xmlns:fx="http://ns.adobe.com/mxml/2009"                    xmlns:s="library://ns.adobe.com/flex/spark"                    xmlns:mx="library://ns.adobe.com/flex/mx"                    xmlns:viewer="com.esri.viewer.*"        xmlns:esri="http://www.esri.com/2008/ags"                    widgetConfigLoaded="init()">     <fx:Script>         <![CDATA[    import com.esri.ags.events.QueryEvent;    import com.esri.ags.tasks.supportClasses.Query;    import com.esri.ags.tasks.supportClasses.StatisticDefinition;        import mx.collections.ArrayCollection;    import mx.controls.Alert;    import mx.events.FlexEvent;    import mx.rpc.events.FaultEvent;        import com.as3xls.xls.Cell;    import com.as3xls.xls.ExcelFile;    import com.as3xls.xls.Sheet;    import mx.controls.dataGridClasses.DataGridColumn;    import mx.collections.ICollectionView;                 //this function called when the widget's configuration is loaded             private function init():void             {                          var query:Query = new Query();     query.where = "1=1";     query.returnGeometry = false;     query.outFields = ["STATE_NAME","CountiesPerState","TotalPopulationIn2000"];     queryTask.execute(query);             }        protected function queryTask_executeCompleteHandler(event:QueryEvent):void    {     var fields:Array = event.featureSet.fields;     var attributes:Array = event.featureSet.attributes;     dg.dataProvider = new ArrayCollection(attributes);    }        protected function esri_faultHandler(event:FaultEvent):void    {     Alert.show("Error: " + event.fault.faultString, "Error code: " + event.fault.faultCode);    }        protected function sumLabelFunction(item:Object, column:GridColumn):String    {     return numberFormatter.format(item["POP_SUM"]);    }        private function exportToExcel():void    {          var sheet:Sheet = new Sheet();     sheet.resize((dg.dataProvider as ICollectionView).length + 1, dg.columns.length + 1);     for(var i:int = 0; i < dg.columns.length; i++) {      var col:DataGridColumn = DataGridColumn(dg.columns);            //sheet.setCell(0, i, (dg.columns as DataGridColumn).headerText);           }          /*     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.name));      sheet.setCell(r+1,1,String(ItemDGDataProvider.salary));      sheet.setCell(r+1,2,String(ItemDGDataProvider.jDate));      sheet.setCell(r+1,3,String(ItemDGDataProvider.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");     */    }                 ]]>     </fx:Script>  <fx:Declarations>   <!-- Query Task -->   <esri:QueryTask id="queryTask"       executeComplete="queryTask_executeCompleteHandler(event)"       fault="esri_faultHandler(event)"       url="http://it.innovateteam.com/arcgis/rest/services/OTIS/TestWebServiceForQueries/MapServer/1"/>      <s:NumberFormatter id="numberFormatter" trailingZeros="false"/>     </fx:Declarations>          <viewer:WidgetTemplate id="frTable"                            width="500" height="500">         <viewer:layout>             <s:VerticalLayout horizontalAlign="center" verticalAlign="middle"/>         </viewer:layout>      <s:DataGrid id="dg"      width="400"      right="20" top="20">    <s:columns>     <s:ArrayList>      <s:GridColumn dataField="STATE_NAME" headerText="US State"/>      <s:GridColumn dataField="CountiesPerState" headerText="No. of counties"/>      <s:GridColumn dataField="TotalPopulationIn2000" headerText="Population(2000)"/>     </s:ArrayList>    </s:columns>   </s:DataGrid>   <mx:Button id="export_data" label="Export To Excel" click="exportToExcel();" width="100"/>      </viewer:WidgetTemplate> </viewer:BaseWidget> 

I have commented some code in the function but I guess it does't matter. I keep getting this error on call to exportToExcel function

ReferenceError: Error #1069: Property 0 not found on mx.collections.ArrayList and there is no default value.
at widgets.FrTable::FrTableWidget/exportToExcel()
at widgets.FrTable::FrTableWidget/__export_data_click()

When I trace dg.columns, I can see 3 objects there. Forum gurus please help me out. Thanks.

[RobertScheitlin__GISP · Accepted solution]

Deewendra,

   Here is the function from my eSearchWidget that exports the datagrid to a csv.

            private function exportCSV(dataGrid:DataGrid, csvSeparator:String=",", lineSeparator:String="\n", sortVector:Vector.<int>=null):String             {                 try{                     var data:String = "";                     var columnArray:ArrayList = dataGrid.columns as ArrayList;                     var columnCount:int = columnArray.length;                     var dataGridColumn:GridColumn;                     var header:String = "";                     var headerGenerated:Boolean = false;                     var dataProvider:ArrayCollection = ObjectUtil.copy(dataGrid.dataProvider) as ArrayCollection;                                          //Set the sorting                     var sortFields:Array = [];                     var dpSort:Sort = new Sort();                     for (var v:int = 0; v < sortVector.length; v++){                         dataGridColumn = columnArray.getItemAt(sortVector) as GridColumn;                         sortFields.push(dataGridColumn.sortField);                     }                     dpSort.fields = sortFields;                     dataProvider.sort = dpSort;                     dataProvider.refresh();                                          //trace(ObjectUtil.toString(dataProvider));                     var rowCount:int = dataProvider.length;                     var dp:Object = null;                     var cursor:IViewCursor = dataProvider.createCursor();                     var j:int = 0;                                          //loop through rows                     while (!cursor.afterLast){                         var object:Object = null;                         object = cursor.current;                         //loop through all columns for the row                         for(var i:int = 0; i < columnCount; i++){                             dataGridColumn = columnArray.getItemAt(i) as GridColumn;                             //Exclude column data which is invisible (hidden)                             if(!dataGridColumn.visible){                                 continue;                             }                             if(dataGridColumn.dataField == "icon"){                                 continue;                             }                             if((dataGridColumn.itemRenderer == HyperLinkColumn)||(dataGridColumn.itemRenderer == HyperLinkIconColumn)){                                 if(object[dataGridColumn.dataField] != null){                                     data += _textQualifier + object[dataGridColumn.dataField] + _textQualifier;                                 }else{                                     data += _textQualifier + _textQualifier;                                 }                             }else{                                 if(dataGridColumn.itemToLabel(object) != null){                                     data += _textQualifier + dataGridColumn.itemToLabel(object)+ _textQualifier;                                 }else{                                     data += _textQualifier + _textQualifier;                                 }                             }                             if(i < (columnCount -1)){                                 data += csvSeparator;                             }                             //generate header of CSV, only if it's not genereted yet                             if (!headerGenerated){                                 header += _textQualifier + dataGridColumn.headerText + _textQualifier;                                 if (i < columnCount - 1){                                     header += csvSeparator;                                 }                             }                         }                         headerGenerated = true;                         if (j < (rowCount - 1)){                             data += lineSeparator;                         }                         j++;                         cursor.moveNext ();                     }                     //set references to null:                     dataProvider = null;                     columnArray = null;                     dataGridColumn = null;                         }                 catch(error:Error){                     return null;                     Alert.show(error.message);                 }                 return (header + lineSeparator + data);             }

[DeewendraShrestha]

Hi Robert, thanks for the code fragment. I am pretty new to Flex so I cannot get my head around the different objects in Flex but your code did help me a lot, specially with getting the datagrid headers and data in ArrayList/ArrayCollection. I ended up using a very simplified version of your function :

private function exportToExcel():void
   {
    var sheet:Sheet = new Sheet();
    var dataProviderCollection:ArrayCollection = ObjectUtil.copy(dg.dataProvider) as ArrayCollection;
    var rowCount:int =  dataProviderCollection.length;
    var columnArray:ArrayList = dg.columns as ArrayList;
    var columnCount:int = columnArray.length;
    var dataGridColumn:GridColumn;
    
    sheet.resize(rowCount+columnCount,10);
    for(var r:int=0;r<columnCount;r++)
    {
     dataGridColumn = columnArray.getItemAt(r) as GridColumn;
     sheet.setCell(0,r,dataGridColumn.headerText);
    }
    
    
    for(var r:int=0;r<rowCount;r++)
    {
     sheet.setCell(r+1,0,String(dataProviderCollection.STATE_NAME));
     sheet.setCell(r+1,1,String(dataProviderCollection.CountiesPerState));
     sheet.setCell(r+1,2,String(dataProviderCollection.TotalPopulationIn2000));
     
    }
    
    var xls:ExcelFile = new ExcelFile();
    xls.sheets.addItem(sheet);
    
    var bytes: ByteArray = xls.saveToByteArray();
    var fr:FileReference = new FileReference();
    fr.save(bytes,"SampleExport.xls");
    
   }

Thanks again.
Regards,
Dee

[DanielSmith]

Hello,

Thank you very much for this thread. i am working on the same issue.

Can either of you please tell me what i need to import (or what lib I need) to be able to use the export to excel function?
At this point I am only missing the import for "new Sheet()" and "new ExcelFile()", probably in the same package (?)

Any help is greatly appreciated.

R.S. , i dug into you git repository but still could not find what i was looking for, perhaps you have a more splendid encapsulated function for this now?

[RobertScheitlin__GISP]

Daniel,

   I don't use the lib that dshrestha is using for my export. I try and keep it as simple as possible and not depend on third party libraries (if at all possible). The export that my eSearch uses exports to csv not excel. MS Excel (any version) understands csv as well as many other programs.

  Here is where you can get the as3XLS lib I believe he is using:

http://code.google.com/p/as3xls/downloads/list

[DanielSmith]

R.S.

Dude, you are the man... i found the lib but decided your version was much better. After some tinkering around i got it working just fine... many thanks for this code.