We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback
Essential JS2

Syncfusion jQuery based widgets are no longer in active development. Switch to our pure JavaScript based next generation Essential JS 2 library.

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

Thread ID:

Created:

Updated:

Platform:

Replies:

146766 Aug 19,2019 05:24 AM UTC Aug 26,2019 11:50 AM UTC jQuery 9
loading
Tags: ejSpreadSheet
Jose Monzon
Asked On August 19, 2019 05:24 AM UTC

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 [Syncfusion]
Replied On August 20, 2019 09:48 AM UTC

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. 
 
 
 
 
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
Replied On August 21, 2019 12:53 AM UTC

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 [Syncfusion]
Replied On August 21, 2019 02:07 PM UTC

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. 
 
 
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
Replied On August 21, 2019 10:53 PM UTC

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 [Syncfusion]
Replied On August 22, 2019 01:46 PM UTC

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. 
 
 
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
Replied On August 23, 2019 01:23 AM UTC

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 [Syncfusion]
Replied On August 23, 2019 05:42 PM UTC

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
Replied On August 25, 2019 12:38 AM UTC

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 [Syncfusion]
Replied On August 26, 2019 11:50 AM UTC

Hi Jose, 
 
Thank you for the information. 
 
Regards, 
Vinoth Kumar S 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon