Merge exported sheet data

[Mitch]

Hey everyone,

We are currently trying out Kendo UI to see if it would fit our needs.
Spreadsheets are very important us, and we want to do something very specific with them.

We want to be able to merge multiple exported spreadsheets (output of saveJSON()), so that the first spreadsheet will be shown on top of the second spreadsheet, ...
We have looked through the developer documentation, but there does not seem to be a way to do it.
The problem is that there is no way to, in code, load/paste data at a specific row index.

If we were able to 'copy' and 'paste' in code, we might get somewhere, although that is still not ideal.

Use case: We want the user to create 'template' spreadsheets, which can later be combined in a single spreadsheet.
f.e.:

- User creates a 'header ' template spreadsheet with content like: Title, name, an image
- User creates a 'body' template spreadsheet specific information regarding ...
- User creates a new empty spreadsheet and loads the header template spreadsheet, and after that, the body spreadsheet.
- The header template will appear above the body spreadsheet.

If someone could help us with this, it would be very much appreciated.

 

[Veselin Tsvetanov]

Hello Mitch,

You could achieve the desired by using the toJSON() and fromJSON() methods of the Spreadsheet widget. Here you could find a small Dojo sample implementing a scenario as the described. The user is allowed to type and style any content in the header and the body Spreadsheet widgets. Upon button click the contents of the two widgets will be merged and passed to the output Spreadsheet:

var header = $('#header').getKendoSpreadsheet();
var body = $('#body').getKendoSpreadsheet();
var output = $('#output').getKendoSpreadsheet();

var json = header.toJSON();
var bodyContent = body.toJSON();
json.sheets[0].rows = json.sheets[0].rows.concat(bodyContent.sheets[0].rows);

output.fromJSON(json);

I hope the above helps you. If you have any other questions, please do not hesitate to contact us.

Regards,
Veselin Tsvetanov
Progress Telerik

[Mitch]

Hey Veselin,

Thank you very much for your answer.
Unfortunately that is not what we want.

What we want is that the content of the body spreadsheet is appended at the bottom of the header spreadsheet.
So the body spreadsheet does not overwrite stuff from the header, but instead starts after the last row of the header:

 

---- header template
header content line 1
header content line 2
---

 

---- body template
body content line 1
body content line 2
---

 

---- output
header content line 1
header content line 2
body content line 1
body content line 2

---

 

As you can understand this is much more difficult because you have to make sure the cell references (formulas) still work, and the row indexes are correct.

 

I am sorry for the misunderstanding... I can see why you understood it this way

 

[Veselin Tsvetanov]

Hello Mitch,

Thank you for the further info provided.

The requested could be achieved by altering a bit the merging logic, as long as you know the expected number of rows in the Header section: 

var header = $('#header').getKendoSpreadsheet();
var body = $('#body').getKendoSpreadsheet();
var output = $('#output').getKendoSpreadsheet();

var json = header.toJSON();
var bodyContent = body.toJSON();

bodyContent.sheets[0].rows.forEach(function(row){
    // 5 rows will be present in the Header Spreadsheet
    row.index = row.index + 5;
});

json.sheets[0].rows = json.sheets[0].rows.concat(bodyContent.sheets[0].rows);

output.fromJSON(json);

Here you could find the new version of the Dojo sample.

That, however, would not resolve the case in which you have formulas in the rows that have their indexes changed. As an alternative, you could keep the initially suggested implementation and disable those rows in the Body Spreadsheet that should be occupied by content from the header:

$('#output').kendoSpreadsheet();
var bodySpread = $('#body').getKendoSpreadsheet();
bodySpread.activeSheet().range('A1:AX5').enable(false).background('red');

and:

var header = $('#header').getKendoSpreadsheet();
var body = $('#body').getKendoSpreadsheet();
var output = $('#output').getKendoSpreadsheet();

var json = header.toJSON();
var bodyContent = body.toJSON();

json.sheets[0].rows = json.sheets[0].rows.concat(bodyContent.sheets[0].rows);

output.fromJSON(json);
output.activeSheet().range('A1:AX5').enable(true).background(null);

Here is the modified version of the Dojo.

Regards,
Veselin Tsvetanov
Progress Telerik