Merge exported sheet data

4 posts, 0 answers
  1. Mitch
    Mitch avatar
    3 posts
    Member since:
    Aug 2019

    Posted 12 Aug 2019 Link to this post

    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.

     
  2. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 14 Aug 2019 Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. Mitch
    Mitch avatar
    3 posts
    Member since:
    Aug 2019

    Posted 14 Aug 2019 in reply to Veselin Tsvetanov Link to this post

    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

     
  4. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 15 Aug 2019 Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top