分析行值时，Kendo电子表格将时间转换为十进制

Question

我正在尝试用js保存kendo电子表格数据。在解析行值时，我看到kendo电子表格将日期转换为数字，将时间转换为小数。我已经使用moment js将数字转换为日期，但无法将十进制数转换为确切的时间。如何转换它，如果我能得到日期和时间，如电子表格中所示，这将是很好的，因为它只意味着字符串。下面是示例代码和屏幕截图。

var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    var spreadsheedata = spreadsheet.toJSON();
    var JsonArray = [];
        for (var i = 1; i < spreadsheedata.sheets[0].rows.length; i++) {
                var dataObject = {};
                dataObject.SortOrder = spreadsheedata.sheets[0].rows[i].cells[0].value;
                dataObject.RoundNo = spreadsheedata.sheets[0].rows[i].cells[1].value;
                dataObject.Team = spreadsheedata.sheets[0].rows[i].cells[2].value;
                dataObject.MatchDate = moment(new Date((new Date('01/01/1900')).setDate(spreadsheedata.sheets[0].rows[i].cells[3].value - 1))).format(spreadsheedata.sheets[0].rows[i].cells[3].format.replace("dd", "DD").replace("mm", "MM"));
                dataObject.StartTime = spreadsheedata.sheets[0].rows[i].cells[4].value;
                dataObject.EndTime = spreadsheedata.sheets[0].rows[i].cells[5].value;
                dataObject.Location = spreadsheedata.sheets[0].rows[i].cells[6].value;
                dataObject.Field = spreadsheedata.sheets[0].rows[i].cells[7].value;
                JsonArray.push(dataObject);
    }

查看单元格编号格式和值

​

​

以下是日期和时间字段

Answer 1

使用函数ConvertExcelTime和momentjs转换StartTime和EndTime。

var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var spreadsheedata = spreadsheet.toJSON();
var JsonArray = [];
    for (var i = 1; i < spreadsheedata.sheets[0].rows.length; i++) {
            var dataObject = {};
            dataObject.SortOrder = spreadsheedata.sheets[0].rows[i].cells[0].value;
            dataObject.RoundNo = spreadsheedata.sheets[0].rows[i].cells[1].value;
            dataObject.Team = spreadsheedata.sheets[0].rows[i].cells[2].value;
            dataObject.MatchDate = moment(new Date((new Date('01/01/1900')).setDate(spreadsheedata.sheets[0].rows[i].cells[3].value - 1))).format(spreadsheedata.sheets[0].rows[i].cells[3].format.replace("dd", "DD").replace("mm", "MM").replace("-", "/").replace("-", "/"));
            dataObject.StartTime = moment(new Date(OrgTrainingCalendar.ConvertExcelTime(spreadsheedata.sheets[0].rows[i].cells[4].value))).format(spreadsheedata.sheets[0].rows[i].cells[4].format.replace("AM/PM", "a"));
            dataObject.EndTime = moment(new Date(OrgTrainingCalendar.ConvertExcelTime(spreadsheedata.sheets[0].rows[i].cells[5].value))).format(spreadsheedata.sheets[0].rows[i].cells[5].format.replace("AM/PM", "a"));
            dataObject.Location = spreadsheedata.sheets[0].rows[i].cells[6].value;
            dataObject.Field = spreadsheedata.sheets[0].rows[i].cells[7].value;
            JsonArray.push(dataObject);
}
ConvertExcelTime: function (excelTimestamp) {
    const secondsInDay = 24 * 60 * 60;
    const excelEpoch = new Date(1899, 11, 31);
    const excelEpochAsUnixTimestamp = excelEpoch.getTime();
    const missingLeapYearDay = secondsInDay * 1000;
    const delta = excelEpochAsUnixTimestamp - missingLeapYearDay;
    const excelTimestampAsUnixTimestamp = excelTimestamp * secondsInDay * 1000;
    const parsed = excelTimestampAsUnixTimestamp + delta;
    return isNaN(parsed) ? null : parsed;
}