Paste rows from Excel into grid

[Daniel Adkins]

I have seen two different blog posts mentioning the possibility of users pasting Excel rows into a grid.  Ideally, I would like to have the users paste content into the grid and then run a validation process in the background before ultimately accepting the validated data into a client table.  Both of the posts contained sample projects but I have been unsuccessful in getting either working with the latest framework installed.  Does a current sample for cut/paste rows exist?  Alternatively, is there a better approach to accomplishing the goals of this project using Syncfusion?

Reference link:  http://www.syncfusion.com/blogs/post/Copy-and-pasting-Excel-Sheet-data-to-Syncfusion-Grid-for-MVC.aspx 

[Jayaprakash Kamaraj]

Hi Daniel,
There is no current sample for cut/paste rows. So, we have created a sample in the latest version that imports data into the grid. In this sample, the jQuery scripts plays a vital role in getting the clipboard Excel information and converting it to JSON. For this purpose, you have to refer to the jquery.pastable.js file.

When you paste the data in div elements, it moves to the controller through AjaxPost. In controller, you have to deserialize the data and return as JSON element. In Success,  you can re-render the grid through the dataSource method.

@(Html.EJ().Grid<object>("Grid")

      .Datasource(ds => ds.URL("/Home/GetData").Offline(true))

              .Columns(col =>

               {

                   col.Field("ID").IsPrimaryKey(true).Width(50).Add();

                   col.Field("Name").Width(20).Add();

                   col.Field("DateOfBirth").Format("{0:dd/MM/yyyy}").Width(20).Add();

                   col.Field("Address").Width(50).Add();

                   col.Field("MobileNumber").Width(50).Add();

               })

    )

     <script>

    $(document).ready(function () {

        // Binding pastable jquery plug-in

        $("#pastable").pastable({

            OnPasteComplete: function (data) {

                // Ensuring pasted is an Excel data / Html Table data

                var jsonData = [];

                if (data instanceof Object)

                    for (i = 0; i < data.length; i++) {

                        jsonData.push(data[i]);

                    }

                $.ajax({

                    url: "/Home/Index",

                    type: "POST",

                    data: { pastedData: JSON.stringify(jsonData) },

                    success: function (gridData) {

                        var gridObj = $("#Grid").data("ejGrid");

                        gridObj.dataSource(ej.parseJSON(gridData));

                    }

                });

            }

        });

    });

     </script>

</div>

<b>Paste your Excel data here:</b>

<br />

<div id="pastable" contenteditable="true" style="width: 0; height: 40px; padding-left: 250px;overflow: hidden; border: solid 1px; text-indent: 100px; font-size: 0;">
</div>

Sample: https://www.syncfusion.com/downloads/support/forum/121813/ze/EJGrid-821489119-410214081

Query:  run a validation process in the background before ultimately accepting the validated data into a client table.

Please share the following information to give a prompt solution, 
                                                                                

1.           In which scenario you want validation?

2.           Share more details about this query
 
Regards,

Jayaprakash K.

[Megatron]

Hi, where is the validation on the data pasted, and how is it conducted.

What if there are foreign keys in the columns, how is that validated?

thanks

[Jayaprakash Kamaraj]

Hi Daniel ,
Query1: where is the validation on the data pasted, and how is it conducted.
In jquery.pastable.js file, we got the pasted data.  Initially, we get pasted data as table format and it will convert into JSON data in pareseHtml method. Here we can validate the data. Please refer to the below code Example and sample.
Code Example:

Jquery.pastable.js

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;

        }

Sample: https://www.syncfusion.com/downloads/support/forum/121813/ze/Sample(2)838293654
Query 2: What if there are foreign keys in the columns, how is that validated?
Same scenario will be followed in foreign key column.
Regards,
Jayaprakash K.

[Megatron]

hi, when I search for the code, fnDelegate, I cannot find any of this code in the sample, even the parse thml is missing.

Can you show this and the FK, with the sample in ASP MVC syncfusion.
I would also like to show user error, if the data pasted was not the correct format as the header/ or parse error, including the data element that failed.

thanks

[Jayaprakash Kamaraj]

Hi Daniel ,
We are sorry for the inconvenience.

Please find the reattached sample.
Sample: https://www.syncfusion.com/downloads/support/forum/121813/ze/EJGrid-821489119-410214081
Regards,
Jayaprakash K.