5 Replies Latest reply on Apr 19, 2007 5:44 AM by rkschulz

    Using Clipbeoard to Export data from datagrid to Excel

    Like2Flex
      I got this code on a forum, tried it, but it does not respond.

      Can anybody tell me what is missing in it.

      I am calling this function on click of a button.


      public static function copyGridData(grd_orders:DataGrid):void {
      var outputA:Array = new Array();

      // header
      var headerA:Array = new Array();
      var columns:Array = grd_orders.columns;
      for each (var col:DataGridColumn in columns ) {
      headerA.push(col.headerText);
      }
      outputA.push(headerA.join("\t"));

      // data
      var data:XMLListCollection = XMLListCollection(grd_orders.dataProvider);
      for each (var r:XML in data){
      var rowA:Array = new Array();
      for each (var col2:DataGridColumn in columns) {
      if (col2.dataField != null){
      rowA.push(r.attribute(col2.dataField.substr(1,col2.dataField.length-1)));
      } else {
      rowA.push("");
      }
      }
      }
      outputA.push(rowA.join("\t"));

      Alert.show(new String("test"));

      System.setClipboard(outputA.join("\n"));
      Alert.show("Data copied to the clipboard.\nOpen target application (e.g. Excel) and paste data.", "Data Copy");
      }


        • 1. Re: Using Clipbeoard to Export data from datagrid to Excel
          dolphi23
          Hi, I'm trying to do this as well. Can someone please help

          thx
          • 2. Re: Using Clipbeoard to Export data from datagrid to Excel
            rddone
            The main problem with the example was getting null for the 'data' variable assignment, so I kept modifying it until it worked. Here is what I ended up with:

            public static function copyGridData(grd_orders:DataGrid):void {
            var outputA:Array = new Array();

            // header
            var headerA:Array = new Array(); //Col Names (For creating the header row)
            var headerB:Array = new Array(); //Col Names (For getting column values)
            var columns:Array = grd_orders.columns;
            for each (var col:DataGridColumn in columns )
            {
            headerA.push(col.headerText);
            headerB.push(col.dataField); //Save for later
            }
            outputA.push(headerA.join("\t"));

            // data
            var i:int=0;
            var j:int=0;
            var data:ArrayCollection = ArrayCollection(grd_orders.dataProvider);
            if(data==null)
            {
            Alert.show("No report data to copy, Please Create Report first","Data Copy Error");
            return;
            }
            var rowA:Array;
            rowA = data.toArray();
            var colName:String = "";
            var colValue:String = "";

            //Do stuff for each row of data
            for(i=0;i<rowA.length;i++)
            {
            var rowB:Array = new Array(); //Yes, want to destroy and create for every loop
            //Do stuff for each column
            for(j=0;j<headerB.length;j++)
            {
            colName = headerB[j].toString(); //Get the name of the column
            colValue = rowA [colName]; //Get tha value of the named column (has to be [][], rowA.colname not work)
            rowB.push(colValue); //Add the value to the array
            }
            outputA.push(rowB.join("\t"));
            }

            System.setClipboard(outputA.join("\n"));
            Alert.show("Data copied to the clipboard.\nOpen target application (e.g. Excel) and paste data.", "Data Copy Success");
            }
            • 3. Re: Using Clipbeoard to Export data from datagrid to Excel
              dolphi23 Level 1
              Many thanks rddone. I couldn't quite get it to work for my example, but now I have something to work from

              thx again
              • 4. Re: Using Clipbeoard to Export data from datagrid to Excel
                kri_gopi
                Hi,
                I making use of the following code for communicating with flex datagrid and excel,
                when I click the button , I am getting the following error:

                TTypeError: Error #1034: Type Coercion failed: cannot convert flash.events::MouseEvent@24286a1 to mx.controls.DataGrid

                My code is like this:

                The button tag is

                <mx:Button label="Click Me" id="b1" x="150" y="500"/>

                The scripts are :


                private function createListener():void {
                b1.addEventListener(MouseEvent.CLICK, copyGridData, false, 0);
                }

                public static function copyGridData(grd_orders:DataGrid):void {
                var outputA:Array = new Array();

                /* Excel wants slash t as column separators and slash n for new lines */

                // header
                var headerA:Array = new Array(); //Col Names (For creating the header row)
                var headerB:Array = new Array(); //Col Names (For getting column values)
                var columns:Array = grd_orders.columns;
                for each (var col:DataGridColumn in columns )
                {
                headerA.push(col.headerText);
                headerB.push(col.dataField); //Save for later
                }
                outputA.push(headerA.join("\t"));

                // data
                var i:int=0;
                var j:int=0;
                var data:ArrayCollection = ArrayCollection(grd_orders.dataProvider);
                if(data==null)
                {
                Alert.show("No report data to copy, Please Create Report first","Data Copy Error");
                return;
                }
                var rowA:Array;
                rowA = data.toArray();
                var colName:String = "";
                var colValue:String = "";

                //Do stuff for each row of data
                for(i=0;i<rowA.length;i++)
                {
                var rowB:Array = new Array(); //Yes, want to destroy and create for every loop
                //Do stuff for each column
                for(j=0;j<headerB.length;j++)
                {
                colName = headerB[j].toString(); //Get the name of the column
                colValue = rowA[colName]; //Get tha value of the named column (has to be [][], rowA.colname not work)
                rowB.push(colValue); //Add the value to the array
                }
                outputA.push(rowB.join("\t"));
                }


                System.setClipboard(outputA.join("\n"));
                Alert.show("Data copied to the clipboard.\nOpen target application (e.g. Excel) and paste data.", "Data Copy Success");
                }

                Could you please solve this.
                • 5. Re: Using Clipbeoard to Export data from datagrid to Excel
                  rkschulz Level 1
                  I posted that original code. I believe I did say it probably doesn't work out of the box (we use a custom data provider and some custom header renderers to make flex do what we want it to do).

                  The problem with the code (and the error message) is that the copyGridData method needs to be called with the source dataGrid as an argument, but you just added it as a listener to the button click which calls it with some sort of event object, hence it blows up with a type error at runtime (but hey, dynamic typing is the way of the future apparently, so these sorts of errors are the future ;-).

                  Anyway, you may be better of to starts with System.setClipboard and pass in tab-separated values and see them turning up in Excel and the hook it up with your grid in a way that makes sense for your grid data structure.

                  R.