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

debugcn 发表于 Dev

香托·西迪克（Shandi Siddiq）

我试图用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);
    }

查看单元格编号格式和值

这是日期和时间字段

香托·西迪克（Shandi Siddiq）

使用功能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;
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。