Edit this page

Copy Columns from Excel and Paste Them on Selected Grid Columns

Environment

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

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>
Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy