Bind spreadsheet data to model?

13 posts, 0 answers
  1. Duke
    Duke avatar
    21 posts
    Member since:
    Dec 2016

    Posted 13 Jan Link to this post

    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.
        )

     

  2. Anton
    Admin
    Anton avatar
    81 posts

    Posted 17 Jan Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Duke
    Duke avatar
    21 posts
    Member since:
    Dec 2016

    Posted 17 Jan in reply to Anton Link to this post

        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.

  4. Duke
    Duke avatar
    21 posts
    Member since:
    Dec 2016

    Posted 18 Jan in reply to Anton Link to this post

    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.

  5. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 19 Jan Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  6. Neil
    Neil avatar
    28 posts
    Member since:
    Aug 2016

    Posted 16 Mar Link to this post

    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.
  7. Neil
    Neil avatar
    28 posts
    Member since:
    Aug 2016

    Posted 16 Mar in reply to Neil Link to this post

    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);
                                            }
                                        }
                                    });
                                });
                        }))
  8. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 17 Mar Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  9. Neil
    Neil avatar
    28 posts
    Member since:
    Aug 2016

    Posted 17 Mar in reply to Veselin Tsvetanov Link to this post

    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>
  10. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 21 Mar Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  11. Neil
    Neil avatar
    28 posts
    Member since:
    Aug 2016

    Posted 21 Mar in reply to Veselin Tsvetanov Link to this post

    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.

  12. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    516 posts

    Posted 23 Mar Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  13. Neil
    Neil avatar
    28 posts
    Member since:
    Aug 2016

    Posted 23 Mar in reply to Veselin Tsvetanov Link to this post

    Veselin,

    Got it working after implementing your suggestions.

    Thanks so much

Back to Top