27

Using Javascript, how do I create an HTML table that can "accept" numeric matrix data from excel (or google spreadsheet), via "copy" in the spreadsheet and then "paste" into the table in the browser.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
perrinmeyer
  • 333
  • 1
  • 3
  • 6

4 Answers4

26

This would only work reliably on IE since Firefox (and likely others) don't allow access to the clipboard without specifically allowing it; the earlier suggestion of pasting into a textarea first might work better than this.

When you copy from a spreadsheet, generally the cells are separated with a tab (chr9) and the rows with a CR (chr13). This script converts the clipboard into a 2D array and then builds a table from it. Not too elegant but it seems to work copying data out of Excel.

<html>
<head>
<script language="javascript">
function clip() {

    // get the clipboard text

    var clipText = window.clipboardData.getData('Text');

    // split into rows

    clipRows = clipText.split(String.fromCharCode(13));

    // split rows into columns

    for (i=0; i<clipRows.length; i++) {
        clipRows[i] = clipRows[i].split(String.fromCharCode(9));
    }


    // write out in a table

    newTable = document.createElement("table")
    newTable.border = 1;
    for (i=0; i<clipRows.length - 1; i++) {

        newRow = newTable.insertRow();

        for (j=0; j<clipRows[i].length; j++) {
            newCell = newRow.insertCell();
            if (clipRows[i][j].length == 0) {
                newCell.innerText = ' ';
            }
            else {
                newCell.innerText = clipRows[i][j];
            }
        }
    }

    document.body.appendChild(newTable);
}
</script>
</head>
<body>
<input type="button" onclick="clip()">
</body>
</html>
Paul Abbott
  • 7,065
  • 3
  • 27
  • 45
  • 2
    I had to use `txt.split("\n");` instead of `txt.split(String.fromCharCode(13));` – Peter Jan 03 '14 at 18:50
  • I had to use split("\n") for firefox and split(String.fromCharCode(13)) for Chrome – vonwolf Jan 20 '16 at 18:36
  • So has anyone tested splitting both 13 & 10? (CR & LF) – Jason K. Sep 10 '16 at 20:09
  • 1
    Wouldn't this break if one of the cells has a `\r\n` in it? – rclai Apr 20 '17 at 14:33
  • @rclai Yes, when cell contains \n in it, this \n logic fails, but fromCharCode(13) not working on macOS, any probable/alternate solutions – MeVimalkumar Aug 17 '18 at 07:11
  • 1
    To get this working on windows and mac I use the following - `let rows = text.split(/\n|\r/).filter(row => row.length).map(row=> row.split(/\t/))` The filter gets rid of blank rows caused by both \n + \r being used in windows. – Cris Ward Sep 17 '20 at 13:00
  • This didn't work for me on Chrome + Windows 10. It just lumped all the data in a single row of the `clipRows` array. I finally decided to stop trying to do this on my own and use a free tool built for this - https://handsontable.com/ – Chris Mar 23 '21 at 18:20
6

Here is the javascript code I created (based on the helpful answers). I'm new to javascript, so I'm sure there is much better way to do this, but it seems to work... The goal is to "paste" two columns of numerical data into the text area from a spreadsheet (I've tried both excel and google spreadsheet) and create floating point vectors "xf" and "yf". Hopefully useful to someone. Criticism welcome...

It assumes these exist on an html page... 

<textarea id="psmtext" rows=24 cols=72> </textarea>

<input type="button" value="run code" onClick="psmtest();">



function psmtest(){


var psmtext = document.getElementById("psmtext");
  var st = psmtext.value;
  Ast = st.split("\n");
  var numrows = Ast.length; 



var ii;
  var xs = [];
  var ys = [];
  for (ii = 0 ; ii < numrows ; ii++) {
      // tab or comma deliminated data
      if ( Ast[ii].split(",",2)[1] != null ){ ys[ii] = Ast[ii].split(",")[1]; xs[ii] = Ast[ii].split(",")[0];}
      if ( Ast[ii].split("\t",2)[1] != null ){ ys[ii] = Ast[ii].split("\t")[1]; xs[ii] = Ast[ii].split("\t")[0];}
  }



var xss = [];
  var yss = [];
  var numgoodrows = 0;
  var iii =0;
  for (ii = 0 ; ii < numrows ; ii++) { 
      if ( xs[ii] != null && ys[ii] != null) {
          xss[iii] = xs[ii];
          yss[iii] = ys[ii];
          iii++;
      }
  }
  numgoodrows = iii;
  // next I need to convert to floating point array var xf = [], var yf = [];



var xf = [];
  var yf = [];
  for (ii = 0 ; ii < numgoodrows ; ii++) { 
    xf[ii] = parseFloat(xss[ii]);
    yf[ii] = parseFloat(yss[ii]);
  }



}
perrinmeyer
  • 333
  • 1
  • 3
  • 6
1

This is going to be very difficult to do really well.

Off the top of my head, I'd say the best approach would be to give the users a <textarea> that they can paste into. Then, in the onchange event handler, use some JS parsing to figure out where the rows and columns line up, create the HTML table and inject it into the DOM.

This should work alright for a relatively "square" dataset - you might run into more issues with merged columns/rows and "jagged" data

nikmd23
  • 9,095
  • 4
  • 42
  • 57
0

Although this question is considered answered already (and is an oldy), I'd like to point out that there are better solutions nowadays, see jQuery Spreadsheet/Grid plugin with copy/paste from/to Excel . I particularly like the minimalism of the HandsOnTable that was suggested by warpech (also the creator of this jQuery plugin). The plugin is still being maintained very well.

Community
  • 1
  • 1
murb
  • 1,736
  • 21
  • 31