MVC Kendo Spreadsheet local data bidding Create/Update/Delete

Question

I use Kendo Spreadsheet widget with local data binding in MVC. How can I set the datasource to the spreadsheet to do create, update and delete operation?

I know this example,

https://demos.telerik.com/aspnet-mvc/spreadsheet/datasource

but ajax remote binding is not fine for me, because the excel file has a realy complex header with many merged cells. And remote data fills the first row which overwrite header files. So I use this example:

https://www.telerik.com/forums/loading-data-into-a-specific-row-in-kendo-spreadsheet

After using it data liested in the proper rows, but it's very slow.

So please give me some information to resolve this issue. A have to save modifications of the spreadsheet.

Here I list a simple example of how I bind the data:

ViewModel:

public int ID { get; set; }
    public int Name { get; set; }
    public string Status { get; set; }
}

Action in controller:

public ActionResult Index()
{
    List<ViewModel> model = service.GetData();
    return View(model);
}

View cshtml:

@model List<Namespace.Model.ViewModel>

@section body {

    @(Html.Kendo().Spreadsheet()
        .Columns(3)
        .Name("DataExcel")
        .HtmlAttributes(new { style = "width:auto; height: 770px;" })
        .Toolbar(false)
        .Sheetsbar(false)
        .Events(e => e
            .Select("Scripts.onSelect"))
        .Sheets(sheets =>
        {
            sheets.Add()
            .Name("DataSheet")
            .Columns(columns =>
            {
                // Megbízott neve és státusza + Tantárgy neve
                columns.Add().Width(50);
                columns.Add().Width(100);
                columns.Add().Width(100);
            }).Rows(rows =>
            {
                foreach (var dataRow in Model)
                {
                    rows.Add().Height(50).Cells(cells =>
                    {
                        // Megbízott státusza
                        cells.Add()
                            .Value(dataRow.ID)
                            .VerticalAlign(SpreadsheetVerticalAlign.Center)
                            .Color("black");

                        // Tantárgy neve
                        cells.Add()
                            .Value(dataRow.Name)
                            .VerticalAlign(SpreadsheetVerticalAlign.Center)
                            .Color("black");

                        // Órakeret
                        cells.Add()
                            .Value(dataRow.Status)
                            .VerticalAlign(SpreadsheetVerticalAlign.Center)
                            .Color("black")
                            .Bold(true);
                    }
                }
            });
        })
    )
}

Answer 1

With DataSource binding setting custom headers will not work, other than the way demonstrated in the example you've seen. And with local data binding you can define the header and merged cells via the spreadsheet definition:

.Rows(rows => {
        rows.Add().Height(50).Cells(cells =>
            {
                cells.Add()
                    .Value("Merged Top Row")
                    .FontSize(32);
            });
        rows.Add().Height(50).Cells(cells =>
            {
                cells.Add();
                cells.Add()
                    .Value("Merged cells on <br/> second row")
                    .Html(true)
                    .FontSize(20);
            });
        foreach (var dataRow in data)
        {
         ...
        }
    });

As for saving with local data - it's not really clear where do you need to save the spreadsheet or do you need to save it at all or are you interested in only the changes made. Also the spreadsheet does not track the edited cells, similar to the grid (and flag them as dirty, for example), so for saving these are the options I can think of:

• To save a .xlsx to the users machine just call the saveAsExcel method - example.
• If you need any data you can use toJSON method, submit to a remote endpoint and serialize the data to a workbook, as shown in one of the demos. You can store it then as desired.
• Use the Range API and the values method to get the value of the desired range. Submit this data or do whatever required.