Set a cell value to a Date with API

2 posts, 0 answers
  1. Richard
    Richard avatar
    140 posts
    Member since:
    Feb 2012

    Posted 03 Nov Link to this post

    I want to use range to set a cells value to a date.

    What is the date literal expression that can be used in range.value() ?

    For example:

    var book = $("#book").kendoSpreadSheet({}).data("kendoSpreadsheet");
    var sheet = book.activeSheet();
     
    sheet.Range("A1")
    .value( ... what spreadsheet date literal goes here ...)
    .format(kendo.spreadsheet.formats.date)
    ;

     

    I have been able to use a formula to put a date value in the cell

    var book = $("#book").kendoSpreadSheet({}).data("kendoSpreadsheet");
    var sheet = book.activeSheet();
      
    var dateTime = new Date ("10/31/2017 12:00:00 AM");
    var dateMeth = "=DATE(" + dateTime.getFullYear() + "," + dateTime.getMonth() + "," + dateTime.getDay() + ")";
     
    sheet.Range("A1")
    .formula(dateMeth)
    .format(kendo.spreadsheet.formats.date)
    ;

     

    It works, but is ugly !

    My actual problem is a little more complicated because I am doing this in a Razor based MVC view that has get the value from a DateTime field in the view model.  The spreadsheet 'construction' is too complicated for using MVC HtmlHelpers.

     

  2. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    621 posts

    Posted 07 Nov Link to this post

    Hi Richard,

    In order to set a cell value to a date, you will need to pass a JavaScript Date object to its value() method:
    var book = $("#book").kendoSpreadsheet({}).data("kendoSpreadsheet");
    var sheet = book.activeSheet();
    var dateTime = new Date("10/31/2017 12:00:00 AM");
     
    sheet.range("A1")
      .value(dateTime)
      .format(kendo.spreadsheet.formats.date);

    Here you will find a small sample demonstrating the above approach.

    In razor, you could get the date from the Model in the following way:
    var dateTime = new Date(@Model.date.ToString("yyyy,MM-1,dd"));

    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