This is a migrated thread and some comments may be shown as answers.

DateTime displayed incorrectly when loaded in spreadsheet

1 Answer 31 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Nithiya
Top achievements
Rank 1
Nithiya asked on 09 Nov 2017, 12:14 PM

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?

1 Answer, 1 is accepted

Sort by
0
Veselin Tsvetanov
Telerik team
answered on 13 Nov 2017, 11:38 AM
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.
Tags
Spreadsheet
Asked by
Nithiya
Top achievements
Rank 1
Answers by
Veselin Tsvetanov
Telerik team
Share this question
or