Bind spreadsheet data to model?

[Duke]

Hi, I'm trying to use server-side wrappers of the UI Spreadsheet for ASP.NET MVC.  I've looked over the documentation and searched this forum, and I can't find the answer to this question:  is it possible to bind the spreadsheet data to a model passed to a view, in the same manner as it's done for the Grid control?  For example, like the below.

Thank you!

example below;

@(Html.Kendo().Grid(Model) //Bind the Grid to the Model property of the view.
          .Name("Grid")
          .Columns(columns =>
          {
              columns.Bound(p => p.ProductID);   //Create a column bound to the "ProductID" property
              columns.Bound(p => p.ProductName); //Create a column bound to the "ProductName" property
              columns.Bound(p => p.UnitPrice);   //Create a column bound to the "UnitPrice" property
              columns.Bound(p => p.UnitsInStock);//Create a column bound to the "UnitsInStock" property
          })
         .Pageable() //Enable paging.
    )

 

[Anton]

Hello Duke,

The Kendo UI Spreadsheet for MVC does not support model binding in the same manner as the Kendo UI Grid for MVC. Our Spreadsheet / DataSource binding demo shows how to bind a sheet's datа source to a View Model.

Regards,
Anton
Telerik by Progress

[Duke]

    Thanks, Anton.  So to summarize, are the two approaches to loading/saving data:  1) the Datasource Binding method you've referenced above, and 2) using my own front-end JSON and Ajax calls?

Thanks again.

[Duke]

Hi Anton,

As a followup question, after seeing the demo you pointed out here: https://demos.telerik.com/aspnet-mvc/spreadsheet/datasource, can you tell me where the libraries for the namespace Kendo.Mvc.Examples.Models are from?  I don't see them in anything in VS that  I have available, and therefore can't get the demo to work.

Thoughts?

Thanks.

[Veselin Tsvetanov]

Hello Duke,

Answering your first question, the Spreadsheet HTML helper, indeed, offers two ways in which it could be populated:

- Its Sheets could be bound to a DataSource, as shown on the demo discussed. In such case, the DataSource will take care to read and send data from / to the controller; or

- The Spreadsheet content could be loaded on the client. In such scenario in could be loaded from a JSON, its data could be explicitly set in the HTML helper, or it could be bound to collection of SpreadsheetSheets (as initially configured in the following demo). In all the the above three cases, any changes on the data / Spreadsheet should be manually sent to the server;

The Kendo.Mvc.Examples.Models namespace is part of the Kendo MVC Examples project, that comes as part of the MVC wrappers suite. If you have a local installation of the Kendo MVC suite, you should be able to find that project on the following location: ​C:\Program Files (x86)\Telerik\UI for ASP.NET MVC R3 2016\wrappers\aspnetmvc\Examples\VS2015

Regards,
Veselin Tsvetanov
Telerik by Progress

[Neil]

Is it possible to dynamically set the columns for the spreadsheet similar to the grid? For my application I will not know until run time what the columns are, and they can change based on the grid view the user chooses.

[Neil]

I'm almost there.

Here's my razor.

Attached is the result. The only problem is that it's showing more columns than I want it to.

 

@(Html.Kendo().Spreadsheet()
                    .Name("spreadsheet")
                    .HtmlAttributes(new { style = "width:100%; height: 700px" })
                    .Toolbar(true)
                    .Sheetsbar(true)
                    .Sheets(sheets =>
                    {
                        sheets.Add()
                            .Name(Model.ViewName)
                            .DataSource<Datum>(ds => ds
                                .Ajax()
                                .Batch(true)
                                .Read("Spreadsheet_Read", "Data", new { moduleId = Model.ModuleId, viewId = Model.ViewId })
                                .Update("Spreadsheet_Update", "Data")
                                .Events(e => e.Change("onChange").Error("OnError"))
                                .Model(m =>
                                {
                                    if (Model.GridColumns != null)
                                    {
                                        m.Id(p => p.Id);
                                        foreach (MyColumnSettings col in Model.GridColumns)
                                        {
                                            m.Field(col.PropertyName, col.ColType).Editable(col.Editable);
                                        }
                                    }
                                })
                            )
                            .Columns(columns =>
                            {
                                columns.Add().Width(100);
                                if (Model.GridColumns != null)
                                {
                                    foreach (MyColumnSettings col in Model.GridColumns)
                                    {
                                        columns.Add().Width(col.Width);
                                    }
                                }
                            })
                            .Rows(rows =>
                            {
                                rows.Add().Height(40).Cells(cells =>
                                {
                                    cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Center).Value("Id").Enable(false);
                                    if (Model.GridColumns != null)
                                    {
                                        foreach (MyColumnSettings col in Model.GridColumns)
                                        {
                                            cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Center)
                                                .Value(col.Title).Enable(col.Editable);
                                        }
                                    }
                                });
                            });
                    }))

