Send contents of Sheet to the controller for structure and data validation

[Jose Monzon]

Hello. I am using Essential JS 1 v.17.2.0.34 where I have this spreadsheet:

I need to validate the structure and contents of the sheet when the user clicks the VALIDATE SHEET button.  If everything is ok, then I will extract the cells from Json string and update the backend database.

So my question is:

How can I send the sheet structure and data to the controller where I will validate its structure and contents? I know it will be Json formatted and that is ok. I found several examples in the forums and KB but none work I need.

I was able to find properties "sharedData" & "ChildrenTokens". ChildrenTokens has something that resembles the structure and contents, but I found no documentation about it. To make matters worse, ChildrenTokes is protected so I cannot Access it's contents from the controller.

This is part of my code in the view, which should send the sheet contents to the controller:

This is the controller where some data is received but I cannot process it to validate structure and contents:

Thanks.

[Vinoth Kumar Sundara Moorthy]

Hi Jose, 

 

Thank you for contacting Syncfusion support. 

 

We have checked your reported requirement and we would like to let you know that it can be achievable by using the client-side public method “saveAsJSON”. Our knowledge base solution explains the way to save and retrieve the Spreadsheet data as JSON in Database. Please refer the links below. 

 

https://www.syncfusion.com/kb/7675/how-to-save-and-retrieve-the-spreadsheet-data-as-json-in-database 

 

Related KB Link: https://www.syncfusion.com/kb/7567/how-to-save-and-retrieve-the-spreadsheet-data-in-database 

 

API Link: https://help.syncfusion.com/api/js/ejspreadsheet#methods:saveasjson 

 

Could you please check the above details and get back to us, with more information like how would you like to validate the sheet content? Based on that we will check and provide you a better solution quickly. 

 

Regards, 

Vinoth Kumar S 

[Jose Monzon]

Hello and thanks, now I can retrieve the sheet's data and structure within the controller.

Now, another less pressing matter always related with ejspreadsheet. Take a look at this screen capture of the same sheet:

As you can see, the formula bar is incomplete, the Title Column is missing, as well as the sheet grid. I was able to determine that this happens when ejspreadsheet resides within a bootstrap modal (I am using bootstrap 4) or when it resides within a div class="card" tag. 

For now, I can render the ejspreadsheet complete by placing a 3 second timer that allows the control to render completely before I collapse the card (#collapse-card-1) with JavaScript, as shown here:

Please let me know how to render correctly the ejspreadsheet inside a bootstrap modal or card, whithout using the setTimeout trick. Thanks.  

[Vinoth Kumar Sundara Moorthy]

Hi Jose, 

 

Thank you for the update. 

 

We have checked your reported issue and the cause of this issue is control not rendered properly within the time span, while using in the bootstrap modal and it may varies depend upon the data loaded. So, we would suggest you use the client-side refresh method after opening the modal dialog instead of using the setTimeOut in your end. Please refer the below API ink for more details. 

 

https://help.syncfusion.com/api/js/ejspreadsheet#methods:refresh 

 

Could you please check the above details and get back to us with more details like issue replicable sample, if you still getting the issue or need any further assistance on this? 

 

Regards, 

Vinoth Kumar S 

[Jose Monzon]

Thanks for your reply, the ejspreadsheet refresh works! and much better than the setTimeout option.

Returning to the matter of validating the contents of the spreadsheet in the controller on the server side, I have another question.

I can see that the Json data on the controller has 2 properties:

- dataContainer

- model

These are my observations regarding dataContainer & model:

- Right now I only see a 1-dimension vector (sharedData) and I don't know where each sheet's row starts and ends.

- "model" has a property named "colCount" stating there are 10 columns but only columns 1 - 8 have data, columns 9 & 10 are empty (see below a screencap of the json object received by the controller). I need to ignore columns 9 & 10.

The way I need to validate is as follows:

- Make sure at least certain number of columns have data, first 8 for this matter. Any columns beyond 8 will be ignored

- First row will be ignored as it is reserved for the column titles

- data (cell contents) will start at row 2, column A

My questions are (always from jsonData received by the controller, either from "dataContainer" or "model"):

- How can I identify where each row starts and ends?

OR

- How can I get contents of a cell by coordinates? i.e.: dataContainer.sheets[0]."GETCELLCONTENTS(2,1)"? this example should return contents of cell A,2

SCREENCAP:

[Vinoth Kumar Sundara Moorthy]

Hi Jose, 

 

Thank you for the update. 

 

Query 1: How can I identify where each row starts and ends? 

 

We have checked your reported requirement and we would like to let you know that it can be achievable by using the usedRange property from the sheet instance using getSheet method. 

 

Query 2: How can I get contents of a cell by coordinates? 

 

We can get the value of the cell by using the getPropertyValue method. For your convenience, we have prepared the sample that alerts the cell content and used range. Please find the link below. 

 

Sample Link: http://jsplayground.syncfusion.com/mwnqck1i 

 

Meanwhile, we would like to know the following details regarding your validation related query to proceed further, 

 

1. Confirm whether you need to get the first eight column values alone and save it to the database? 

 

2. Please provide the detailed description about your validation query and share your exact use case for validating eight columns alone. 

 

Could you please check the above details and get back to us with the above requested information? Based on that we will check and provide you a better solution quickly. 

 

Regards, 

Vinoth Kumar S 

[Jose Monzon]

Hello. Thanks for the reply. Your suggestions work almost as I needed, but I can do it your way. Let me explain:

The example you provided extracts the contents of the cells using coordinate, on the client side. I wanted to do it on the server side. I moved validation to the client side and now everything is fine, perhaps even better than fine.

As I stated before, the sheet must hold 8 columns, anything beyond that will be ignored. Rows are unlimited.  Now after I validate the contents on the client side, I push each cell (8 times per row) into an array, and then send the stringified array to the controller with ajax.  This is working fine, so thanks for your example.

Now, I will answer your questions:

1. Confirm whether you need to get the first eight column values alone and save it to the database 

Each column in the spreadsheet corresponds to a column in a table, and some of the columns are required, i.e.: non-nullable. That means that I need to get the 8 columns and save their contents individually to the database.

 

2. Please provide the detailed description about your validation query and share your exact use case for validating eight columns alone. 

This is the JAVASCRIPT within the view that holds ejspreadsheet:

I'll let you know how the insertion process in the database goes. Thanks.

[Vinoth Kumar Sundara Moorthy]

Hi Jose, 

 

Thank you for the update. We have checked your approach and it will work as you expected. Please let us know if you need any further assistance. 

 

Regards, 

Vinoth Kumar S 

[Jose Monzon]

Hi. Just to let you know that everything is working great. The contents of the spreadsheet are sent successfully to the controller after a brief validation on the client side. On the server side, data is validated for duplicated data and if nothing fails, rows from the spreadsheet are inserted in the corresponding table.

Thanks for your help.

[Vinoth Kumar Sundara Moorthy]

Hi Jose, 

 

Thank you for the information. 

 

Regards, 

Vinoth Kumar S