PDA

View Full Version : Pasting data from Excel to the Grid



gav
18 Oct 2006, 10:38 AM
First off, very impressive work!

I have a hopefully straightforward question, but haven't seen anything yet on the site or the forums that does this. We have an app we are trying to build to replace something that people regularly do with Excel. The quickest way we are going to be able to adapt our new users to this is by having functionality that would allow them to copy a row and column set (say, 3 highlighted rows of 5 columns in width) and hit paste in the first square in the browser window and have all the appropriate elements populated.

Things that I know so far:
1) The data in the clipboard is tab-delimited for the columns and CR/LF-delimited for the rows.
2) Pasting this data into a regular HTML text field just shows me the 5 columns of the first row with tabs between them, and throws away the other rows.
3) Using the example editable grid you have posted on one of the blog pages gave the same results as (2) in the field I had currently selected as editable.
4) I am pretty sure that it is possible in the scope of the browser, since the spreadsheet application at docs.google.com does do what I expected (fills out the 15 cells in 3 rows with the data from Excel).

So, my questions probably come down to:
1) Does this functionality already exist, and where should I read about it?
2) If it doesn't exist, is it something that an event handler (at the keyboard or cell level) could handle without too much evilness?

I have no problem with hacking around, but I didn't want to get too far into it if there was some obvious reason why this wouldn't work.

Thanks,
George

jack.slocum
18 Oct 2006, 11:01 AM
It's not currently built-in. While it could probably be done in the grid, in order to maintain validation support you'd probably want to handle the event yourself.

The way you could implement something like this (cross-browser) very easy would be to create a custom validation function and assign it to the editor fields in your grid. Then, when the user pastes a keypress would be triggered and your validation function would get called with the new data. You could look at the value, see if it looks like excel data, if it does then parse it and update your data model. You'd also want to return false so that the data is cleared. This is obviously completely hypothetical, but I don't see why it wouldn't work and it could probably be done in less than 20 lines of code.

crankycoder
7 May 2007, 5:29 AM
Hi all,

I'm trying to implement cut/paste from Excel into a datagrid using a custom validator.

Here's what I have working so far - I've got a grid that uses a customized subclass of the TextArea field. The CustomTextArea has a customized validator which parses input text for tab delimited line data and then packages of the data into a MemoryProxy.

Once that happens, I replaced the datasource of the grid and tell the grid to reload like this:


var ds = new Ext.data.Store({
proxy: new Ext.data.MemoryProxy(rows),
reader: new Ext.data.ArrayReader({}, [
... column definitions here ...
])
});

grid.dataSource = ds;
grid.dataSource.load();


So now on to my problem - the grid needs to be re-rendered as the grid is displaying stale data. As I tab through the individual cells on the grid - I can see the proper values in the underlying datasource, but once I leave the cell - the grid reverts back to the old value.

So... I *think* what I need to do is to trap the afteredit event and then tell the grid to render again, but I seem to be doing something simple and wrong.

Here's my code snippet where I define my grid object and try to subscribe to the afteredit.

When running in Firebug - the javascript never seems to enter the afteGridEdit function.

Any tips?



// create the editor grid
var grid = new Ext.grid.EditorGrid('MyDataGrid', {
ds: ds,
cm: cm,
autoSizeColumns: true,
autoSizeHeaders: true,
enableColumnMove: true,
stripeRows: true,
enableColLock:false
});

var afterGridEdit = function(param) // <- this never seems to be called
{
// grid, record, field, value, originalValue, row, column)
grid.render();
}

grid.on("afteredit", afterGridEdit); // This event never seems to fire

crankycoder
7 May 2007, 7:39 AM
Ok, so I've dug through the EditorGrid and I've noticed that the onEditComplete method fires the "afteredit" event, and it only does so for validated inputs.

After changing my custom textarea to return 'true' after parsing all the excel data in the validator, my grid now renders properly!

Yay!

There *is* a remaining problem where I can't seem to call grid.render() without triggering an error deep in the ext-all-debug.js script.

The odd part is that if I put a breakpoint into my code in the afteredit callback, and then manually call grid.render() in firebug, I don't get an exception.

The error I'm getting is:

ext-all-debug.js (line 25173)
[Break on this error] return source.rows[rowIndex].childNodes[colIndex];
source.rows[rowIndex] has no properties

I *thought* that this had to do with a selection rows being active still, but my validator in my custom textarea clears the selection in the grid.

My validator just parses all the incoming data into an array called 'row' and then does this:



if (rows.length > 1)
{
var selection_model = grid.getSelectionModel();
selection_model.clearSelections();
// Bind the new set of rows into a MemoryProxy
// to populate the datagrid.
var ds = new Ext.data.Store({
proxy: new Ext.data.MemoryProxy(rows),
reader: new Ext.data.ArrayReader({}, [
%(json_mapping)s
])
});

grid.dataSource = ds;
grid.dataSource.reload();
}

