Essential JS 2: Copying and Pasting Excel Sheet Data to Grid ASP.NET MVC

Copy and paste are most frequently used actions in applications. One of the important action is copying data from excel and pasting it to the grid in web browser but achieving this scenario would be very tricky because browser prevent paste option from clipboard due to security reasons.

Here we are going to guide you to achieve copy and paste Excel sheet data to Essential JS 2 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 in your ASP.NET MVC project.

Prerequisites

The following items are required to use Essential JS 2 component in 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.

ASP.NET MVC DataGrid - Create ASP.NET MVC Web Application wizard

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

ASP.NET MVC DataGrid - Create ASP.NET MVC Web Application wizard - Select template

Adding Syncfusion packages

Now we are going to add Essential JS 2 packages to our web application using nuget package

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 keyword “essentialjs2”.

ASP.NET MVC DataGrid - Add Syncfusion Essential JS 2 nuget package

Now we will be left with the above available Syncfusion nuget packages which we can select as 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 in Web.config file

We have successfully added Syncfusion packages in 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 control 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 below 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.

<link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet"/>

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 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 below code example.

  @(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.

Essential JS 2 ASP.NET MVC DataGrid: Initial Rendering

Configuring paste action

Now we are going to configure our application and grid to handle paste operation. For simplicity, I am going to split this section into below three sub sections. They are,

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

Registering paste event

We need to register paste event to the grid element and the paste operation will be handled at the event handler. We can take leverage of the paste event as it has no effect when 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 send 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. And we need to convert the pasted TSV data to JSON which can be then assigned to grid data source. We can use the below simple utility function 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 which will be used to merge pasted data with the actual data. When the data get 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 paste event in IE

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

Now the above event registration and listener code has 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 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.

Essential JS 2 ASP.NET MVC DataGrid: Internet explorer permission prompt

Before Pasting

Essential JS 2 ASP.NET MVC DataGrid: Before pasting excel data

 

Essential JS 2 ASP.NET MVC DataGrid: Excel sheet dataAfter Pasting

Essential JS 2 ASP.NET MVC DataGrid: After pasting excel dataSummary

In this blog, we have gone through steps for adding grid control to an ASP.NET MVC application and performing copy and pasting Excel Data to the Essential JS 2 Grid. You can also find the runnable demo of this blog in our GitHub repository. To check our features and other details of our ASP.NET MVC DataGrid, you can visit the sample browser and documentation to explore live samples of its features and API.

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:

Tags:

Share this post:

Related Posts

Leave a comment