Copy Columns from Excel and Paste Them on Selected Grid Columns

Environment

Product Version	2018.1 221
Product	Progress® Kendo UI® Grid for jQuery

Description

How can I copy Excel columns and paste them on the selected columns in the Kendo UI Grid?

Solution

Handle the keydown event of the Grid—in the event handler and if the user is pasting:

1. Programmatically focus an invisible text-area to paste the content on it.
2. Use the value of the text-area to create an array of single cell values.
3. Get the selected cells of the Grid.
4. For every selected cell, shift a value from the array.

<div id="grid"></div>
<textarea id="ta" style="opacity: 0"></textarea>
<script>
$("#grid").kendoGrid({
    selectable: "multiple cell",
    allowCopy: true,
    columns: [
    { field: "productName0" },
    { field: "productName1" },
    { field: "productName2" },
    { field: "productName3" },
    { field: "productName4" }
    ],
    dataSource: [
    { productName0: "Tea", productName1: "Tea", productName2: "Tea", productName3: "Tea", productName4: "Tea" },
    { productName0: "Coffee", productName1: "Coffee", productName2: "Coffee", productName3: "Coffee", productName4: "Coffee"},
    { productName0: "Ham", productName1: "Ham", productName2: "Ham", productName3: "Ham", productName4: "Ham"},
    { productName0: "Bread", productName1: "Bread", productName2: "Bread", productName3: "Bread", productName4: "Bread"}
    ]
});

var grid = $('#grid').data('kendoGrid');

grid.element.on('keydown', function(e) {
    if(e.keyCode===86 && e.ctrlKey===true){
    var textarea = $("#ta");
    textarea.val("");
    textarea.focus();

    setTimeout(function(e){
        var value = $.trim(textarea.val());
        var grid = $("#grid").data("kendoGrid");
        var rows = value.split('\n');
        var data = [];

        for (var i = 0; i < rows.length; i++) {
        var cells = rows[i].split('\t');
        for (var j = 0; j < cells.length; j++) {
            data.push(cells[j]);
        }
        };
        var select = grid.select();
        select.each(function(i, e){
        var cell = $(this);
        var row = cell.closest("tr");
        var dataItem = grid.dataItem(row);
        var dataField = $(grid.thead.find("th")[cell[0].cellIndex]).attr("data-field");

        dataItem[dataField] = data.shift()
        });
        grid.refresh();

    },1)
    }
})
</script>