Category / Section
How to add/update Grid data through copy and paste from Excel?
6 mins read
This documentation explained how to add or update the Grid data through copy and paste from Excel sheet.
Solutions:
If you want to add or update the grid data from Excel sheet, first you need to convert the data into JSON object. In here we have converted this pasted data by using oninput event of jquery. Then we have merged this JSON object with Grid dataSource. After that we have assigned that merged dataSource to the Grid by using dataSource method of the Grid.
HTML
<div id="Grid"></div> <b>Paste your Excel data here:</b> <br> <div id="pastable" contenteditable="true" style="width: 80%; height: 50%; overflow: hidden; border: solid 1px; "> </div>
JS
1. Render the Grid control.
$(function () {
$("#Grid").ejGrid({
dataSource: window.gridData, // the datasource "window.gridData" is referred from jsondata.min.js
allowPaging: true,
columns: [
{ field: "OrderID", headerText: "Order ID", isPrimaryKey: true, width: 90 },
{ field: "CustomerID", headerText: "Customer ID", width: 90 },
{ field: "EmployeeID", headerText: "EmployeeID", width: 90 },
{ field: "Freight", headerText: "Freight", width: 90, format: "{0:C2}"},
]
});
});
Razor
@(Html.EJ().Grid<object>("Grid")
.Datasource((IEnumerable<object>)ViewBag.datasource)
.AllowPaging()
.Columns(col =>
{
col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width(90).Add();
col.Field("CustomerID").HeaderText("Customer ID").Width(90).Add();
col.Field("EmployeeID").HeaderText("Employee ID").Width(90).Add();
col.Field("Freight").HeaderText("Freight").Width(90).Format("{0:C2}").Add();
})
)
Controller
namespace sample.Controllers
{
public class GridController : Controller
{
public ActionResult GridFeatures()
{
var DataSource = new NorthwindDataContext().OrdersViews.ToList();
ViewBag.datasource = DataSource;
return View();
}
}
}
Aspx
<ej:Grid ID="Grid" runat="server" AllowPaging="true">
<Columns>
<ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="True" Width="90" />
<ej:Column Field="CustomerID" HeaderText="Customer ID" Width="90" />
<ej:Column Field="EmployeeID" HeaderText="Employee ID" Width="90" />
<ej:Column Field="Freight" HeaderText="Freight" Width="90" Format="{0:C2}" />
</Columns>
</ej:Grid>
Controller
namespace Sample
{
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.Grid.DataSource = new NorthwindDataContext().OrdersViews.ToList();
this.Grid.DataBind();
}
}
}
Core
<ej-grid id="Grid" datasource="ViewBag.datasource" allow-paging="true">
<e-columns>
<e-column field="OrderID" header-text="OrderID" is-primary-key="true" width="90"></e-column>
<e-column field="CustomerID" header-text="CustomerID" width="90"></e-column>
<e-column field="EmployeeID" header-text="EmployeeID" width="90"></e-column>
<e-column field="Freight" header-text="Freight" width="90" format="{0:C2}"></e-column>
</e-columns>
</ej-grid>
Controller
namespace sample.Controllers
{
public partial class GridController : Controller
{
public ActionResult GridFeatures()
{
var DataSource = new NorthwindDataContext().OrdersViews.ToList();
ViewBag.datasource = DataSource;
return View();
}
}
Jquery event
$('#pastable').on('input', function () {
var tr = $(this).find("tr"), headerTds = [], data = [];
var hTd = tr.first().children("td, th");
for (var i = 0; i < hTd.length; i++)
headerTds.push(hTd.eq(i).text()); // get header text from the pasted data
for (var i = 1; i < tr.length; i++) {
var json = {}, td = tr[i].cells;
for (var j = 0; j < td.length; j++) {
json[headerTds[j]] = td[j].textContent; // get the textContent from the pasted data
}
data.push(json); // Stored that text content as array of object
}
setData(data);
});
function setData(data) {
var jsonData = [];
if (data instanceof Object) {
for (i = 0; i < data.length; i++) {
jsonData.push(data[i]);
}
}
var gridObj = $("#Grid").data("ejGrid");
$.merge(jsonData, gridObj.model.dataSource); // Merged the collected data with the Grid dataSource
gridObj.dataSource(jsonData); // Changed the Grid dataSource with the new merged datasource by using datasource method of the Grid
}
Angular
<ej-grid id="Grid" [dataSource]="gridData" [allowPaging]="true"> <e-columns> <e-column field="OrderID" headerText="OrderID" [isPrimaryKey]="true" width="90"></e-column> <e-column field="CustomerID" headerText="CustomerID" width="90"></e-column> <e-column field="EmployeeID" headerText="EmployeeID" width="90"></e-column> <e-column field="Freight" headerText="Freight" width="90"></e-column> </e-columns> </ej-grid> <b>Paste your Excel data here:</b> <br> <div id="pastable" contenteditable="true" (input)="click($event)" style="width: 80%; height: 50%; overflow: hidden; border: solid 1px; "> </div>
Typescript
@ViewChild('grid') Grid: EJComponents<any, any>;
click()
{
var tr = $(event.target).find('tr'), headerTds = [], data = [];
var hTd = tr.first().children("td, th");
for (var i = 0; i < hTd.length; i++)
headerTds.push(hTd.eq(i).text()); // get header text from the pasted data
for (var i = 1; i < tr.length; i++) {
var json = {}, td = tr[i].cells;
for (var j = 0; j < td.length; j++) {
json[headerTds[j]] = td[j].textContent; // get the textContent from the pasted data
}
data.push(json); // Stored that text contenet as array of object
}
this.setData(data);
}
setData(data) {
var jsonData = [];
if (data instanceof Object) {
for (var i = 0; i < data.length; i++) {
jsonData.push(data[i]);
}
}
$.merge(jsonData, this.Grid.widget.model.dataSource());
this.Grid.widget.dataSource(jsonData); // Change the Grid dataSource
}
Note:
IE browser doesn’t support jquery oninput event. So we have created the jquery.pastable.js script to achieve this requirement in IE browser. You need to refer this script file in your application to achieve this requirement in IE. Refer the following code snippet to how to use this script file in your project.
JS
$(function () {
$("#pastable").pastable({
OnPasteComplete: function (data) {
var jsonData = [];
// Ensuring the pasted information is Excel data or HTML table data.
if (data instanceof Object) {
for (i = 0; i < data.length; i++) {
jsonData.push(data[i]);
}
}
var gridObj = $("#Grid").data("ejGrid");
$.merge(jsonData, gridObj.model.dataSource);
gridObj.dataSource(jsonData);
}
});
});
Jquery.pastable.js
(function ($) {
$.fn.pastable = function (model) {
// Function to trigger custom events of plug-in
var triggerEvent = function (name, args) {
model = model || {};
if (model[name] != null) {
var fn = model[name];
fn.call(this, args);
}
};
// Function attach current object to delegate
var fnDelegate = function (fn, object) {
return function () { return fn.call(object, null); }
}
// This parse the html to Json object with assemption that first row is header
function parseHtml(table) {
var tr = table.find("tr"), headerTds = [], data = [];
var hTd = tr.first().children("td, th");
for (var i = 0; i < hTd.length; i++)
headerTds.push(hTd.eq(i).text());
for (var i = 1; i < tr.length; i++) {
var json = {}, td = tr.eq(i).find("td, th");
for (var j = 0; j < td.length; j++) {
json[headerTds[j]] = td.eq(j).text();
}
data.push(json);
}
return data;
}
this.processPaste = function () {
if (this.html() == "") {
setTimeout(fnDelegate(this.processPaste, this), 20);
return;
}
var child = this.children(":first");
var data = null;
if (model.DontTryParse || child.length == 0 || child[0].tagName.toLowerCase() != "table")
data = this.html();
else
data = parseHtml(child);
if (!model.KeepDataOnPasteComplete)
this.html("");
triggerEvent.call(this, "OnPasteComplete", data);
}
// This will work only on Div element.
if (!this.is("div"))
throw "Div element is required";
// Converting DIV to editable so that pasting is possible.
this.attr("contenteditable", true);
// Binding paste event to element to access the pasted data.
this.bind("paste", fnDelegate(function () {
var self = this.html("");
triggerEvent.call(this, "OnPasting", null);
setTimeout(fnDelegate(this.processPaste, this), 20);
}, this));
return this;
};
})(jQuery);
The following output is displayed the above behavior
Fig 1: Initial Grid Render
Fig 2: copy from Excel sheet
Fig 3: To add new data in grid via pasting from Excel
