Paste Excel data into html table

perrinmeyer picture perrinmeyer · Jul 8, 2009 · Viewed 47.6k times · Source

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.

Answer

Paul Abbott picture Paul Abbott · Jul 8, 2009

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>