Spreadsheet with DataSource - how to format DateTime Cells

[Philipp]

Hi,

I have defined my Spreadsheet as follows:

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
    .Sheets(sheets =>
    {
        sheets.Add()
            .Name("mySheet")
            .Columns(c =>
            {
                c.Add().Width(150);
                c.Add().Width(150);
            })
            .DataSource<MyViewModel>(ds => ds
                .Ajax()
                .Batch(true)
                .Read("ReadTimeSeries", "MyController")
                .Model(m => {
                    m.Field(p => p.Timestamp);
                    m.Field(p => p.Value);
                })
            );
    })
    .Sheetsbar(false)
)

When the Spreadsheet loads the data, it is displayed as Date only - the time part seems to be missing.

This is my DataModel:

    public class MyViewModel
    {
        [Display(Name = "Zeitstempel")]
        [DisplayFormat(DataFormatString = "{0:dd.MM.yyyy HH:mm}"]
        public DateTime Timestamp { get; set; }

        [Display(Name = "Wert")]
        [DisplayFormat(DataFormatString = "{0:#,###.###}")]
        public decimal Value { get; set; }
    }

The DisplayFormatAttribute seems to be ignored. The Problem is that the count of the rows vary, so I can't predefine the format using Rows.Add().Cells(c => c.Add().Format("dd.MM.yyyy HH:mm").

 

How can I achieve the Timestamps to be displayed with date AND time?

[Veselin Tsvetanov]

Hello Philipp,

You will need to set the desired format for the cells (the entire column range), after the data has been populated in the Spreadsheet. To do that you could use the second render event of the widget:

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
...
    .Events(e => e.Render("onRender"))
...

and:

var renderNember = 0;

function onRender(e) {
  var spreadsheet = e.sender;

  renderNember++;

  if (renderNember < 2) {
    return;
  } else if (renderNember > 2) {
    spreadsheet.unbind('render');
    return;
  }

  var sheet = spreadsheet.activeSheet();
  // In case the column "C" contains the dates to be formatted
  var range = sheet.range('C2:C200');
  range.format('d/M/yyyy hh:mm');
}

Regards,
Veselin Tsvetanov
Progress Telerik