[Veselin Tsvetanov]

Hi Neil,

I am afraid, that the desired dynamic configuration of columns could not be applicable for the Spreadsheet widget. The reason for that is the fact, that the Spreadsheet actually disregards the Fields configured in the DataSource and displays all the fields from the Model in the respective Sheet.

A possible workaround for the above issue could be the suggest in the following forum thread. Attached you will find a very simple sample on how the above could be applied to an MVC Spreadsheet. Note, that the actual DataSource initialization was moved outside of the HTML helper definition.

Regards,
Veselin Tsvetanov
Telerik by Progress

[Neil]

Thanks Veselin, I think I'm close. Here's my modified code.

I'm getting an e.slice error though on the sheet.setDataSource call.

Attached is the expected output from the datasource read and the GetsFieldsForSpreadsheet.

@using SOCKETWorx.Site.Models
@model GridDynamicViewModel

@{
    ViewBag.Title = "Spreadsheet Mode";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<p>
    <div>
        <table style="width:85%">
            <tr>
                <td>
                    <span class="text-bold" id="span_ModuleName"><a href="javascript: returnToGrid();">@Model.ModuleName</a></span>
                </td>
            </tr>
            <tr>
                <td colspan="4" style="text-align:left">
                    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                </td>
            </tr>
            <tr>
                <td>
                    <hr />
                </td>
            </tr>
            <tr>
                <td>
                   @(Html.Kendo().Spreadsheet()
                    .Name("spreadsheet")
                    .HtmlAttributes(new { style = "width:100%; height: 700px" })
                    .Toolbar(true)
                    .Sheetsbar(true)
                    .Sheets(sheets =>
                    {
                        sheets.Add()
                            .Name(Model.ViewName)
                            .Columns(columns =>
                            {
                                if (Model.GridColumns != null)
                                {
                                    foreach (MyColumnSettings col in Model.GridColumns)
                                    {
                                        columns.Add().Width(col.Width);
                                    }
                                }
                            })
                            .Rows(rows =>
                            {
                                rows.Add().Height(40).Cells(cells =>
                                {
                                    cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Center).Value("Id").Enable(false);
                                    if (Model.GridColumns != null)
                                    {
                                        foreach (MyColumnSettings col in Model.GridColumns)
                                        {
                                            cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Center)
                                                .Value(col.Title).Enable(col.Editable);
                                        }
                                    }
                                });
                            });
                    })
                )
                </td>
            </tr>
        </table>
    </div>
</p>

<script type="text/javascript">
    $(document).ready(function () {
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        var sheet = spreadsheet.activeSheet();

        var dataSource = new kendo.data.DataSource({
            batch: true,
            transport: {
                read: '@(Url.Action("Spreadsheet_Read", "Data", new { moduleId = Model.ModuleId, viewId = Model.ViewId }))'
            },
            schema: {
                model: {
                    id: "Id"
                }
            },
        });

        $.get({
            url: '@(Url.Action("GetFieldsForSpreadsheet", "Data"))',
            data: { moduleId: @Model.ModuleId, viewId: @Model.ViewId },
            success: function (result) {
                var cols = [];

                for (var i = 0; i < result.Data.length; i++) {
                    var currentField = result.Data[i];

                    cols.push({
                        field: currentField.PropertyName,
                        title: currentField.Title,
                        type: currentField.ColType
                    });
                }

                sheet.setDataSource(dataSource, cols);
            }
        });
    });

    function OnError(e) {
        if (e.errors) {
            var message = "Errors:\n";
            $.each(e.errors, function (key, value) {
                if ('errors' in value) {
                    $.each(value.errors, function () {
                        message += this + "\n";
                    });
                }
            });
            alert(message);
        }
    }

    function returnToGrid() {
        var moduleId = "@Model.ModuleId";
        var viewId = "@Model.ViewId";
        var target = '@Url.Action("List", "Data")';

        $.post(target, { moduleId: moduleId, viewId: viewId });
    }
