Update Datasource After Import

[Marc]

Hi

I have a spreadsheet component, with a datasource. I am exporting a dataset from the spreadsheet, changing it in Excel, and then reimporting it. After I import data, if the imported data has more records than the current dataset, the dataSource is not updated so when I save nothing happens.

In my save, I use dataSource.hasChanges to check if we have changes in the dataset. This is true, even if I have only added a record. However - dataSource.data().length is still the original size. I have tried with dataSource.sync(). Is there something I am missing?

[Boyan Dimitrov]

Hello Marc,

I am not sure that I understand the problem you are facing. Could you please replicate the described behavior in a simple dojo? A sample list of steps and explanation of the actual behavior and expected behavior will be very helpful. 

Regards,
Boyan Dimitrov
Telerik by Progress

[Marc]

Hi Boyan,

I have a spreadsheet with dataSource component. When the user goes to the screen, they import some data into the spreadsheet (using the spreadSheet.fromFile method). They then press a save button. In the function of the save button, I call the method dataSource.data().length, but it is empty. Does that make sense?

[Boyan Dimitrov]

Hello Marc,

Thank you for the clarification. 

Indeed this is rather expected behavior since the fromFile method of the Kendo UI SpreadSheet does not create a DataSource for the sheet or update the existing one (if a DataSource is set initially for the sheet).

In order to change the data of the DataSource of specific sheet you can use the data method of Kendo UI DataSource or the setDataSource method of the sheet. 

Regards,
Boyan Dimitrov
Telerik by Progress

[Marc]

Hi Boyan,

Thanks for the information! I can understand why it behaves like this. Is there any way to update the datasource with the new data?

Ie, after spreadsheet.fromFile is there a parameter that gives me the new data which is derived from the imported file? Something to set the data to as below:

spreadSheet.fromFile(importFileElement.files[0]).then(function () {

mydataSource.data(I need a variable here?);

})

Thanks!

Marc

[Boyan Dimitrov]

Hello Marc,

I am afraid that there is no variable in the promise resolve function with the new data. Furthermore, the approach from your last response will populate the SpreadSheet (fromFile method) and then populate the DataSource of the DataSource. The initial operation of populating the SpreadSheet from file will be unnecessary. Especially that in the promise resolve there is no data one additional operation will be required in order to export to JSON the SpreadSheet after populating from file. This will help you to have data from the excel file as JSON and pass it to the data method of the DataSource. 

My suggestion is to simply find a way to extract data from the excel file to JSON array and pass it directly to the DataSource of the Sheet. 

Regards,
Boyan Dimitrov
Telerik by Progress

[Marc]

Hi Boyan,

Thanks for the information. So essentially the export/import functionality you have (fromFile etc methods) are only for displaying data inside the spreadsheet and cannot be used with the transport layer to save the data?

[Boyan Dimitrov]

Hello Marc,

Unfortunately the fromFile does not create/update a DataSource for a sheet. Indeed its purpose is to display data and it can not be used with the transport layer out of the box. As I mentioned a solution would be extract JSON data from the excel file and pass it to the DataSource of the SpreadSheet. 

Regards,
Boyan Dimitrov
Telerik by Progress

[Marc]

Hi Boyan,

Thanks again for the help. I managed to work around this by importing the data into the spreadsheet using the built in method, and then I use the .range of the spreadsheet to select all the values, create my JS objects and then used dataSource.data(myArrayOfObjects) to sync the dataSource.

Thanks again!

[Doug]

I am having problems determining how to configure this properly.  I have a remote transport data source bound to the spreadsheet, and want to allow the user to upload a spreadsheet, and I need to intercept the uploaded spreadsheet data to modify it.  Since there is no hook event before sheet.fromFile(...) populates the spreadsheet, I have started to use a third party library to upload the spreadsheet and parse it to get json.  From there I modify the data, then call sheet.fromJSON(...).  However, this doesn't show the new data, if the old sheet had already been initialized.  I'm not sure why this is, since this example properly loads the json after the sheet has been initialized.  Perhaps something with multiple tabs.  I'll need to break down the current app to find the problem.  But even if it loaded properly, I am wanting to have it replace the data on the server as well as the display.  There are options to reset the datasource, which clears out all the displayed items, but that is not calling destroy on the server.  So possibly I need to iterate over all items in the existing sheet and call datasource.remove(...) on it, which will call the datasource's destroy which will delete from the server.  Then I can iterate over the new data and call datasource.add(...).  But even after doing this, the spreadsheet still shows the old data.  And this approach suffers from a flaw that if the uploaded data has more rows than the existing spreadsheet, it throws an error because the spreadsheet sheet cannot have more rows than it was initialized with (serious limitation).  So, instead of calling remove and add on the existing datasource, should I create a new datasource and insert a new sheet in the workbook using fromJSON(...), which is sort of back to the original approach?  I've played around with insertSheet, removeSheet, and setting activeSheet, but it is not working correctly.

 

This is a long winded way of asking, of the many options, what is the best setup for this situation?

 

[Veselin Tsvetanov]

Hello Doug,

As far as I can understand, you need to:
- Initially populate a Spreadsheet Sheet from a DataSource;
- Allow the user to upload a file and at this point alter only the data in the Sheet bu t keep the DataSOurce configuration;
- Save the changed values / data to the server.

If this is the case, I am afraid, that the fromJSON() method of the Spreadsheet is not an appropriate option. This is because it will simply remove the whole Sheets configuration from the widget (including the DataSource configured) and replace it with the data from the JSON. The same will happen if you use fromFile() too.

Therefore, there are two options that you could proceed with:

- As you suggested, you could destroy all the existing items in the Sheet DataSource and add the new once which you will get from the file. Note that the important part here is to call dataSource.sync() as without it the changes won't be sent to the server.

- The other (and simpler) option would be to read the new data from file, send it with an AJAX call to the server, repopulate it there and fter that has been done, call dataSource.read() on the Sheet DataSource object to retrieve the new data from the server;

Regards,
Veselin Tsvetanov
Progress Telerik