Excel-to-Grid and Grid-to-Excel Copy/Paste


Grid-toExcel and Excel-to-Grid Copy/Paste

 

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.

In addition to it, the article will also show how you can carry out data validation to accept or reject a paste on a grid panel.

Pre-requisites

Working knowledge of JavaScript, HTML, CSS, and ExtJS 3.x

System Requirements

Sencha ExtJS 3.4, Sencha ExtJS 3.4 compatible browser

How to do it

Following are the steps:

Step 1: Define a record

var dummyRec = {continent: '',

countryName: '',

capital: '',

countryCode: '',

area: '',

population: null,

gdp: null,

government: '',

id: null,

version: null};

Step 2: Define two variables to keep track of the row and column when the user selects a cell for copy-n-paste. Default value is -1 to detect whether a cell has been selected by the user or not.

var gRow = -1;

var gCol = -1;

Step 3: While instantiating the grid store, set the handler for the load event where we add a dummy empty row to the grid. This row is used to paste data from the Spreadsheet in order to append the data to the grid.

var store = new Ext.data.Store({

autoLoad : true,

url : ‘getgriddata.php’, //your url to get the grid data

reader: new Ext.data.JsonReader({

idProperty: 'id',

root: 'data',

fields: data.fields,

totalProperty: 'total'

}),

listeners: {

load: function() {

//adding dummy record

var RowRec = Ext.data.Record.create(data.fields);

store.add(new RowRec(dummyRec));

}

});

Step 4: Create the grid panel with MultiCellSelectionModel as the selModel so that we can select more than one grid cells

selModel: new Ext.ux.MultiCellSelectionModel({}),

Step 5: Handle cellclick to set the gRow and gCol as this will be the starting cell for copying and starting cell for paste operation

Step 6: Implement the viewready event handler to register the key mapping of Ctrl-c on the grid and what shall be done when Ctrl-c is pressed. The handler code

  • converts the selected records from the grid into CSV (Comma Separated Values), which is more suitable for the Spreadsheet program to consume
  • creates a text field, which is not visible to the user (rendered on -1000, -1000 co-ordinate)
  • sets the CSV data as the text field value and (4) moves the focus to the text field where is selects the complete string entered into the text field, which is our CVS data
var map = new Ext.KeyMap(grid.getEl(), [{

key: "c",

ctrl:true,

fn: function(keyCode, e) {

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

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

var clipText = Ext.getCmp('grid-pnl').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);

}

}

},

Step 7: We also implement the key mapping for Ctrl-v on the grid to handle the paste functionality. The handler –

  • creates a text field, which is not visible to the user (rendered on -1000, -1000 co-ordinate)
  • copies the clipboard data to the text field
  • converts the text field value, which is in CSV format, into the record format that is more suitable for the grid store to consume
, {

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();

}

}]

getCsvDataFromRecs and getRecsFromCsv are two helper methods that take care of the data conversion. Following is the complete grid panel code:

var grid = new Ext.grid.GridPanel({

xtype : 'grid',

id: 'grid-pnl',

columns: data.columns,

store: store,

selModel: new Ext.ux.MultiCellSelectionModel({}),

listeners: {

cellclick: function(grid, row, col) {

gRow = row;

gCol = col;

},

viewready: function() {

var map = new Ext.KeyMap(grid.getEl(), [{

key: "c",

ctrl:true,

fn: function(keyCode, e) {

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

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

var clipText = Ext.getCmp('grid-pnl').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);

}

}

}, {

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();

}

}]);

}

},

getCsvDataFromRecs: function(cells) {

var clipText = '';

var currRow = cells[0][0];

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

var r = cells[i][0];

var c = cells[i][1];

var cv = this.initialConfig.columns[c].dataIndex;

var rec = this.getStore().getAt(r);

var val = rec.data[cv];

if (r === currRow) {

if (i === cells.length-1)

clipText = clipText.concat(val);

else

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

} else {

currRow = r;

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

}

}

return clipText;

},

getRecsFromCsv: function(grid, ta) {

document.body.removeChild(ta);

var RowRec = Ext.data.Record.create(data.fields);

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 || gCol === -1) {

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

return;

}

var cfg = {continent: '',

countryName: '',

capital: '',

countryCode: '',

area: '',

population: 0,

gdp: 0,

government: '',

id: null,

version: null};

var tmpRec = store.getAt(gRow);

var existing = false;

if (tmpRec) {

cfg = tmpRec.data;

existing = true;

}

var l = cols.length;

if (gCol+cols.length > columns.length)

l = columns.length - gCol;

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

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

return;

}

if (columns[j].dataIndex === 'population') {

if (isNaN(cols[j-gCol]*1) || typeof (cols[j-gCol]*1) != 'number') {

Ext.Msg.alert('ERROR', 'population is not a number. Skipping rest of the records!');

return;

}

}

if (columns[j].dataIndex === 'gdp') {

if (isNaN(cols[j-gCol]*1) || typeof (cols[j-gCol]*1) != 'number') {

Ext.Msg.alert('ERROR', 'gdp is not a number. Skipping rest of the records!');

return;

}

}

cfg[columns[j].dataIndex] = cols[j-gCol];

}

var tmpRow = gRow;

var tmpCol = gCol;

grid.getSelectionModel().clearSelections(true);

var tmpRec = new RowRec(cfg);

if (existing)

store.removeAt(tmpRow);

store.insert(tmpRow, tmpRec);

gRow = ++tmpRow;

gCol = tmpCol;

}

if (gRow === store.getCount()) {

var RowRec = Ext.data.Record.create(data.fields);

store.add(new RowRec({continent: '',

countryName: '',

capital: '',

countryCode: '',

area: '',

population: null,

gdp: null,

government: '',

id: null,

version: null}));

}

gRow = 0;

gCol = 0;

}

});

Summary

In this article we saw how we could implement the copy-paste using a hidden textfield, which helped us to move the data across the ExtJS grid and the Spreadsheet via the system clipboard. Using this mechanism, we can implement any copy-paste across different types of application.

In the next article, I will be wrapping the functionality in the form of a component. Also, I will be creating a similar component for ExtJS 4.x.

References

http://www.sencha.com/forum/showthread.php?53118-Multiple-Cells-Selection-Model

Co-founder of Walking Tree, Speaker, Sencha Trainer, Author of Sencha Charts Essentials, Sencha Touch Cookbook, Sencha MVC Architecture, and ADempiere Cookbook.

Tagged with: , , , , ,
Posted in Sencha ExtJS

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

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Do More. With Sencha.

Recent Publication
%d bloggers like this: