Loading Data into a specific row in Kendo Spreadsheet

[Ian]

Just venturing into Kendo Spreadsheet and already hit upon a problem and I can't seem to find a solution anywhere in the documentation. I am using this demo https://demos.telerik.com/kendo-ui/spreadsheet/datasource. Simple enough to get going and hook into my own data. I can add a totals row, but I want it at the top just below the headings. I can add a new row easy enough but when the data loads it overwrites that (new) row.
How can I tell the spreadsheet to load the data at a specific row (say from row 4 onwards)?

Thanks for any help. I'll keep digging in the meantime

[Petar]

Hi Ian,

The described functionality can be easily implemented if the Spreadsheet component will be used only for displaying the data. I am saying this because by adding a new row that will display the totals of given columns we are changing the DataSource of the component. The last means that if you add a new row for the totals, edit some data and save it, you will not only save the edited data but the new "totals" row. 

If the above "read-only" scenario is valid for the application you are working on, please check this Dojo project demonstrating the desired functionality. What we do with the above-linked Dojo is to use the requestEnd event of the Spreadsheet's DataSource and run the following code on its first triggering:

dataSource.one("requestEnd", function(){
    setTimeout(function(){
      var sheet = $("#spreadsheet").data("kendoSpreadsheet").activeSheet();
      sheet.insertRow(1);
      sheet.range("A2").formula("sum(A3:A100)").background("#5557b0").color("#ffffff"); 
      sheet.range("B2").value("Changed 'ProductName'").background("#5557b0").color("#ffffff");
      sheet.range("C2").formula("sum(C3:C100)").background("#5557b0").color("#ffffff");;   
      sheet.range("D2").formula("sum(D3:D100)").background("#5557b0").color("#ffffff");     
      sheet.range("E2").background("#5557b0").color("#ffffff"); 
    })
  })

Using the range method we select a cell/range to format and with the background, color, value, etc. methods we format the cells in the desired way. You can read more about all the format options on this link. Before setting each cell's data, we insert a new row to shift the current data down.

If the application will need the "totals" row every time a Spreadsheet is opened and there is no issue with saving this additional row, the formulas calculating the "totals" can be added directly to the remotely fetched DataSource. Thus when the data is fetched, the row with the formulas will be displayed according to what has been set in the DataSource. For more info about adding formulas inside the DataSource, you can check this project: https://demos.telerik.com/kendo-ui/spreadsheet/index from our demo page.  

Please check the above examples and let me know if you need further assistance regarding the current case.

Regards,
Petar
Progress Telerik