保存时的Kendo jQuery电子表格日期值

Question

So I have a Kendo jQuery spreadsheet that is bound to a remote datasource. The fields in the spreadsheet are also data-driven, so I have a process in place that will loop over all of the columns and apply a validation to the column based on the type of data. When I apply the validation to date values it seems like all the values are converted into JavaScript Date objects.

This causes me two problems. 1. When I go to save the data it sends the date object to the server, not a string of the mm/dd/yyyy. 2. It treats blank values as 12/30/1899.

Here is a portion of the code that I use:

var record_id = 1;
var columnData = {
    field_name1: {"title":"FIELD1", "type":"string", "width":50, "order":0},
    field_name2: {"title":"FIELD2", "type":"date", "width":50, "order":1}
};
var columnMap = ['field_name1', 'field_name2'];

// The following code executes in the event that is triggered when the data is successfully read.
function readSource(e) {
    $.ajax({
        url: './spreadsheet.php',
        data: { id: record_id, action: 'list' },
        dataType: 'json',
        method: 'POST',
        success: function(result) {
            e.success(result);
            $.each(columnData, function(field, data) {
                var range = toColumnName(columnMap.indexOf(field) + 1) + '2:' + toColumnName(columnMap.indexOf(field) + 1) + (result.count + 1);
                var validator = false;
                var format = '';
                switch (data.type) {
                    case 'date': {
                        validator = {
                            dataType: 'date',
                            comparerType: 'between',
                            from: 'DATEVALUE("1/1/2000")',
                            to: 'DATEVALUE("1/1/2100")',
                            allowNulls: true,
                            showButton: true,
                            type: 'reject',
                            titleTemplate: 'Date Error',
                            messageTemplate: 'Enter a valid date between 1/1/2000 and 1/1/2100.'
                        };
                        format = 'mm/dd/yyyy';
                        break;
                   }
                }
                if (validator !== false) {
                    sheet.range(range).validation(validator);
                }
                if (format === '') {
                    sheet.range(range).format(format);
                }
            });
        }
    });
}

// On save
function submitSource(e) {
    $.ajax({
    url: './spreadsheet.php',
    data: { action: 'update', id: record_id, records: e.data },
    method: 'POST',
    dataType: 'json',
    success: function (result) {
        e.success(result.Updated, 'update');
        e.success(result.Created, 'create');
        e.success(result.Destroyed, 'destroy');
    }
}

// Convert index to column letter (1 => 'A', 27=>'AA')
function toColumnName(num) {
    for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
        ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
    }
    return ret;
}

I will include a link to a screenshot that shows what happens to cells that should be blank when I click the save button. The first row in the image had dates that were populated. The rows below that were blank.

What needs to change to allow the appropriate date values to be sent back to the server and how can I avoid the trouble with the blanks?

Answer 1

The spreadsheet.php code probably doesn't properly format the date.

December 30, 1899 is the epoch date for Microsoft Excel and others: https://en.wikipedia.org/wiki/Epoch_(reference_date)#Notable_epoch_dates_in_computing

So you are probably feeding the value "1" instead of null.