DateTime displayed incorrectly when loaded in spreadsheet

[Nithiya]

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?

[Veselin Tsvetanov]

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