How to make HTML table "Excel-like" editable for multiple cells, i.e. simultaneous copy-paste features?

Question

I need my HTML table to be editable so that user inserted data could be sent to the server. However due to the table's size (50 rows) it would not be convenient for users to insert data points one-by one if I introduce contenteditable attribute as following:

<table>
<tr><td><div contenteditable>editable</div></td><td><div contenteditable>editable</div></td></tr>
//...........
</table>

How can I make my table be editable similar to excel spreadsheet, if possible without using dojo etc? I have done this in Java using ExcelAdapter class. Is there any way I could do it in HTML?

Answer 1

Pure javascript. Just mark a table with 'editabletable' class.

index.html:

<table class="editabletable" border="1">
  <tr> <td>a</td> <td>b</td> <td>c</td> </tr>
  <tr> <td>d</td> <td>e</td> <td>f</td> </tr>
  <tr> <td>g</td> <td>h</td> <td>i</td> </tr>
</table>
<script src="index.js"></script>

index.js:

function makeeditable(table) {
    for (let i = 0; i < table.rows.length; i++) {
        for (let j = 0; j < table.rows[i].cells.length; j++) {
            // with contenteditable in each cell, it is possible to navigate through them with tab key
            table.rows[i].cells[j].setAttribute("contenteditable", "true");
        }
    }
}

function addpastelistener(table) {
    table.addEventListener('paste', (event) => {
        event.preventDefault();
        let paste = (event.clipboardData || window.clipboardData).getData('text');
        let col = event.target;
        while (col && col.tagName != 'TD') col = col.parentElement;
        let row = col;
        while (row && row.tagName != 'TR') row = row.parentElement;
        let tab = row;
        while (tab && tab.tagName != 'TBODY' && tab.tagName != 'TABLE') tab = tab.parentElement;
        let rows = paste.replace(/(\r\n)|\r|\n/g, '\n').split("\n");
        for (let i = 0, r = row.rowIndex; i < rows.length && r < table.rows.length; i++) {
            let cells = rows[i].split("\t");
            for (let j = 0, c = col.cellIndex; j < cells.length && c < table.rows[r].cells.length; j++) {
                table.rows[r].cells[c].innerHTML = cells[j].trim();
                c++;
            }
            r++;
        }
    });
}

function init() {
    const editabletables = document.getElementsByClassName('editabletable');
    for (let i = 0; i < editabletables.length; i++) {
        makeeditable(editabletables[i]);
        addpastelistener(editabletables[i]);
    }
}

init();

Answer 2

you can achieve this easily by adding handsontable

change any div to a excel table

var $container = $("#divid");
$container.handsontable({
data: getData(),
rowHeaders: true,
colHeaders: true,
contextMenu: true  //forces dom to use custom right click functionality like add row delete row add column etc 
});

jsFiddle : http://jsfiddle.net/jwtskyLa/

Answer 3

You can add a listener to the input event of each cell, and if the cell contains multiple data items split them across the next cells.

function init()
{
    var tables = document.getElementsByClassName("editabletable");
    var i;
    for (i = 0; i < tables.length; i++)
    {
        makeTableEditable(tables[i]);
    }
}

function makeTableEditable(table)
{
    var rows = table.rows;
    var r;
    for (r = 0; r < rows.length; r++)
    {
        var cols = rows[r].cells;
        var c;
        for (c = 0; c < cols.length; c++)
        {
            var cell = cols[c];
            var listener = makeEditListener(table, r, c);
            cell.addEventListener("input", listener, false);
        }
    }
}

function makeEditListener(table, row, col)
{
    return function(event)
    {
        var cell = getCellElement(table, row, col);
        var text = cell.innerHTML.replace(/<br>$/, '');
        var items = split(text);

        if (items.length === 1)
        {
            // Text is a single element, so do nothing.
            // Without this each keypress resets the focus.
            return;
        }

        var i;
        var r = row;
        var c = col;
        for (i = 0; i < items.length && r < table.rows.length; i++)
        {
            cell = getCellElement(table, r, c);
            cell.innerHTML = items[i]; // doesn't escape HTML

            c++;
            if (c === table.rows[r].cells.length)
            {
                r++;
                c = 0;
            }
        }
        cell.focus();
    };
}

function getCellElement(table, row, col)
{
    // assume each cell contains a div with the text
    return table.rows[row].cells[col].firstChild;
}

function split(str)
{
    // use comma and whitespace as delimiters
    return str.split(/,|\s|<br>/);
}

window.onload = init;

Demo: http://jsfiddle.net/yRnkF/