Edit this page

Copy and Paste Multiple Rows from Excel to the Grid

Environment

Product Progress Kendo UI Grid

Description

I am trying to implement the copy-and-paste functionality for multiple rows from Excel to the Kendo UI Grid. I am using the example on copying data from Excel, but after I paste them, none of the events fire andit takes much time to add rows.

How can I enable the copying of multiple rows from Excel and pasting them in the Grid?

Solution

  1. Configure the CRUD operations for the Grid in which you want to implement the copy-paste functionality by using a batch data source to send all the requests at once.

  2. Adjust the width and height of the text area to exclude the pager and the scrollbar.

    if($(e.target).hasClass("k-link") || $(e.target).hasClass("k-grid-toolbar")){
      return;
    }
    
    // crete a textarea element which will act as a clipboard
    var textarea = $("<textarea>");
    // position the textarea on top of the grid and make it transparent
    textarea.css({
      position: 'absolute',
      opacity: 0,
      top: offset.top,
      left: offset.left,
      border: 'none',
      width: $(this).find("table").width(),
      height: $(this).find(".k-grid-content").height()
    })
    
  3. To add a native Kendo UI look and feel, add the dirty indicator to each new item and its fields.

    dataBound: function(e){
      var grid = this;
      var rows = grid.items();
      rows.each(function(idx, row){
       var dataItem = grid.dataItem(row);
       if(dataItem.isNew()){
        var td = $(row).find("td");
        td.each(function(idx, cell){
         if($(cell).text()){
           $(cell).prepend("<span class='k-dirty'></span>");
         }
        })
       }
      })
     }
    

Full Implementation

The following example demonstrates the full implementation of the approach.

     <div id="example">
      <p>Click the grid to edit it or right click to paste edit</p>
      <p>Copy paste from excel with a text column and a number column</p>
      <div id="grid"></div>

      <script>
        var crudServiceBaseUrl = "https://demos.telerik.com/kendo-ui/service",
            dataSource = new kendo.data.DataSource({
              transport: {
                read:  {
                  url: crudServiceBaseUrl + "/Products",
                  dataType: "jsonp"
                },
                update: {
                  url: crudServiceBaseUrl + "/Products/Update",
                  dataType: "jsonp"
                },
                destroy: {
                  url: crudServiceBaseUrl + "/Products/Destroy",
                  dataType: "jsonp"
                },
                create: {
                  url: crudServiceBaseUrl + "/Products/Create",
                  dataType: "jsonp"
                },
                parameterMap: function(options, operation) {
                  if (operation !== "read" && options.models) {
                    return {models: kendo.stringify(options.models)};
                  }
                }
              },
              batch: true,
              pageSize: 10,
              schema: {
                model: {
                  id: "ProductID",
                  fields: {
                    ProductID: { editable: false, nullable: true },
                    ProductName: { validation: { required: true } },
                    UnitPrice: { type: "number", validation: { required: true, min: 1}, defaultValue:0 },
                    Discontinued: { type: "boolean" },
                    UnitsInStock: { type: "number", validation: { min: 0, required: true } }
                  }
                }
              }
            });

        var grid = $("#grid").kendoGrid({
          dataSource: dataSource,
          dataBound: function(e){
            var grid = this;
            var rows = grid.items();
            rows.each(function(idx, row){
              var dataItem = grid.dataItem(row);
              if(dataItem.isNew()){
                var td = $(row).find("td");
                td.each(function(idx, cell){
                  if($(cell).text()){
                    $(cell).prepend("<span class='k-dirty'></span>");
                  }
                })
              }
            })
          },
          toolbar: ["save"],
          height: 550,
          sortable: true,
          pageable: true,
          editable: true,
          columns: [
            { field: "ProductID", title: "Product ID", width: 100 },
            { field: "ProductName", title: "Product Name" },
            { field: "UnitPrice", title: "Unit Price", width: 150 },
          ]
        }).on('contextmenu', function (e) {

          if($(e.target).hasClass("k-link") || $(e.target).hasClass("k-grid-toolbar")){
            return;
          }
          // Get the position of the Grid.
          var offset = $(this).find("table").offset();
          // Crete a textarea element which will act as a clipboard.
          var textarea = $("<textarea>");
          // Position the textarea on top of the Grid and make it transparent.
          textarea.css({
            position: 'absolute',
            opacity:0,
            top: offset.top,
            left: offset.left,
            border: 'none',
            width: $(this).find("table").width(),
            height: $(this).find(".k-grid-content").height()
          })
            .appendTo('body')
            .on('paste', function (e) {
            setTimeout(function () {
              var value = $.trim(textarea.val());
              var grid = $("[data-role='grid']").data("kendoGrid");
              var rows = value.split('\n');
              var data = [];

              for (var i = 0; i < rows.length; i++) {
                var cells = rows[i].split('\t');
                var newItem = {
                  ProductName: cells[0],
                  UnitPrice: cells[1]
                }

                grid.dataSource.insert(0,newItem);
              };
              textarea.remove();
            });

          }).focus();
        });

      </script>
    </div>
Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy