Grid-to-excel and excel-to-grid copy/paste in Sencha ExtJS 4.x


The article walks us through the steps required to implement the two-way copy-n-paste functionality between ExtJS Grid and Spreadsheet (MS Excel/OpenOffice Spreadsheet/Google Spreadsheet/etc.) using keyboard.

Problem statement:

Grid is a favorite UI component to render tabular data. Look-n-feel wise, an ExtJS Grid resembles Spreadsheet. However, Spreadsheet is much richer in functionality and convenient to use as compared to ExtJS grid. And, if you are involved in reviewing the existing records in your application (which is shown in an ExtJS grid) and add new records or massage the existing record and updated them back into the system, then it becomes a lot easier if the copy-n-paste is available across the grid and the spreadsheet. e.g. maintenance applications. In the absence of it, you will have to provide the a form panel to the user to do the data entry to add new records or edit an existing one, which makes the working with bulk data very tedious. In this article we would address this aspect by adding the copy-n-paste functionality where you can select one or more columns and rows in an ExtJS grid and copy them by pressing Ctrl-c on your keyboard and paste those copied record into the Spreadsheet program by pressing Ctrl-v and vice versa.

This articles explains you, how to implement  copy pasting grid-to-excel and vice-versa using ExtJS 4.x.

ExtJS 3.x version of the this is discussed here.

Pre-requisites:

  1. ExtJS 4.0 or higher
  2. You need to have an ExtJS sample project running in your machine. If you don’t have, visit this blog to know steps to set up.

How to do?

Step 1) Create Sencha ExtJS project using Sencha Cmd

1)  Create Sencha ExtJS project by following instructions provided in this blog.

2)  While creating project using Sencha Cmd, specify project name as GridExcelCopy.

Step 2)  Edit the main view file

Edit the main view file created after your project creation in app/view/Main.js as below.

Ext.define("GridExcelCopy.view.Main", {
 extend: 'Ext.Container',
 xtype : 'mainview',

 initComponent : function() {

    var me = this;

    var gRow = -1;   // define a variable gRow.
    Ext.apply(me, {

        items:[{
           //   grid panel code will be added here.
        }]
    });
     me.callParent(arguments);
 }
});

Step 3)  Define a model

Define a model in the main view file’s initComponent method (while component is being initialized).

    Ext.define('Country',{
       extend: 'Ext.data.Model',
       fields: [
          {name: 'id'},
          {name: 'continent', type: 'string'},
          {name: 'countryName', type: 'string'},
          {name: 'capital', type: 'string'},
          {name: 'countryCode', type: 'string'},
          {name: 'area', type: 'string'},
          {name: 'population', type: 'string'},
          {name: 'gdp', type: 'string'},
          {name: 'government', type: 'string'},
          {name: 'id', type: 'string'},
          {name: 'version', type: 'string'}
      ]
    });

Step 4)  Create a store

Create store after model has created.

var countryStore = Ext.create('Ext.data.Store', {

   storeId:'simpsonsStore',
   autoLoad : true,
   model : 'Country',
   proxy: {
      type: 'ajax',
      url: 'data/country.json',    // Give the filename that contains JSON data which matches defined fields.
      reader: {

         type: 'json',
         idProperty: 'id',
         root: 'data',
         totalProperty: 'total'
      }
   }
 });

Step 5) Create grid panel view

1)  Create a grid panel view by adding items in the view file. Add these items as childs to the main container. Get the created store while component is being initialized and refer that store to grid panel.

      items:[{
        xtype: 'gridpanel',
        layout : 'fit',
        height: 650,
        forceFit : true,
        title: 'Grid-to-excel and excel-to-grid copy-paste in ExtJS 4.x',
        id: 'grid-pnl',
        store: countryStore,
        layout : 'fit',
        multiSelect : true,   // Allows multiple row selection
        columns: [

             { text: 'continent', dataIndex: 'continent' },
             { text: 'countryName', dataIndex: 'countryName'},
             { text: 'capital', dataIndex: 'capital' },
             { text: 'countryCode', dataIndex: 'countryCode' },
             { text: 'area', dataIndex: 'area' },
             { text: 'population', dataIndex: 'population' },
             { text: 'gdp', dataIndex: 'gdp' },
             { text: 'government', dataIndex: 'government' },
             { text: 'version', dataIndex: 'version' },

       ]
    }]

Run the application in browser. You should be able to see the data rendered into grid panel.

Image

2)  Add an empty record to the countryStore after store loading completes. Add load listener for countryStore.

listeners: {

   load: function(store,records) {

      var rowRec = Ext.create('Country',{});
      this.add(rowRec);
      me.storeInitialCount = records.length;
   }
 }

Run application in browser. You can see the empty row added to the grid at last.Image

Step 6) Implement copy functionality from grid to excel

1)  Add viewready listener to the grid panel and implement functionality for copy (Ctrl+c).

Register key mapping of Ctrl+c in viewready. Get the selected records and pass them to getCsvDataFromRecs method, which handles conversion of data.

