Paste from Excel to SlickGrid

CellExternalCopyManager is a SlickGrid plugin to copy/paste data from/to MS Excel (or compatible).

Slickgrid is a great open source javascript widget which displays tabular data very fast.

However, it is impossible to copy a range of cells in, lets say, Microsoft Excel, and paste it into a SlickGrid displayed in a browser.
The reverse is not possible either: you can't copy a range of cells in a SlickGrid and paste it into Excel. This is also true for other spreadsheet applications such as OpenOffice-Spreadsheet or even web based google docs.

SlickGrid repository contains a spreadsheet example demonstrating copy/paste cells feature (called CellCopyManager), but it can only copy/paste data from the same SlickGrid instance. It does not allow copy/pasting data from/to an external spreadsheet, not even to another webpage containing a SlickGrid.

At Nereo, we really needed this feature in our leave management application, since our users had to deal a lot with tabular data that they need to copy from a classical spreadsheet application.

Fortunately, the modulable nature (and the very well-written codebase) of the SlickGrid widget allows us to create our own copy manager. And that's what we did. We created CellExternalCopyManager plugin (named after CellCopyManager) and we use it in our application. We wanted to share it with SlickGrid community and release it under open source licence as well.
Here is the online demo. This is basically similar to the spreadsheet example that comes with SlickGrid, with the difference of using CellExternalCopyManager instead of CellCopyManager.
You can paste data copied in the clipboard from a native application such as MS-Excel, select a cell in SlickGrid and press CTRL-V to paste it into the grid.
Here is the code. This is the same repo than SlickGrid with the addition of slick.cellexternalcopymanager.js and its demonstration in example-excel-compatible-spreadsheet.html.
We were looking for a cross-browser, platform independent solution.

As you may know, in the web dev, it is not possible to access clipboard data using javascript. Actually, you do have access to the clipboard in IE, but an IE-only solution is not acceptable. There are also some tricks using a flash component which is allowed to access to the clipboard. In the era of HTML5, it is not acceptable either.

So how we do it?

Given the aim and the constraints, you don't have a lot of choice. You have to use only javascript, the browser's classical event handling and the dom. I will explain the trick for the paste feature, and it is very similar for the copy. Basically the idea is:
  • Handle keystrokes to detect Ctrl+V
  • In the handler, before giving the control back to the browser, dynamically create a textarea in the document
  • Set the focus on this textarea
  • Give back the control to the browser so it pastes the tabular data into the textarea
  • Read the content of the textarea
  • The textarea contains a text-serialized version of the copied data, analyse it and fill the grid accordingly


First of all, we handle keystrokes and detect copy/paste shortcuts.
function init(grid) {
  _grid = grid;
  _grid.onKeyDown.subscribe(handleKeyDown);
}

function handleKeyDown(e, args) {
  if (e.which == 86 && (e.ctrlKey || e.metaKey)) {    // CTRL + V
      ...
  }
}
                
Then, we create an invisible textarea that gets the focus:
var ta = document.createElement('textarea');
ta.style.position = 'absolute';
ta.style.left = '-1000px';
ta.style.top = '-1000px';
document.body.appendChild(ta);
document.designMode = 'off';
ta.focus();
                
And then, we return 'false' to let the browser do its job, ie pasting the data into the textarea. But before, we put a timer to read the content of the textarea after, say, 100 ms. This setTimeout is the reason why we call this solution a trick. It is obviously not perfect, the browser can take longer than 100ms to paste the data in the textarea. However, it is a pragmatical approach and still better than a IE-only or flash based solution.
var ta = document.createElement('textarea');
ta.style.position = 'absolute';
ta.style.left = '-1000px';
ta.style.top = '-1000px';
document.body.appendChild(ta);
document.designMode = 'off';
ta.focus();

setTimeout(function(){
    var clipText = ta.value;
    ...
}, 100);

return false;
                
So here we are, we have a string containing something that corresponds to the copied data from a native spreadsheet application. It actually contains the content of all the copied cells with tab and eol separators. Columns are split with a tab character ( "\t" chr(9) ) and lines are split with an end of line character ("\n" chr(10)).

All we have to do with clipText is to split it into arrays, first with "\n" character to get lines, and for each line split with "\t" to get cells.
We invite you to have a look on the code for more details. Any comment is welcome.
We obtained browser and platform independent way to copy/paste tabular data for the incredible SlickGrid.
The solution consists on a javascript trick, it is not a perfectly clean solution but we believe that that's the best one can do without a native plugin.