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:
- ExtJS 4.0 or higher
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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.
2) Add an empty record to the countryStore after store loading completes. Add load listener for countryStore.
1 2 3 4 5 6 7 8 9 |
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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’.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
{ 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
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.
6) Goto grid panel view, select last empty row and paste the copied rows using Ctrl+v. Edited rows will be added from bottom.
7) Paste the rows at any row of grid, which is already having row content.
References:
- https://github.com/walkingtree/sample-projects/tree/master/ExtJS/GridExcelCopyExt4
- 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.