viewready: function( grid ) {

 var map = new Ext.KeyMap(grid.getEl(), 
 [{
   key: "c",
   ctrl:true,
   fn: function(keyCode, e) {

      var recs = grid.getSelectionModel().getSelection();

      if (recs && recs.length != 0) {

           var clipText = grid.getCsvDataFromRecs(recs);

           var ta = document.createElement('textarea');

           ta.id = 'cliparea';
           ta.style.position = 'absolute';
           ta.style.left = '-1000px';
           ta.style.top = '-1000px';
           ta.value = clipText;
           document.body.appendChild(ta);
           document.designMode = 'off';

           ta.focus();
           ta.select();

           setTimeout(function(){

               document.body.removeChild(ta);

           }, 100);
     }
   }
 }
]);
}

2)  Place implementation code for getCsvDataFromRecs method.

Find the index of each row using store.find method and check for currRow value, which handles dividing items with ‘\n’.

getCsvDataFromRecs: function(records) {
   var clipText = '';
   var currRow = countryStore.find('id',records[0].data.id);
  for (var i=0; i<records.length; i++) {

     var index = countryStore.find('id',records[i].data.id);

     var r = index;

     var rec = records[i];
     var cv = this.initialConfig.columns;

     for(var j=0; j < cv.length;j++) {

        var val = rec.data[cv[j].dataIndex];

        if (r === currRow) {

             clipText = clipText.concat(val,"\t");

        } else {

             currRow = r;

            clipText = clipText.concat("\n", val, "\t");

        }
    }

  }

 return clipText;
}

Run the application in browser and select multiple rows

Image

Copy the rows by pressing Ctrl+c , open a spreadsheet and paste by pressing Ctrl+v. The selected rows will be pasted in the order of selection.

Image

3)  Implement functionality for pasting rows from Excel sheet into grid panel.

Register key mapping of Ctrl+v. Add code as an item in keymap array after Ctrl+c implementation.

{

 key: "v",
 ctrl:true,
   fn: function() {

     var ta = document.createElement('textarea');
     ta.id = 'cliparea';

     ta.style.position = 'absolute';
     ta.style.left = '-1000px';
     ta.style.top = '-1000px';
     ta.value = '';

     document.body.appendChild(ta);
     document.designMode = 'off';

     setTimeout(function(){

         Ext.getCmp('grid-pnl').getRecsFromCsv(grid, ta);

     }, 100);

     ta.focus();
     ta.select();
 }
}

4)  Place implementation code for getRecsFromCsv

getRecsFromCsv: function(grid, ta) {

  document.body.removeChild(ta);
  var del = '';

  if (ta.value.indexOf("\r\n")) {

      del = "\r\n";
  } else if (ta.value.indexOf("\n")) {

      del = "\n"
  }
  var rows = ta.value.split("\n");

  for (var i=0; i<rows.length; i++) {

     var cols = rows[i].split("\t");

     var columns = grid.initialConfig.columns;

         if (cols.length > columns.length)

         cols = cols.slice(0, columns.length-1)

         if (gRow === -1 ) {

             Ext.Msg.alert('Select a cell before pasting and try again!');

           return;

         }

         var cfg = {};

         var tmpRec = countryStore.getAt(gRow);

         var existing = false;

         if ( tmpRec ) {

            cfg = tmpRec.data;

            existing = true;

         }

         var l = cols.length;

         if ( cols.length > columns.length )

              l = columns.length;

         for (var j=0; j<l; j++) {

           if (cols[j] === "") {

              return;

           }

           cfg[columns[j].dataIndex] = cols[j];
         }

      me.storeInitialCount++;

      cfg['id'] = me.storeInitialCount;

      var tmpRow = gRow;

      grid.getSelectionModel().clearSelections(true);

      var tmpRec = Ext.create('Country',cfg);

      if (existing)

         countryStore.removeAt(tmpRow);

      countryStore.insert(tmpRow, tmpRec);

      gRow = ++tmpRow;

 }
   if (gRow === countryStore.getCount()) {

    var RowRec = Ext.create('Country',{});
    countryStore.add(RowRec);
   }
   gRow = 0;
}

5)  Edit the previously copied rows and copy them.

Image

6) Goto grid panel view, select last empty row and paste the copied rows using Ctrl+v. Edited rows will be added from bottom.

Image

7)  Paste the rows at any row of grid, which is already having row content.

Image

References:

  1. https://github.com/walkingtree/sample-projects/tree/master/ExtJS/GridExcelCopyExt4
  2. http://wtcindia.wordpress.com/2013/03/16/excel-to-grid-and-grid-to-excel-copypaste/

At Walking Tree we practice and recommend Sencha Products like ExtJS and Sencha Touch to build amazing web / touch apps. In case you are looking for training or professional assistance, contact us by visiting our website.

Tagged with: , , , , , , ,
Posted in Sencha ExtJS
4 comments on “Grid-to-excel and excel-to-grid copy/paste in Sencha ExtJS 4.x
  1. elaine says:

    Thanks so much for your post, although your code has some bugs, but it is working after fixing those minor bugs!

    • Alok Ranjan says:

      Hi Elaine
      We are happy that you found this helpful.

      Can you mention the ExtJS version that you are using. Also, please list out the issues faced by you and the fix that you eventually came up with. This will help other developers.

      Thanks & Regards
      Alok

  2. vinodkannekanti says:

    Nice stuff…Thank for the article…

  3. indu says:

    copy paste in grid with locked column not working. could you please suggest what to do on that case

    Thanks,
    indu :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 344 other followers

%d bloggers like this: