{{announcement.body}}
{{announcement.title}}

Essential JS 2: Copy and Paste Excel Data to Grid

DZone 's Guide to

Essential JS 2: Copy and Paste Excel Data to Grid

Copy and paste from Excel to an ASP.NET MVC Grid.

· Web Dev Zone ·
Free Resource

Copy and paste are some of the most frequently used actions in applications. One important action associated with copying and pasting is copying data from Excel and pasting it to a grid in a web browser. However, doing this can be very tricky because browsers prevent pasting from the clipboard for security reasons.

Here, we are going to guide you through giving users the ability to copy and paste Excel sheet data to an ASP.NET MVC Grid. If you are new to ASP.NET MVC Essential JS 2 Grid, then please refer the getting started of grid control.

Setting Up the Development Environment

All Essential JS 2 components are available as NuGet packages. You can use them to integrate your ASP.NET MVC project.

Prerequisites

The following items are required to use the Essential JS 2 component in your ASP.NET MVC application.

  • Net Framework 4.5 and above.
  • NET MVC 4 or ASP.NET MVC 5.
  • Visual Studio.

Creating ASP.NET MVC Web Application

If you have an existing ASP.NET MVC project, then you can jump to the next section. If not, please create one by selecting File -> New -> Project.

New MVC project

New MVC project


Select MVC as the project template from the project template wizard to create the MVC web application.

Selecting the MVC template

Selecting the MVC template


Adding Syncfusion Packages

Now, we are going to add Essential JS 2 packages to our web application using the NuGet packages.

To add Essential JS 2 package, open Nuget Package Manager by selecting Project -> Manage Nuget Packages… and search for Essential JS 2 NuGet packages by using the keyword “essentialjs2.”

Adding NuGet packages

Adding NuGet packages


Now, we will be left with the available Syncfusion NuGet packages, which we can select per our requirement. Since the project we created from the previous step uses MVC5, I am going to select and install Syncfusion.EJ2.MVC5 package from the list.

Configuring Assemblies and Namespaces

We have successfully added Syncfusion packages in our web application. Now, we need to register the Essential JS 2 assemblies and namespaces in the Web.config file.

[Views/Web.config]

<namespaces>
 <add namespace="System.Web.Mvc" />
 <add namespace="System.Web.Mvc.Ajax" />
 <add namespace="System.Web.Mvc.Html" />
 <add namespace="System.Web.Optimization"/>
 <add namespace="System.Web.Routing" />
 <add namespace="EJ2_MVC_Grid_Copy_Paste" />
 <add namespace="Syncfusion.EJ2"/>
</namespaces>


[Web.config]

<system.web>
  <authentication mode="None" />
  <compilation debug="true&uot; targetFramework="4.6&aquot;>
    <assemblies>
      <add assembly="Syncfusion.EJ2"/>
    </assemblies>
  </compilation>
  <httpRuntime targetFramework="4.6" />
</system.web>


Now, the application is configured with Essential JS 2 packages. We will see how to add grid controls to this application.

Adding Script and Theme

Apart from Essential JS 2 packages, we need to refer the client-side resources to render the grid control. Essential JS 2 provides the following list of built-in themes.

  • Material.
  • Office 365.
  • Bootstrap.
  • High Contrast.

For this demo, we have added the material theme of the grid control in _Layout.cshtml page.

	<linkhref="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css"rel="styleshee


Add ej2.min.js file to the _Layout.cshtml page.

<script src="//cdn.syncfusion.com/ej2/dist/ej2.min.js"></script>


Adding Script Manager

You need to add the Essential JS 2 script manager at the bottom of the _Layout.cshtml page. It will maintain client-side resources to render the grid control.

<body>
    . . . .  .
    @Html.EJS().ScriptManager();
</body>


Providing Row Data

Here, we have initialized a collection of data for dataSource at the controller, which will be passed to the Index.cshtml page by ViewBag.

public class HomeController : Controller
{
    public static List&amp;amp;lt;Orders&amp;amp;gt; OrdersList = new List&amp;amp;lt;Orders&amp;amp;gt;();
    public ActionResult Index()
    {
        OrdersList = new List&amp;amp;lt;Orders&amp;amp;gt;() {
            new Orders() { OrderID = 10248, CustomerName = &amp;amp;quot;VINET&amp;amp;quot;, ShipCountry = &amp;amp;quot;Rio&amp;amp;quot;, Freight = 32.48, OrderDate = DateTime.Now },
            new Orders() { OrderID = 10249, CustomerName = &amp;amp;quot;HANAR&amp;amp;quot;, ShipCountry = &amp;amp;quot;Alaska&amp;amp;quot;, Freight = 2.8, OrderDate = DateTime.Now }
        };
        ViewBag.dataSource = OrdersList;
        return View();
    }   
}


Adding Grid Control

Now, the application is all set, and you can add the grid control to the application. For this demo, we have initialized a simple grid in the Index.cshtml page with DataSource from Viewbag, as shown in the following code block.

@(Html.EJS().Grid(&amp;amp;quot;grid&amp;amp;quot;)
      .DataSource((List&amp;amp;lt;EJ2_MVC_Grid_Copy_Paste.Controllers.Orders&amp;amp;gt;)ViewBag.dataSource)
      .Columns(col =&amp;amp;gt;
      {
          col.Field(&amp;amp;quot;OrderID&amp;amp;quot;).HeaderText(&amp;amp;quot;Order ID&amp;amp;quot;).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width(&amp;amp;quot;120&amp;amp;quot;).Add();
          col.Field(&amp;amp;quot;CustomerName&amp;amp;quot;).HeaderText(&amp;amp;quot;Customer Name&amp;amp;quot;).Width(&amp;amp;quot;150&amp;amp;quot;).Add();
          col.Field(&amp;amp;quot;ShipCountry&amp;amp;quot;).HeaderText(&amp;amp;quot;Ship Country&amp;amp;quot;).Width(&amp;amp;quot;130&amp;amp;quot;).Add();
      })
      .Render())


Now, when we run the application, we can see the grid control as follows.

Grid control

Grid

Configuring Paste Action

Now, we are going to configure our application and grid to handle the paste operation. For simplicity, I am going to split this section into three subsections:

  1. Registering paste event.
  2. Converting Pasted Data to JSON.
  3. Add Action to handle pasted data.

Registering Paste Event

We need to register the paste event to the grid element; the paste operation will be handled at the event handler. We leverage the paste event, as it has no effect when a pasted target is not content editable.

In the paste event handler, we need to get the pasted data and parse it to JavaScript objects. Then, the JSON data can be sent to a controller action where we do the data merge operation. Then, at the success handler, we have to refresh the grid with the new data source.

document.getElementById('grid').addEventListener('paste', function (e) {
    var tsv = e.clipboardData.getData('Text')
     
    if (tsv) {
        var ajax = new ej.base.Ajax("@Url.Action("OnPaste")");
        ajax.type = "POST";
        ajax.send(JSON.stringify({ pastedData: tsvJSON(tsv) }))
            .then(function (data) {
                var grid = document.getElementById('grid').ej2_instances[0];
                grid.dataSource = JSON.parse(data);
            });
    }
});


Converting Pasted Data to JSON

When the copied Excel sheet data is pasted, we will get the pasted data as Tab Separated Values (TSV) in the paste event handler. We need to convert the pasted TSV data to JSON, which we can then assign to the grid data source. We can use the simple utility function displayed below to convert the TSV to JavaScript objects.

function tsvJSON(tsv) {
    var lines = tsv.trim().split("\n");
    var result = [];
    var headers = lines[0].split("\t");
 
    for (var i = 1; i < lines.length; i++) {
        var obj = {};
        var currentline = lines[i].split("\t");
 
        for (var j = 0; j < headers.length; j++) {
            obj[headers[j].trim()] = currentline[j];
        }
        result.push(obj);
    }
    return result; //JSON
}


Add Action to Handle Pasted Data

Now, we need to create a controller action that will be used to merge pasted data with the actual data. When the data gets pasted, we will send the pasted data to this action, and the pasted data will be added to the actual data source. This action should return the resultant data, which will be bound to the grid.

public class HomeController : Controller
{
    public static List<Orders> OrdersList = new List<Orders>();
    public ActionResult Index()
    {
        OrdersList = new List<Orders>() {
            new Orders() { OrderID = 10248, CustomerName = &quot;VINET&quot;, ShipCountry = &quot;Rio&quot;, Freight = 32.48, OrderDate = DateTime.Now },
            new Orders() { OrderID = 10249, CustomerName = &quot;HANAR&quot;, ShipCountry = &quot;Alaska&quot;, Freight = 2.8, OrderDate = DateTime.Now }
        };
        ViewBag.dataSource = OrdersList;
        return View();
    }
 
    [HttpPost]
    public ActionResult OnPaste(PostData data, string action)
    {
        OrdersList.AddRange(data.pastedData);
        return Json(OrdersList);
    }
}


Handling a Paste Event in IE

Internet Explorer triggers a paste event only if the paste target is a content editable or form element. To overcome this problem, we can bind a keydown event to trap CTRL+V keystrokes and handle the paste operation in its event handler.

Now, the above event registration and listener code have to be changed as follows.

document.getElementById('grid').addEventListener(ej.base.Browser.isIE ? 'keydown' : 'paste', function (e) {
    var tsv;
    if (e.ctrlKey && (e.keyCode == 86)) {
        tsv = window.clipboardData.getData('Text');
    } else {
        tsv = e.clipboardData.getData('Text');
    }
    if (tsv) {
        var ajax = new ej.base.Ajax("@Url.Action("OnPaste")");
        ajax.type = "POST";
        ajax.send(JSON.stringify({ pastedData: tsvJSON(tsv) }))
            .then(function (data) {
                var grid = document.getElementById('grid').ej2_instances[0];
                grid.dataSource = JSON.parse(data);
            });
    }
});


When using the keydown event, you can get the clipboard data using the window.clipboardData property. For security reasons, you will be prompted with a permission access request while pasting data in Internet Explorer that provides access to get clipboard data programmatically.

Permisson access request

Permission access request

Before Pasting

Original grid

Original grid


Copying from Excel sheet

Copying from Excel sheet

After Pasting

Grid after pasting from Excel sheet

Grid after pasting from Excel sheet

Summary

In this blog, we have gone through steps for adding grid control to an ASP.NET MVC application and performing copy and paste functions from Excel Data to the Essential JS 2 Grid. You can also find the runnable demo of this blog in our GitHub repository. 

If you have any questions or require clarification, please let us know in the comments section. You can also contact us through our support forum or Direct-Trac. We are happy to assist you!

If you would like to learn more, check out these related posts and free resources:

Topics:
web dev ,c# ,asp.net ,mvc ,java-to-javascript ,cshtml ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}