Essential JS 2: Copy and Paste Excel Data to Grid
Copy and paste from Excel to an ASP.NET MVC Grid.
by
·
Join the DZone community and get the full member experience.
Join For FreeCopy 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.
Select MVC as the project template from the project template wizard to create the MVC web application.
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.”
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="stylesheeAdd 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;lt;Orders&amp;gt; OrdersList = new List&amp;lt;Orders&amp;gt;();
public ActionResult Index()
{
OrdersList = new List&amp;lt;Orders&amp;gt;() {
new Orders() { OrderID = 10248, CustomerName = &amp;quot;VINET&amp;quot;, ShipCountry = &amp;quot;Rio&amp;quot;, Freight = 32.48, OrderDate = DateTime.Now },
new Orders() { OrderID = 10249, CustomerName = &amp;quot;HANAR&amp;quot;, ShipCountry = &amp;quot;Alaska&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;quot;grid&amp;quot;)
.DataSource((List&amp;lt;EJ2_MVC_Grid_Copy_Paste.Controllers.Orders&amp;gt;)ViewBag.dataSource)
.Columns(col =&amp;gt;
{
col.Field(&amp;quot;OrderID&amp;quot;).HeaderText(&amp;quot;Order ID&amp;quot;).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width(&amp;quot;120&amp;quot;).Add();
col.Field(&amp;quot;CustomerName&amp;quot;).HeaderText(&amp;quot;Customer Name&amp;quot;).Width(&amp;quot;150&amp;quot;).Add();
col.Field(&amp;quot;ShipCountry&amp;quot;).HeaderText(&amp;quot;Ship Country&amp;quot;).Width(&amp;quot;130&amp;quot;).Add();
})
.Render())Now, when we run the application, we can see the grid control as follows.
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:
- Registering paste event.
- Converting Pasted Data to JSON.
- 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 = "VINET", ShipCountry = "Rio", Freight = 32.48, OrderDate = DateTime.Now },
new Orders() { OrderID = 10249, CustomerName = "HANAR", ShipCountry = "Alaska", 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.
Before Pasting
After Pasting
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:
- [Blog Post] HTML to PDF Conversion Using ASP.NET Core in Linux Docker
- [Ebook] ASP.NET Webhooks Succinctly
- [Ebook] ASP.NET Core Succinctly
- [Ebook] ASP.NET Multitenant Applications Succinctly
Published at DZone with permission of Madhu Sudhanan. See the original article here.
Opinions expressed by DZone contributors are their own.
Trending
-
A React Frontend With Go/Gin/Gorm Backend in One Project
-
What Is Envoy Proxy?
-
From On-Prem to SaaS
-
Performance Comparison — Thread Pool vs. Virtual Threads (Project Loom) In Spring Boot Applications
Comments