</script>

[Veselin Tsvetanov]

Hello Neil,

Attached you will find a modified version of the sample sent, based on the additional information provided. Note the following:

- I have removed the square brackets ('[]') from the names of the properties used, so they are correctly mapped to the names of the properties in the Datum view model. In case those property names have the brackets in their names in the view model, you will have to put them back;

- I have removed the Editable configuration of the columns, as those won't be respected;

- I have mapped the values (field) to the ​DBColumnName property name, as in the Datum view model those fields hold the actual values;

Regards,
Veselin Tsvetanov
Telerik by Progress

[Neil]

I still get the following error. This is happening on sheet.setDataSource(dataSource, cols); call.

Uncaught TypeError: e.slice is not a function
    at init.success (kendo.all.js:6677)
    at success (kendo.all.js:6610)
    at Object.n.success (kendo.all.js:5599)
    at i (jquery.min.js:2)
    at Object.fireWith [as resolveWith] (jquery.min.js:2)
    at y (jquery.min.js:4)
    at XMLHttpRequest.c (jquery.min.js:4)
success @ kendo.all.js:6677
success @ kendo.all.js:6610
n.success @ kendo.all.js:5599
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
y @ jquery.min.js:4
c @ jquery.min.js:4

 

Attached is my view, controller, and sql scripts needed to create a similar environment that I have.

[Veselin Tsvetanov]

Hello Neil,

I have noticed that in your ​Spreadsheet_Read controller action you are returning:

return Json(dataTable.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);

May I ask you to use IEnumerable instead of a DataTable? Also, remove the ​ToDataSourceResult(request) call. Basically the return statement should be altered to the following:

return Json(resultEnumerable, JsonRequestBehavior.AllowGet);

If this does not help, in order to be able to recreate the same environment at my end, I will also need the MyColumnSettings ViewModel used to pass data to the view, any other models, helpers and classes. Therefore, I would like to ask you to send the entire MVC project, that you have created.

Please, remove any external dependencies, that are not related to the discussed Spreadsheet implementation. You could also remove the bin, obj and packages folders from the project, to make the zip smaller.

Regards,
Veselin Tsvetanov
Telerik by Progress

[Neil]

Veselin,

Got it working after implementing your suggestions.

Thanks so much

[Jonathan]

[quote]Veselin Tsvetanov said:
The Kendo.Mvc.Examples.Models namespace is part of the Kendo MVC Examples project, that comes as part of the MVC wrappers suite. If you have a local installation of the Kendo MVC suite, you should be able to find that project on the following location: C:\Program Files (x86)\Telerik\UI for ASP.NET MVC R3 2016\wrappers\aspnetmvc\Examples\VS2015
[/quote]

Hello,

I'm looking for this exact example as well.  My local install is in this folder:

C:\Program Files (x86)\Progress\Telerik UI for ASP.NET MVC R1 2019\wrappers\aspnetmvc

... but there is no Examples subfolder (only Binaries/EditorTemplates).

Any other way to find this example?

Thanks,

Jonathan

 

[Veselin Tsvetanov]

Hi Jonathan,

The Demos application is also present in the telerik.ui.for.aspnetmvc.2019.1.220.commercial archive, available for download in your Telerik account. The examples are available in the \wrappers\aspnetmvc\Examples\ folder of the archive.

Regards,
Veselin Tsvetanov
Progress Telerik