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")){
      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>");
         }
        })
       }
      })
     }
    

The following example demonstrates the full implementation of the approach.

    <div id="example">
      <p>Click the grid to focus it, then right click and paste content</p>
      <div id="grid"></div>

      <script type="text/x-kendo-template" id="template">
                <div class="refreshBtnContainer">
                    <a href="\\#" class="k-link k-button" title="Refresh"><span class="k-icon k-i-refresh"></span></a>
        </div>
                <div class="toolbar">
                    <label class="category-label" for="category">Show products by category:</label>
                    <input type="search" id="category" style="width: 150px"/>
        </div>
      </script>
      <script>
        $(document).ready(function() {
          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} },
                      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('focusin', function (e) {
            if($(e.target).hasClass("k-link")){
              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 () {
              // Handle the paste event.                         
              setTimeout(function () {

                kendo.ui.progress($("#grid"), true);
                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);
                };


                kendo.ui.progress($("#grid"), false);
              });

            }).on('focusout', function () {
              // Remove the textarea when it loses focus.
              $(this).remove();
            });
            // Focus the textarea.
            setTimeout(function () {
              textarea.focus();
            });
          });
          var dropDown = grid.find("#category").kendoDropDownList({
            dataTextField: "CategoryName",
            dataValueField: "CategoryID",
            autoBind: false,
            optionLabel: "All",
            dataSource: {
              type: "odata",
              severFiltering: true,
              transport: {
                read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Categories"
              }
            },
            change: function() {
              var value = this.value();
              if (value) {
                grid.data("kendoGrid").dataSource.filter({ field: "CategoryID", operator: "eq", value: parseInt(value) });
              } else {
                grid.data("kendoGrid").dataSource.filter({});
              }
            }
          });

          grid.find(".k-grid-toolbar").on("click", ".k-i-refresh", function (e) {
            e.preventDefault();
            alert("sgdfgd");
          });
        });

      </script>
      <style>
        #grid .k-grid-toolbar
        {
          padding: .6em 1.3em .6em .4em;
        }
        .category-label
        {
          vertical-align: middle;
          padding-right: .5em;
        }
        #category
        {
          vertical-align: middle;
        }
        .refreshBtnContainer {
          display: inline-block;
        }
        .toolbar {
          float: right;
        }
      </style>
    </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