// This part is not entirely obvious - when the validator
// returns true, an afteredit event is fired.

// Validators that return false will *not* fire the afteredit
// event
return true;
...
...
...

// Basic grid with an afteredit listener bound in

// create the editor grid
var grid = new Ext.grid.EditorGrid('test', {
ds: ds,
cm: cm,
autoSizeColumns: true,
autoSizeHeaders: true,
enableColumnMove: true,
stripeRows: true,
enableColLock:false
});

grid.addListener('afteredit', function(){
// To see this work in Firebug, comment out the render() call, uncomment the
// alert, and set a breakpoint at the alert in Firebug. When the breakpoint is hit
// after the paste, manually invoke grid.render(). Watch as no errors get triggered.
// weird.


// alert ('pause here');
grid.render();
});



Ideas?

clynn
27 Jul 2007, 9:01 AM
Hey CrankyCoder,

I am new to this board (you get my first post =) ) and I was wondering if you found a solution to your error at source.rows[rowIndex]....
In my app, I have a search menu with a couple grids. The search populates Grid 1, and upon selection of a row in Grid 1, Grid 2 populates through a DB call. When I select a row in Grid 2, the error occurs. Also, after another search and selecting a row in Grid 1, the error occurs. The functionality is fine but Firebug complains and I don't like that.
I added a listener to Grid 1 as follows:
<code>

grid_group_list.addListener("rowclick", function (grid, rowIndex, colIndex, e) {

var temp = ds_group_list.getAt(rowIndex);

var tempcm = cm_group_list.getDataIndex(0);
var detailsurl = 'http://localhost:8080/details.do?groupname=' + temp.get(tempcm);

ds_group_details = new Ext.data.Store({
proxy: new Ext.data.HttpProxy( {url: detailsurl }),

reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total',
id: 'id'
},
[
{name: 'id', mapping: 'id', type: 'int'},
{name: 'num', type: 'string'},
{name: 'desc', type: 'string'},
{name: 'value'}
])

});
ds_group_details.load();
var cm_group_details = new Ext.grid.ColumnModel
([ {header: "Name", dataIndex: 'num', sortable: true, width: 85},
{header: "Type",dataIndex: 'desc', sortable: true, width: 100},
{header: "Market Value",dataIndex: 'value', sortable: true, width: 100}
]);

grid_group_details = new Ext.grid.Grid('group_details', {
ds: ds_group_details,
cm: cm_group_details,

});

grid_group_details.render();

});
</code>

Thanks for your time.

Cheers

clynn
27 Jul 2007, 9:32 AM
To whoever might be interested.

I figured out how to solve this issue for my problem and that was to destroy ( grid.destroy() ) my grid and then repopulate (render) the grid every time. I am not sure if this is the most efficient solution but it deals with the issue. A good thread that addresses multiple grids and where I got my inspiration is by cluettr at http://extjs.com/forum/showthread.php?t=9950&highlight=grid+rowclick

Cheers

crankycoder
27 Jul 2007, 10:23 AM
I found I had to do the same thing as you. Destroy and recreate the entire grid.

My bigger problem ended up being render time in IE6 though. I found a couple timing issues where I ended up using the defer() function to make everything work in IE, but even then - IE tended to crash all the time.

The final 'solution' to this was dropping ExtJS entirely and just reverting to a pure YUI datatable solution.

vic

knighto
23 Jun 2008, 6:04 AM
It's not currently built-in. While it could probably be done in the grid, in order to maintain validation support you'd probably want to handle the event yourself.

The way you could implement something like this (cross-browser) very easy would be to create a custom validation function and assign it to the editor fields in your grid. Then, when the user pastes a keypress would be triggered and your validation function would get called with the new data. You could look at the value, see if it looks like excel data, if it does then parse it and update your data model. You'd also want to return false so that the data is cleared. This is obviously completely hypothetical, but I don't see why it wouldn't work and it could probably be done in less than 20 lines of code.

Complete Newbie to extJs - Loving it though.

I have a Editor Grid and I want to be able to copy individual columns from an Excel spreadsheet straight on to coresponding columns on my Editor Grid.

The raw data looks as follow when i cut it from my excel spreadsheet :

Name 1 <CR><LF>
Name 2 <CR><LF>
....

When I paste into the first cell on the destination column (enabled with a TextField editor) of the Editor Grid, the original text has been stripped to just "Name 1", all subsequent lines have been removed....

Ideally I want to be able to capture the original Text and paste it in to the selected and subsequent cells in my column.

Can anyone shed any light on how to capture and manipulate the original text ? I am not wanting a coded solution, just a nudge in the right direction :-)

Thanks in advance !