DateTime displayed incorrectly when loaded in spreadsheet

2 posts, 0 answers
  1. Nithiya
    Nithiya avatar
    2 posts
    Member since:
    May 2017

    Posted 09 Nov Link to this post

    Hi

    I am loading a spreadsheet with the help of a dataset result. I have a stored procedure that output's the result to a dataset and this dataset is being loaded to a datatable and rendered on the speadsheet using datasource. Below is my code

    public ActionResult Products_Load([DataSourceRequest]DataSourceRequest request)
            {
                DataTable dt = new DataTable();
                dt.Clear();
     
                DataSet dsGetData = HandlerUtilities.HandlerFunctions.GetDynamicSheetData(1, 4);
                if (dsGetData != null)
                {
                    dt = dsGetData.Tables[0];
                }
     
                dt.Columns.Remove("atUserid");
                dt.Columns.Remove("atTimestamp");
                dt.AcceptChanges();
     
                DataSourceResult result = dt.ToDataSourceResult(request);
                JsonResult oJsonResult = Json(result, JsonRequestBehavior.AllowGet);
                return oJsonResult;
            }

     

    When the data is rendered on the spreadsheet, the datetime values are getting converted into /Date(xxxxxx)/ instead of original values. I have attached the screenshot of my output for reference. My dataset is dynamic and everytime it will get changed and due to this I am not using a view model to bind the data.  Hence, I cant provide the fieldname and its format in the client side as I dont know what the fieldnames will be. Is there any workaround fr this?

  2. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    621 posts

    Posted 13 Nov Link to this post

    Hi Nithiya,

    In order to properly display Date values in a Spreadsheet with DataSource configured, you will need to pass the appropriate type to the DataSource.schema.model.fields configuration. In case you columns are dynamically loaded and they are not known in advance, you will need to dynamically configure that option. In this case you could predefine an empty Spreadsheet:
    @(Html.Kendo().Spreadsheet()
        .Name("spreadsheet")
        .HtmlAttributes(new { style = "width:100%; height: 700px" })
        .Sheets(sheets =>
        {
            sheets.Add()
                .Name("spreadsheet");
        })
    )

    And configure its DataSource on initial page load:
    $(document).ready(function () {
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        var sheet = spreadsheet.activeSheet();
     
        $.get({
            url: '@(Url.Action("GetFieldsForSpreadsheet", "Data"))',
            success: function (result) {
                var cols = [];
                var fields = {};
                 
                for (var i = 0; i < result.Data.length; i++) {
                    var currentField = result.Data[i];
     
                    cols.push({
                        field: currentField.ColumnName,
                        title: currentField.Title,
                        type: currentField.ColType
                    });
     
                    fields[currentField.ColumnName] = {
                        type: currentField.ColType
                    };
                }
     
                var dataSource = new kendo.data.DataSource({
                    batch: true,
                    transport: {
                        read: '@(Url.Action("Spreadsheet_Read", "Data"))'
                    },
                    schema: {
                        model: {
                            id: "Id",
                            fields: fields
                        }
                    },
                });
     
                sheet.setDataSource(dataSource, cols);
            }
        });
    });

    Where the GetFieldsForSpreadsheet call should return the definition of the currently loaded columns. For example:
    public ActionResult GetFieldsForSpreadsheet()
    {
        var result = new List<ColumnSettings>()
        {
            new ColumnSettings() { ColumnName = "Id", Title = "Title Id", ColType = "number" },
            new ColumnSettings() { ColumnName = "Group", Title = "Title GROUP", ColType = "string" },
            new ColumnSettings() { ColumnName = "Date", Title = "Date", ColType = "date" }
        };
     
        return Json(new { Data = result }, JsonRequestBehavior.AllowGet);
    }

    Attached you will find a simple MVC project, implemnting the above suggestion.

    Regards,
    Veselin Tsvetanov
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top